Detecting auto-incremented keys in SQL Server -- verification

Question

The accepted answer to this discussion says

SELECT 
is_identity
FROM sys.columns
WHERE 
    object_id = object_id('assignments')
    AND name = 'id'

tells whether the field id of table assignments is an auto-incremented field.

When i run this, i get the value 1 for the is_identity field. Does this 1 mean true-- "the key of this table is autoincremented"-- and is there any exception to this?

Way too new to SQL Server -- looking for verification.

//=====================

EDIT:

Added the following to the query:

SELECT 
is_identity,  max_length
FROM sys.columns
WHERE 
    object_id = object_id('assignments')
    AND name = 'id'

max_length is coming out as 4. id is of type int, and from what i know, int is up to 2^32. higher than 9999 in any case. What's missing here?

Yes, `is_identity` is a `bit` field, where 1 = yes or true, and 0 = no or false. — beercodebeer, Dec 29 '14 at 21:41
Also look at `sys.identity_columns`. And be careful about `assignments` vs. `dbo.assignments`. Schema is important. — Aaron Bertrand, Dec 29 '14 at 21:41
thx both for the verification. i'll add sme more to the Q-- one thing i looked since doing something i didn't expect. maybe you can answer rather than comments this time s.t. i can accept. — user4402742, Dec 29 '14 at 21:45

score 1 · Answer 1 · answered Dec 29 '14 at 21:52

Not much to answer here except to repeat what others have said in the comments: is_identity = 1 means the column is auto-increment.

MAX_LENGTH = 4 means the column is 4-byte (32-bit) wide. For character-type columns (char, varchar, nchar, nvarchar), it means the maximum length of the string.

Detecting auto-incremented keys in SQL Server -- verification

1 Answers1