GETDATE() uniqueness when used on INSERT

Question

Recently I was reading this blog post: Link

which contains this section:

Picking a Clustered Index

There are a couple of strategies for picking your clustered indexes; one of the easiest and best is to add another column of data type datetime and use that column for your clustered index. Here is what you need to do:

1. Add the column as data type datetime

2. I usually call it Date

3. Set the Default Value to GetDate().

4. Make it non-null.

5. Create your clustered index on it before you insert data into you.

My question is would this ever create two identical values for the Date? Does this answer change if parallelism is in use? (Assume value never specified, always comes from GetDate())

I believe I'm correct in assuming it wouldn't matter due to the behind-the-scenes uniqueifier being added, right? But I'm interested anyway.

I'm asking from a SQL2008R2 perspective but would be interested if the answer differs for any version of SQL Server from 7.0 up.

Answer 1

GETDATE() is not guaranteed to be unique, no. Especially if it is a datetime, where milliseconds are rounded up or down, and of course when not all data is coming from the same user, you are almost guaranteed to have collisions.

Of course, a clustered index does not need to be unique, since SQL Server will make it so if it isn't (but only when it needs to). If you need to identify a specific row yourself (as opposed to just the uniquifier for SQL Server's internal usage), and there are no other candidate key columns (this is possible with things like event logging tables), you could add a non-clustered primary key that is, say, an IDENTITY column. Or if you really want web scale - and care more about insert performance than storage or any subsequent use of the data - you could use a uniqueidentifier column populated with NEWID().

---

Example

Let's take a look at an example, and see the differences.

USE tempdb;
GO

-- rely on uniqifier

CREATE TABLE dbo.Test1
(
  g DATETIME
);
CREATE CLUSTERED INDEX x ON dbo.Test1(g);


-- use an IDENTITY column

CREATE TABLE dbo.Test2
(
  i INT IDENTITY(1,1) PRIMARY KEY NONCLUSTERED,
  g DATETIME
);
CREATE CLUSTERED INDEX x ON dbo.Test2(g);


-- use a GUID

CREATE TABLE dbo.Test3
(
  n UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID()
    PRIMARY KEY NONCLUSTERED,
  g DATETIME
);
CREATE CLUSTERED INDEX x ON dbo.Test3(g);
GO

---

Insert speed

I populated all three tables with about 500,000 rows, using the following script:

SET NOCOUNT ON;
GO
SELECT SYSDATETIME();
GO
INSERT dbo.Test1(g) 
  SELECT DATEADD(SECOND, ABS([object_id])/1000, GETDATE()) 
  FROM sys.all_columns;
GO 100

SELECT SYSDATETIME();
GO
INSERT dbo.Test2(g) 
  SELECT DATEADD(SECOND, ABS([object_id])/1000, 
  GETDATE()) FROM sys.all_columns;
GO 100

SELECT SYSDATETIME();
GO
INSERT dbo.Test3(g) 
  SELECT DATEADD(SECOND, ABS([object_id])/1000, 
  GETDATE()) FROM sys.all_columns;
GO 100

SELECT SYSDATETIME();

Results:

Uniquifier:    2.26 seconds
IDENTITY:      3.89 seconds
GUID:          5.06 seconds

---

Scan Speed

SELECT SYSDATETIME();
DBCC DROPCLEANBUFFERS;
SELECT * FROM dbo.Test1;
SELECT SYSDATETIME();
DBCC DROPCLEANBUFFERS;
SELECT * FROM dbo.Test2;
SELECT SYSDATETIME();
DBCC DROPCLEANBUFFERS;
SELECT * FROM dbo.Test3;
SELECT SYSDATETIME();

Results:

Uniquifier:  3.89 seconds
IDENTITY:    4.21 seconds
GUID:        4.85 seconds

---

Space used

Looking at simple results from sp_spaceused:

EXEC sp_spaceused N'dbo.Test1';
EXEC sp_spaceused N'dbo.Test2';
EXEC sp_spaceused N'dbo.Test3';

Results:

                      Rows      Reserved    Data      Index size
Test1 - Uniquifier    513300    14024 KB    13880 KB       96 KB
Test2 - IDENTITY      513300    29072 KB    15792 KB    13200 KB
Test3 - GUID          513300    49936 KB    21960 KB    27928 KB

