Chapter 8 Creating Tables and Enforcing Data Integrity

Question 1

What is the main method used for storing data?

Answer 1

Tables (Base tables). When you query a database for data, ultimately, that data is located in tables.

Question 2

How do tables work with the SQL Server backup/restore process?

Answer 2

When you back up a database, all its tables are backed up, and when you restore the database, all those tables are restored with the same data they had when the backup occurred.

Question 3

What are the system tables?

Answer 3

They store system data for SQL Server in specially reserved tables called system tables.

Question 4

What is a temporary table?

Answer 4

Tables that exist in tempdb and last only as long as a session or scope referencing them endures.

Question 5

What are table variables?

Answer 5

Variables that can store table data but only for the duration of a T-SQL batch.

Question 6

What are views?

Answer 6

Views appear just like tables but they do not store any data. The are derived from queries against tables.

Question 7

What are indexed views?

Answer 7

Indexed views store data but are defined as views and are updated whenever the base tables are updated.

Question 8

What are derived tables and table expressions?

Answer 8

Subqueries that are referenced like tables in queries.

Question 9

What are the two ways to create a table in T-SQL?

Answer 9

(1) By using the CREATE TABLE statement where you explicitly define the components of the table. (2) By using the SELECT INTO statement which creates a table automatically by using the output of a query for the basic table definition.

Question 10

What options are allowed in the CREATE TABLE statement?

Answer 10

(1) Schema name, (2) Table name, (3) Table columns and for each column: data type, data type length/precision, special types of columns (computed, IDENTITY), collation (4) Constraints, (5) Storage options (file group, partition schema, table compression).

Question 11

What is a database schema?

Answer 11

A database schema is a named container (a namespace) that you can use to group tables and other database objects. A database schema also allows many tables with the same table name to belong to different schemas. This works because the database schema becomes part of the table’s name and helps identify the table. You should always reference objects by using a two-part name (w/ both schema and table name).

Question 12

What happens if you create a table without specifying the database schema?

Answer 12

SQL Server will fill in the database schema with your user name’s default schema.

Question 13

What are four built in database schemas that cannot be dropped?

Answer 13

(1) dbo - The default database schema for new objects created by users having db_owner or db_ddl_admin roles. (2) guest - The schema used to contain objects that would be available to the guest user - rarely used. (3) INFORMATION_SCHEMA - This schema is used by the Information Schema views which provide ANSI standard access to metadata, (4) sys - The sys schema is reserved by SQL Server for system objects such as system tables and views. Also, there are an additional set of database schemas that are named after the built-in database roles.

Question 14

Can you create schemas that aren’t linked to users?

Answer 14

Yes. Starting with SQL 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.

Question 15

Can a database schema contain another database schema?

Answer 15

No. There can only be one level of database schema; one schema cannot contain another.

Question 16

How many users can own a database schema?

Answer 16

Every database schema must be owned by exactly one authorized database user. That database schema owner can then grant permissions to other users regarding objects in the schema. One user can own many different database schemas.

Question 17

How do you create a database schema and define who owns it?

Answer 17

CREATE SCHEMA Production AUTHORIZATION dbo;

Question 18

How can you move a table from one schema to another?

Answer 18

ALTER SCHEMA Sales TRANSFER Production.Categories; Moves the Production.Categories table to the Sales database schema. To move it back, use: ALTER SCHEMA Production TRANSFER Sales.Categories;

Question 19

What are the length restrictions on SQL Server identifiers?

Answer 19

Identifiers must be one character long and no longer than 128 characters.

Question 20

What are the two types of SQL Server identifiers?

Answer 20

Regular and Delimited. Regular identifiers are names that follow a set of rules and don’t need to be surrounded by delimiters like square brackets ([]) or quotes (‘’). Delimited identifiers are names that do not adhere to the rules for regular identifiers. You must use delimiters in order to reference them.

Question 21

What are the rules for regular identifiers?

Answer 21

A regular identifier must have as the first character a letter in the range A-Z (upper or lower), _, @, or #. Variables must begin with an at (@) sign and temporary tables or procedures must begin with a number sign (#). Subsequent characters can include letters, numbers, @, $, #, _. The identifier must not be a reserved keyword in SQL. The identifier must not have spaces.

Question 22

What are the rules for delimited identifiers?

Answer 22

There is no restriction on what characters can be embedded in them as long as they are delimited, e.g. [Yesterday’s News].

Question 23

What’s the difference between the brackets and quotes delimiters?

