Find the right art of your primary key
Posted by Frank Wenner
Vor einiger Zeit wurde ich in meinem Job mit der Frage vonfrontiert was denn der ideale Weg für einen Primärschlüssel
für eine Tabelle auf Microsft® SQL Server ist. Es gibt hier die unterschiedlichsten Ansätze.
Ich habe hier mal die häufigsten Ansätze vergleichen.
- manuelle vergebene Nummern
- Identity Werte
- GUID als Default
- GUID manuell vergeben
Fazit
Das einzig Ware und Schnelle ist (fast schon logisch) das Microsoft® SQL Server Feature der IDENTITY für Columns zu verwenden.
Skript für das Anlegen einer eigenen Tablle für Nummern
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[_MyManuelNumbers]'))
DROP TABLE [dbo].[_MyManuelNumbers]
CREATE TABLE [dbo].[_MyManuelNumbers] (
[ID] [int] IDENTITY(1,1) NOT NULL,
[NumberName] [varchar](50) NOT NULL,
[NumberValue] [int] NOT NULL,
CONSTRAINT [PK__MyManuelNumbers] PRIMARY KEY CLUSTERED ( [ID] ASC )
)
INSERT INTO [_MyManuelNumbers] (NumberName, NumberValue) VALUES ('MyManuelNumber', 1)
Skript für das Anlegen einer Prozedure für das Hochzählen der eigenen Nummer
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[_spGetNextManuelIdentityValue]'))
DROP PROCEDURE [dbo].[_spGetNextManuelIdentityValue]
CREATE PROCEDURE [dbo].[_spGetNextManuelIdentityValue] @NumberName VARCHAR(50), @iReturn INT OUT AS
BEGIN
DECLARE @OldValue INT; SET @OldValue = 0
DECLARE @NewValue INT; SET @NewValue = 0
SET NOCOUNT ON
WHILE 1=1
BEGIN
SET @OldValue = (SELECT NumberValue FROM _MyManuelNumbers WHERE NumberName = @NumberName)
IF @@ROWCOUNT = 0 -- no matching row found
BREAK
IF @OldValue = 0
BREAK
SET @NewValue = @OldValue + 1
UPDATE _MyManuelNumbers SET NumberValue = @NewValue +
WHERE NumberName = @NumberName AND NumberValue = @OldValue
IF @@ROWCOUNT = 1
BREAK
END
IF @NewValue = 0
SET @iReturn = -999999999
ELSE
SET @iReturn = @NewValue
SET NOCOUNT OFF
END
Tabllen für das Einfügen der Testdaten erzeugen
/*********************************************************************************
* table - int manuell add identity values
*********************************************************************************/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[_InsertTest_ManuelIntIdentity]') AND type in (N'U'))
DROP TABLE [dbo].[_InsertTest_ManuelIntIdentity]
CREATE TABLE [dbo].[_InsertTest_ManuelIntIdentity] (
[ID] [int] NOT NULL,
[Int1] [int] NULL,
[Int2] [int] NULL,
[Int3] [int] NULL,
[Char1] [varchar](50) NULL,
[Char2] [varchar](50) NULL,
[Char3] [varchar](50) NULL,
CONSTRAINT [PK__InsertTest_ManuelIntIdentity] PRIMARY KEY CLUSTERED ([ID] ASC)
)
CREATE NONCLUSTERED INDEX [IX__InsertTest_ManuelIntIdentity1]
ON [dbo].[_InsertTest_ManuelIntIdentity] ([Int2] ASC)
CREATE NONCLUSTERED INDEX [IX__InsertTest_ManuelIntIdentity2]
ON [dbo].[_InsertTest_ManuelIntIdentity] ([Char2] ASC)
/*********************************************************************************
* table - int IDENTITY
*********************************************************************************/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[_InsertTest_IntIdentity]'))
DROP TABLE [dbo].[_InsertTest_IntIdentity]
CREATE TABLE [dbo].[_InsertTest_IntIdentity] (
[ID] [int] IDENTITY(1,1) NOT NULL,
[Int1] [int] NULL,
[Int2] [int] NULL,
[Int3] [int] NULL,
[Char1] [varchar](50) NULL,
[Char2] [varchar](50) NULL,
[Char3] [varchar](50) NULL,
CONSTRAINT [PK__InsertTest_IntIdentity] PRIMARY KEY CLUSTERED ([ID] ASC)
)
CREATE NONCLUSTERED INDEX [IX__InsertTest_IntIdentity1]
ON [dbo].[_InsertTest_IntIdentity] ([Int2] ASC)
CREATE NONCLUSTERED INDEX [IX__InsertTest_IntIdentity2]
ON [dbo].[_InsertTest_IntIdentity] ([Char2] ASC)
/*********************************************************************************
* table - int IDENTITY + GUID
*********************************************************************************/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[_InsertTest_IntGuidIdentity]'))
DROP TABLE [dbo].[_InsertTest_IntGuidIdentity]
CREATE TABLE [dbo].[_InsertTest_IntGuidIdentity] (
[ID] [int] IDENTITY(1,1) NOT NULL,
[RowGuid] [char](36) NOT NULL,
[Int1] [int] NULL,
[Int2] [int] NULL,
[Int3] [int] NULL,
[Char1] [varchar](50) NULL,
[Char2] [varchar](50) NULL,
[Char3] [varchar](50) NULL,
CONSTRAINT [PK__InsertTest_IntGuidIdentity] PRIMARY KEY CLUSTERED ([ID] ASC)
)
CREATE NONCLUSTERED INDEX [IX__InsertTest_IntGuidIdentityRowGuid]
ON [dbo].[_InsertTest_IntGuidIdentity] ([RowGuid] ASC)
CREATE NONCLUSTERED INDEX [IX__InsertTest_IntGuidIdentity1]
ON [dbo].[_InsertTest_IntGuidIdentity] ([Int2] ASC)
CREATE NONCLUSTERED INDEX [IX__InsertTest_IntGuidIdentity2]
ON [dbo].[_InsertTest_IntGuidIdentity] ([Char2] ASC)
/*********************************************************************************
* table - uniqueidentifier IDENTITY
*********************************************************************************/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[_InsertTest_GuidIdentity]'))
DROP TABLE [dbo].[_InsertTest_GuidIdentity]
CREATE TABLE [dbo].[_InsertTest_GuidIdentity] (
[ID] [uniqueidentifier] ROWGUIDCOL NOT NULL,
[Int1] [int] NULL,
[Int2] [int] NULL,
[Int3] [int] NULL,
[Char1] [varchar](50) NULL,
[Char2] [varchar](50) NULL,
[Char3] [varchar](50) NULL,
CONSTRAINT [PK__InsertTest_GuidIdentity] PRIMARY KEY CLUSTERED ([ID] ASC)
)
CREATE NONCLUSTERED INDEX [IX__InsertTest_GuidIdentity1]
ON [dbo].[_InsertTest_GuidIdentity] ([Int2] ASC)
CREATE NONCLUSTERED INDEX [IX__InsertTest_GuidIdentity2]
ON [dbo].[_InsertTest_GuidIdentity] ([Char2] ASC)
/*********************************************************************************
* table - char IDENTITY and guid value
*********************************************************************************/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[_InsertTest_GuidCharIdentity]'))
DROP TABLE [dbo].[_InsertTest_GuidCharIdentity]
CREATE TABLE [dbo].[_InsertTest_GuidCharIdentity] (
[ID] [char](36) NOT NULL,
[Int1] [int] NULL,
[Int2] [int] NULL,
[Int3] [int] NULL,
[Char1] [varchar](50) NULL,
[Char2] [varchar](50) NULL,
[Char3] [varchar](50) NULL,
CONSTRAINT [PK__InsertTest_GuidCharIdentity] PRIMARY KEY CLUSTERED ([ID] ASC)
)
CREATE NONCLUSTERED INDEX [IX__InsertTest_GuidCharIdentity1]
ON [dbo].[_InsertTest_GuidCharIdentity] ([Int2] ASC)
CREATE NONCLUSTERED INDEX [IX__InsertTest_GuidCharIdentity2]
ON [dbo].[_InsertTest_GuidCharIdentity] ([Char2] ASC)
Testdaten in die Tabllen Einfügen
SET NOCOUNT ON
DECLARE @iCountMax INT; SET @iCountMax = 1000
DECLARE @iRunMax INT; SET @iRunMax = 100
DECLARE @iRun INT; SET @iRun = 0
DECLARE @iIntIdentityTime INT; SET @iIntIdentityTime = 0
DECLARE @iManuelIntIdentityTime INT; SET @iManuelIntIdentityTime = 0
DECLARE @iGuidIdentityTime INT; SET @iGuidIdentityTime = 0
DECLARE @iGuidCharIdentityTime INT; SET @iGuidCharIdentityTime = 0
DECLARE @iIntGuidIdentityTime INT; SET @iIntGuidIdentityTime = 0
DECLARE @iCount INT; SET @iCount = 0
DECLARE @StartTime DATETIME; SET @StartTime = GETDATE()
DECLARE @iManuelIdentityValue INT; SET @iManuelIdentityValue = 0
DECLARE @iBuffer INT; SET @iBuffer = 0
DECLARE @RowGuid char(36); SET @RowGuid = ''
WHILE @iRun < @iRunMax
BEGIN
SET @iRun = @iRun + 1
-- table 1
SET @iCount = 0; SET @StartTime = GETDATE()
BEGIN TRANSACTION
WHILE @iCount < @iCountMax
BEGIN
SET @iCount = @iCount + 1
EXECUTE _spGetNextManuelIdentityValue 'MyManuelNumber', @iManuelIdentityValue OUT
INSERT INTO _InsertTest_ManuelIntIdentity (ID, Int1, Int2, Int3, Char1, Char2, Char3)
VALUES (@iManuelIdentityValue, @iCount, @iCount + 10, @iCount + 100, NEWID(), NEWID(), NEWID())
END
COMMIT TRANSACTION
SET @iManuelIntIdentityTime = @iManuelIntIdentityTime + DATEDIFF(ms,@StartTime,GETDATE())
-- table 2
SET @iCount = 0; SET @StartTime = GETDATE()
BEGIN TRANSACTION
WHILE @iCount < @iCountMax
BEGIN
SET @iCount = @iCount + 1
INSERT INTO _InsertTest_IntIdentity (Int1, Int2, Int3, Char1, Char2, Char3)
VALUES (@iCount, @iCount + 10, @iCount + 100, NEWID(), NEWID(), NEWID())
SET @iBuffer = SCOPE_IDENTITY()
END
COMMIT TRANSACTION
SET @iIntIdentityTime = @iIntIdentityTime + DATEDIFF(ms,@StartTime,GETDATE())
-- table 3
SET @iCount = 0; SET @StartTime = GETDATE()
BEGIN TRANSACTION
WHILE @iCount < @iCountMax
BEGIN
SET @iCount = @iCount + 1
SET @RowGuid = NEWID()
INSERT INTO _InsertTest_IntGuidIdentity (RowGuid, Int1, Int2, Int3, Char1, Char2, Char3)
VALUES (@RowGuid, @iCount, @iCount + 10, @iCount + 100, NEWID(), NEWID(), NEWID())
SET @iBuffer = (SELECT ID FROM _InsertTest_IntGuidIdentity WHERE RowGuid = @RowGuid)
END
COMMIT TRANSACTION
SET @iIntGuidIdentityTime = @iIntGuidIdentityTime + DATEDIFF(ms,@StartTime,GETDATE())
-- table 4
SET @iCount = 0; SET @StartTime = GETDATE()
BEGIN TRANSACTION
WHILE @iCount < @iCountMax
BEGIN
SET @iCount = @iCount + 1
SET @RowGuid = NEWID()
INSERT INTO _InsertTest_GuidIdentity (ID, Int1, Int2, Int3, Char1, Char2, Char3)
VALUES (@RowGuid, @iCount, @iCount + 10, @iCount + 100, NEWID(), NEWID(), NEWID())
END
COMMIT TRANSACTION
SET @iGuidIdentityTime = @iGuidIdentityTime + DATEDIFF(ms,@StartTime,GETDATE())
-- table 5
SET @iCount = 0; SET @StartTime = GETDATE()
BEGIN TRANSACTION
WHILE @iCount < @iCountMax
BEGIN
SET @iCount = @iCount + 1
SET @RowGuid = NEWID()
INSERT INTO _InsertTest_GuidCharIdentity (ID, Int1, Int2, Int3, Char1, Char2, Char3)
VALUES (@RowGuid, @iCount, @iCount + 10, @iCount + 100, NEWID(), NEWID(), NEWID())
END
COMMIT TRANSACTION
SET @iGuidCharIdentityTime = @iGuidCharIdentityTime + DATEDIFF(ms,@StartTime,GETDATE())
END
SET NOCOUNT OFF
SET @iCount = @iCount * @iRun
PRINT 'Manuel Integer Identity - time for [' + CAST(@iCount AS VARCHAR) + ']
rows is [' + CAST(@iManuelIntIdentityTime AS VARCHAR) + '] milliseconds'
PRINT 'Integer Identity (SCOPE_IDENTITY) - time for [' + CAST(@iCount AS VARCHAR) + ']
rows is [' + CAST(@iIntIdentityTime AS VARCHAR) + '] milliseconds'
PRINT 'Integer Identity (SELECT RowGuid) - time for [' + CAST(@iCount AS VARCHAR) + ']
rows is [' + CAST(@iIntGuidIdentityTime AS VARCHAR) + '] milliseconds'
PRINT 'Guid Identity - time for [' + CAST(@iCount AS VARCHAR) + ']
rows is [' + CAST(@iGuidIdentityTime AS VARCHAR) + '] milliseconds'
PRINT 'Guid Char Identity - time for [' + CAST(@iCount AS VARCHAR) + ']
rows is [' + CAST(@iGuidCharIdentityTime AS VARCHAR) + '] milliseconds'
Auswertung der Indexfragmentierung
SELECT a.database_id
,a.object_id
,a.index_id
,b.name
,a.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(), object_id('_InsertTest_GuidCharIdentity'),
NULL, NULL, NULL) AS a
JOIN sys.indexes AS b
ON a.object_id = b.object_id
AND a.index_id = b.index_id
WHERE a.database_id = db_id() and Name like 'PK_%'
UNION
SELECT a.database_id
,a.object_id
,a.index_id
,b.name
,a.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(), object_id('_InsertTest_GuidIdentity'),
NULL, NULL, NULL) AS a
JOIN sys.indexes AS b
ON a.object_id = b.object_id
AND a.index_id = b.index_id
WHERE a.database_id = db_id() and Name like 'PK_%'
UNION
SELECT a.database_id
,a.object_id
,a.index_id
,b.name
,a.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(), object_id('_InsertTest_IntIdentity'),
NULL, NULL, NULL) AS a
JOIN sys.indexes AS b
ON a.object_id = b.object_id
AND a.index_id = b.index_id
WHERE a.database_id = db_id() and Name like 'PK_%'
UNION
SELECT a.database_id
,a.object_id
,a.index_id
,b.name
,a.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(), object_id('_InsertTest_IntGuidIdentity'),
NULL, NULL, NULL) AS a
JOIN sys.indexes AS b
ON a.object_id = b.object_id
AND a.index_id = b.index_id
WHERE a.database_id = db_id() and Name like 'PK_%'
UNION
SELECT a.database_id
,a.object_id
,a.index_id
,b.name
,a.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(), object_id('_InsertTest_ManuelIntIdentity'),
NULL, NULL, NULL) AS a
JOIN sys.indexes AS b
ON a.object_id = b.object_id
AND a.index_id = b.index_id
WHERE a.database_id = db_id() and Name like 'PK_%'