The uniquifier takes up less space than the IDENTITY column (and obviously both take less than the GUID), since it is only needed for collisions (and probably other optimizations I'm not aware of, such as compression).

We can also look at the index pages for the clustered index on the datetime column (index id = 1) and the non-clustered primary key (index id = 2):

DBCC TRACEON(3604,-1);

-- Uniquifier
DBCC IND('tempdb', 'dbo.Test1', 1); -- 1,747 index pages
-- no second index for this table

-- IDENTITY
DBCC IND('tempdb', 'dbo.Test2', 1); -- 1,987 index pages
DBCC IND('tempdb', 'dbo.Test2', 2); -- 1,637 index pages

-- GUID
DBCC IND('tempdb', 'dbo.Test3', 1); -- 2,764 index pages
DBCC IND('tempdb', 'dbo.Test3', 2); -- 3,472 index pages

---

Page/Row Contents

And finally, we can take a look at a specific page to see what is stored in a row. I simply took the first row from each of the clustered index DBCC IND results from above (your page id values will almost certainly be different):

DBCC PAGE('tempdb',1, 153, 1);
DBCC PAGE('tempdb',1, 199, 1);
DBCC PAGE('tempdb',1, 217, 1);

Uniquifier - pay particular attention to length/record size:

Slot 0, Offset 0x60, Length 15, DumpStyle BYTE
----------------------------^^

Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP     
Record Size = 15
--------------^^

Memory Dump @0x000000000F7EA060

0000000000000000:   10000c00 0333ba00 fba20000 020000††††.....3º.û¢.....  

Slot 1, Offset 0x6f, Length 23, DumpStyle BYTE
----------------------------^^

Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP VARIABLE_COLUMNS
Record Size = 23              
--------------^^       
Memory Dump @0x000000000F7EA06F

0000000000000000:   30000c00 0333ba00 fba20000 02000001 †0....3º.û¢...... 
0000000000000010:   00170001 000000††††††††††††††††††††††.......          

IDENTITY seems to have 4 extra bytes in the clustered index:

Slot 0, Offset 0x60, Length 19, DumpStyle BYTE
----------------------------^^

Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP     
Record Size = 19
--------------^^

Memory Dump @0x0000000011DAA060

0000000000000000:   10001000 a735ba00 fba20000 03020000 †....§5º.û¢...... 
0000000000000010:   030000†††††††††††††††††††††††††††††††...              

Slot 1, Offset 0x73, Length 27, DumpStyle BYTE
----------------------------^^

Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP VARIABLE_COLUMNS
Record Size = 27              
--------------^^       
Memory Dump @0x0000000011DAA073

0000000000000000:   30001000 a735ba00 fba20000 04020000 †0...§5º.û¢...... 
0000000000000010:   03000001 001b0001 000000†††††††††††††...........      

GUID has an extra 16 bytes in the clustered index:

Slot 0, Offset 0x60, Length 31, DumpStyle BYTE
----------------------------^^

Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP     
Record Size = 31
--------------^^

Memory Dump @0x0000000011DAA060

0000000000000000:   10001c00 393aba00 fba20000 f3233e73 †....9:º.û¢..ó#>s 
0000000000000010:   a36e114b b1229a80 a5cb090a 030000††††£n.K±".¥Ë  ....  

Slot 1, Offset 0x7f, Length 39, DumpStyle BYTE
----------------------------^^

Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP VARIABLE_COLUMNS
Record Size = 39
--------------^^                     
Memory Dump @0x0000000011DAA07F

0000000000000000:   30001c00 393aba00 fba20000 c7bb2544 †0...9:º.û¢..Ç»%D 
0000000000000010:   4ad3574f a2c2029f e4abc9d7 03000001 †JÓWO¢Â.ä«É×.... 
0000000000000020:   00270001 000000††††††††††††††††††††††.'.....          

---

Conclusion

Seems to me, by all accounts, you are better off letting the uniquifier do its own thing (assuming you don't expect to be able to differentiate between two rows with the exact same date/time value). The only time you can get in trouble with this, if any single value is duplicated 2,147,483,648 times, at which point you will overflow the uniquifier range of integer.