Why is using NEWID () using more space than NEWSEQUENTIALID ()?

Question

I am doing a little research, and I came across an anomaly that I cannot explain (and I could not find anything on Google). Consider the following SQL:

CREATE TABLE MyGuid ( ID UNIQUEIDENTIFIER PRIMARY KEY DEFAULT NEWID() ) GO CREATE TABLE MyGuidSeq ( ID UNIQUEIDENTIFIER PRIMARY KEY DEFAULT NEWSEQUENTIALID() ) GO DECLARE @i INT, @noRecords INT SET @noRecords = 1000000 -- MyGuid SET @i = 1 WHILE (@i <= @noRecords) BEGIN INSERT INTO MyGuid DEFAULT VALUES SET @i = @i + 1 END -- MyGuidSeq SET @i = 1 WHILE (@i <= @noRecords) BEGIN INSERT INTO MyGuidSeq DEFAULT VALUES SET @i = @i + 1 END exec sp_spaceused 'MyGuid', true exec sp_spaceused 'MyGuidSeq', true 

Results:

 Table name No. Rows Reserved Space Actual space Index Size Unused Space MyGuid 1,000,000 34,760 KB 34,552 KB 160 KB 48 KB MyGuidSeq 1,000,000 24,968 KB 24,768 KB 176 KB 24 KB 

Question

Can anyone explain why the reserved / actual space is significantly less when using NEWSEQUENTIALID () over NEWID ()?

In response to the answer

I checked the following test to check the answer I gave Luaan below:

 CREATE TABLE MyGuid ( ID UNIQUEIDENTIFIER DEFAULT NEWID() ) GO CREATE TABLE MyGuidSeq ( ID UNIQUEIDENTIFIER DEFAULT NEWSEQUENTIALID() ) GO DECLARE @i INT, @noRecords INT SET @noRecords = 1000000 -- MyGuid SET @i = 1 WHILE (@i <= @noRecords) BEGIN INSERT INTO MyGuid DEFAULT VALUES SET @i = @i + 1 END -- MyGuidSeq SET @i = 1 WHILE (@i <= @noRecords) BEGIN INSERT INTO MyGuidSeq DEFAULT VALUES SET @i = @i + 1 END exec sp_spaceused 'MyGuid', true exec sp_spaceused 'MyGuidSeq', true 

The difference is that I deleted the primary key (this means the table is now a heap table). Now this results in exactly the same sizes for both tables. This proves that the table is physically organized by its cluster index.

 name rows reserved data index_size unused MyGuid 1000000 25992 KB 25976 KB 8 KB 8 KB MyGuidSeq 1000000 25992 KB 25976 KB 8 KB 8 KB 

Answer 1

This is due to separation. Basically, newId() will create a GUID in random order, which means that you are constantly pasting in the middle of the table. Sequential identifiers, on the other hand, will always be added to the end of the table, which is much simpler.

If you want to know more, look at some search call content. A good start would be the official MSDN page on MS SQL Paging - http://technet.microsoft.com/en-us/library/ms190969(v=sql.105).aspx

You should also understand that strings are essentially organized by identifier in physical files storing database data. A file without spaces between identifiers (for example, when using identification columns and without deletion) may take up less space to store the same amount of data.

I expect that full database compression will significantly reduce the amount of fragmentation space lost in the MyGuid table, while it will be very small for the size of MyGuidSeq.

If you can use sequential GUIDs, do it - they greatly improve the performance of INSERT, and in addition, indexes can also be less fragmented and smaller overall.

You do not show the debug time outputs, but I expect that they are significantly different (although this can be very much compensated by the memory available for the database), it does not need to be changed data files immediately, if you want to know more about it, look at that- then in transaction logs).