Using GUIDs as primary keys/clustered indexes

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Using GUIDs as primary keys/clustered indexes...

Using GUIDs as primary keys/clustered indexes

Rate Topic

Display Mode

Topic Options

Author

Message

Sapen

Posted Tuesday, September 04, 2012 5:06 AM

Ten Centuries

Group: General Forum Members
Last Login: Friday, May 17, 2013 7:49 AM
Points: 1,281, Visits: 1,564

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.

Post #1353815

Sapen

Posted Tuesday, September 04, 2012 5:20 AM

Ten Centuries

Group: General Forum Members
Last Login: Friday, May 17, 2013 7:49 AM
Points: 1,281, Visits: 1,564

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.

Post #1353825

Suresh B.

Posted Tuesday, September 04, 2012 5:59 AM

Ten Centuries

Group: General Forum Members
Last Login: Today @ 4:49 AM
Points: 1,075, Visits: 5,119

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.

Post #1353837

Stewart "Arturius" Campbell

Posted Tuesday, September 04, 2012 7:34 AM

Hall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 3:48 AM
Points: 3,125, Visits: 4,311

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”

Post #1353883

Ed B

Posted Wednesday, September 05, 2012 10:35 AM

SSC-Addicted

Group: General Forum Members
Last Login: Today @ 10:01 AM
Points: 494, Visits: 1,517

If the keys are generated by a column default, you can use NEWSEQUENTIALID.

Post #1354739

« Prev Topic | Next Topic »

Permissions