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.

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.

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.

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
     
    

    

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

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

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

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

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

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.

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