GUID clustered indexes vs. RID

Question

GUID clustered indexes vs. RID

Viewing 3 posts - 16 through 17 (of 17 total)

You must be logged in to reply to this topic. Login to reply

WayneS SSC Guru Points: 95409 More actions · Answer 1

I just read through this thread, and what I found interesting was what was missing (or, I just failed to see it...)

The keys in a clustered index are used in all non-clustered indexes (and if there isn't a clustered index, the hidden "Row ID" column (mentioned above) is used) to provide a lookup path back to the row. This means that in addition to a GUID column, the 16 bytes it takes up in storage space is also used in all non-clustered indexes. An integer identity column only uses 4 bytes. So, using an integer uses only 1/4 the space in the table, and saves 12 bytes * # rows * # non-clustered indexes. The space savings can really add up.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Johan Bijnens SSC Guru Points: 135265 More actions · Answer 2

WayneS (10/16/2010)
I just read through this thread, and what I found interesting was what was missing (or, I just failed to see it...)
The keys in a clustered index are used in all non-clustered indexes (and if there isn't a clustered index, the hidden "Row ID" column (mentioned above) is used) to provide a lookup path back to the row. This means that in addition to a GUID column, the 16 bytes it takes up in storage space is also used in all non-clustered indexes. An integer identity column only uses 4 bytes. So, using an integer uses only 1/4 the space in the table, and saves 12 bytes * # rows * # non-clustered indexes. The space savings can really add up.

That is indeed an extra negative side effect. ( caused by the way sqlserver implemented clustering indexes )

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Rob Schripsema SSCertifiable Points: 7469 More actions · Answer 3

WayneS (10/16/2010)
I just read through this thread, and what I found interesting was what was missing (or, I just failed to see it...)
The keys in a clustered index are used in all non-clustered indexes (and if there isn't a clustered index, the hidden "Row ID" column (mentioned above) is used) to provide a lookup path back to the row. This means that in addition to a GUID column, the 16 bytes it takes up in storage space is also used in all non-clustered indexes. An integer identity column only uses 4 bytes. So, using an integer uses only 1/4 the space in the table, and saves 12 bytes * # rows * # non-clustered indexes. The space savings can really add up.

Yes, a very good point. Hadn't thought of that.

Rob Schripsema
Propack, Inc.