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, 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. CREATE TABLE: A statement where you explicitly define the components of the table.
2. SELECT INTO: A 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
   
   1. 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, or namespace,

It is used to group tables and objects.

A database schema allows tables with the same name to belong to the same database.

The database schema is part of the table’s name and helps identify the table.

It is a best practice to use both Schema and Table/Object 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.
5. There are additional database schemas 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

A database schema is (and must be) owned by exactly one authorized database user.

The database schema owner grants permissions to other users for objects in the schema.

One user can own many 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;

This moves the table, Production.Categories, into the Sales schema.

It becomes Sales.Categories.

To move it back, use:

ALTER SCHEMA Production TRANSFER Sales.Categories;

Question 19

What are SQL Server identifiers?

What are the length restrictions on SQL Server identifiers?

Answer 19

1. The database object name is referred to as its identifier. Everything in Microsoft SQL Server can have an identifier. Servers, databases, and database objects, such as tables, views, columns, indexes, triggers, procedures, constraints, and rules, can have identifiers. Identifiers are required for most objects, but are optional for some objects such as constraints.
2. Both regular and delimited identifiers must contain from 1 through 128 characters. For local temporary tables, the identifier can have a maximum of 116 characters.

Question 20

What are the two types of SQL Server identifiers?

Answer 20

1. Regular
   
   1. Regular identifiers are names that follow a set of rules and don’t need to be surrounded by delimiters like square brackets ([]) or quotes (‘’).
2. Delimited
   
   1. 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

1. The first character is a letter in the range A-Z (upper or lower), _, @, or #.
2. After the first character, characters can include letters, numbers, @, $, #, _.
3. Must not have spaces.
4. Must not be a reserved keyword in SQL.
5. Variables must begin with the at (@) sign
6. Temporary tables must begin with the number sign (#).

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

1. Quotes are the ANSI SQL standard.
2. In T-SQL the use of quotes requires SET QUOTED_IDENTIFIER is set to ON
   
   1. The SQL Server default is SET QUOTED_IDENTIFIER ON;
   2. Since it is possible for the setting to be OFF, using quotation marks is risky.
3. Square brackets are the T-SQL Standard

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 NVARCHAR vs NCHAR data types?

Answer 25

For character strings that vary in length use the NVARCHAR or VARCHAR data type

For character strings that are frequenntly updated or short use NCHAR or CHAR.

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.

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. DECIMAL is prefered 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

Never use TIMESTAMP. It is deprecated.

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

ROWVERSION is a data type that exposes an automatically generated, unique binary number within a database. The storage size is 8 bytes. The rowversion data type is just an incrementing number and does not preserve a date or a time.

Each database has a counter that is incremented for each insert or update operation that is performed on a table that contains a rowversion column within the database. This counter is the database rowversion. This tracks a relative time within a database, not an actual time that can be associated with a clock. A table can have only one rowversion column. Every time that a row with a rowversion column is modified or inserted, the incremented database rowversion value is inserted in the rowversion column. This property makes a rowversion column a poor candidate for keys, especially primary keys. Any update made to the row changes the rowversion value and, therefore, changes the key value. If the column is in a primary key, the old key value is no longer valid, and foreign keys referencing the old value are no longer valid.

Question 30

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 30

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

CREATE TABLE Production.Categories

(

description VARCHAR(200) NOT NULL DEFAULT (‘’)

)

Question 31

What does the IDENTITY property on a column do?

Answer 31

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

CREATE TABLE Production.Categories

(

categoryid INT IDENTITY(1,1) NOT NULL

)

Question 32

What is the sequence object?

Answer 32

Sequence objects are similar to the IDENTITY column. Unlike the IDENTITY column, they are independent and are not attached to any table. Sequence objects are used both independently and within the DML statements i.e. INSERT, UPDATE and DELETE.

Syntax
CREATE SEQUENCE [schema].[Name_of_Sequence]
[AS <data>]<br></br> [START WITH <value>]<br></br> [INCREMENT BY <value>]<br></br> [MINVALUE <value> | NO MINVALUE]<br></br> [MAXVALUE <value> | NO MAXVALUE]<br></br> [CYCLE | NO CYCLE]<br></br> [CACHE value | NO CACHE];<br></br>The syntax works as follows:</value></value></value></value></data>

1. CREATE SEQUENCE
   
   1. Used to create a sequence followed by a database schema and the name of the sequence
2. AS <bigint> Specifies the data type of the sequence.
   </bigint><ol>
   <li>Data types can be Decimal, Int, SmallInt, TinyInt, and BigInt. </li>
   <li>The default value for the data type is BigInt</li>
   </ol></bigint>
3. START WITH
   
   1. Sets the starting value for the sequence object
4. INCREMENT BY
   
   1. Sets the amount that you want your sequence object to increment by
5. MIN VALUE
   
   1. This is an optional parameter that specifies the minimum value for the sequence object
6. MAX VALUE
   
   1. This is an optional parameter that sets the maximum value for the sequence object
7. CYCLE
   
   1. This specifies if the sequence object should be restarted once it has reached its maximum or minimum value.
   2. It is an optional parameter for which the default value is NO CYCLE
8. CACHE
   
   1. This is used to cache sequence object values.
   2. It is also optional parameter with the default value of NO CACHE

A Simple Example
CREATE SEQUENCE [dbo].[NewCounter7]
AS INT
START WITH 10
INCREMENT BY 10
MINVALUE 10
MAXVALUE 50
CYCLE

Question 33

What are computed columns?

Answer 33

Table columns defined as values computed from expressions. The expressions can be based on other columns in the table’s row or based on T-SQL functions.

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

The computed column can be persisted, where the value is stored instead of calculated on the fly.

Persisted Calculated Columns must be deterministic.

Non-Deterministic Functions, such as, GETDATE() or CURRENT_TIMESTAMP cannot be used.

Question 34

What are the two levels of table compression available?

Answer 34

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

Question 35

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

Answer 35

CREATE TABLE ( … )

WITH (DATA_COMPRESSION = ROW/PAGE);

Or…

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

Question 36

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

Answer 36

Use the

sp_estimate_data_compression_savings

stored procedure.

Question 37

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

Answer 37

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 38

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

Answer 38

1. Cannot change a column’s name
   
   1. –Renaming Column
      EXEC sp_rename‘Schema.Table.Column’,‘NewName’,‘COLUMN’;
      GO
2. Cannot add an identity property
3. Cannot remove an identity property.

Question 39

How do you use ALTER TABLE to add a column?

Answer 39

ALTER TABLE Production.CategoriesTest ADD categoryname VARCHAR(15) NOT NULL;

Question 40

What is IDENTITY_INSERT Schema.Table ON/OFF?

Answer 40

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

Question 41

What are the 6 different types of constraints?

Answer 41

1. NULL
2. CHECK
3. DEFAULT
4. PRIMARY KEY
5. FOREIGN KEY
6. UNIQUE KEY

Question 42

What is declarative data integrity?

Answer 42

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

Question 43

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

Answer 43

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

Question 44

Do constraints require unique names across the database?

Answer 44

Yes.

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

Question 45

What is a primary key?

Answer 45

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.

There can only be one primary key per table.

Question 46

What is a natural/business key?

Answer 46

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 47

What is a surrogate key?

Answer 47

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

Question 48

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

Answer 48

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 49

How do you declare a primary key?

Answer 49

CREATE TABLE or ALTER table statements, e.g.

CREATE TABLE Production.Categories

(

categoryid INT NOT NULL IDENTITY,

…

CONSTRAINT PK_Categories PRIMARY KEY(categoryid)

)

ALTER TABLE Production.Categories

ADD CONSTRAINT PK_Categories

PRIMARY KEY(categoryid);

Question 50

What are the requirements to create a primary key?

Answer 50

There are 3 requirements:

1. No 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 51

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

Answer 51

SQL Server enforces a constraint behind the scenes by creating a unique index on the primary key column(s) and then uses the primary key column(s) as the key of the index.

Question 52

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

Answer 52

SELECT *

FROM sys.key_constraints

WHERE type = ‘PK’

Question 53

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

Answer 53

SELECT *

FROM sys.indexes AS IX

WHERE

IX.OBJECT_ID = OBJECT_ID(‘Production.Categories’)

AND

IX.name = ‘PK_Categories’

Question 54

What is a unique constraint?

Answer 54

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

Question 55

How do you declare/drop a unique constraint?

Answer 55

ALTER TABLE Production.Categories

ADD CONSTRAINT UC_Categories

UNIQUE (categoryname);

Or…

ALTER TABLE Production.Products

DROP CONSTRAINT U_Productname;

Question 56

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

Answer 56

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 57

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

Answer 57

No.

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

Question 58

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

Answer 58

Yes.

The column must be deterministic.

Question 59

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

Answer 59

1. A maximum 16 columns
2. The 16 columns’ data cannot exceed 900 bytes across all columns.

Question 60

How can you list unique constraints in a database?

Answer 60

SELECT *

FROM sys.key_constraints

WHERE type=’UQ’;

Question 61

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

Answer 61

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

Question 62

What is a foreign key?

Answer 62

A column or set of columns in a table that are a link to look up data in another table.

Use fk constraints to enforce data entry into the fk column. This ensures the data is a valid value from the lookup table.

Question 63

How do you create/drop a foreign key?

Answer 63

1. CREATE:

ALTER TABLE Production.Products

WITH CHECK ADD CONSTRAINT FK_Products_Categories

FOREIGN KEY(categoryid)

REFERENCES Production.Categories (categoryid)

1. DROP:

ALTER TABLE Production.Products

DROP CONSTRAINT FK_Products_Categories;

Question 64

How does the ADD CONSTRAINT command work?

ALTER TABLE Production.Products

WITH CHECK ADD CONSTRAINT FK_Products_Categories

FOREIGN KEY(categoryid)

REFERENCES Production.Categories (categoryid)

Answer 64

1. A FK is declared on the table where the key is foreign
2. WITH CHECK throws an error if any existing table data violates the constraint
3. Name the FK constraint
4. Enter the type of constraint, FOREIGN KEY
   
   1. then state, in parenthesis, the column(s) names to be validated by a lookup into another table.
5. State the other table using REFERENCES and the columns in parenthesis.
   
   1. The column in the Referenced Table must be a
      
      1. Primary Key,
      2. Unique Constraint,
      3. or have a Unique Index.

Question 65

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

Answer 65

1. The columns must be exactly the same data types and collation.
2. The Reference Table columns must be in a Primary Key, Unique Key, or Unique Index.

Foreign Key Constraints can be on computed columns

Question 66

How can you boost performance on fk joins?

Answer 66

Create a nonclustered index on the key columns in the Foreign Key Table (also called the Referencing Table). Useful only if the table is large.

The Referenced Table is already indexed.

Question 67

How can you find the foreign keys in the database?

Answer 67

SELECT *

FROM sys.foreign_keys

WHERE name = ‘FK_Products_Categories’

Question 68

What is a check constraint?

Answer 68

A Check Constraint specifies an expression used to constrain a column’s valid values. This is an additional constraint on the ranges or set of allowable values. The expression can reference other columns in the same row of the table, as well as, use built in SQL functions.

ALTER TABLE dbo.Table1 –> Table

ADD ColumnD int NULL –> Add Column

CONSTRAINT CHK_ColumnD –> Constraint Name

CHECK (ColumnD > 10 AND ColumnD < 50); –>Constraint Expression

GO

– Adding values that will pass the check constraint

INSERT INTO dbo.DocExc (ColumnD)

VALUES (49);

GO

– Adding values that will fail the check constraint

INSERT INTO dbo.DocExc (ColumnD)

VALUES (55);

GO

Question 69

How do you create a check constraint?

Answer 69

ALTER TABLE Production.Products

WITH CHECK ADD CONSTRAINT CHK_Products_unitprice

CHECK (unitprice >= 0);

Question 70

What are some of the advantages of using check constraints?

Answer 70

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 71

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

Answer 71

1. For columns allowing NULLs, the expression must account for NULLs
2. A Check Constraint cannot customize the error message for violations, whereas, a trigger can customize error messaging.
3. A Check Constraint cannot reference another row’s value. To do that, a trigger must be used.

Question 72

How do you list the check constraints for a table?

Answer 72

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 73

What is a default constraint?

Answer 73

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

Question 74

When are default constraints useful?

Answer 74

When a column does Not allows NULLs, a default constraint allows an insert to succeed by assigning a value in the table’s DDL.

When a table’s column does not allow NULL, and you want a NULL INSERT to succeed.

Question 75

How do you create a default constraint?

Answer 75

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 76

Should a default constraints have unique names?

Answer 76

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 77

How do you list the default constraints for a table?

Answer 77

SELECT *

FROM sys.default_constraints

WHERE parent_object_id = OBJECT_ID(‘Production.Products’);