Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Identifier Columns


Identifier Columns

Author
Message
Marius Els
Marius Els
SSC Veteran
SSC Veteran (218 reputation)SSC Veteran (218 reputation)SSC Veteran (218 reputation)SSC Veteran (218 reputation)SSC Veteran (218 reputation)SSC Veteran (218 reputation)SSC Veteran (218 reputation)SSC Veteran (218 reputation)

Group: General Forum Members
Points: 218 Visits: 179
Comments posted to this topic are about the item Identifier Columns
DiverKas
DiverKas
SSC Veteran
SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)

Group: General Forum Members
Points: 253 Visits: 460
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.
mtassin
mtassin
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4097 Visits: 72512
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.



--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link
For tips on how to post your problems
Marius Els
Marius Els
SSC Veteran
SSC Veteran (218 reputation)SSC Veteran (218 reputation)SSC Veteran (218 reputation)SSC Veteran (218 reputation)SSC Veteran (218 reputation)SSC Veteran (218 reputation)SSC Veteran (218 reputation)SSC Veteran (218 reputation)

Group: General Forum Members
Points: 218 Visits: 179
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...BigGrin
DiverKas
DiverKas
SSC Veteran
SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)

Group: General Forum Members
Points: 253 Visits: 460
mtassin (12/10/2008)
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.


Your right, if your using TSQL to create the CREATE TABLE statement. However, it is incorrect if you use Enterprise Manager.
mtassin
mtassin
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4097 Visits: 72512
DiverKas (12/10/2008)
[quote]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.



--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link
For tips on how to post your problems
James Rochez
James Rochez
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1529 Visits: 824
There is an exception (isn't there alwaysSmile).

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



mtassin
mtassin
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4097 Visits: 72512
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?



--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link
For tips on how to post your problems
Marius Els
Marius Els
SSC Veteran
SSC Veteran (218 reputation)SSC Veteran (218 reputation)SSC Veteran (218 reputation)SSC Veteran (218 reputation)SSC Veteran (218 reputation)SSC Veteran (218 reputation)SSC Veteran (218 reputation)SSC Veteran (218 reputation)

Group: General Forum Members
Points: 218 Visits: 179
That is quite interesting bit of info
kwest
kwest
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 2
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search