August 9, 2011 at 4:54 am
Hi All,
I'm having 40 tables in my database and each table is having uniqueidentifier datatype column as primary key. recently i came to know that using uniqueidentifiers as primary key will increases the page fragmentation and clustered indexing issues.
the reason why i used uniqueidentifiers was, when we need to insert one unique value into 2 tables at the same time we can make sure the same values by creating a GUID in .NET frontend application and then push the saem unique values into 2 tables.
can anyone please let me know the disadvantages of using uniqueidentifiers and how to overcome them in sql server 2008?
Thanks.
August 9, 2011 at 5:17 am
http://msdn.microsoft.com/en-us/library/ms190215.aspx
You will find disadvantages at the bottom of article...
August 9, 2011 at 6:10 am
You can't overcome all the disadvantages. The primary problem is not using this datatype as the primary key. The primary problem is using this data type as the clustered index. This is where the fragmentation can become an issue. To reduce, but no eliminate, the fragmentation, if you can, use NEWSEQUENTIALID(). That will order your uniqeidentifier values, reducing the amount of fragmentation caused by the random nature of the things. Otherwise, you're stuck dealing with the other part of uniqueidentifier, it's size. They're fairly large. Which means fewer key values per page, which means more pages on every seek & scan, reducing efficiency.
However, all that said, you may need to use these things, and properly managed, they don't destroy performance, just make it less.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 9, 2011 at 10:49 pm
Hi,
Thanks for the reply..
Can you please let me know how to overcome this, i want to know from your practises because you are SQL DBA..
Also you suggested me to use NEWSEQUENTIALID(), How can i use this? Is it a good practise to have both int type and Uniqueidentifier(ROW GUID) in the same table?
Please help me out with a sample schema as i'm very much interested to learn and apply new things in SQL Server..
Thanks again.
August 10, 2011 at 1:58 am
If you need the guid generated outside of SQL Server then you will have to create a sequential guid yourself. In your case this is because you need to insert an identical guid into 2 tables at the same time, but it could be for other reasons too. An option might be to have a master table which uses default newsequentialid() to create a sequential guid, and then use this guid id, or a reference to the autoincremented int id of the master table in your other tables. Here is a link to generating sequential guids in c# : http://developmenttips.blogspot.com/2008/03/generate-sequential-guids-for-sql.html
August 10, 2011 at 2:49 am
August 10, 2011 at 3:16 am
maruthipuligandla (8/9/2011)
...the reason why i used uniqueidentifiers was, when we need to insert one unique value into 2 tables at the same time we can make sure the same values by creating a GUID in .NET frontend application and then push the saem unique values into 2 tables.
...
It's hardly good reason for using GUID's as PK. How your application works with the database? Do you use stored procedures? nhyberate? Direct sql queries?
If you use the stored procs or nhybernate then there is no issue in "making sure the same value can be pushed into 2 tables". With direct queries it's possible as well, but it's highly unlikely you have many of them as it would constitute quite bad application design.
So, take th e previous given advices, and, if you can change design, use IDENTITY for PK's.
Nowadays, I would only see two-way-replication is the only good enough reason to use GUID datatype for PK.
However, you are bound to hear different opinions on this subject, as there are many people how genuinely love GUID's 😀
August 10, 2011 at 6:31 am
maruthipuligandla (8/9/2011)
Hi,Thanks for the reply..
Can you please let me know how to overcome this, i want to know from your practises because you are SQL DBA..
Also you suggested me to use NEWSEQUENTIALID(), How can i use this? Is it a good practise to have both int type and Uniqueidentifier(ROW GUID) in the same table?
Please help me out with a sample schema as i'm very much interested to learn and apply new things in SQL Server..
Thanks again.
I don't understand what's the overcome. If you have to let the .NET code generate primary keys, then GUIDs are the way to go.
On a forum like this, I'm only going to post a few lines of explanation, not get into a full design session for you for free.
I've suggested mechanisms for getting around the issue. Take the time to examine them & learn about them and then apply them to your situation. Check the marked answer here on StackOverflow. It shows how to use .NET to create the equivalent of NEWSEQUENTIALID.
The thing is, you don't need to do this. You can structure your code such that you pass sets of data to SQL Server and then use IDENTITY values on the tables. Don't confuse the structures in .NET and the structures in SQL Server. Yes, it is easier on the code side if they're identical. But it's harder on the database side and you really need to take both sides into account to make a good app.
Also, something that everyone forgets when using UNIQUEIDENTIFER and IDENTITY. Just because those values uniquely mark a row doesn't mean that from a business stand point that row is uniquely marked. With either of these in place, I can insert the same address into a database one million times because, after all, it's unique because of the IDENTITY value. You also have to have a business key identified so that you don't put duplicate information into the system. This is called a natural key.
In short, this is a huge topic.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 10, 2011 at 11:02 am
CELKO (8/10/2011)
...I have written tens of thousands of words on this in my books
...
Writing thousands words has nothing to do with being right. There are a lot of fat books with crap in it.
In the specific example of US states, yes it is a simple and natural to use state code which is unique as PK.
However it's just one case out of many.
What about personal data? Is any naturally universal and world standard unique person identifier?
Do you have some unique number printed on your head top in time of birth and its uniqueness controled by UN? Yeah, DNA is unique, will you advise to use it as PK in all databases?
What about some transactions which can be naturally duplicated?
Or even just simple orders?
What are natural keys there?
Real life rules are:
It's fine to use natural keys and it's fine to use artficial keys (GUIDs) as it's all depend on the case. Sometimes one more appropriate than other.
And the last thing. Instead the J.C. books - read The Bible. It is claimed that it contains answers to all questions, and you can find free copy of it in almost every hotel (except, of cause, ones in non-christian countries). Until... You wish to support J.C. lavish lifestyle 😀
August 10, 2011 at 12:01 pm
CELKO (8/10/2011)
Please read any book on RDBMS. ANY BOOK! A key is by definition a subset of attributes that are unique in every instance of an entity of some particular kind. That means that UNIQUEIDENTIFIERs cannot ever be keys in a properly designed RDBMS....
"cannot ever" - Total bullsheet!
Do you want example where UNIQUEIDENTIFIER is a natural key?
Table in the database which stores the Classes!
Tell me now what is the more naturally unique key in this case?
:hehe::hehe::hehe::hehe::hehe:
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply