Posted by: camz | April 14, 2007

SQL Server Suckage


I’ve been working on a system written in .Net/C# that uses SQL Server, my database experience prior to SQL Server included Oracle, Ingress, Sybase, Postgresql, and some MySql. Each had their quirks, but I’m learning more and more of the SQL Server quirks, and at times seriously having to wonder how Microsoft gets off calling this an enterprise class product. I will fully admit that some of my complaints are with how .Net communicates with SQL Server, and these probably are not Sql Server’s fault.

Here is my short list of gripes (in no particular order):

  1. No Sequences
    Yes, I know, you can use IDENTITY columns, but they are not the same, and SQL Server places some foolish restrictions on their use. Instead of an IDENTITY column being a “normal” scalar column it is special and you can never update the column, although you can insert, but only after additional SQL directives. The IDENTITY implementation is close to a sequence, but Microsoft just didn’t implement it quite right. I do wonder why they didn’t introduce proper sequences with SQL Server 2005, they could have done this quite easily, a proper sequence could peacefully co-exist with the existing IDENTITY nonsense.
  2. Inconsistent Parsing
    I was creating query that needed to run properly against two versions of a table, one version had an additional column to simplify the query. Using the IF EXISTS statement, I wrote a query similiar to the following:
    IF EXISTS (
    SELECT NULL
    FROM sysobjects so, syscolumns sc
    WHERE so.id = sc.id
    AND so.name = 'table_name'
    AND so.xtype = 'U'
    AND sc.name = 'column_name'
    ) SELECT column_name FROM table_name

    When I ran it against a database that had the column it worked, when I ran it against one that didn’t have the column, I got an error of it complaining that column_name didn’t exist. I shouldn’t have even tried, that is the whole idea behind using the IF EXISTS… The following query:
    IF EXISTS (
    SELECT NULL
    FROM sysobjects
    AND name = 'table_name'
    AND so.xtype = 'U'
    ) SELECT * FROM table_name

    Works as expected when run against a database that does not have the table.

    So, in one case it parses the statement inside the IF EXISTS and does some checking, in another case it does not.

  3. NOCHECK Constraints
    The ability to temporarily disable constraint checking can be quite convenient, particularly while bulk loading data. What I don’t like about this is that this “WITH NOCHECK” state can actually stick around indefinitely, and is included in backups of the database. The constraint appears to be present, but is not actually being enforced.
  4. Escaping Reserved Words & Special Characters
    This is one of those things that looks reasonably useful until you actually take a couple moments to think about it. SQL Server will let you use special characters like spaces, periods, and even reserved words when defining your schema, all you need to to is enclose them in square brackets. The following is valid:
    CREATE TABLE [Table] (
    [hard.to.use.column] varchar(20),
    [also hard to use] varchar(10),
    [null] varchar(10)
    )

    There are good reasons why we have reserved words, and why spaces and such are not allowed in table or column names. While it is admirable that Microsoft provided such a convenient way to differentiate the real reserved words from the ones we want to use, it’s horribly misguided. No one should do this, ever, it’s just plain confusing, not to mention non-portable, foolish, and, oh yeah… stupid.
  5. Inconsistent context from .Net/C#
    I will admit that I do not know if this is a SQL Server thing, or a .Net/C# thing. This is related to the debate about using parameters or not (which I won’t get into), the issue is that .Net/C# does not execute these in the same way. Here is a code sample to help demonstrate this:
    public Query1( SqlConnection cnx, int id )
    {
    cmd = cnx.CreateCommand();
    cmd.CommandText = String.Format( "SELECT * FROM myTable WHERE id = {0}", id );
    SqlDataReader r = cmd.ExecuteReader();
    if( r.Read() ) { /* read result */ }
    r.Close()
    }

    public Query2( SqlConnection cnx, int id )
    {
    cmd = cnx.CreateCommand();
    cmd.CommandText = "SELECT * FROM myTable WHERE id = @id";
    cmd.Paramaters.AddWithValue( "@id", id );
    SqlDataReader r = cmd.ExecuteReader();
    if( r.Read() ) { /* read result */ }
    r.Close()
    }


    Query1 will be sent to SQL Server as a normal query. If you were to build another query in the same program it would execute in the same context. In other words, you can do a transaction by issuing the raw SQL as multiple statements using a combination of cmd.ExecuteReader() and cmd.ExecuteNonQuery(). You could also issue the sql to allow IDENTITY INSERTS, and then loop through some in-memory data and create multiple INSERT statements from that data.

    Query2 should be identical, but it is not. It executes in it’s own context as a side-effect of the fact that a stored procedure is actually used to execute the actual query. The parameters are defined and their initial value set as arguments to the stored procedure, with the actual parameterized query being passed un-altered from .Net to the stored procedure as one of the arguments.

    While it seems a bit excessive to invoke a stored procedure to deal with the parameters, this is not what bothers me. What bothers me is that a parameter-less query and a parameterized query are not handled in an identical manner by .Net/C#, especially with the nice side effect of the difference in execution context of the two. Code that produces valid SQL without parameters can break without explanation simply by implementing parameters.

    Another issue related to this is that .Net/C# appears to always parse the sql before deciding how to execute it. In an attempt to reduce potential errors where you might build a query with undefined parameters, it actually makes it impossible to create a stored procedure or user defined function from C#. Since these things will use parameters in T-SQL, they conflict with how C# handles parameters, the following code snippet will produce a .Net exception:
    cmd.CommandText = "CREATE PROCEDURE foo @id int AS SELECT * FROM myTable WHERE id = @id";
    cmd.ExecuteNonQuery();

    The exception will be that you have not defined @id with a cmd.Parameters.AddWithValue() statement. Of course, you didn’t do that because you are not trying to use a parameter, you are issuing DDL SQL to create a stored procedure that will use one. I have yet to find out how to tell .Net/C# to stop doing this so that this SQL can be executed. I means you can’t programatically create a database schema from within a C# application.

  6. Column Aliases not Available to ORDER BY
    This one should be a bug, the fact that if you define an alias for a column you should be able to use that alias in the ORDER BY clause. You can’t. This is particularly annoying for a calculated column or one that transforms the data using CASE or by concatenating text results. You wind up having to duplicate the calculation in the ORDER BY clause. Silly.
  7. Default Collation Sequence is Case-Insensitive
    When you install SQL Server (2000, MSDE, 2005, 2005 EE) the default collation sequence is case-insensitive. I’d never seen this before when working with other databases. I only discovered it by accident when looking at a client’s data and discovered that data being inserted into a column with an FK constraint was allowing lower-case when I knew the referenced table was all upper case. I was shocked. This might make some sense for some other alphabets, but it certainly produced some unexpected results for me.

    You can actually create each table and column with a different collation sequence. This might be flexible, but I suspect it breaks several rules of design and should be avoided.

So there you go. Some of the reasons why I dislike SQL Server. I’m sure I will think of some more… I’ll add some of them to the list as I think of them.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Categories

%d bloggers like this: