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.
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;
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