INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

What is is_rowguidcol

What is is_rowguidcol

(OP)
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

RE: What is is_rowguidcol

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...

CODE

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

RE: What is is_rowguidcol

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

RE: What is is_rowguidcol

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.

RE: What is is_rowguidcol

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close