locked
Guid Primary /Foreign Key dilemma SQL Server RRS feed

  • Question

  • Hi guys,

    I am faced with the dilemma of changing my primary keys from int identities to Guid. I'll put my problem straight up. It's a typical Retail management app, with POS and back office functionality. Has about 100 tables. The database synchronizes with other databases and receives/ sends new data.

    Most tables don't have frequent inserts, updates or select statements executing on them. However, some do have frequent inserts and selects on them, eg. products and orders tables.

    Some tables have upto 4 foreign keys in them. If i changed my primary keys from 'int' to 'Guid', would there be a performance issue when inserting or querying data from tables that have many foreign keys. I know people have said that indexes will be fragmented and 16 bytes is an issue.

    Space wouldn't be an issue in my case and apparently index fragmentation can also be taken care of using 'NEWSEQUENTIALID()' function. Can someone tell me, from there experience, if Guid will be problematic in tables with many foreign keys.

    I'll be much appreciative of your thoughts on it...
    Tuesday, June 8, 2010 9:11 AM

Answers

All replies

  • Check this and evaluate yourself

     

    http://databases.aspfaq.com/database/what-should-i-choose-for-my-primary-key.html



    SQLEnthusiast
    http://sqlsimplified.com/
    Please click the Mark as Answer button if a post solves your problem!
    • Marked as answer by Xience Thursday, June 10, 2010 7:12 AM
    Tuesday, June 8, 2010 10:13 AM
  • Is the problem that you want uniqueness across all four server?

    ADD a second COLUMN to each of the TABLEs specifying server id, and change the PRIMARY/FOREIGN KEYs to composite keys. With such a small amount of servers, TINYINT could be used.

    Add a server TABLE for the COLUMN to FK to. Use DEFAULT on each of the servers to set the COLUMN correctly.

    The only problem with this is that the schemata will not be exactly the same across all the servers because of the DEFAULTs. Is that a problem?

    Tuesday, June 8, 2010 11:20 AM
    Answerer
  • hey i will recommend you to have int as primary key, add int column in each table and have a running primary key, it will be clustered index and will boost performance
    Everything about SQL Server | Experience inside SQL Server -Mohammad Nizamuddin
    Wednesday, June 9, 2010 8:21 PM
  • To make it the same across all servers, add a user @@SERVERNAME instead.

    If that is too long, CREATE a Server TABLE, assigning an id (TINYINT) to each server. The COLUMN could then use the Id for each server. Perhaps CREATEing a FUNCTION to RETURN the Id: DEFAULT(dbo.Server_Id(@@SERVERNAME))

    Thursday, June 10, 2010 11:10 AM
    Answerer