Introduction
Default values
How to auto-generate values in a database
10. Auto-generated values
Summary

Instruction

Fantastic! In this section, we'll learn something new: how to auto-generate values in a database. There are a few ways SQL Server can auto-generate values. We'll talk about three of them: IDENTITY, sequence, and the NEWID() function.

But first, let's talk about how a database identifies rows. Each table in a database contains a special column (or a group of columns) called the primary key. These columns are usually of the integer data type. Each primary key value must be unique, as it indicates each unique record in the table. Therefore, this column (often named Id) usually stores auto-generated numbers; you rarely have to insert values into a primary key column. Values for primary keys are usually automatically generated, so you can be sure that the column values are unique.

This statement

INSERT INTO Author (FirstName, LastName)
VALUES (N'Anne', N'Barry');

inserts data into the Author table. The Id column is not on the list. Why not? Because the Id column is auto-generated. You omit it in the query, and Id will get a new value generated by the database.

Exercise

Add a new author, John Summer, to the Author table. Don't add the Id column to the column list; it will be automatically generated by the database.