Answer 23

Using quotes as delimiters is the ANSI SQL standard. However, use of quotes requires SET QUOTED_IDENTIFIER is set to ON which is the SQL Server default. Because it’s possible to turn that setting to OFF, using quotation marks is risky. In T-SQL, square brackets can always be used for
delimited identifiers.

Question 24

Why is it a best practice to use regular identifiers?

Answer 24

If one of your users does not use the delimiters in a query, their query will still succeed.

Question 25

When should you use N/VARCHAR vs N/CHAR data types?

Answer 25

When you need to store character strings, if they will likely vary in length, use the NVARCHAR or VARCHAR data type rather than the fixed NCHAR or CHAR. If the column value might be updated often, and especially if it is short, using the fixed length can prevent excessive row movement.

Question 26

When should you use DATETIME vs DATETIME2 data types?

Answer 26

The DATE, TIME, and DATETIME2 data types can store data more efficiently and with better precision than DATETIME and SMALLDATETIME.

Question 27

When should you use TEXT, NTEXT, and IMAGE data types?

Answer 27

Never. They are deprecated. Instead, use the VARCHAR(MAX), NVARCHAR(MAX), and VARBINARY(MAX).

Question 28

When should you use DECIMAL/NUMERIC vs FLOAT/REAL?

Answer 28

DECIMAL and NUMERIC are the same data type but people generally prefer DECIMAL because the name is a bit more descriptive. Use DECIMAL/NUMERIC instead of FLOAT/REAL data types unless you really need floating-point precision and are familiar with the error and possible rounding issues.

Question 29

When should you use ROWVERSION vs TIMESTAMP?

Answer 29

TIMESTAMP is deprecated. Use ROWVERSION to version-stamp table rows. It is just an incrementing number and does not preserve a date or time.

Question 30

How can you specify whether a column allows NULLs or not?

Answer 30

You simply state 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. If a value for a column is optional because no value is known at the time the row is inserted, then define the column as NULL.

Question 31

What should you use if you don’t want to allow NULL in the column but you do want to specify some default value to indicate the column has not been populated?

Answer 31

Use a DEFAULT constraint by adding the DEFAULT clause right after saying NOT NULL, e.g.

CREATE TABLE Production.Categories
(
description VARCHAR(200) NOT NULL DEFAULT (‘’)
)

Question 32

What does the IDENTITY property on a column do?

Answer 32

The IDENTITY property can be assigned to a column in order to automatically generate a sequence of numbers. You can use it for only one column of a table and you can specify both seed (value to begin with) and increment (amount to increment each new number by) values for the number sequence generated. The most common pair is (1,1). e.g.

CREATE TABLE Production.Categories
(
categoryid INT IDENTITY(1,1) NOT NULL
)

Question 33

What is the sequence object?

Answer 33

You can use sequence objects as an optional way to generate unique numeric values in a table.

Question 34

What are computed columns?

Answer 34

Table columns can be defined as values that are computed based on expressions. These expressions could be based on the value of other columns in the row or based on T-SQL functions, e.g.

CREATE TABLE Sales.OrderDetails
(
initialcost AS unitprice * qty
)

Also, you can make the computed column persisted, i.e. SQL will store the computed values with the table’s data and not compute the values on the fly. If persisted, the column cannot make use of any functions that are non-deterministic which means that the expressions cannot reference various dynamic functions like GETDATE() or CURRENT_TIMESTAMP.

Question 35

What are the two levels of table compression available?

Answer 35

(1) Row - SQL Server applies a more compact storage format to each row of a table and (2) Page - SQL Server applies row-level compression plus additional compression algorithms that can be performed at the page level.

Question 36

How do you add row/page level compression to a table?

Answer 36

CREATE TABLE
(
...
)
WITH (DATA_COMPRESSION = ROW/PAGE);

or you can use:

ALTER TABLE Sales.OrderDetails
REBUILD WITH (DATA_COMPRESSION = PAGE);

Question 37

How can you determine whether a table with data in it would benefit from compression?

Answer 37

SQL provides the sp_estimate_data_compression_savings stored procedure.

Question 38

What parts of a table definition can be changed with the ALTER TABLE statement?

Answer 38

(1) Add and/or remove columns (new columns are placed at the end of the table’s column order), (2) Change properties of a column (data type, nullability, constraints).

Question 39

What parts of a table definition cannot be changed with the ALTER TABLE statement?

Answer 39

