If you have a non clustered index on unique identifier you might have identified a serious performance problem.

The SQL server creates for each non clustered index a separate table. By inserting a unique identifier with “NEWUID() stored procedure” each new data row has to be inserted somewhere within the table which needs a lot of performance and creates fragmentation of the table. In this case you need a function to be sure the insertion process will be made at the end of the table.

In real life you should prevent creating a non clustered index on an unique identifier but on the SQL Server side you have the option to use the “NEWSEQUENTIALID() stored procedure”. It generates an sequencial unique id based of the Windows restarting time. With this procedure you might to reach almost the same inserting performance like a non clustered index on a INT IDENTITY column. Regarding to the sequencial ascendending numbers it has the advantage that the SQL Server inserts the new rows at the end of the table.

In my example I could reduce the performance costs over 60% (see image TableIXIDReferenceTime vs. TableIXIDReferenceSequencialTime)!

Below you find the SQL-Query-Scripts which I used for the performance tests.

Creating Tables:

Create Table Test.Table_Heap
(
  ID int IDENTITY(1,1),
  FirstName nvarchar(100),
  LastName nvarchar(100),
  Comments nvarchar(400),
  IDReference uniqueidentifier );
GO

Create Table Test.Table_IX_ID
(
  ID int IDENTITY(1,1),
  FirstName nvarchar(100),
  LastName nvarchar(100),
  Comments nvarchar(400),
  IDReference uniqueidentifier
);
GO
CREATE CLUSTERED INDEX IX_ID
  ON Test.Table_IX_ID(ID);
 GO

CREATE Table Test.Table_IX_IDReference
(
  ID int IDENTITY(1,1),
  FirstName nvarchar(100),
  LastName nvarchar(100),
  Comments nvarchar(400),
  IDReference uniqueidentifier
);
GO
CREATE CLUSTERED INDEX IX_IDReference
  ON Test.Table_IX_IDReference(IDReference);
GO

CREATE Table Test.Table_IX_IDReferenceSequential
(
  ID int IDENTITY(1,1),
  FirstName nvarchar(100),
  LastName nvarchar(100),
  Comments nvarchar(400),
  IDReference uniqueidentifier DEFAULT NEWSEQUENTIALID()
);
GO
CREATE CLUSTERED INDEX IX_IDReferenceSequential
  ON Test.Table_IX_IDReferenceSequential(IDReference);
GO

Inserting Data:

DECLARE @Counter INT;
DECLARE @StartTime DATETIME2;
DECLARE @StopTableHeapTime DATETIME2;
DECLARE @StopTableIXIDTime DATETIME2;
DECLARE @StopTableIXIDReferenceTime DATETIME2;
DECLARE @StopTableIXIDReferenceSequentialTime DATETIME2;

SET NOCOUNT ON;

SET @StartTime = SYSDATETIME();
PRINT 'Loading Table_Heap...';
SET  @Counter = 0; WHILE (@COUNTER < 300000)
BEGIN
  INSERT INTO Test.Table_Heap
  ("FirstName", "LastName", "Comments", "IDReference")
  VALUES   ('A','B','C',NEWID())
    SET @Counter +=1;
END
PRINT 'Table_Heap loaded.';
SET @StopTableHeapTime = SYSDATETIME();

PRINT 'Loading Table_IX_ID...';
SET  @Counter = 0; WHILE (@COUNTER < 300000)
BEGIN
  INSERT INTO Test.Table_IX_ID
   ("FirstName", "LastName", "Comments", "IDReference")
  VALUES   ('A','B','C',NEWID())
    SET @Counter +=1;
END
PRINT 'Table_IX_ID loaded.';

SET @StopTableIXIDTime = SYSDATETIME();
PRINT 'Loading Table_IX_IDReference...';
SET  @Counter = 0; WHILE (@COUNTER < 300000)
BEGIN
  INSERT INTO Test.Table_IX_IDReference
   ("FirstName", "LastName", "Comments", "IDReference")
  VALUES
   ('A','B','C',NEWID())
    SET @Counter +=1;
END
PRINT 'Table_IX_IDReference loaded.';

SET @StopTableIXIDReferenceTime = SYSDATETIME();
PRINT 'Loading Table_IX_IDReferenceSequential...';
SET  @Counter = 0;
WHILE (@COUNTER < 300000)
BEGIN
  INSERT INTO Test.Table_IX_IDReferenceSequential
   ("FirstName", "LastName", "Comments")
  VALUES
   ('A','B','C')
    SET @Counter +=1;
END
PRINT 'Table_IX_IDReferenceSequential loaded.';

SET @StopTableIXIDReferenceSequentialTime = SYSDATETIME();

SELECT DATEDIFF(second,@StartTime, @StopTableHeapTime) AS "TableHeapTime",
  DATEDIFF(second,@StopTableHeapTime, @StopTableIXIDTime) AS "TableIXIDTime",
  DATEDIFF(second,@StopTableIXIDTime, @StopTableIXIDReferenceTime) AS "TableIXIDReferenceTime",
  DATEDIFF(second,@StopTableIXIDReferenceTime, @StopTableIXIDReferenceSequentialTime) AS TableIXIDReferenceSequencialTime;