Introduction
Default values
How to auto-generate values in a database
Summary
18. Summary

Instruction

Great! We've reached the final section for this part. Let's review what we've learned.

A database architect can define a default value for a column in a table. If that's the case, you can use default value:

  • In an INSERT statement:
  • INSERT INTO Author (Id, FirstName, LastName, Photo, CreateDateTime, IsActive) 
    VALUES (120, N'Gary', N'Brown', N'imgs/gary.jpg', '2018-08-25', DEFAULT);
  • By omitting columns in INSERT:
  • INSERT INTO Author (Id, FirstName, LastName) 
    VALUES (121, N'Mary', N'Taylor');
  • During an UPDATE operation:
  • UPDATE Author
    Photo = DEFAULT
    WHERE Id = 120;

Plus, you learned how to auto-generate unique values by:

  • Using IDENTITY and omitting the name of the Id column:
  • INSERT INTO Author(FirstName, LastName) 
    VALUES (N'Stanley', N'Lewis');

    The Id column, which isn't listed in the query, uses IDENTITY to auto-generate values unique to this column.

  • Using NEWID():
  • INSERT INTO Author (Id, FirstName, LastName,  Photo)
    VALUES (NEWID(),  N'Eva', N'Rayan', N'imgs/lisa3.jpg');
  • Using a sequence:
  • INSERT INTO Author (Id, FirstName, LastName, IsActive) 
    VALUES (NEXT VALUE FOR IdSeq,  N'Miriam', N'Clerk', 1);

    Auto-generated values from the same sequence can be used in multiple tables, and each value will be unique.

Exercise

Well done! You've completed Part 3 and learned various ways how SQL Server can automatically generate values.

Click Next exercise to finish this part.