SQL Server CLR unordered auto-increment (supporting concurrency) Performance Comparison

CLR function script

---------------------------------------------------------------------------------

Using system;
Using system. Data;
Using system. Data. sqlclient;
Using system. Data. sqltypes;
Using Microsoft. sqlserver. server;
Using system. collections;

/// <Summary>
/// Custom CLR function used to generate a sequence
/// </Summary>

Public partial class test
{

Public static hashtable ht = new hashtable (); // create a hashtable instance

[Microsoft. sqlserver. server. sqlfunction]
Public static sqlint64 getrownum (sqlguid guid1)
{
Try
{
If (! Ht. Contains (guid1) // determines whether a hash table contains a specific key. The return value is true or false.
{
HT [guid1] = 0;
}
Int64 I = convert. toint64 (HT [guid1]. tostring ());
I ++;
HT [guid1] = I. tostring ();
Return I;

}
Catch
{
Return-1;
}
}

/// <Summary>
/// Delete the hash table Value
/// </Summary>
/// <Param name = "guid1"> </param>
[Microsoft. sqlserver. server. sqlprocedure]
Public static void clearguid (sqlguid guid1)
{
Try
{
Ht. Remove (guid1 );
Return;
}
Catch
{

}
}
};

 

Bytes ------------------------------------------------------------------------------------------------------------------------------

Start CLR

Exec sp_configure 'show advanced options', '1 ';
Go
Reconfigure with override
Go
Exec sp_configure 'clr enabled', '1'
Go
Reconfigure with override

Alter database Test Set trustworthy on

-----------------------------------------------------------------------
Drop function getrownum
Drop proc clearguid

Drop Assembly testss

Create Assembly testss from 'C: \ Documents and Settings \ Administrator \ Desktop \ test \ sqlserverproject1 \ sqlserverproject1 \ bin \ debug \ sqlserverproject1.dll 'with permission_set = unsafe;

 

Go

Create a function
Create Function DBO. getrownum
(
@ Guid uniqueidentifier
)
Returns bigint
As external name
Testss. Test. getrownum

 

Create proc clearguid
(
@ Guid uniqueidentifier
)
As external name
Testss. Test. clearguid

 

Bytes ----------------------------------------------------------------------------------------------------

 

Comparison between getrownum and identity

 

Declare @ guid uniqueidentifier
Set @ guid = newid ()

Select DBO. getrownum (@ guid)
From list

 

-- Clear the hash table

Exec clearguid @ guid

 

Select Identity (INT, 1, 1) as ID into # T from List

Select ID from # T
Drop table # T

943761 data volume

 

Time in a hurry, after multiple tests, preliminary results

1. getrownum (5591) consumes nearly 6278 milliseconds less time than identity (700)

2. the CPU usage of getrownum (2171) is much higher than that of identity (922 ).

3. getrownum (2352) reads half of identity (4828 ).

4. getrownum is not written, and identity is written to 1519.

 

Preliminary conclusion

1. getrownum consumes more CPU for Calculation

2. getrownum consumes less disk space than identity, and is not written

3. If the table already has auto-increment (discontinuous), identity cannot be used. getrownum can

4. getrownum returns data instantly and the identity must be paused.

 

......................