Introduction to Surrogate Keys

A surrogate key is a column whose values are provided, or generated automatically, by the database engine. A surrogate key is used when there is no clear or justifiable way to use the values of a known column as the primary key. The values of a surrogate key are usually integers with no obvious or clear meaning. This also means that the values of a surrogate key mean nothing to the user and in fact the user doesn't have to know or care about them.

Microsoft SQL Server 2012 provides many ways to create a surrogate key. Two of the solutions are identify keys and sequences.

Introduction to Identity Columns

One of the goals of a good table is to be able to uniquely identity each record. In most cases, the database engine should not confuse two records. Consider the following table:

Category Item Name Size Unit Price
Women Long-sleeve jersey dress Large 39.95
Boys Iron-Free Pleated Khaki Pants S 39.95
Men Striped long-sleeve shirt Large 59.60
Women Long-sleeve jersey dress Large 45.95
Girls Shoulder handbag   45.00
Women Continental skirt Petite 39.95

Imagine that you want to change the value of an item named "Long-sleeve jersey dress". Because you must find the item programmatically, you can start looking for an item with that name. This table happens to have two items with that name. You may then decide to look for an item using its category. In the Category column, there are too many items named "Women". In the same way, there are too many records that have a "Large" value in the Size column, same problem in the Unit Price column. This means that you don't have a good criterion you can use to isolate the record whose Item Name is Long-sleeve shirt.

To solve the problem of uniquely identifying a record, you can create a column whose main purpose is to distinguish one record from another. To assist you with this, the SQL allows you to create a column whose data type is an integer type but the user doesn't have to enter data for that column. A value would automatically be entered into the field when a new record is created. This type of column is called an identity column.

You cannot create an identity column on an existing table, only on a new table.

We continue our university database. In this version, University6, we will add new tables:

  • Departments: This table will hold information about the various sections used to run the school
  • Locations: This is a list of different buildings and areas in the school. Some buildings are used for offices and some buildings hold classrooms
  • Course Delivery Types: This table will describes the various the courses are taught, such as face-to-face, online, etc
  • Undergraduate Courses: This will be a list of all courses dispensed by the schools. New courses are added on a regular basis and some courses that have become useless are deleted. Each course has a course code, a course name, the number of credits, the prerequisites if any, and a description of the course
  • Genders: This table will be used to specify the gender of a person. This table will serve the tables that contain information about human beings such as employees, teachers, and students
  • Teachers: This is the list of people who teach courses. Some are main teachers while others are assistants. The information of each teacher consists of a unique number (equivalent to an employee number), a name (first, middle, and last), the degrees held by the teacher, etc
  • Undergraduate Majors: A major is the main area of studies for a student. To diversify its portfolio, a school covers many areas of studies, thus many majors (some schools specialize in certain areas; therefore, there are law schools, medical schools, etc). Our database will have a table of majors
  • Minors: As its name indicates, a minor is an area of studies that requires less concentration than the major but still has a number of requirements. Our database will include a table of minors

Some tables that we previously mentioned will also be added to this version of the database. These include the tables for employees, semesters, and schedules.

Practical LearningPractical Learning: Introducing Identity Columns
  1. Start Microsoft SQL Server connect
  2. Right-click the name of the server and click New Query
  3. To start a new database, type the following code: 
    USE master;
GO
DROP DATABASE University5
GO

CREATE DATABASE University6;
GO
USE University6;
GO
CREATE SCHEMA Academics;
GO
CREATE SCHEMA Administration;
GO

/* Instead of specified a deterministic date of birth, we will supply 
   a number of days to this function and, based on day this script is 
   run, the function will subtract the number of days from that date.
   That's how we will get the date of birth of a student. */
   
CREATE FUNCTION Administration.SetDateOfBirth(@days int)
RETURNS Date
AS
BEGIN
	RETURN DATEADD(d, @days, SYSDATETIME());
END
GO

CREATE TABLE Administration.Genders
(
    GenderLetter nvarchar(3) not null,
    GenderName nvarchar(50)
);
GO
CREATE TABLE Administration.Departments
(
    DepartmentCode nvarchar(4) not null,
    DepartmentName nvarchar(50) default N'N/A'
);
GO
CREATE TABLE Administration.Employees
(
    EmployeeNumber nvarchar(8) not null,
    FirstName nvarchar(20),
    MiddleName nvarchar(20),
    LastName nvarchar(20) not null,
    DepartmentCode nvarchar(4),
    Title nvarchar(100),
    Gender nvarchar(3) default N'N/A'
);
GO
CREATE TABLE Academics.CourseDeliveryTypes
(
    CourseDeliveryTypeCode nvarchar(1) not null,
    CourseDeliveryType nvarchar(25),
    Notes nvarchar(max)
);
GO
CREATE TABLE Academics.UndergraduateCourses
(
    CourseCode nvarchar(8) not null,
    CourseName nvarchar(100),
    Credits integer not null,
    CourseDescription nvarchar(max),
    Prerequisites nvarchar(100),
    Notes nvarchar(max)
);
GO
CREATE TABLE Administration.Locations
(
    LocationCode nvarchar(5) not null,
    LocationName nvarchar(50) not null,
    Notes nvarchar(max)
);
GO

INSERT INTO Administration.Genders
VALUES(N'M', N'Male'),
      (N'F', N'Female'),
      (N'U', N'Unknown');
GO
INSERT INTO Administration.Departments(DepartmentCode, DepartmentName)
VALUES(N'N/A', NULL),
      (N'ADMN', N'Administration, Admissions, and Students Affairs'),
      (N'HRMN', N'Human Resources and Management'),
      (N'WRTG', N'Wrighting'),
      (N'EDUC', N'Educational Studies'),
      (N'LNGS', N'Languages and Linguistics'),
      (N'PSOP', N'Psychology, Sociology, and Philosophy'),
      (N'ITEC', N'Information Technology'),
      (N'FINA', N'Finances'),
      (N'ACCT', N'Accounting'),
      (N'EBCM', N'Economics, Business, Commerce, and Marketing'),
      (N'CJLE', N'Criminal Justice and Law Enforcement'),
      (N'CHEM', N'Chemistry'),
      (N'CMSC', N'Computer Sciences'),
      (N'ELCE', N'Electrical and Computer Engineering'),
      (N'CMST', N'Computer Studies'),
      (N'HSGE', N'History and Geography'),
      (N'ANTH', N'Anthropology'),
      (N'BIOL', N'Biology and Biotechnology'),
      (N'HLTH', N'Health Care and Gerontology'),
      (N'MATH', N'Mathematics and Statistics'),
      (N'GVPS', N'Government and Political Sciences');
GO
INSERT INTO Administration.Employees(EmployeeNumber, FirstName, MiddleName, LastName, DepartmentCode, Title, Gender)
VALUES(N'279227', N'Donald',  N'Henry',  N'Leighton',  N'ADMN', N'President', N'M'),
      (N'502494', N'Anthony', N'Robert', N'Parrish',   N'ADMN', N'Provost', N'M'),
      (N'247591', N'Leonid',  N'George', N'Hawthorne', N'HSGE', N'Dean of History, Geography, and Political Sciences', N'M');
GO
INSERT INTO Administration.Employees(EmployeeNumber, FirstName, LastName, DepartmentCode, Title, Gender)
VALUES(N'400384', N'Jennifer',  N'Palermo', N'HRMN', N'Dean of Human Resources and Management Studies', N'F');
GO
INSERT INTO Administration.Employees(EmployeeNumber, FirstName, MiddleName, LastName, DepartmentCode, Title, Gender)
VALUES(N'274039', N'Joyce',    N'Denise',   N'Blue',      N'CHEM', N'Dean of Chemistry Studies', N'F'),
      (N'409260', N'Edmond',   N'Gabriel',  N'Harrington', N'CJLE', N'Dean of Criminal Justice Studies', N'M'),
      (N'828347', N'Robert',   N'Elie',     N'Marsden',   N'MATH', N'Dean of Mathematics, statistics, and Physics', N'M'),
      (N'640207', N'Kimberly', N'Carlette', N'Edelman',   N'PSOP', N'Dean of Psychology, Sociology, and Philosophy', N'F'),
      (N'161138', N'Laura',    N'Fannie',   N'Joansen',   N'ADMN', N'Dean of Litterary Studies', N'F'),
      (N'605924', N'Phillipe', N'Ernest',   N'Portman',   N'BIOL', N'Dean of Biological and Biotechnology Studies', N'M'),
      (N'908047', N'Ann',      N'Laura',    N'Tenney',    N'FINA', N'Cashier',  N'F');
GO
INSERT INTO Administration.Employees(EmployeeNumber, FirstName, LastName, DepartmentCode, Title, Gender)
VALUES(N'582007', N'Alexander', N'Nolan',  N'CMSC', N'Dean of Computer Sciences and Computer Engineering', N'M'),
      (N'697300', N'Albert',    N'Harney', N'FINA', N'Dean of Financial and Accounting Studies', N'M');
GO
INSERT INTO Administration.Employees(EmployeeNumber, FirstName, MiddleName, LastName, DepartmentCode, Title, Gender)
VALUES(N'702048', N'Laurentine', N'Felicité', N'Avrilien', N'EDUC', N'Dean of Educational Studies',  N'F'),
      (N'927486', N'Robert',     N'John',     N'Preston',  N'CMST', N'Dean of Computer Studies', N'M'),
      (N'930248', N'Jeannette',  N'Veronica', N'Holms',    N'ADMN', N'Vice President for Government Relations', N'F');
GO

INSERT INTO Academics.CourseDeliveryTypes
VALUES(N'F', N'Face-to-Face', N'The course will be taught in a classroom. Consult the schedule to find out how often the class will meet.'),
      (N'L', N'Online', N'The course will de delivered over the Web and other means of remote communications such as video and/or conference call.'),
      (N'H', N'Hybrid', N'The course will be taught face-to-face part-time and online part-time. For example, the teacher and students may meet face-to-face in one session and online for the next section. Consult the course schedule or the teacher communicate how the class will be conducted.');
GO
INSERT INTO Academics.UndergraduateCourses(CourseCode, CourseName, Credits)
VALUES(N'LBRS 100', N'Library and Research', 1),
      (N'EDPD 100', N'Education and Personal Development', 3),
      (N'CMSC 101', N'Introduction to Computer Programming With C++', 3),
      (N'WRTG 101', N'Introduction to Writing', 3);
GO
INSERT INTO Academics.UndergraduateCourses(CourseCode, CourseName, Credits, Prerequisites)
VALUES(N'MATH 106', N'College Algebra', 3, N'MATH 012'),
      (N'MATH 115', N'Pre-Calculus', 3, N'MATH 012');
GO
INSERT INTO Academics.UndergraduateCourses(CourseCode, CourseName, Credits)
VALUES(N'HIST 140', N'Technological Transformations', 3),
      (N'GVPS 140', N'National and Local Governments', 3);
GO

INSERT INTO Administration.Locations(LocationCode, LocationName)
VALUES(N'ADMAS', N'Administration and Auxiliary Services'),
      (N'HMNSS', N'Humanities and Social Sciences Building'),
      (N'FSOPR', N'Facilities Services and Operations'),
      (N'SEAMT', N'Science, Engineering, and Mathematics Building'),
      (N'NAASB', N'Natural and Applied Sciences Building'),
      (N'PACTR', N'Performing Arts Center'),
      (N'PEAHL', N'Physical Education and Health Building'),
      (N'LATAC', N'Literatrue and Academic Building'),
      (N'SPASC', N'Space Science Center'), 
      (N'SMALC', N'Sun, Moon, and Life Center'),
      (N'JPTRC', N'Jupiter Center'),
      (N'LIBRM', N'Libraries and Media Building'),
      (N'DSSSS', N'Disability Support Services'),
      (N'SECRB', N'Security Building'),
      (N'ONLNE', N'Online'),
      (N'HYBRD', N'Hybrid');
GO
  4. To execute the SQL statement, press F5
  5. In the Object Explorer, expand the Databases node
  6. Expand University6
  7. Right-click Tables and click New Table...
  8. While the table is selected, in the Properties window, click Schema and select Academics

Visually Creating an Identity Column

Introduction to Surrogate Keys

To create an identity column, if you are visually working in the design view of the table, in the top section, specify the name of the column. By tradition, the name of this column resembles that of the table but in singular. Also, by habit, the name of the column ends with _id, Id, or ID.

After specifying the name of the column, set its data type to an integer-based type. Usually, the data type used is int. In the bottom section, click and expand the Identity Specification property. The first action you should take is to set its (Is Identity) property from No to Yes.

The Seed of an Identity Column

Once you have set the value of the (Is Identity) property to Yes, the first time the user performs data entry, the value of the first record would be set to 1. This characteristic is controlled by the Identity Seed property. If you want the count to start to a value other than 1, specify it on this property.

The Identity Increment

After the (Is Identity) property has been set to Yes, the SQL interpreter would increment the value of each new record by 1, which is the default. This means that the first record would have a value of 1, the second would have a value of 2, and so on. This aspect is controlled by the Identity Increment property. If you want to increment by more than that, you can change the value of the Identity Increment property.

Practical LearningPractical Learning: Visually Creating an Identity Column
  1. Click the box under Column Name
  2. Type TeacherID and press Tab
  3. Set the data type to int
  4. In the lower section of the table, expand Identity Specification and double-click (Is Identity) to set its value to Yes.
  5. Notice that the Identity Increment to is automatically set to 1 and the Identity Seed is set to 1
  6. Complete the table as follows:
     
    Column Name Data Type Allow Nulls
    TeacherID    
    TeacherNumber nvarchar(10)  
    FirstName nvarchar(25)  
    MiddleName nvarchar(25)  
    LastName nvarchar(25) Unchecked
    [Degrees] nvarchar(40)  
    DepartmentCode nvarchar(4) Unchecked
    Gender nvarchar(3) Unchecked
  7. Right-click TeacherNumber and click Set Primary Key
  8. To save and close the table, close the Query Editor
  9. When asked whether you want to save, click Yes
  10. Set the name of the table to Teachers
  11. Click OK
  12. In the Object Explorer, right-click Tables and click New Table...
  13. While the table is selected, in the Properties window, click Schema and select Academics
  14. Click the box under Column Name
  15. Type SemesterID and press Tab
  16. Right-click SemesterID and click Set Primary Key
  17. Set the data type to int
  18. In the lower section of the table, expand Identity Specification and double-click (Is Identity) to set its value to Yes
  19. Set the Identity Increment to 1 (it should be set already).
    Set the Identity Seed to 101
     

    Creating an Identity Column

  20. Complete the table as follows:
     
    Column Name Data Type Allow Nulls
    SemesterID    
    Semester nvarchar(30) Unchecked
    SemesterStart date Unchecked
    SemesterEnd date Unchecked
    Session1Start date Unchecked
    Session1End date Unchecked
    Session2Start date Unchecked
    Session2End date Unchecked
    OnlineStart date Unchecked
    OnlineEnd date Unchecked
  21. To save and close the table, close the Query Editor
  22. When asked whether you want to save, click Yes
  23. Set the name of the table to Semesters
  24. Click OK
  25. Click inside the Query Editor and press Ctrl + A
  26. Type the following lines: 
    USE University6;
GO

CREATE TABLE Academics.UndergraduateSchedules
(
    UndergraduateScheduleID int not null,
    SemesterID 	  int,
    StartDate  	  date,
    EndDate       date,
    StartTime  	  time,
    EndTime 	  time,
    Weekdays 	  nvarchar(32),
    TeacherNumber nvarchar(10),
    CourseCode 	  nvarchar(8),
    CourseDeliveryTypeCode nvarchar(1),
    LocationCode  nvarchar(5) not null,
    RoomNumber    nvarchar(40)
);
GO
  27. To execute, press F5
Creating an Identity Column Using SQL

Creating an Identity Column Using SQL

If you are programmatically creating a column, to indicate that it would be used as an identity column after its name and data type, type identity followed by parentheses. Between the parentheses, enter the seed value, followed by a comma, followed by the increment value. Here is an example:

CREATE TABLE StoreItems(
    ItemID int IDENTITY(1, 1) NOT NULL, 
    Category nvarchar(50),
    [Item Name] nvarchar(100) NOT NULL,
    Size varchar(20),
    [Unit Price] money);
GO

Practical LearningPractical Learning: Creating Identity Columns
  1. Click inside the Query Editor and press Ctrl + A
  2. Type the following lines: 
    USE University6;
GO

CREATE TABLE Academics.UndergraduateMajors
(
    MajorID int identity(1001, 1) not null,
    Major nvarchar(60) unique,
    Dean nvarchar(8) not null
);
GO
  3. To execute, press F5

Data Entry With an Identity Column

After creating an identity column, when performing data entry, don't specify a value for that column. Here is an example:

USE Exercise;
GO

CREATE TABLE StoreItems
(
	ItemID int identity(1, 1) NOT NULL, 
	Category nvarchar(50),
	[Item Name] nvarchar(100) NOT NULL,
	Size nvarchar(20),
	[Unit Price] money
);
GO

INSERT INTO StoreItems(Category, [Item Name], Size, [Unit Price])
VALUES(N'Men', N'Simplicity Leather Dress Oxfords', N'9', 65.85);
GO

Identity Column

If you provide a value for the identity column, you would receive an error:

Identity Column

 

Practical LearningPractical Learning: Creating Identity Records
  1. In the Object Explorer, right-click Tables and click Refresh
  2. If necessary, expand the Tables node.
    Right-click Academics.Teachers and click Edit Top 200 Rows
  3. Click the first box under TeacherNumber
  4. Type 293804 and press Tab
  5. Complete the table with the following values:
     
    Teacher Number First Name Middle Name Last Name [Degrees] Department Code Gender
    293804 Stephen   Martins MA, PhD WRTG M
    603925 Donna   Yoder MS, MA LNGS F
    979384 David Justin Palmer BA, MA EDUC M
    283029 Donald   Fisher MA, MS HSGE M
  6. Close the table
  7. In the Object Explorer, eright-click Academics.Semesters and click Edit Top 200 Rows
  8. Complete the table with the following values:
     
    Semester Semester Start Semester End Session1 Start Session1 End Session2 Start Session2 End Online Start Online End
    SPRING 2012 01/09/2012 04/29/2012 01/09/2012 02/19/2012 02/27/2012 04/29/2012 01/09/2012 03/18/2012
    SUMMER 2012 05/14/2012 08/19/2012 05/14/2012 07/08/2012 07/16/2012 08/19/2012 05/14/2012 07/08/2012
    FALL 2012 09/17/2012 12/16/2012 09/17/2012 10/14/2012 10/22/2012 12/16/2012 09/20/2012 10/14/2012     
  9. Close the table
  10. Click inside the Query Editor and press Ctrl + A
  11. Type the following lines: 
    USE University6;
GO
INSERT INTO Academics.Teachers(TeacherNumber, FirstName, LastName, DepartmentCode, Gender)
VALUES(N'820384', N'Marianne', N'Oslin', N'EDUC', N'F');
GO
INSERT INTO Academics.Teachers(TeacherNumber, FirstName, MiddleName, LastName, [Degrees], DepartmentCode, Gender)
VALUES(N'160205', N'Steve', N'Alxeander', N'Rosner', N'MA, PhD', N'CMSC', N'M');
GO
INSERT INTO Academics.Teachers(TeacherNumber, FirstName, LastName, [Degrees], DepartmentCode, Gender)
VALUES(N'520203', N'Anne',    N'Wine',       N'MS, PhD',     N'MATH', N'F'),
      (N'297940', N'Thomas',  N'Phillips',   N'BS, MS, PhD', N'MATH', N'M'),
      (N'640840', N'Maryam',  N'Whittaker',  N'MA, MS, PhD', N'MATH', N'F'),
      (N'339429', N'Lisa',    N'Williamson', N'PhD',         N'EBCM', N'F'),
      (N'249382', N'Johanna', N'Possemato',  N'PhD',         N'GVPS', N'F');
GO

INSERT INTO Academics.UndergraduateMajors(Major, Dean)
VALUES(N'English',      N'161138'),
      (N'Linguistics',  N'161138'),
      (N'History',      N'247591'),
      (N'Geography',    N'247591'),
      (N'Finance',      N'697300'),
      (N'Sociology',    N'640207'),
      (N'Psychology',   N'640207'),
      (N'Economics',    N'908047'),
      (N'Marketing',    N'908047'),
      (N'Statistics',   N'828347'),
      (N'Accounting',   N'697300'),
      (N'Gerontology',  N'640207'),
      (N'Biology',      N'605924'),
      (N'Chemistry',    N'274039'),
      (N'Anthropology', N'247591'),
      (N'Political Science',	     N'247591'),
      (N'Criminal Justice and Law Enforcement', N'409260'),
      (N'Emergency Management',	     N'400384'),
      (N'Business Administration',   N'908047'),
      (N'Human Resource Management', N'400384'),
      (N'Computer Science',                 N'582007'),
      (N'Computer Networks and Security',   N'927486'),
      (N'Information Systems Management',   N'927486'),
      (N'Computer and Information Science', N'927486'),
      (N'Health Care Management and Policy', N'927486');
GO
INSERT INTO Academics.Semesters(Semester, SemesterStart, SemesterEnd, Session1Start, Session1End, 
				Session2Start, Session2End, OnlineStart, OnlineEnd)
VALUES(N'SPRING 2013', N'20130107', N'20130428', N'20130107', N'20130217', N'20130225', N'20130428', N'20130107', N'20130317'),
      (N'SUMMER 2013', N'20130513', N'20130818', N'20130513', N'20130707', N'20130715', N'20130818', N'20130513', N'20130707'),
      (N'FALL 2013',   N'20130916', N'20131215', N'20130916', N'20131103', N'20131021', N'20131215', N'20130919', N'20131013');
GO
  12. To execute, press F5
  13. Click the SQLQuery1.sql tab and press Ctrl + A
  14. Type the following: 
    SELECT Instructors.TeacherNumber [Teacher #],
       Instructors.FirstName [First Name],
       LEFT(Instructors.MiddleName, 1) MI,
       Instructors.LastName [Last Name],
       Instructors.[Degrees],
       Instructors.DepartmentCode [Dept Code],
       CASE Instructors.Gender
		WHEN N'M' THEN N'Male'
		WHEN N'F' THEN N'Female'
		ELSE N'Unknomn'
       END Gender
FROM Academics.Teachers Instructors
ORDER BY Instructors.LastName;
GO
  15. Right-click anywhere in the window and click Execute SQL
  16. Change the statement in the Query Editor as follows: 
    SELECT sems.Semester,
       FORMAT(sems.SemesterStart, N'yyyy') Year,
       FORMAT(sems.SemesterStart, N'MMMM') "Semester spans from",
       FORMAT(sems.SemesterEnd, N'MMMM') "Semester spans to",
       FORMAT(sems.SemesterStart, N'D') "Start of Semester",
       FORMAT(sems.SemesterEnd, N'D') "End of Semester",
       FORMAT(sems.Session1Start, N'D') "Start of Session 1", 
       FORMAT(sems.Session1End, N'D') "End of Session 1", 
       FORMAT(sems.Session2Start, N'D') "Start of Session 2", 
       FORMAT(sems.Session2End, N'D') "End of Session 2", 
       FORMAT(sems.OnlineStart, N'D') "End of Online Sessions"
FROM Academics.Semesters sems
ORDER BY sems.SemesterStart;
GO
  17. Right-click anywhere in the window and click Execute SQL

Creating a Value for an Identity Column

If you want to specify a value for the identity column, call the SET IDENTITY_INSERT flag. The formula it uses is:

SET IDENTITY_INSERT [ database_name.[schema_name].] table { ON | OFF }

The database_name is the optional name of the database that owns the table. If you previously use the USE statement, in most cases, you can omit the name of the database. The schema_name is the (optional) name of the schema in which the table was created. The table factor is the name of the table the identity column belongs to. After specifying the name of the table, set the flag as ON to allow a value for the identity column, or OFF to disallow it.

If you decide to use the SET IDENTITY_INSERT, you must provide a list of columns after the name of the table in the INSERT or INSERT INTO statement. Here is an example:

USE Exercise1;
GO

SET IDENTITY_INSERT StoreItems ON;
GO
INSERT INTO StoreItems(ItemID, Category, [Item Name], Size, [Unit Price])
VALUES(10, N'Girls', N'Girls 2-6X Short-Sleeved Mesh Polo Shirt', N'2T', 18.95);
GO

This time, the data entry would not produce an error.

As mentioned already, after setting the IDENTITY_INSERT ON, you can put it back OFF and add a record without a value for the identity column. Here is an example:

USE Exercise1;
GO

SET IDENTITY_INSERT StoreItems ON;
GO
INSERT INTO StoreItems(ItemID, Category, [Item Name], Size, [Unit Price])
VALUES(50, N'Men', N'Simplicity Leather Dress Oxfords', N'10.5', 65.85);
GO

SET IDENTITY_INSERT StoreItems OFF;
GO

INSERT INTO StoreItems(Category, [Item Name], Size, [Unit Price])
VALUES(N'Women', N'Fiona High Heel Boot', N'6.50', 295.00);
GO
INSERT INTO StoreItems(Category, [Item Name], Size, [Unit Price])
VALUES(N'Women', N'Saddle East/West Leather Hobo Bag', N'Medium', 345.50);
GO

If you do this, the next value of the identity column would be the increment from the previous value.

Selecting the Values of the Identity Column

As we know already, to get a list of values from a column, you can use a SELECT statement and give the name of the column. If a table is using an identity column, Transact-SQL provides the $IDENTITY flag that allows you to 

USE Exercise;
GO
SELECT $IDENTITY FROM StoreItems;
GO
Previous Copyright © 2008-2022, FunctionX, Inc. Next