SQL Server Performance Forum – Threads Archive

Mulitple Client Single Database Design Help

Hi I writing an asp.net 2.0 application that will have multiple clients in a single database. A client will NEVER see the data of any other client and a client may have multiple users who can log in and see the clients data. My design was to have a client table whose primary key (ClientID) is a GUID of data type uniqueidentifier. Now I was going to use this ClientID in pretty much all tables within the database as this will make it easier to extract data for a single client. My proboblem arises when I want to extract the data from one database and import it into another database – perhaps as they have decided to upgrade versions of the application I am developing. Now all the other keys in the database will be your usual identity columns and duplicates will happen if I’m transfering data between databases. I was going to therefore use the ClientID as a composite key (is this a unique index in sql servrer?) but it seemed to me that this would impact greatly on performance. Should I have GUID’s for all primary keys in all tables? This would probably make maintenance/ad hock querries a bit of a nightmare? Any help/thoughts on the matter would be greatly appreciated as I’m much more the programmer than the DBA, so the subtleties of sql server performance appear somewhat as a black art to me. cheers.

Nightmare, indeed. Avoid GUIDs – they’re not guaranteed to be unique anyway. When exporting to an outside database, then the question must be: does the other db have a different list of client codes? If so, then you could just add a column to the other db’s client table where the "remote key" is stored. This allows you to replace the remote key on the data that you’re importing with the local key. If the client list on the other db is completely controlled by the source data, then the only issue is inserting identity values (look at SET IDENTITY_INSERT in BOL).

GUIDs as PRIMARY KEYs are fine when you think of them as an convenient way to generate a unique value, but… at 16 bytes per value they are wide. 4 times as wide as an INT column (typically used with the IDENTITY property). So, you widen each row, the wider the row the less rows fit on a single data page, thus you have more pages which force SQL Server to do more reads to fulfill a request. Also, they are stored as FOREIGN KEYs in related tables, so you widen these tables as well. They are rather obscure and barely intuitive to a human. So, as you’ve mentioned, ad hoc queries will get difficult. Unless you’re using some kind of sequential GUIDs generator, due to their random nature, they are not generated in an ever increasing pattern. So, you will have to deal with page splits when your PRIMARY KEY is also the clustered index (which is the default, unless you specify otherwise). There is only one valid reason AFAIK to have GUIDs as PRIMARY KEYs. That is when you’re replicating that table. So, I guess bottom line is, aviod GUIDs as PRIMARY KEYs, if you can. Apart from this is a composite index not necessarily also a unique index. Composite only means that such an index is build from 2 or more columns, which might or might not be unique. Adriaan, <blockquote id="quote">quote:<hr height="1" noshade id="quote"> they’re not guaranteed to be unique anyway. <hr height="1" noshade id="quote"></blockquote id="quote"> I think, I don’t understand you here. Can you explain a bit? [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] – Frank Kalis Microsoft SQL Server MVP http://www.insidesql.de</a> Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a> Ich unterstuetze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>)

Thanks for the replies. If GUID’s are the root of all evil – so to speak – then I might have to bite the bullet and have another database with one table that just stores all client data with an identity column as the primary key instead of a GUID. This will ensure at least a unique client id across my organisation at least. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />]

quote:Adriaan, quote:
——————————————————————————– they’re not guaranteed to be unique anyway. ——————————————————————————–
I think, I don’t understand you here. Can you explain a bit?

I got it wrong again, didn’t I? I seem to remember GUIDs are not guaranteed to be globally unique – as in real-world-wide. Perhaps only within the SQL Server instance perhaps?

To quote BOL <blockquote id="quote">quote:<hr height="1" noshade id="quote"> A GUID is a unique binary number; no other computer in the world will generate a duplicate of that GUID value. The main use for a GUID is for assigning an identifier that must be unique in a network that has many computers at many sites. <hr height="1" noshade id="quote"></blockquote id="quote"> Personally I find that very hard to believe. Just like thinking that the universe is endless. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] Anyway, even if such a value isn’t unique for whatever reason, the PRIMARY KEY constraint would be violated and an INSERT or UPDATE would fail. – Frank Kalis Microsoft SQL Server MVP http://www.insidesql.de</a> Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a> Ich unterstuetze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>)

Somewhere I read GUID is not always unique. Dont remember where I read Madhivanan Failing to plan is Planning to fail

Agreed. I would like to see the formula Microsoft has created to ensure uniqueness world wide. The only thing I can think of is using the date/time to the millisecond of the key generation and some other random variables such as server mac address to make the key unique. Even then, there are chances for duplicates they would just not be very common. John

]]>