Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Identifier Columns Expand / Collapse
Author
Message
Posted Tuesday, December 9, 2008 10:15 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, November 5, 2014 12:06 AM
Points: 217, Visits: 172
Comments posted to this topic are about the item Identifier Columns
Post #616667
Posted Wednesday, December 10, 2008 7:32 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, August 16, 2013 8:28 AM
Points: 249, 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.
Post #617008
Posted Wednesday, December 10, 2008 7:54 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, September 30, 2014 7:42 AM
Points: 3,688, Visits: 72,435
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
Post #617048
Posted Wednesday, December 10, 2008 8:46 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, November 5, 2014 12:06 AM
Points: 217, Visits: 172
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
Post #617105
Posted Wednesday, December 10, 2008 9:04 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, August 16, 2013 8:28 AM
Points: 249, 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.
Post #617132
Posted Wednesday, December 10, 2008 9:12 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, September 30, 2014 7:42 AM
Points: 3,688, Visits: 72,435
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
Post #617140
Posted Wednesday, December 10, 2008 9:42 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, April 30, 2013 9:07 PM
Points: 1,529, Visits: 824
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



Post #617170
Posted Wednesday, December 10, 2008 9:49 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, September 30, 2014 7:42 AM
Points: 3,688, Visits: 72,435
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
Post #617182
Posted Wednesday, December 10, 2008 10:07 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, November 5, 2014 12:06 AM
Points: 217, Visits: 172
That is quite interesting bit of info
Post #617590
Posted Thursday, December 11, 2008 7:26 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, December 18, 2008 7:54 AM
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.
Post #617888
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse