Microsoft.Press.Training.Kit.Exam.70-461.Nov.2012

It is common to define the table constraints later, after the table is created, using an ALTER TABLE command. You’ll take a look at each of these in order, starting with the required ele- ments, in the following sections. Specifying a Database Schema Every table belongs to a grouping of objects within a database called a database schema. The database schema is a named container (a namespace) that you can use to group tables and Key other database objects. For the TSQL2012 table Production.Categories, the database schemaTerms is Production. The primary purpose of a database schema is to group many database objects, such as tables, together. In the case of tables, a database schema also allows many tables with the same table name to belong to different schemas. This works because the database schema becomes a part of the table's name and helps identify the table. If you don't supply a data- base schema name when you create a table, SQL Server will supply one based on your data- base user name's default schema. Important  Database Schema and Table Schema Do not confuse the term database schema with table schema. A database schema is a database-wide container of objects. A table schema is the definition of a table that includes the CREATE TABLE statement with all the column definitions. For example, look at the following query. SELECT TOP (10) categoryname FROM Production.Categories; The name Production.Categories specifies the table name within the database. There could be other objects in the same database name Categories, but only one object with that name can exist in the Production database schema. So to exactly specify the name of a table, you must supply the database schema name. The following four built-in database schemas cannot be dropped: ■■ The dbo database schema is the default database schema for new objects created by users having the db_owner or db_ddl_admin roles. ■■ The guest schema is used to contain objects that would be available to the guest user. This schema is rarely used. ■■ The INFORMATION_SCHEMA schema is used by the Information Schema views, which provide ANSI standard access to metadata. ■■ The sys database schema is reserved by SQL Server for system objects such as system tables and views. An additional set of database schemas are named after the built-in database roles, and though they can be dropped, they are meant to pair up with the database roles. They are also seldom used. Lesson 1: Creating and Altering Tables Chapter 8 269

Before SQL Server 2005, when the user names that owned objects were the same as schemas, it was common to assign objects to the dbo owner when they needed to be shared across all users. Beginning with SQL Server 2005, you can create schemas that have no intrinsic relationship to users and can serve to give a finer-grained permissions structure to the tables of a database. For example, in the TSQL2012 database, you will notice four user- defined database schemas: HR, Production, Sales, and Stats. Notice that when you view a table list in SQL Server Management Studio (SSMS), every table has two parts to its name: the database schema followed by the table name within the schema, such as Production.Categories. Note  Database Schemas Are Not Nested There can be only one level of database schema; one schema cannot contain another schema. Every database schema must be owned by exactly one authorized database user. That da- tabase schema owner can then grant permissions to other users regarding the objects in this schema. For example, the following statement creates the Production database schema. CREATE SCHEMA Production AUTHORIZATION dbo; GO The schema named Production is actually owned by the user named dbo, not by the dbo database schema. This allows one user (for example, dbo) to own many different database schemas. Exam Tip You can move a table from one schema to another by using the ALTER SCHEMA TRANSFER statement. Assuming there is no object named Categories in the Sales database schema, the following statement moves the Production.Categories table to the Sales database schema. ALTER SCHEMA Sales TRANSFER Production.Categories; To move the table back, issue the following. ALTER SCHEMA Production TRANSFER Sales.Categories; Naming Tables and Columns You are free to choose a wide variety of names for schemas, tables, and columns. However, there are some important restrictions and best practices, as detailed in this section. All schema, table, and column names must be valid SQL Server identifiers. Identifiers must be at least one character long and no longer than 128 characters. There are two types of identifiers: regular and delimited.2 70 Chapter 8 Creating Tables and Enforcing Data Integrity

