SQLConfiguration SQLConfiguration


Table Column Attributes  «Prev  Next»
Lesson 9

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:
  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)

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

Glossary terms

This module introduced you to the following terms:
  1. Datatype
  2. Precision
  3. Scale
  4. Default
  5. Identity
  6. GUID
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