Over a million developers have joined DZone.

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

DZone's Guide to

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

· Database Zone ·
Free Resource

Built by the engineers behind Netezza and the technology behind Amazon Redshift, AnzoGraph is a native, Massively Parallel Processing (MPP) distributed Graph OLAP (GOLAP) database that executes queries more than 100x faster than other vendors.  

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"))
            var sql = @"
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;

IF NOT EXISTS (SELECT * FROM sys.assemblies asms WHERE asms.name = N'SqlFunctions' and is_user_defined = 1)
FROM 0x4D5A… (rest omitted, use full script)

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'))
execute dbo.sp_executesql @statement = N'
 CREATE FUNCTION [dbo].[newsequentialid]()
 RETURNS uniqueidentifier
 AS EXTERNAL NAME [SqlFunctions].[SqlFunctions].[newsequentialid];

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…

Download AnzoGraph now and find out for yourself why it is acknowledged as the most complete all-in-one data warehouse for BI style and graph analytics.  


Published at DZone with permission of

Opinions expressed by DZone contributors are their own.

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

{{ parent.tldr }}

{{ parent.urlSource.name }}