Regular identifiers are names that follow a set of rules and don't need to be surrounded by delimiters like square brackets ([ ]) or quotation marks (the single character \"). For regular identifiers, the characters can be: ■■ Letters as defined in the Unicode Standard 3.2. ■■ Decimal numbers from either Basic Latin or other national scripts. The first character must be a letter defined in the Unicode Standard 3.2 or an underscore (_), and cannot be a digit. However, there are two exceptions: ■■ Variables must begin with an at sign (@). ■■ Temporary tables or procedures must begin with a number sign (#). Subsequent identifier characters can include: ■■ Letters as defined in the Unicode Standard 3.2. ■■ Numerals from Basic Latin (0 through 9) or other collations. ■■ The at sign (@), the dollar sign ($), the number sign (#), and the underscore (_). A regular identifier cannot be a T-SQL reserved word and cannot include embedded spaces or special characters other than those previously mentioned. For example, the table named Production.Categories uses two valid regular identifiers: Production as the schema name, and Categories as the table name. Note  Use Regular Identifiers When Possible Even though you can embed special characters such as @, #, and $ in an identifier for a schema, table, or column name, that action makes the identifier delimited, no longer regu- lar. Generally, it is a best practice to use regular identifiers, using just letters, numbers, and underscores. Then users do not need delimiters to refer to the object names. Some T-SQL developers like to embed underscores between names, to help readability. For example, they might write the column categoryid as category_id. Delimited identifiers are names that do not adhere to the rules for regular identifiers. There is no restriction on what characters can be embedded in them, but when they do not obey the rules for regular identifiers, you must use either square brackets or quotation marks as delimiters in order to reference them. In T-SQL, square brackets can always be used for delimited identifiers. Using quotation marks as delimiters is the ANSI SQL standard. However, use of quotation marks as delimiters requires that the SET QUOTED_IDENTIFIER setting is set to ON, which is the SQL Server default. Because it is possible to turn that setting to OFF, using quotation marks is risky. For example, you could create a table as follows. CREATE TABLE Production.[Yesterday's News] … Lesson 1: Creating and Altering Tables Chapter 8 271

Or you could write it in the following way. CREATE TABLE Production.\"Tomorrow's Schedule\" … Because of the embedded space and apostrophe, these are not regular identifiers and they require the use of delimiters. Note  Regular Identifiers Are More User-Friendly Even though you can use square brackets as delimiters, it is a best practice to always make sure those names follow the rules for regular identifiers. That way, if one of your users does not use the delimiters in a query, their query will still succeed. When choosing the name of schemas, tables, and columns, it is a best practice to follow your organization’s or project’s naming guidelines. Note  Do Not Make Object Names Very Long Don't make schema, table, or column names too long. Organizations often make it part of the naming convention for constraint and index names to include the table name and the names of the columns used as keys in the constraint or index name. Because constraint and index names must also be identifiers, they cannot exceed the maximum identifier length of 128 characters. Generally, the best practice is to make your schema, table, and column names short but descriptive. Also, avoid abbreviations unless they are really necessary or commonly under- stood. For example, the column name categoryid uses the abbreviation id (short for identifi- cation), but it is so common that there's little risk of being misunderstood. Choosing Column Data Types The data type used for each column is very important. For full information about data types, see Lesson 2, “Working with Data Types and Built-in Functions,” in Chapter 2, \"Getting Started with the SELECT Statement.\" Here are some brief guidelines that you can use for choosing data types for columns: ■■ Try to use the most efficient data type: one that requires the least amount of disk stor- age and adequately captures the data, and won't need to be changed later on when the table fills with data. ■■ When you need to store character strings, if they will likely vary in length, use the NVARCHAR or VARCHAR data types rather than the fixed NCHAR or CHAR. If the col- umn value might be updated often, and especially if it is short, using the fixed length can prevent excessive row movement.2 72 Chapter 8 Creating Tables and Enforcing Data Integrity

■■ The DATE, TIME, and DATETIME2 data types can store data more efficiently and with better precision than DATETIME and SMALLDATETIME. ■■ Use VARCHAR(MAX), NVARCHAR(MAX), and VARBINARY(MAX) instead of the depre- cated TEXT, NTEXT, and IMAGE data types. ■■ Use ROWVERSION instead of the deprecated TIMESTAMP. ■■ DECIMAL and NUMERIC are the same data type, but generally people prefer DECI- MAL because the name is a bit more descriptive. Use DECIMAL and NUMERIC instead of FLOAT or REAL data types unless you really need floating-point precision and are familiar with possible rounding issues. NULL and Default Values How to handle unknowns is a difficult problem in database theory and is just as difficult in database design. When you cannot enter data into a particular column of a row, how do you indicate that? T-SQL follows the ANSI SQL standard in allowing one non-value property of a column called NULL. NULL is not the value of a column; it's just a way of saying the value is completely and totally unknown. You can specify whether a column allows NULL by stating NULL or NOT NULL right after the column’s data type. NULL means the column allows NULLs, and NOT NULL means it does not allow NULLs. Use the following guidelines: ■■ If you know that a value for a column must be optional because sometimes no value is known at the time the row will be inserted, then define the column as NULL. ■■ If you don't want to allow NULL in the column, but you do want to specify some de- fault value to indicate that the column has not yet been populated, you can specify a DEFAULT constraint by adding the DEFAULT clause right after saying NOT NULL. For example, you could indicate that the values for the description column in the Production.Categories table are not yet entered by supplying an empty string (two single quotation marks with no space between them: '') as the default value. CREATE TABLE Production.Categories( categoryid INT IDENTITY(1,1) NOT NULL, categoryname NVARCHAR(15) NOT NULL, description NVARCHAR(200) NOT NULL DEFAULT ('') ) ON [PRIMARY]; GO Now if the application inserts a row with a new category, the user does not need to add a description immediately but can return later to update the row with the description. For more information about default values, see “Default Constraints” in Lesson 2. The Identity Property and Sequence Numbers In T-SQL, the Identity property can be assigned to a column in order to automatically gener- ate a sequence of numbers. You can use it for only one column of a table, and you can specify both seed and increment values for the number sequence generated. Lesson 1: Creating and Altering Tables Chapter 8 273

When you define the property in a CREATE TABLE statement, you can specify a seed value(that is, the value to begin with), and then an increment amount (that is, the amount to incre-ment each new sequence number by). The most common values for seed and increment are(1,1) as shown in the following example from the TSQL2012 Production.Categories table.CREATE TABLE Production.Categories( categoryid INT IDENTITY(1,1) NOT NULL, … Many of the TSQL12 tables have primary key columns with identity properties. SQL Server 2012 introduces an optional way to generate sequence numbers by using se-quence objects. You can use sequence objects as an optional way to generate unique numericvalues in a table. However, because sequence objects behave differently from the Identityproperty, they may or may not be a good substitute for the Identity property. For more information about the Identity property and sequence objects, see Lesson 1,“Using the Sequence Object and IDENTITY Column Property,” in Chapter 11, “Other DataModification Aspects.”Computed ColumnsYou can also define columns as values that are computed based on expressions. These expres-sions could be based on the value of other columns in the row or based on T-SQL functions.For example, you might query the data in the table Sales.OrderDetails and realize that twocolumns can be multiplied together, unitprice and qty, to get the initial cost of the order detailline (before applying the discount). You could compute this in a SELECT statement as follows.SELECT TOP (10) orderid, productid, unitprice, qty, unitprice * qty AS initialcost -- expressionFROM Sales.OrderDetails; You can take that expression, unitprice * qty AS initialcost, and embed it in the CREATETABLE statement as a computed column, as follows.CREATE TABLE Sales.OrderDetails( orderid INT NOT NULL,… initialcost AS unitprice * qty -- computed column); Also, you can make the computed column persisted, meaning that SQL Server will storethe computed values with the table's data, and not compute the values on the fly. However,if a computed column is to be persisted, the column cannot make use of any functions thatare not deterministic, which means that the expression cannot reference various dynamicfunctions like GETDATE() or CURRENT_TIMESTAMP. For more information about deterministicfunctions, see \"Deterministic and Nondeterministic Functions\" at http://msdn.microsoft.com/en-us/library/ms178091.aspx.2 74 Chapter 8 Creating Tables and Enforcing Data Integrity

Table CompressionYou can compress the data in a table, in addition to the indexes, to get more efficient storage,if you use the Enterprise edition of SQL Server 2012 (in addition to SQL Server 2008 and SQLServer 2008 R2.) Table compression has two levels: ■■ Row For row-level compression, SQL Server applies a more compact storage format to each row of a table. ■■ Page  Page-level compression includes row-level plus additional compression algo- rithms that can be performed at the page level. The following command adds row-level compression to the Production.OrderDetails tableas part of the CREATE TABLE statement.CREATE TABLE Sales.OrderDetails( orderid INT NOT NULL,… ) WITH (DATA_COMPRESSION = ROW); To change the command to apply page compression, just state DATA_COMPRESSION =PAGE. You can also use the ALTER command to alter a table to set its compression.ALTER TABLE Sales.OrderDetailsREBUILD WITH (DATA_COMPRESSION = PAGE); SQL Server provides the sp_estimate_data_compression_savings stored procedure to helpyou determine whether a table with data in it would benefit from compression. For moreinformation about table compression, see “Data Compression” at http://msdn.microsoft.com/en-us/library/cc280449.aspx and “sp_estimate_data_compression_savings (Transact-SQL)” athttp://msdn.microsoft.com/en-us/library/cc280574.aspx. Quick Check 1. Can a table or column name contain spaces, apostrophes, and other nonstan- dard characters? 2. What types of table compression are available? Quick Check Answer 1. Yes, table and column names can be delimited identifiers that contain nonstan- dard characters. 2. You can use either page or row compression on a table. Page compression includes row compression. Lesson 1: Creating and Altering Tables Chapter 8 275