AzureFeeds

In the previous post, I talked about how you could add default values for columns.

One special type of default value is called an IDENTITY constraint. These columns are one way to achieve automatic numbering in a column. There are many pros and cons about using these but at this point, I want to make sure you know how to use them.

The main difference from other columns is that you don’t put them in your INSERT statements. In fact, unlike other types of default constraint, you cannot just put the values in there. It will raise an error.

In the example shown, the CinemaGroupID column value will be automatically generated. You can see that it is not mentioned in the INSERT code below but because it’s defined as NOT NULL (and that’s required where it’s a primary key anyway), it will have a value automatically inserted.

IDENTITY_INSERT

You can manually insert identity column values if you first execute:

SET IDENTITY_INSERT dbo.CinemaGroups ON;

Then you do the insert operation and later turn this setting back off.

Learning T-SQL

It’s worth your while becoming proficient in SQL. If you’d like to learn a lot about T-SQL in a hurry, our Writing T-SQL Queries for SQL Server course is online, on-demand, and low cost.

The post T-SQL 101: #131 What are Identity Columns in SQL Server? appeared first on The Bit Bucket.