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…