CE's Blog

I could have named this “most annoying limitations” as well:

SQL Server 2008

 
No automatic cascading updates of foreign keys with multiple references.

The only workaround for this common scenario is to use triggers for the second column. Ouch. This even applies if the foreign keys pointing to the same primary key are not in the same table, but in different tables which have FK-relationships.
The following will not work, even when we specify ON UPDATE SET NULL for the second USER_ID foreign key.

CREATE TABLE USERS ( ID INT PRIMARY KEY IDENTITY( 0, 1 ), SURNAME INT )
CREATE TABLE ACTIONS ( 
    ID INT PRIMARY KEY IDENTITY( 0, 1 ), 
    CREATING_USER_ID INT FOREIGN KEY REFERENCES USERS( ID ) ON UPDATE CASCADE, 
    EXECUTING_USER_ID INT FOREIGN KEY REFERENCES USERS( ID ) ON UPDATE CASCADE,
    VALUE1 INT )

Fails with error: “Introducing FOREIGN KEY constraint ‘XXX’ on table ‘ACTIONS’ may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.”. The only thing permitted is ON UPDATE NO ACTION.

Using Table Valued Parameters With Any Type

TVPs offer big benefits when sending mass data from a client application to a SQL Server. However they are restricted to be used only in conjunction with custom types. It is not possible to use TVPs without prior declaration of a custom type – which necessitates additional changes to the database schema and permissions for every usage of a TVP.
Supported example:

CREATE TYPE dbo.MyType AS TABLE( MyColumn INT ); 
GRANT EXEC ON TYPE::dbo.MyType TO MyRole;

SqlParameter UrlParam = cmd.Parameters.AddWithValue( "@MyTVP", records );
UrlParam.SqlDbType = SqlDbType.Structured;
UrlParam.TypeName = "dbo.MyType";

More flexible solution, but not supported:

SqlParameter UrlParam = cmd.Parameters.AddWithValue( "@MyTVP", records );
UrlParam.SqlDbType = SqlDbType.Structured;
UrlParam.TypeName = "TABLE( MyColumn INT )";

Declaring Variables With Block Scope

It is as if you are forced to work with global variables as in the good old times of programming. The following looks weird, but will actually compile and run, although variable declaration and initialization are always skipped:

CREATE PROCEDURE spTEST AS
IF 0 = 1
BEGIN
    DECLARE @v INT
    SET @v = 0
END
SELECT @v

CREATE OR REPLACE / CREATE OR ALTER

No create or replace / create or alter for user defined objects. Something that makes a developer temporarily switch from ALTER PROCEDURE to CREATE PROCEDURE and back every morning after the dev database has been wiped clean.

There is a workaround, however it is a bad one:

IF OBJECT_ID( "database.schema.mytable" ) IS NOT NULL
    DROP TABLE mytable
CREATE TABLE mytable ...

In this case we do not need to modify our code depending on if the object exists or not. But what if the object exists and there are already some permissions assigned to it? Go figure.

SQL Server 2005

 
Capturing non-inserted Columns

When doing a mass insert using INSERT … SELECT, it is not possible to capture columns from the source or destination table which are not part of the insert. Is is especially useful when inserting into a table with an IDENTITY PRIMARY KEY. SQL Server 2008 improves this by introducing the MERGE statement. For a workaround, see my post on this subject.

Variable Declaration and Assignment

Only available from SQL Server 2008.

DECLARE @v INT = 0;

Instead of:

DECLARE @v INT
SET @v = 0

SQL Server 2000

 
Builtin Paging in Result Sets

Has to be done the hard way. Resolved in SQL Server 2005 by introduction of the ROW_NUMBER() OVER (…) expression:

WITH CTE AS ( SELECT *, ROW_NUMBER() OVER ( ORDER BY id ASC ) AS ROW FROM syscolumns )
SELECT * FROM CTE WHERE ROW BETWEEN 13 AND 26

This is a workaround for SQL Server 2000, assuming that row numbers start with 1:

CREATE TABLE #TMP( ID1 INT PRIMARY KEY IDENTITY( 1, 1 ), ID2 INT )
INSERT INTO #TMP ( ID2 ) SELECT id FROM sysobjects ORDER BY crdate
SELECT o.* FROM sysobjects AS o JOIN #TMP AS t ON o.id = t.ID2 WHERE t.ID1 BETWEEN 3 AND 9
DROP TABLE #TMP