What is is_rowguidcol 2

[grnzbra]

In sys.columns, what does a 1 in the is_rowguidcol column signify? I thought it was the key field, but it appears not. If not, what in sys.columns indicates that a field is the primary key for the table

 

[gmmastros]

There is nothing in sys.columns that indicates that a field is the primary key. Unfortunately, it's a bit more complicated than that because a table could have a composite primary key where multiple columns in the table make up the primary key.

To get the primary key columns in a table...

Select  schema_Name(schema_id) As SchemaName, 
        object_name(indexes.object_id) As TableName, 
        Columns.name As ColumnName,
        index_columns.Key_ordinal
From    sys.indexes 
        Inner Join sys.index_columns
           On indexes.object_id = index_columns.object_id
           And indexes.index_id = index_columns.index_id
        Inner Join sys.columns
           On index_columns.object_id = columns.object_id
           and index_columns.column_id = columns.column_id
        Inner Join sys.all_objects
           On indexes.object_id = all_objects.object_id
Where   indexes.is_primary_key = 1
Order By schema_name(schema_id), 
        object_name(indexes.object_id), 
        index_columns.key_ordinal

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

Oh yeah.... row guid columns are used internally by microsoft to support replication and file streams.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Olaf Doschke]

In short: You find primary keys in sys.indexes via is_primary_key.

It's at least good practice, if not best practice and rule to most DBAs to have a single field as primary key column and thus the primary key index would only have that column, but indeed the primary key is rather defined by an index neither allowing NULL nor duplicates and has no constraint on the number of columns involved.

RowGuid:
Whenever you add a column and pick uniqueidentifier, the column properties Rowguid attrtibute becomes enabled and you can pick Yes. The moment you do that, it puts (newid()) as default value/binding for this column. But that's not all. Define a second similar column and set it to be RowGuid in the same way, then look back at the previous column. Its reset to No. Only one uniqueidentifier column can be the RowGuid uniqueidentifier column of a table.

The property of being a RowGuid marks the column used in replication to identify a row. Only one uniqueidentifier column is pickable as RowGuid and doing that is your promise to never change its value after its set at insert and keep it as replication identifier. It is usable as primary key for that reason, too, but the primary key can also be some sequence you define per database server, for example when it needs to be a small int value usable as UPC/EAN barcode. You may always define those barcode able values as secondary key, but data often has a long history. Besides of course int identity still is a very popular primary key column type.

Bye, Olaf.

 

[grnzbra]

Thank you both for the clarification. That code worked beautifully for my needs.