Column Attributes for a Table 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
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
There are five ways to generate GUID primary key values when inserting new rows:
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:
- 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() .
This module introduced you to the following terms:
In the next module, you will learn how cursors can help you manipulate data in SQL Server recordsets.
Sql Server Column Attributes - Quiz