SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Marius Els / Identifier Columns / Latest Posts

RE: Identifier Columns

SanDroid — Tue, 23 Aug 2011 08:00:41 GMT

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.

RE: Identifier Columns

jdshabat — Thu, 18 Dec 2008 06:04:59 GMT

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.

RE: Identifier Columns

Marius Els — Thu, 11 Dec 2008 22:49:35 GMT

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:[url=http://www.devx.com/dbzone/Article/10167][/url]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.

RE: Identifier Columns

kwest — Thu, 11 Dec 2008 07:26:00 GMT

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 [b][i]not[/i][/b] 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.

RE: Identifier Columns

Marius Els — Wed, 10 Dec 2008 22:07:45 GMT

That is quite interesting bit of info

RE: Identifier Columns

mtassin — Wed, 10 Dec 2008 09:49:26 GMT

I also noticed this is not forward carrying and is only for one version of SQL 2000 CE...[quote]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[/quote]So apparently even MS realized this was bad?

RE: Identifier Columns

James Rochez — Wed, 10 Dec 2008 09:42:08 GMT

There is an exception (isn't there always:)).[quote]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.[/quote]This quote taken from [url]http://support.microsoft.com/kb/274189[/url]

RE: Identifier Columns

mtassin — Wed, 10 Dec 2008 09:12:17 GMT

[quote][b]DiverKas (12/10/2008)[/b][hr][quote][b]mtassin (12/10/2008)[/b][hr]Your right, if your using TSQL to create the CREATE TABLE statement. However, it is incorrect if you use Enterprise Manager.[/quote]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.

RE: Identifier Columns

DiverKas — Wed, 10 Dec 2008 09:04:09 GMT

[quote][b]mtassin (12/10/2008)[/b][hr]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.[/quote]Your right, if your using TSQL to create the CREATE TABLE statement. However, it is incorrect if you use Enterprise Manager.

RE: Identifier Columns

Marius Els — Wed, 10 Dec 2008 08:46:01 GMT

[quote][b]DiverKas (12/10/2008)[/b][hr]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.[/quote]Hi DiverKasI 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

RE: Identifier Columns

mtassin — Wed, 10 Dec 2008 07:54:11 GMT

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.

RE: Identifier Columns

DiverKas — Wed, 10 Dec 2008 07:32:23 GMT

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.

Identifier Columns

Marius Els — Tue, 09 Dec 2008 22:15:08 GMT

Comments posted to this topic are about the item [B]Identifier Columns[/B]