I have been talking to different programmers about the primary field of my tables. I am being pointed in a 1000 different directions, if thats possible. Anyway I use a lot of spreadsheets at work and I want to develop an application to store my data in a database and be able to manipulate and generate reports from that data. Should I use an integer identity seed or a GUID? I'm trying to get this right the first time, is that possible?
I would go with the Integer identity field - it's shorter & more easily readable by humans, takes up less space in the database...
You'll get more than one suggestion from here as well I'm afraid. Is there anything that makes each row of data unique in your spreadsheets? If so, then use that where applicable, providing it isnt too wide a column - something like a customer reference or a purchase order number etc etc would be fine but trying to index a CHAR(2000) will be problematic. Where there isnt a natural key then you may well need to create a column for the PK in a table but the data type plays the biggest part on inserts as generating a GUID takes more time than an INT and so on and so forth. Do you have many inserts? If not then it wont really matter and you may not know the difference from a performance point of view. As with so much in a DBA's world - it depends.
Duplicate of http://www.sqlservercentral.com/Forums/Topic936368-1292-1.aspx
Maybe you are getting confused because you are asking the wrong questions. First determine what keys make sense from a business perspective. If you are thinking purely in terms of IDENTITY or GUIDs then you probably aren't considering the real needs of the data model. Ask yourself what facts you are trying to represent in the database and what identifiers the consumers of the data will expect to see. Then you'll be able to determine what the business keys should be. Adding a surrogate key (if any) should always be a secondary consideration - and it's not totally clear from your question if that's what you are really asking about.
Also understand that indexes and keys are two entirely different concerns that are best kept separate. Keys are about the logical correctness of the data whereas indexes are tool for performance optimisation.
If your choices are between creating a GUID or identity, and you have no real need for the benefits of a GUID (which I'm willing to bet you haven't if this is replicating a spreadsheet based data source) - then the integer identity is by far your best option.
As others have said - it very much depends on your circumstances - but you're sure to run into issues sooner with GUIDs than with integers
