Lesson 9

This module discussed how to specify the different attributes available for columns within MS SQL Server 2012 tables. Having completed this module, you should be able to:

1. Specify and identify the correct datatype for your SQL Server columns
2. Specify the length, precision, and scale for your columns
3. Understand when and how to use null and default values
4. Specify identity and RowGuid flags
5. Create and use your own datatypes

Globally unique identifiers (GUIDs) are sometimes, and with great debate, used as primary keys. A GUID can be the best choice when you have to generate unique values at different locations (i.e., in replicated scenarios), but hardly ever otherwise. With regard to the insertion of new rows, the major difference between identity columns and GUIDs is that GUIDs are generated by the SQL code or by a column default, rather than automatically generated by the engine at the time of the insert. This means that the developer has more control over GUID creation.
There are five ways to generate GUID primary key values when inserting new rows:

1. The NEWID() function can create the GUID in T-SQL code prior to the INSERT.
2. The NEWID() function can create the GUID in client code prior to the INSERT.
3. The NEWID() function can create the GUID in an expression in the INSERT command.
4. The NEWID() function can create the GUID in a column default.
5. The NEWSEQUENTIALID() function can create the GUID in a column default. This is the only method that avoids the page split performance issues with GUIDs. If you must use a GUID, then I strongly recommend using NEWSEQUENTIALID() .

The following sample code demonstrates various methods of generating GUID primary keys during the addition of new rows to the ProductCategory table in the OBXKites database. The first query simply tests the NEWID() function:

USE OBXKites;
Select NewID();

Result:

5CBB2800-5207-4323-A316-E963AACB6081

This module introduced you to the following terms:

1. Datatype: The specification for the type of data a column is to represent.
2. Default: A database object that is used to insert a value into a column with which the default is bound, in case the value is omitted in a Transact-SQL statement.
3. GUID: A SQL Server 7 term used to indicate that unique values are automatically generated for each and every row of data that is inserted into a table.
4. Identity: A SQL Server 7 term used to indicate that unique values are automatically generated for each and every row of data that is inserted into a table.
5. Precision: An attribute of decimal and numeric datatypes that specifies the number of decimal places to the left and right of the decimal point.
6. Scale: An attribute of numeric datatypes that specifies the number of decimal places to the right of the decimal point.

In the next module, you will learn how cursors can help you manipulate data in SQL Server recordsets.

Before moving on to the next module, click the Quiz link below on the left to check your knowledge of the material covered in this module.
SQL-Server Column Attributes - Quiz