Over a million developers have joined DZone.

SQL Server - Using newsequentialid() as a Function, Similar to newid()

Build fast, scale big with MongoDB Atlas, a hosted service for the leading NoSQL database. Try it now! Brought to you in partnership with MongoDB.

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…

Now it's easier than ever to get started with MongoDB, the database that allows startups and enterprises alike to rapidly build planet-scale apps. Introducing MongoDB Atlas, the official hosted service for the database on AWS. Try it now! Brought to you in partnership with MongoDB.

Topics:

Published at DZone with permission of Erik Ejlskov Jensen, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

The best of DZone straight to your inbox.

SEE AN EXAMPLE
Please provide a valid email address.

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.
Subscribe

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}