Lesson 9

Table Column Attributes Conclusion

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:

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

SQL Server Analysis Services

Globally unique identifiers (GUIDs)

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:

The NEWID() function can create the GUID in T-SQL code prior to the INSERT.
The NEWID() function can create the GUID in client code prior to the INSERT.
The NEWID() function can create the GUID in an expression in the INSERT command.
The NEWID() function can create the GUID in a column default.
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

Glossary terms

This module introduced you to the following terms:

Datatype: The specification for the type of data a column is to represent.
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.
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.
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.
Precision: An attribute of decimal and numeric datatypes that specifies the number of decimal places to the left and right of the decimal point.
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.

Sql Server Column Attributes - Quiz

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