You cannot use ALTER TABLE to (1) change a column’s name, (2) add an identity property, (3) remove an identity property.

Question 40

How do you use ALTER TABLE to add a column?

Answer 40

ALTER TABLE Production.CategoriesTest

ADD categoryname VARCHAR(15) NOT NULL;

Question 41

What is IDENTITY_INSERT_ON/OFF?

Answer 41

Allows a row to be inserted with an explicit identity value via INSERT statement.

Question 42

What are the 6 different types of constraints?

Answer 42

(1) NULL, (2) CHECK, (3) DEFAULT, (4) PK, (5) FK, (6) UNIQUE

Question 43

What is declarative data integrity?

Answer 43

When you embed methods of data validation inside the definition of the table itself.

Question 44

What is the best way to enforce data integrity in tables?

Answer 44

Creating or declaring constraints. You apply these constraints to a table and its columns using the CREATE TABLE/ALTER TABLE statements.

Question 45

Do constraints require unique names across the database?

Answer 45

Yes. All table constraints are database objects, therefore, they must have unique names.

Question 46

What is a primary key?

Answer 46

A primary key supplies a unique value for each row and provides a method of distinguishing each row from all the others. It could be one or more columns - most often, a single column. There can only be one primary key per table.

Question 47

What is a natural/business key?

Answer 47

A column or combination of columns within the domain of the table’s data that uniquely identifies every row (e.g. categoryname in Production.Categories).

Question 48

What is a surrogate key?

Answer 48

A special column with numeric data type (INT) which will have a unique but otherwise meaningless value.

Question 49

Is a natural/business key more appropriate than a surrogate key?

Answer 49

It’s more common to use the surrogate key as the primary key and validate the natural key’s uniqueness using a unique constraint.

Question 50

How do you declare a primary key?

Answer 50

You can declare a primary key with CREATE TABLE or ALTER table statements, e.g.

CREATE TABLE Production.Categories
(
   categoryid INT NOT NULL IDENTITY,
   ...
   CONSTRAINT PK_Categories PRIMARY KEY(categoryid)
)

or

ALTER TABLE Production.Categories
ADD CONSTRAINT PK_Categories PRIMARY KEY(categoryid);

Question 51

What are the requirements to create a primary key?

Answer 51

To create a primary key on a column, there are 3 requirements:

(1) The column cannot allow NULL.
(2) Any data already in the table must have unique values in the primary key column.
(3) There can only be one primary key constraint in a table at a time.

Question 52

What happens behind the scenes when you create a primary key?

Answer 52

SQL Server enforces the constraint behind the scenes by creating a unique index on that column and using the primary key column as the keys of the index.

Question 53

How can you list the primary key constraints in a database?

Answer 53

SELECT *
FROM sys.key_constraints
WHERE type = ‘PK’

Question 54

How can you find the unique index that SQL Server uses to enforce a primary key constraint?

Answer 54

SELECT *
FROM sys.indexes
WHERE object_id=OBJECT_ID(‘Production.Categories’) AND name = ‘PK_Categories’

Question 55

What is a unique constraint?

Answer 55

A unique constraint is very similar to a primary key constraint but are better for validating uniqueness on natural keys.

Question 56

How do you declare/drop a unique constraint?

Answer 56

ALTER TABLE Production.Categories
ADD CONSTRAINT UC_Categories UNIQUE (categoryname);

ALTER TABLE Production.Products
DROP CONSTRAINT U_Productname;

Question 57

What happens behind the scenes when you create a unique constraint?

Answer 57

SQL Server automatically creates a unique index with the same name as the constraint. By default, the index will be non-clustered. SQL Server uses that index to enforce the uniqueness of the column.

Question 58

Does a unique constraint require the column to be NOT NULL?

Answer 58

No. You can allow NULL in a column and still have a unique constraint, but only one row can be NULL.

Question 59

Can you create a PK/Unique Constraint on a computed column?

Answer 59

Yes.

Question 60

What are the size limitations of a PK/Unique Constraint as an index?

Answer 60

You can combine no more than 16 columns as the key columns of the index, and there is a maximum combined width of 900 bytes across those columns.

Question 61

How can you list unique constraints in a database?

Answer 61

By querying the sys.key_constraints table filtering on a type of ‘UQ’, e.g.

SELECT *
FROM sys.key_constraints
WHERE type=’UQ’;

Question 62

How does SQL Server enforce uniqueness in both primary key and unique constraints?

Answer 62

