December 9, 2008 at 10:15 pm
Comments posted to this topic are about the item Identifier Columns
December 10, 2008 at 7:32 am
I selected True and was told I got it wrong. However, if I choose to make a column a GUID column, choose identity and select it to be RowGuidCol, it creates the newid function for me. SO in my mind, it is automatic. I would have to go and remove the newid function call from the default setting.
I guess one could argue if you created a table in TSQL, it would not be default. But the question didnt say that.
December 10, 2008 at 7:54 am
From BOL
The Database Engine does not automatically generate values for the column. To insert a globally unique value, create a DEFAULT definition on the column that uses the NEWID function to generate a globally unique value.
December 10, 2008 at 8:46 am
DiverKas (12/10/2008)
I selected True and was told I got it wrong. However, if I choose to make a column a GUID column, choose identity and select it to be RowGuidCol, it creates the newid function for me. SO in my mind, it is automatic. I would have to go and remove the newid function call from the default setting.I guess one could argue if you created a table in TSQL, it would not be default. But the question didnt say that.
Hi DiverKas
I see where you are coming from, I haven't used management studio to create tables for a while and I did the test as you mention and saw that when you specify the RowGuid it automatically specifies newid() as the default value or binding.
So I'm not sure if that is a managent studio feature to ease the creation of SQL tables, taking into account that according to the database engine it doesn't automatically generate values for the column?
This is my first question and was expecting different views aired, and this helps to think about formatting question wording for next time...:D
December 10, 2008 at 9:04 am
mtassin (12/10/2008)
From BOLThe Database Engine does not automatically generate values for the column. To insert a globally unique value, create a DEFAULT definition on the column that uses the NEWID function to generate a globally unique value.
Your right, if your using TSQL to create the CREATE TABLE statement. However, it is incorrect if you use Enterprise Manager.
December 10, 2008 at 9:12 am
DiverKas (12/10/2008)
mtassin (12/10/2008)
Your right, if your using TSQL to create the CREATE TABLE statement. However, it is incorrect if you use Enterprise Manager.
No I'm right. SSMS may create those for you, but the question was about the database engine. MS SQL 2k5 does not autocreate the default values, SSMS adds extra SQL to the CREATE TABLE statement to ease your burden.
December 10, 2008 at 9:42 am
There is an exception (isn't there always:)).
However, the ROWGUIDCOL attribute for a column has been overloaded to indicate that a default value should be generated. This behavior is specific to SQL Server 2000 Windows CE Edition.
This quote taken from http://support.microsoft.com/kb/274189
December 10, 2008 at 9:49 am
I also noticed this is not forward carrying and is only for one version of SQL 2000 CE...
This workaround applies only to the current release of SQL Server CE and is not forward-compatible with SQL Server. On SQL Server 2000, the ROWGUIDCOL property does not automatically generate values for new rows that are inserted into the table. This workaround may not apply in future releases. Also, a table can only have one ROWGUIDCOL column
So apparently even MS realized this was bad?
December 10, 2008 at 10:07 pm
That is quite interesting bit of info
December 11, 2008 at 7:26 am
I have used GUIDs (uniqueidentifier) as PK columns, and use them for the clustered index. Thus, newid() is not a good default for the column, since it isn't sequential, and new rows will fragment my clustered index. Instead we use newsequentialid(), but it is not guaranteed to be globally unique.
Fortunately, this database is not yet distributed, so I'm not worried about creating conflicting PK values in separated instances, but I'm curious to know what the ideal solution is.
December 11, 2008 at 10:49 pm
I find working with GUIDs very cumbersome and in the application that I inherented at my company, I haven't seen any advantage of using them, normal "int with identity(1, 1) not null" would have fit the bill perfectly.
I found this article explaining a bit on the purpose and origin of the GUID:
One has to consider the purpose and functionality of the application and the network environment it will be deployed on to determine either using GUIDs or Ints as primary keys?
(I'm probably not mentioning half of the things to look at in making such a decision...)
So I think it's going to be a case by case evaluation to find the best solution.
December 18, 2008 at 6:04 am
I got this question right. But, it had a POORLY WORDED answer. You do NOT ask a question and then pose a "true/false" answer. You make a STATEMENT and then pose a "true/false" confirmation. Goodness, folks. If you are asking a question, please use "yes/no". Thank you.
August 23, 2011 at 8:00 am
I have stopped caring about getting poorly worded version ambigous questions wrong.
If anyone complain about it some users get all Hatefull like they own the place.
What a waste.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply