Home

Surrogate Keys

     
Introduction to Surrogate Keys

Identity Columns

 

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 Long-sleeve jersey dress Large 39.95
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 jersey dress.

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.

Monson University

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 describe the various the courses are taught, such as face-to-face, online, etc
  • Undergraduate Courses: This will be a list of all courses taught in the school. 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. The table will serve the lists 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 of a student. To diversify its portfolio, a school covers many areas of studies, thus many majors (some schools specialize in certain areas; as a result, there are law schools, medical schools, etc)
  • 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

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

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, 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 to which the identity column belongs. 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 flag, 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 get the values of that column. Here is an example of using it:

USE Exercise;
GO
SELECT $IDENTITY FROM StoreItems;
GO

Introduction to Sequences

 

Overview

A sequence is a series of numbers that are continually generated and assigned to a column of a table. This works like an identity column. The difference is that, if you need an identity, you must create it in a column of a table and if you need the same type of identity on a column of another table, you must create the identity in the column of the other table. On the other hand, a sequence is a programmatic object, like a function, that you create at the database level and you can apply that sequence to any table you want.

Visually Creating a Sequence

To visually create a sequence, in the Object Explorer, expand the desired database and the Programmability nodes. Right-click Sequences and click New Sequence...

Visually Creating a Sequence

This would present the New Sequence dialog box with some default (basic) values:

New Sequence

We will review all the options of this dialog box in the next sections.

Programmatically Creating a Sequence

The Transact-SQL syntax to create a sequence is:

CREATE SEQUENCE [schema_name . ] sequence_name
    [ AS [ built_in_integer_type | user-defined_integer_type ] ]
    [ START WITH <constant> ]
    [ INCREMENT BY  <constant> ]
    [ { MINVALUE [  <constant> ] } | { NO MINVALUE } ]
    [ { MAXVALUE [  <constant> ] } | { NO MAXVALUE } ]
    [ CYCLE | { NO CYCLE } ]
    [ { CACHE [  <constant> ] } | { NO CACHE } ]
    [ ; ]

You start with the CREATE SEQUENCE expression.

Characteristics of a Sequence

A sequence shares many characteristics with an identity column but adds some others. Therefore, the characteristics of a sequence are:

Practical LearningPractical Learning: Creating Sequences

  1. In the Object Explorer, under University6, expand Programmability
  2. Right-click Sequences and click New Sequence...
  3. In the Sequence Name, type SeqSchedules
  4. On the right side of the Sequence Schema text box, click the browse button Ellipsis
  5. In the Enter the Object Names text box, type Academics
  6. Click Check Names and notice that [Academics] has been selected
  7. Click OK
  8. In the Data Type combo box, select int
  9. In the Start Value text box, type 10101
  10. In the Increment By text box, type 1
     
    New Sequence
  11. Click OK
  12. Click inside the Query Editor and press Ctrl + A
  13. To programmatically create a sequence and a table that would use it, type the following code:
    USE University6;
    GO
    
    CREATE SEQUENCE Academics.SeqStudents
        AS Int
        START WITH   1
        INCREMENT BY 1;
    GO
  14. To execute, press F5

Using a Sequence

After creating a sequence, it becomes an object you can use in any new table. Because a sequence generates (unique increment/decrement) values that a column would use, when creating the field on a table, specify its data type as the same or compatible type that the sequence is using. Here is an example:

CREATE TABLE Inventory.StoreItems
(
    ItemNumber int,
    ItemName nvarchar(60),
    UnitPrice money
);
GO

A sequence is used during data entry. When specifying the value of its column, type a formula as:

NEXT VALUE FOR [schema_name . ] sequence_name

The database engine would then find the next number in the sequence and assign it to the column. Here are examples:

CREATE SCHEMA Inventory;
GO
CREATE SEQUENCE Inventory.ItemsCodes
    AS int
	START WITH 10001
	INCREMENT BY 1;
GO
CREATE TABLE Inventory.StoreItems
(
	ItemNumber int,
	ItemName nvarchar(60),
	UnitPrice money
);
GO
INSERT INTO Inventory.StoreItems
VALUES(NEXT VALUE FOR Inventory.ItemsCodes, N'Short Sleeve Shirt', 34.95),
      (NEXT VALUE FOR Inventory.ItemsCodes, N'Tweed Jacket', 155.00),
      (NEXT VALUE FOR Inventory.ItemsCodes, N'Evaded Mini-Skirt', 72.45),
      (NEXT VALUE FOR Inventory.ItemsCodes, N'Lombardi Men''s Shoes', 79.95);
GO

This would produce:

ItemNumber ItemName UnitPrice
10001 Short Sleeve Shirt 34.95
10002 Tweed Jacket 155.00
10003 Evaded Mini-Skirt 72.45
10004 Lombardi Men's Shoes 79.95
 

Practical LearningPractical Learning: Using Sequences

  1. Click inside the Query Editor and press Ctrl + A
  2. Type the following:
    USE University6;
    GO
    
    INSERT INTO Academics.UndergraduateSchedules(UndergraduateScheduleID, SemesterID, StartDate, EndDate, TeacherNumber, CourseCode, CourseDeliveryTypeCode, LocationCode)
    VALUES(NEXT VALUE FOR Academics.SeqSchedules, 101, N'20120109', N'20120205', N'820384', N'LBRS 100', N'L', N'ONLNE'),
          (NEXT VALUE FOR Academics.SeqSchedules, 101, N'20120109', N'20120205', N'979384', N'LBRS 100', N'L', N'ONLNE');
    GO
    INSERT INTO Academics.UndergraduateSchedules(UndergraduateScheduleID, SemesterID, StartDate, EndDate, StartTime, EndTime, Weekdays, TeacherNumber, CourseCode, CourseDeliveryTypeCode, LocationCode, RoomNumber)
    VALUES(NEXT VALUE FOR Academics.SeqSchedules, 101, N'20120109', N'20120318', N'08:15', N'10:10', N'M-W', N'293804', N'WRTG 101', N'F', N'LATAC', N'204'),
          (NEXT VALUE FOR Academics.SeqSchedules, 101, N'20120109', N'20120318', N'10:25', N'12:20', N'M-W', N'293804', N'WRTG 101', N'F', N'LATAC', N'104'),
          (NEXT VALUE FOR Academics.SeqSchedules, 101, N'20120109', N'20120226', N'08:15', N'10:10', N'M-W-F', N'293804', N'WRTG 101', N'F', N'LATAC', N'102'),
          (NEXT VALUE FOR Academics.SeqSchedules, 101, N'20120109', N'20120226', N'10:25', N'12:20', N'M-W-F', N'293804', N'WRTG 101', N'F', N'LATAC', N'102'),
          (NEXT VALUE FOR Academics.SeqSchedules, 101, N'20120109', N'20120318', N'10:25', N'12:20', N'M-W', N'520203', N'MATH 106', N'F', N'LATAC', N'102');
    GO
  3. To execute, press F5
  4. Click inside the Query Editor and press Ctrl + A
  5. To see a list of schedules, type the following code:
    SELECT schd.*
    FROM Academics.UndergraduateSchedules schd;
    GO
  6. To execute the statement, press F5

Details on Sequences

   

Sharing a Sequence

A sequence can be shared by many tables. This means that, after creating a sequence, you can apply it on any table that needs that series of numbers. When using a sequence from one table to another, if you use the NEXT VALUE FOR routine, the series would continue from where it left up. This is not an anomaly. It is by design, so that various tables can share the same sequence.

Resetting a Sequence

Resetting a sequence consists of restarting it from a certain point. To do this, use the following formula:

ALTER SEQUENCE [schema_name. ] sequence_name
    [ RESTART [ WITH constant ] ]
    [ INCREMENT BY constant ]
    [ { MINVALUE constant } | { NO MINVALUE } ]
    [ { MAXVALUE constant } | { NO MAXVALUE } ]
    [ CYCLE | { NO CYCLE } ]
    [ { CACHE [ constant ] } | { NO CACHE } ]
    [ ; ]

Practical LearningPractical Learning: Resetting a Sequence

  1. Click inside the Query Editor and press Ctrl + A
  2. Type the following:
    USE University6;
    GO
    
    ALTER SEQUENCE Academics.SeqSchedules
    RESTART WITH 10201
    INCREMENT BY 1;
    GO
    
    INSERT INTO Academics.UndergraduateSchedules(UndergraduateScheduleID, SemesterID, StartDate, EndDate, StartTime, EndTime, Weekdays, TeacherNumber, CourseCode, CourseDeliveryTypeCode, LocationCode, RoomNumber)
    VALUES(NEXT VALUE FOR Academics.SeqSchedules, 102, N'20120514', N'20120819', N'08:15', N'10:10', N'M-W', N'293804', N'WRTG 101', N'F', N'LATAC', N'204');
    GO
    INSERT INTO Academics.UndergraduateSchedules(UndergraduateScheduleID, SemesterID, StartDate, EndDate, StartTime, EndTime, Weekdays, TeacherNumber, CourseCode, CourseDeliveryTypeCode, LocationCode, RoomNumber)
    VALUES(NEXT VALUE FOR Academics.SeqSchedules, 102, N'20120514', N'20120819', N'10:25', N'12:20', N'M-W', N'603925', N'EDPD 100', N'F', N'HMNSS', N'101');
    GO
    INSERT INTO Academics.UndergraduateSchedules(UndergraduateScheduleID, SemesterID, StartDate, EndDate, StartTime, EndTime, Weekdays, TeacherNumber, CourseCode, CourseDeliveryTypeCode, LocationCode, RoomNumber)
    VALUES(NEXT VALUE FOR Academics.SeqSchedules, 102, N'20120514', N'20120819', N'08:15', N'10:10', N'T-H', N'520203', N'MATH 106', N'F', N'LATAC', N'104');
    GO
    INSERT INTO Academics.UndergraduateSchedules(UndergraduateScheduleID, SemesterID, StartDate, EndDate, TeacherNumber, CourseCode, CourseDeliveryTypeCode, LocationCode)
    VALUES(NEXT VALUE FOR Academics.SeqSchedules, 102, N'20120514', N'20120205', N'820384', N'LBRS 100', N'L', N'ONLNE');
    GO
    INSERT INTO Academics.UndergraduateSchedules(UndergraduateScheduleID, SemesterID, StartDate, EndDate, StartTime, EndTime, Weekdays, TeacherNumber, CourseCode, CourseDeliveryTypeCode, LocationCode, RoomNumber)
    VALUES(NEXT VALUE FOR Academics.SeqSchedules, 102, N'20120514', N'20120819', N'08:15', N'10:10', N'M-W', N'160205', N'CMSC 101', N'F', N'SEAMT', N'210');
    GO
    INSERT INTO Academics.UndergraduateSchedules(UndergraduateScheduleID, SemesterID, StartDate, EndDate, StartTime, EndTime, Weekdays, TeacherNumber, CourseCode, CourseDeliveryTypeCode, LocationCode, RoomNumber)
    VALUES(NEXT VALUE FOR Academics.SeqSchedules, 102, N'20120514', N'20120819', N'13:25', N'15:20', N'M-W', N'297940', N'MATH 115', N'F', N'NAASB', N'102');
    GO
    INSERT INTO Academics.UndergraduateSchedules(UndergraduateScheduleID, SemesterID, StartDate, EndDate, StartTime, EndTime, Weekdays, TeacherNumber, CourseCode, CourseDeliveryTypeCode, LocationCode, RoomNumber)
    VALUES(NEXT VALUE FOR Academics.SeqSchedules, 102, N'20120514', N'20120819', N'08:15', N'10:10', N'T-H', N'283029', N'HIST 140', N'F', N'HMNSS', N'103');
    GO
    
    ALTER SEQUENCE Academics.SeqSchedules
    RESTART WITH 10301
    INCREMENT BY 1;
    GO
    
    INSERT INTO Academics.UndergraduateSchedules(UndergraduateScheduleID, SemesterID, StartDate, EndDate, StartTime, EndTime, Weekdays, TeacherNumber, CourseCode, CourseDeliveryTypeCode, LocationCode, RoomNumber)
    VALUES(NEXT VALUE FOR Academics.SeqSchedules, 103, N'20120920', N'20121014', N'08:15', N'10:10', N'M-W', N'293804', N'WRTG 101', N'F', N'LATAC', N'101');
    GO
    INSERT INTO Academics.UndergraduateSchedules(UndergraduateScheduleID, SemesterID, StartDate, EndDate, StartTime, EndTime, Weekdays, TeacherNumber, CourseCode, CourseDeliveryTypeCode, LocationCode, RoomNumber)
    VALUES(NEXT VALUE FOR Academics.SeqSchedules, 103, N'20120920', N'20121014', N'10:25', N'12:20', N'M-W', N'640840', N'MATH 106', N'F', N'LATAC', N'106');
    GO
    INSERT INTO Academics.UndergraduateSchedules(UndergraduateScheduleID, SemesterID, StartDate, EndDate, StartTime, EndTime, Weekdays, TeacherNumber, CourseCode, CourseDeliveryTypeCode, LocationCode, RoomNumber)
    VALUES(NEXT VALUE FOR Academics.SeqSchedules, 103, N'20120920', N'20121014', N'13:25', N'15:20', N'M-W', N'283029', N'HIST 140', N'F', N'HMNSS', N'103');
    GO
    INSERT INTO Academics.UndergraduateSchedules(UndergraduateScheduleID, SemesterID, StartDate, EndDate, StartTime, EndTime, Weekdays, TeacherNumber, CourseCode, CourseDeliveryTypeCode, LocationCode, RoomNumber)
    VALUES(NEXT VALUE FOR Academics.SeqSchedules, 103, N'20120920', N'20121014', N'13:25', N'15:20', N'T-H', N'283029', N'HIST 140', N'F', N'HMNSS', N'103');
    GO
    
    ALTER SEQUENCE Academics.SeqSchedules
    RESTART WITH 10401
    INCREMENT BY 1;
    GO
    
    INSERT INTO Academics.UndergraduateSchedules(UndergraduateScheduleID, SemesterID, StartDate, EndDate, StartTime, EndTime, Weekdays, TeacherNumber, CourseCode, CourseDeliveryTypeCode, LocationCode, RoomNumber)
    VALUES(NEXT VALUE FOR Academics.SeqSchedules, 104, N'20130107', N'20130217', N'08:15', N'10:10', N'M-W', N'283029', N'HIST 140', N'F', N'HMNSS', N'105');
    GO
    INSERT INTO Academics.UndergraduateSchedules(UndergraduateScheduleID, SemesterID, StartDate, EndDate, StartTime, EndTime, Weekdays, TeacherNumber, CourseCode, CourseDeliveryTypeCode, LocationCode, RoomNumber)
    VALUES(NEXT VALUE FOR Academics.SeqSchedules, 104, N'20130107', N'20130217', N'15:35', N'17:30', N'M-W', N'249382', N'GVPS 140', N'F', N'HMNSS', N'107');
    GO
    
    
  3. To execute, press F5
  4. Click inside the Query Editor and press Ctrl + A
  5. To see a list of schedules, type the following code:
    SELECT schd.UndergraduateScheduleID,
           schd.SemesterID, 
           schd.StartDate, 
           schd.EndDate, 
           schd.TeacherNumber, 
           schd.CourseCode, 
           schd.CourseDeliveryTypeCode, 
           schd.LocationCode
    FROM Academics.UndergraduateSchedules schd;
    GO
  6. To execute the statement, press F5

Setting a Sequence as Default

So far, to specify the value of a column with sequence, we were calling NEXT VALUE FOR. If you know that you will keep caling a sequence to provide the values of a column, you can set that sequence as the default value of the column. If you do this, you can omit the column in the INSERT statement. Here is an example:

USE LambdaSquare1;
GO
CREATE TABLE Rentals.Registrations
(
	RegistrationID int not null
		DEFAULT (NEXT VALUE FOR Rentals.SeqRegistrations),
	RegistrationDate Date,
	EmployeeNumber int, -- Processed By
	TenantCode int, -- Processed For
	UnitNumber int not null,
	RentStartDate date,
	Notes nvarchar(max)
);
GO

After doing this, you can create the values of the column as done for an identity, by omiting the name of the column in the INSERT statement.

INSERT INTO Academics.UndergraduateStudents(StudentNumber, FirstName, MiddleName, LastName, BirthDate, Gender, EmailAddress, MajorID, MinorID, Username)
VALUES(N'88130480', N'Marie', N'Annette', N'Robinson', Administration.SetDateOfBirth(-6817), N'F', N'mrobinson@yahoo.com',    1021, 1004, N'mrobinson'),
      (N'24795711', N'Roger', N'Dermot',  N'Baker',    Administration.SetDateOfBirth(-6570), N'M', N'rbaker2020@hotmail.com', 1005, 1002, N'rbaker');
