This blog post shows how you can use newsequentialid() as a function in scripts etc., not only as a column default value.
In many scenarios, unique identifiers are used a clustered, primary keys in database tables for various reasons. This blog post will not discuss the pros and cons of doing this.
Usage of GUID/uniqueidentifer and it’s implication on fragmentation, and how newsequentialid() can help improve this, has been documented in various places
A limitation of newsequentialid() is that it can only be used as a default value for a column, not as a function, in for example ad-hoc INSERT scripts. By taking advantage of SQLCLR, this situation can be changed.
using System;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Data.SqlClient;
public class SqlFunctions
{
[Microsoft.SqlServer.Server.SqlFunction(DataAccess = DataAccessKind.Read)]
public static SqlGuid newsequentialid()
{
using (SqlConnection connection = new SqlConnection("context connection=true"))
{
connection.Open();
var sql = @"
DECLARE @NewSequentialId AS TABLE (Id UNIQUEIDENTIFIER DEFAULT(NEWSEQUENTIALID()))
INSERT INTO @NewSequentialId DEFAULT VALUES;
SELECT Id FROM @NewSequentialId;";
using (SqlCommand cmd = new SqlCommand(sql, connection))
{
object idRet = cmd.ExecuteScalar();
return new SqlGuid((Guid)idRet);
}
}
}
}
The code above implements a SQLCLR function named newsequentialid(), To build this code, simply create a C# class library, include the code, and build. The code is inspired by this thread on SQLServerCentral: http://www.sqlservercentral.com/Forums/Topic1006731-2815-1.aspx
To make deploying the function even simpler, the script outlined below can add the assembly code to your database and register the function:
EXEC sp_configure @configname=clr_enabled, @configvalue=1;
GO
RECONFIGURE;
GO
IF NOT EXISTS (SELECT * FROM sys.assemblies asms WHERE asms.name = N'SqlFunctions' and is_user_defined = 1)
CREATE ASSEMBLY [SqlFunctions]
FROM 0x4D5A… (rest omitted, use full script)
WITH PERMISSION_SET = SAFE
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[newsequentialid]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
BEGIN
execute dbo.sp_executesql @statement = N'
CREATE FUNCTION [dbo].[newsequentialid]()
RETURNS uniqueidentifier
AS EXTERNAL NAME [SqlFunctions].[SqlFunctions].[newsequentialid];
'
END
GO
You can download the full script from here: http://sdrv.ms/1hhYDY1
Testing with 50.000 inserts, like in the CodeProject article reveals the following figures:
Newsequentialid as DEFAULT:
Run time: 1:18, pages: 1725, fragmentation: 0,7 %
Newsequentialid as function in INSERT statement, no default value on table:
Run time: 2:03, pages: 1725, fragementation: 0,7 %
To use the function as a replacement for newid(), simply use dbo.newsequentialid() instead. But please also consider using another column as clustering key in your table…
2 comments:
Instead of creating a table variable, why not simply P/Invoke the UuidCreateSequential function?
http://pinvoke.net/default.aspx/rpcrt4/UuidCreateSequential.html
Richard: Excellent question, but that would make the assembly UNSAFE, which is not recommended and not allowed by many DBAs http://technet.microsoft.com/en-us/library/ms189524.aspx
Post a Comment