SQL Server uses unique indexes to enforce uniqueness for both primary key and unique constraints.

Question 63

What is a foreign key?

Answer 63

A fk is a column or combination of columns in one table that serve as a link to look up data in another table. You can use fk constraints to enforce that every entry into the fk column is a valid value in the lookup table.

Question 64

How do you create/drop a foreign key?

Answer 64

ALTER TABLE Production.Products WITH CHECK
ADD CONSTRAINT FK_Products_Categories FOREIGN KEY(categoryid) REFERENCES Production.Categories (categoryid)

ALTER TABLE Production.Products DROP CONSTRAINT FK_Products_Categories;

Question 65

How does the ADD CONSTRAINT command work?

Answer 65

(1) You always declare the fk on the table for which this key is “foreign”. (2) You can decide whether to allow violations when you create the constraint. Creating a constraint WITH CHECK implies that if there is any data in the table already and if there would be any violations of the constraint, then the ALTER TABLE will fail. (3) Specify the name of the fk constraint (4) After entering the type of constraint, FOREIGN KEY, you then in parenthesis state the column(s) in this table that you are constraining to be validated by a lookup into another table. (5) Then you state what the other table is using REFERENCES along with the columns in parenthesis. The column in the referenced table must be a primary key, unique constraint, or have a unique index.

Question 66

What rules should you keep in mind when creating foreign keys?

Answer 66

(1) The columns must have exactly the same data types and collation (if they have string data type), (2) The columns of the referenced table must have a unique index created on them either implicitly with a pk or unique constraint or explicitly. (3) You can also create fk constraints on computed columns.

Question 67

How can you boost performance on fk joins?

Answer 67

You can create a nonclustered index on the fk in the referencing table. There is already a unique index on the column in the referenced table, but if the referencing table has a lot of rows, it may help SQL Server to resolve the join faster if it can use an index on the big table.

Question 68

How can you find the foreign keys in the database?

Answer 68

SELECT *
FROM sys.foreign_keys
WHERE name=’FK_Products_Categories’

Question 69

What is a check constraint?

Answer 69

With a check constraint, you specify some expression so that SQL Server can constrain the valid values. This goes beyond data type validation and adds additional constraints on the ranges or set of allowable values. The expression can reference other columns in the same row of the table and use built in SQL functions.

Question 70

How do you create a check constraint?

Answer 70

ALTER TABLE Production.Products WITH CHECK
ADD CONSTRAINT CHK_Products_unitprice
CHECK (unitprice >= 0)

Question 71

What are some of the advantages of using check constraints?

Answer 71

(1) Their expressions are similar to the filter expressions in a WHERE clause of a SELECT statement. (2) The constraint is in the table, so it is always enforced, as long as WITH CHECK is specified. (3) They can perform better than the alternative methods of constraining columns such as triggers.

Question 72

What are some things to watch out for when using check constraints?

Answer 72

(1) If the column allows for NULLs, make sure the expression accounts for potential NULLs, (2) You cannot customize the error message from a check constraint as you can if you implemented the constraint using a trigger, (3) A check constraint cannot reference the previous value of a column in the check constraint expression, e.g. if you wanted to enforce a constraint that unitprice could not be increased by more then 25%. If you need to do that, you must use a trigger.

Question 73

How do you list the check constraints for a table?

Answer 73

SELECT *
FROM sys.check_constraints
WHERE parent_object_id = OBJECT_ID(‘Production.Products’);

The parent_object_id is the object_id of the table to which the check constraint belongs.

Question 74

What is a default constraint?

Answer 74

A default constraint supplies a default value during an INSERT if no other value is supplied.

Question 75

When are default constraints useful?

Answer 75

When you have a column in a table that does not allow NULL, but you don’t want to prevent an INSERT from succeeding if it does not specify a value for the column.

Question 76

How do you create a default constraint?

Answer 76

CREATE TABLE Production.Products
(
   unitprice MONEY NOT NULL
      CONSTRAINT DEF_Products_unitprice DEFAULT(0)
)

The default constraint is listed right after the column’s data type. Alternatively, an ALTER TABLE statement could be used to create the constraint.

Question 77

Should default constraints have unique names?

Answer 77

Yes. They are database wide objects. Their names must be unique across the entire database. No two tables can have default constraints named the same.

Question 78

How do you list the default constraints for a table?

Answer 78

SELECT *
FROM sys.default_constraints
WHERE parent_object_id=OBJECT_ID(‘Production.Products’)