GO
INSERT INTO Academics.UndergraduateStudents(StudentNumber, FirstName, LastName, BirthDate, Gender, EmailAddress, MajorID, MinorID, Username)
VALUES(N'18073572', N'Patrick', N'Wisne', Administration.SetDateOfBirth(-11012), N'M', N'pwisdom@attmail.com', 1001, 1008, N'pwisne');
GO

Practical LearningPractical Learning: Setting a Sequence as Default

  1. Click inside the the Query Editor and press Ctrl + A
  2. Type the following:
    USE University6;
    GO
    
    CREATE TABLE Academics.UndergraduateStudents
    (
        StudentID int not null
    	DEFAULT (NEXT VALUE FOR Academics.SeqStudents),
        StudentNumber nvarchar(8) not null,
        FirstName nvarchar(20),
        MiddleName nvarchar(20),
        LastName nvarchar(20),
        BirthDate date,
        Gender nvarchar(3) default N'N/A',
        MajorID int not null
    );
    GO
    
    INSERT INTO Academics.UndergraduateStudents(StudentNumber, FirstName, MiddleName, LastName, BirthDate, Gender, MajorID)
    VALUES(N'88130480', N'Marie', N'Annette', N'Robinson', Administration.SetDateOfBirth(-6817), N'F', 1021),
          (N'24795711', N'Roger', N'Dermot',  N'Baker',    Administration.SetDateOfBirth(-6570), N'M', 1005);
    GO
    INSERT INTO Academics.UndergraduateStudents(StudentNumber, FirstName, LastName, BirthDate, Gender, MajorID)
    VALUES(N'18073572', N'Patrick', N'Wisne', Administration.SetDateOfBirth(-11012), N'M', 1001);
    GO
    INSERT INTO Academics.UndergraduateStudents(StudentNumber, FirstName, MiddleName, LastName, BirthDate, Gender, MajorID)
    VALUES(N'22803048', N'Gary', N'Jonathan', N'Jones', Administration.SetDateOfBirth(-19926), N'M', 1019),
          (N'97394285', N'Jessica', N'Danielle', N'Weisburgh', Administration.SetDateOfBirth(-12081), N'F', 1009),
          (N'97596002', N'Laurent', N'Frank', N'Simonson', Administration.SetDateOfBirth(-17503), N'M', 1016),
          (N'94708257', N'Christopher', N'Sheldon', N'Dale', Administration.SetDateOfBirth(-6570),  N'M', 1006),
          (N'48009520', N'Diane', N'Kathy', N'Paglia', Administration.SetDateOfBirth(-13840), N'F', 1006),
          (N'13048039', N'Joseph', N'Christian', N'Riback', Administration.SetDateOfBirth(-7909),  N'M', 1011),
          (N'92270397', N'Patrick', N'Jonathan', N'Brzeniak', Administration.SetDateOfBirth(-17361), N'M', 1021);
    GO
    INSERT INTO Academics.UndergraduateStudents(StudentNumber, FirstName, LastName, BirthDate, Gender, MajorID)
    VALUES(N'70840584', N'Tracy', N'Sikorowski', Administration.SetDateOfBirth(-11650), N'M', 1006);
    GO
    
    
  3. Right-click anywhere in the window and click Execute
  4. Click inside the Query Editor and press Ctrl + A
  5. To see a list of students, type the following:
    SELECT Students.StudentID ID,
           Students.StudentNumber [Student #],
           Students.FirstName [First Name],
           Students.MiddleName [Middle Name],
           Students.LastName [Last Name],
           FORMAT(Students.BirthDate, N'D') [Birth Date],
           DATEDIFF(yyyy, BirthDate, SYSDATETIME()) Age,
           CASE Students.Gender
    		WHEN N'M' THEN N'Male'
    		WHEN N'F' THEN N'Female'
    		ELSE N'Unknown'
           END Gender,
           MajorID Major
    FROM Academics.UndergraduateStudents Students
    ORDER BY Students.StudentID;
    GO
  6. To execute, press F5
  7. Close Microsoft SQL Server
  8. If asked whether you want to save the file, click No

Exercises

   

Lesson Summary Questions

  1. If you add an expression to a column but want the value to be stored in the table, what keyword would you add to the column?
    1. PERSISTED
    2. DEFAULT
    3. PERSISTANT
    4. EXPRESSION
    5. SERIALIZE
  2. Which of the following is a valid way to create an expression?
    1. CREATE TABLE Circle
      (
          CircleID int identity(1,1) NOT NULL,
          Radius decimal(8, 3) NOT NULL,
          Area DEFAULT Radius * Radius * PI()
      );
      GO
    2. CREATE TABLE Circle
      (
          CircleID int identity(1,1) NOT NULL,
          Radius decimal(8, 3) NOT NULL,
          Area = Radius * Radius * PI()
      );
      GO
    3. CREATE TABLE Circle
      (
          CircleID int identity(1,1) NOT NULL,
          Radius decimal(8, 3) NOT NULL,
          Area AS Radius * Radius * PI()
      );
      GO
    4. CREATE TABLE Circle
      (
          CircleID int identity(1,1) NOT NULL,
          Radius decimal(8, 3) NOT NULL,
          Area(Radius * Radius * PI())
      );
      GO
    5. CREATE TABLE Circle
      (
          CircleID int identity(1,1) NOT NULL,
          Radius decimal(8, 3) NOT NULL,
          Area HAVING(Radius * Radius * PI())
      );
      GO
  3. Which of the following is a valid way to specify the default value of a column?
    1. CREATE TABLE Students
      (
          FirstName nvarchar(20),
          LastName nvarchar(20),
          Gender DEFAULT = N'Unknown'
      );
      GO
    2. CREATE TABLE Students
      (
          FirstName nvarchar(20),
          LastName nvarchar(20),
          Gender SET DEFAULT = N'Unknown'
      );
      GO
    3. CREATE TABLE Students
      (
          FirstName nvarchar(20),
          LastName nvarchar(20),
          Gender WITH DEFAULT AS N'Unknown'
      );
      GO
    4. CREATE TABLE Students
      (
          FirstName nvarchar(20),
          LastName nvarchar(20),
          Gender nvarchar(20) DEFAULT N'Unknown'
      );
      GO
    5. CREATE TABLE Students
      (
          FirstName nvarchar(20),
          LastName nvarchar(20),
          Gender nvarchar(20),
          CONSTRAINT D_Gender DEFAULT(N'Unknown')
      );
      GO
  4. Which of the following codes will create a column that uses an expression?
    1. CREATE TABLE Employees
      (
          FirstName nvarchar(20),
          LastName nvarchar(20),
          FullName = FirstName + N' ' + LastName
      );
    2. CREATE TABLE Employees
      (
          FirstName nvarchar(20),
          LastName nvarchar(20),
          FullName AS FirstName + N' ' + LastName
      );
    3. CREATE TABLE Employees
      (
          FirstName nvarchar(20),
          LastName nvarchar(20),
          FullName SET AS FirstName + N' ' + LastName
      );
    4. CREATE TABLE Employees
      (
          FirstName nvarchar(20),
          LastName nvarchar(20),
          FullName nvarchar(41) WITH FullName = FirstName + N' ' + LastName
      );
    5. CREATE TABLE Employees
      (
          FirstName nvarchar(20),
          LastName nvarchar(20),
          FullName nvarchar(41),
          CONSTRAINT E_FullName EXPRESSION(FullName = FirstName + N' ' + LastName)
      );
  5. Which of the following are keywords in Transact-SQL?
    1. EACH
    2. PRESERCE
    3. DEFAULT
    4. UNIQUE
    5. DO
  6. What's the name of the permission that allows a user to successfully call a function?
    1. SELECT
    2. CALL
    3. PRODUCE
    4. GET
    5. EXECUTE
  7. Which of the following two are permissions of a function (Select 2)?
    1. DELETE
    2. STORE
    3. ALTER
    4. CREATE
    5. REFERENCES
  8. Which of the following three are permissions of a function (Select 3)?
    1. CONTROL
    2. TAKE OWNERSHIP
    3. INSERT
    4. VIEW DEFINITION
    5. UPDATE

Answers

  1. Answers
    1. Right Answer: The PERSISTED keyword asks the database engine to save the value of a computed column
    2. Wrong Answer: The DEFAULT keyword has nothing to do with storing a
    3. Wrong Answer: There is no PERSISTANT keyword in Transact-SQL
    4. Wrong Answer: There is no EXPRESSION keyword in Transact-SQL
    5. Wrong Answer: There is no SERIALIZE keyword in Transact-SQL
  2. Answers
    1. Wrong Answer: The DEFAULT keyword is not used
    2. Wrong Answer: The assignment operator cannot be used
    3. Right Answer: That will work
    4. Wrong Answer: The parentheses are not used
    5. Wrong Answer: There is no place for HAVING here
  3. Answers
    1. Wrong Answer: You don't assign the value
    2. Wrong Answer: The SET keyword is not used
    3. Wrong Answer
    4. Right Answer: After the DEFAULT keyword, simply provide the desired value
    5. Wrong Answer
  4. Answers
    1. Wrong Answer: The assignment operator is not valid
    2. Right Answer: The FullName column will combine the first name, a space, and the last name for its value
    3. Wrong Answer: The SET AS expression has no place here
    4. Wrong Answer: The WITH keyword in not valid in this context
    5. Wrong Answer: There is no reason to use HAVING in this type of expression
  5. Answers
    1. Wrong Answer
    2. Wrong Answer
    3. Right Answer
    4. Right Answer
    5. Wrong Answer
  6. Answers
    1. Wrong Answer
    2. Wrong Answer
    3. Right Answer
    4. Wrong Answer
    5. Right Answer
  7. Answers
    1. Wrong Answer
    2. Wrong Answer
    3. Right Answer
    4. Wrong Answer
    5. Right Answer
  8. Answers
    1. Right Answer
    2. Right Answer
    3. Wrong Answer
    4. Right Answer
    5. Wrong Answer

Practice Questions

  1. Create

Previous Copyright © 2012-2013 FunctionX Next