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