Thursday, January 2, 2014

SQL Server - using newsequentialid() as a function, similar to newid()

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:

Unknown said...

Instead of creating a table variable, why not simply P/Invoke the UuidCreateSequential function?

http://pinvoke.net/default.aspx/rpcrt4/UuidCreateSequential.html

ErikEJ said...

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