NOLOCK vs Clustered Index Order Part V

Just when I had given up hope to ever knowing the answer to the following posts.

Part I
Part II
Part III
Part IV

Itzik Ben-Gan comes up with this 3 part series. It was really something that puzzled me and kept me busy quite some time. You see patience is rewarded somehow :-)

Great job Itzik, you really made my day! As a reward I just ordered both parts of Inside SQL Server 2005 :-)

Foreign Keys DROP/CREATE generator

We were in the process of moving tables between filegroups and the way to do this is to drop and recreate the clustered index. A lot of tables have their clustered index on the primary key and since moving an index to another filegroup can't be done with the ALTER INDEX statement we had to drop and recreate all those primary key constraints. But wait a minute... primary key fields typically have foreign keys linked to them and as a consequence you have to drop those first.

Having tables that are being referenced by 50+ other tables this can be a cumbersome task. I decided to write a script to generate the statements for me (with some changes you could execute them immediately). Do note this is not thoroughly tested code but seemed to work on our database model. I don't know of course what kind of funky stuff you guys come up with.

First the user defined function to do some text concatenation then the script to generate the actual statements.


IF OBJECT_ID (N'dbo.GetFieldsConcat', N'FN') IS NOT NULL
DROP FUNCTION dbo.GetFieldsConcat;
GO
CREATE FUNCTION dbo.GetFieldsConcat (@objectName sysname, @foreignKeyName sysname, @isParent bit)
RETURNS varchar(max)
WITH EXECUTE AS CALLER
AS
BEGIN
DECLARE @theFields varchar(max)
SET @theFields = ''
SELECT @theFields = @theFields + '[' + CASE WHEN @isParent = 1 THEN pc.name ELSE cc.name END + '],'
FROM sys.foreign_keys fk
INNER JOIN sys.schemas fks ON fks.schema_id = fk.schema_id
INNER JOIN sys.objects oc ON fk.parent_object_id = oc.object_id
INNER JOIN sys.schemas cs ON cs.schema_id = oc.schema_id
INNER JOIN sys.objects op ON fk.referenced_object_id = op.object_id
INNER JOIN sys.schemas ps ON ps.schema_id = op.schema_id
INNER JOIN sys.foreign_key_columns pfkc ON pfkc.constraint_object_id = fk.object_id
INNER JOIN sys.columns pc ON pc.column_id = pfkc.referenced_column_id AND pc.object_id = fk.referenced_object_id
INNER JOIN sys.columns cc ON cc.column_id = pfkc.parent_column_id AND cc.object_id = fk.parent_object_id
WHERE ((@isParent = 1 AND op.name = @objectName) OR (@isParent = 0 AND oc.name = @objectName))
AND fk.name = @foreignKeyName
IF LEN(@theFields) > 0
SET @theFields = LEFT(@theFields, LEN(@theFields) - 1)
RETURN(@theFields)
END;
GO


DECLARE @TableName sysname
SET @TableName = 'tbl_stockable_type'
SELECT 'IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N''[' + fks.name + '].[' + fk.name + ']'') AND parent_object_id = OBJECT_ID(N''[' + cs.name + '].[' + oc.name + ']''))' + CHAR(13) + CHAR(10) +
'ALTER TABLE [' + cs.name + '].[' + oc.name + '] DROP CONSTRAINT [' + fk.name + ']'
FROM sys.foreign_keys fk
INNER JOIN sys.schemas fks ON fks.schema_id = fk.schema_id
INNER JOIN sys.objects oc ON fk.parent_object_id = oc.object_id
INNER JOIN sys.objects op ON fk.referenced_object_id = op.object_id
INNER JOIN sys.schemas cs ON cs.schema_id = oc.schema_id
WHERE op.name = @TableName
UNION ALL
SELECT 'ALTER TABLE [' + cs.name + '].[' + oc.name + '] WITH NOCHECK ADD CONSTRAINT [' + fk.name + '] FOREIGN KEY(' + dbo.ufn_GetFieldsConcat(oc.name, fk.name, 0) + ')' + CHAR(13) + CHAR(10)
+ 'REFERENCES [' + ps.name + '].[' + op.name + '] (' + dbo.ufn_GetFieldsConcat(op.name, fk.name, 1) + ')'
FROM sys.foreign_keys fk
INNER JOIN sys.schemas fks ON fks.schema_id = fk.schema_id
INNER JOIN sys.objects oc ON fk.parent_object_id = oc.object_id
INNER JOIN sys.schemas cs ON cs.schema_id = oc.schema_id
INNER JOIN sys.objects op ON fk.referenced_object_id = op.object_id
INNER JOIN sys.schemas ps ON ps.schema_id = op.schema_id
WHERE op.name = @TableName

SQL Server 2005 Books Online (July 2006)

Updated Books Online - Download
Updated Samples - Download

Hello world from Vista B2

Ah, finally took me the time to install the Vista B2 release. It looks really great so I hope it works as good as it looks! Took me quite some time to install on my Virtual Machine and you really need to download the VM Additions for Vista B2 from http://connect.microsoft.com in order to get it to run smoothly. Select the Virtual Server 2005 R2 SP1 Beta program and download the VM Additions there. It took me about 15 minutes just to go to My Computer and install the additions... just to show you the importance of the VM additions ;-)

Next on the list Office 2007!