SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Using GUIDs as primary keys/clustered indexes


Using GUIDs as primary keys/clustered indexes

Author
Message
sasken
sasken
SSCertifiable
SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)

Group: General Forum Members
Points: 5602 Visits: 2007
Hi All,

One of my production databases has 120 tables out of which 104 tables are using GUIDs as clustered indexes. This is a brand new database and we are still in the process of making changes. Looking at the size of the database (30MB) this is not an issue now but this is expected to grow to more than a 100 GB with in a few days time. My only concern is that use of GUIDs causes heavy fragmentation and I am thinking if we can avoid this. Per our developers requirements they cannot use identity values in place of GUIDS. In my scenario what can you think will be a best column that I can have the primary key on.

Thanks for your inputs in advance.

“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
sasken
sasken
SSCertifiable
SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)

Group: General Forum Members
Points: 5602 Visits: 2007
I will also be adding few maintenance jobs on this database that will actually purge data from this database on a daily basis and we are using Entity framework scripts for this application and those huge scripts will be doing heavy inserts and updates on some of these tables daily. So performance is what my major concern is.

“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
Suresh B.
Suresh B.
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8296 Visits: 5330
Sapen (9/4/2012)
...In my scenario what can you think will be a best column that I can have the primary key on.

Thanks for your inputs in advance.

By default primary key creates clustered index.
But you create primary key with non-clustered index and create clustered index on some other column.
Stewart "Arturius" Campbell
Stewart "Arturius" Campbell
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24809 Visits: 7501
Clustered indexes are best suited to columns most frequently used in range-based searches (e.g. MyDateColumn BETWEEN @Date1 AND @Date2).
Check all the queries run against the database to determine if there are any range-based (or exact) searches and allocate the clustered index accordingly.

____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
Ed B
Ed B
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1471 Visits: 1814
If the keys are generated by a column default, you can use NEWSEQUENTIALID.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search