• Tom.Thomson (3/8/2011)


    The right answer of course is "each of them can (but only one at a time)" so of the options provided 3 is closest to correct. 1 (the "right" answer) would only be really correct if the question was "how many of them can be rowguidcol columns at the same time".

    I have to agree to disagree with this statement.

    "enabled" is the key word in this question and makes perfect english as the last word in the question. This makes it the main describer for the question.

    You can "enable" ROWGUIDCOL on any UID column in a table, but it can only be "enabled" on one row.

    When something is done and the switch is on it is enabled.

    The concept I learned from the question is that enabling ROWGUID on any column automaticly disables it on all other columns when using Table Designer.

    When being done from a script the DDL you have to use to make another column the ROWGUIDCOL really brings home why the question makes perfect sense.

    Create a table called Table_1 give it three UID columns and set the first or second one to ROWGUID.

    CREATE TABLE [dbo].[Table_1](

    [uid01] [uniqueidentifier] ROWGUIDCOL NULL,

    [uid02] [uniqueidentifier] NULL,

    [uid03] [uniqueidentifier] NULL

    ) ON [PRIMARY]

    ALTER TABLE [dbo].[Table_1] ADD CONSTRAINT [DF_Table_1_uid01] DEFAULT (newid()) FOR [uid01]

    ALTER TABLE [dbo].[Table_1] ADD CONSTRAINT [DF_Table_1_uid02] DEFAULT (newid()) FOR [uid02]

    ALTER TABLE [dbo].[Table_1] ADD CONSTRAINT [DF_Table_1_uid03] DEFAULT (newid()) FOR [uid03]

    GO

    Now try to Alter the table and set any other column to ROWGUID.

    ALTER TABLE dbo.Table_1 ALTER COLUMN uid02

    ADD ROWGUIDCOL

    GO

    It can't be done because ROWGUIDCOL is already enabled on another COLUMN and it can only be enabled on ONE column. This looks really wierd when using the querry designer becuase it will let you select YES to the option on any column, but it is only enabled on the last one you pick.

    Try it out.

    BEGIN TRANSACTION

    ALTER TABLE dbo.Table_1 ALTER COLUMN uid03

    DROP ROWGUIDCOL

    GO

    ALTER TABLE dbo.Table_1 ALTER COLUMN uid02

    ADD ROWGUIDCOL

    GO

    COMMIT

    So lets read the qustion again:

    How many UID columns in a table can have ROWGUIDCOL enabled?

    You can enable it on any UID column in a table, but it can only be enabled on one column.

    One last thing to point out about ROWGUIDCOL property is that differant from an Identity Column, the Object explorer gives you no clues about which column has ROWGUIDCOL enabled.