Chapter 8: Creating Tables and Enforcing Data Integrity

Question 1

What is a Base Table?

Answer 1

It’s a table which his definition and content remains in the SQL Server Instance, no matter if the SQL Server instance is restarded or shutdown.

Question 2

What is a temporary table?

Answer 2

It’s a base table stored in the tempdb. His purpose is to use it while a session or until the user want.

Question 3

What’s a table variable?

Answer 3

It’s a variable that can store data as a table does, but his existence is limited by the duration of the T SQL batch (this is, just during the execution of a T SQL batch).

Question 4

What’s a view?

Answer 4

A View show data as a table does, but it’s defined by a query and if the view is queried, the query which build the view, it’s triggered.

Question 5

What’s an index view?

Answer 5

Unlike a standard view, the index view is stored in the database as object. That’s because has a index in his structure.

Question 6

What are the ways which T SQL allows you create a table?

Answer 6

The first one is used the statement CREATE TABLE, and the second one is using the SELECT INTO. The latter one creates a table through a query’s output.

Question 7

What’s a database schema?

Answer 7

It’s a named container (like a namespace) which groups table and database objects.

Question 8

What’s the difference between a database schema and a table schema?

Answer 8

A database schema is a container which groups database objects (like tables, functions, stored procedures, and so on), whereas a table schema is the structure of the table (like his colums and data types, and so on).

Question 9

Mention the built-in database schemas that cannot be dropped.

Answer 9

1. dbo: this schema is used when a database object is created for users which has the role of db_owner. Also, it’s the default schema if the schema is not specified.
2. guest: it’s rarely used, but as his name says, it’s for guest users.
   3 INFORMATION_SCHEMA: this schema has information about other schemas (REVIEW THIS ONE).
3. sys: it’s a SQL Server reserved schema, contains information about the current database.

Note: each database has each one of the mentioned schema (his own one).

Question 10

Can a database schema be nested?

Answer 10

No, it can’t.

Question 11

How a database schema can be created?

Answer 11

With this statement: CREATE SCHEMA [Schema Name] AUTHORIZATION [User who is owner of this Schema]. We need to know that a schema need to have a owner, just an owner. That owner can grant permission of this schema to other users.

Question 12

Can a table which is in a schema, can be trasfered or moved to another schema?

Answer 12

Yes, it can. The statement ALTER SCHEMA [Destination Schema] TRANSFER [Source Schema].[Table Name]. If there exists a table in the destination schema with the same name of the source schema, then it’ll trigger an error (two tables cannot have the same name in a schema, it needs to be unique).

Question 13

How I can handle if a column doesn’t allow NULL but I want to make clear that a specific value means that row doesn’t have the value?

Answer 13

Use DEFAULT after specifiying NOT NULL in a column definition. For example, DESCRIPTION VARCHAR(20) NOT NULL DEFAULT(‘EMPTY’). This column doesn’t allow NULL, but in case a row is inserted and doesn’t have a value to the DESCRIPTION column, for example, then it’ll take the string ‘EMPTY’ as placeholder to indicate it’s NULL (but it’s not a real NULL).

Question 14

Can a table have more than an IDENTITY column?

Answer 14

No, it can’t. The IDENTITY is used in this way: IDENTITY(seed value, increment amount). The first parameter is for know with which value is going to begin, and with the increment number each time a row is inserted.

Question 15

What are the level of table compression?

Answer 15

There’s two table level compression: row level and page level (the page level is deeper than row level compression). To apply the table compression, when the table is being created, it must add the clause WITH (DATA_COMPRESSION = ROW/PAGE) at the bottom of the CREATE statement. Also it can add the compression altering the table.

Question 16

How a table modification can be achieved?

Answer 16

With the statement ALTER TABLE.

Question 17

In which case the ALTER TABLE cannot be used?

Answer 17

To rename a column, to add IDENTITY property to a column or to drop IDENTITY property.

Question 18

What’s declarative data integrity?

Answer 18

Whe you know which column should have unique values, what ranges of valid values a column might accept, and whether a value of the column match some column in a different table. All these methods are aplicated in the table definition.

Question 19

How is stored a constraint in a database?

Answer 19

As a database object, such as a table, a view, a stored procedure, a function, and so on.

Question 20

What’s a natural key?

Answer 20

It’s a value which represents something to the business, let’s say it’s a business value. It’s a good candidate for primary key.

Question 21

What’s a surrogate key?

Answer 21

It’s a value which doesn’t have any meaning, just to identity each row in a table (it’s usually used as primary key).

Question 22

What are the requirements to create a primary key on a column?

Answer 22

*. The column or columns cannot allow NULL. If the column or columns allow NULL, the
constraint command will fail.
*. Any data already in the table must have unique values in the primary key column or
columns. If there are any duplicates, the ALTER TABLE statement will fail
*. There can be only one primary key constraint at a time in a table. If you try to create
two primary key constraints on the same table, the command will fail.

Question 23

Exam tip about unique constraint and NULL.

Answer 23

The unique constraint does not require the column to be NOT NULL. You can allow NULL in
a column and still have a unique constraint, but only one row can be NULL.

Question 24

What’s going to do SQL Server when this query run: ALTER TABLE ADD CONSTRAINT [Constraint Name] UNIQUE [Column Name?]

Answer 24

It’ll create a unique non clustered index on the column specified.

Question 25

What’s the difference between a unique constraint and an unique index (non clustered)?

Answer 25

Both are the same, althought both has different syntax. A unique index doens’t allow duplicate in the key column (or columns). The unique constraint also do the same, doesn’t allow duplicates in the column specified.

Question 26

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

Answer 26

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

Question 27

Can a primary key on one table have the same name as the primary key in another table in the same database?

Answer 27

No, all table constraints must have unique names in a database.

Question 28

What’s the syntax to add a foreign key?

Answer 28

ALTER TABLE [Table name] WITH CHECK
ADD CONSTRAINT FK_[Referencing table name]_[Referenced table name ]FOREIGN KEY([Referencing column name] REFERENCES [Referenced table name] ([Primary Key of Referenced Table])

Question 29

EXAM TIP JOIN FOREING KEY

Answer 29

Because joins often occur on foreign keys, it can help query performance to create a
nonclustered index on the foreign key in the referencing table. There is already a unique
index on the corresponding column in the referenced table, but if the referencing table,
like Production.Products, has a lot of rows, it may help SQL Server resolve the join faster if
it can use an index on the big table.

Question 30

What’s a check constraint?

Answer 30

It’s additional constraint to the column, for example, column length, column specific value, and so on. For example:

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

Question 31

What’s a default constraint?

Answer 31

It’s a constraint which usually constrains data value when it’s going to be inserted, for example. It’s rarely use this constraint, instead of this, DEFAULT is used often in the CREATE TABLE statement.

Question 32

How I can create a table using T SQL?

Answer 32

There’s two way to create a table using T-SQL. The first one it’s using the statement CREATE TABLE, where you explicitly you define the structure of the table, like column, column’s data type, and so on. The other way it’s using SELECT INTO, where the table it’s created implicitly from a query result.

Question 33

What are the principal schemas of a database?

Answer 33

user schemas: user defined schemas.
dbo: default database schema.
sys: SQL Server system schema, where system objects are stored.
INFORMATION_SCHEMA: ANSI standard for access to metadata.
guest: objects that can use guests.

Question 34

How I can create a database schema?

Answer 34

CREATE SCHEMA schema_name AUTHORIZATION schema_owner. A schema needs an owner, this owner grant permission to other users to use this schema.

Question 35

How I can transfer a object which belongs to a schema to another schema?

Answer 35

ALTER SCHEMA destination_schema TRANSFER object_from_source_schema

Question 36

How a variable and a temporal table needs to begin?

Answer 36

A variable needs to begin with “@” and a temporal table with “#”.

Question 37

What are the features of a regular identifier?

Answer 37

1. The object name cannot begin with a digit.
2. It can begin with a underscore ‘_’.
3. It can begin with a letter.
4. Cannot contain spaces (embeded).

Question 38

What’s a delimited identifier?

Answer 38

When it brokes one of the regular identifier rules.

Question 39

How to choose properly a data type?

Answer 39

If the data’s language is english, then it should use CHAR or VARCHAR. In case it’s not only english, then it should use NCHAR or NVARCHAR.

Use VARCHAR(MAX), NVARCHAR(MAX) or VARBINARY(MAX) instead of TEXT, NTEXT and IMAGE.

Use ROWVERSION instead TIMESTAMP.

Question 40

What are database objects?

Answer 40

Even the database it’s considered as an object (the grand object). The database objects are tables, views, indexes, schemas, triggers, stored procedures, and so on.

Question 41

What’s the hierarchy of a database?

Answer 41

Database Server (Also called database cluster) -> Catalog (Database) -> Schema -> Table

Question 42

Explain more detailed the hierarchy of a table?

Answer 42

1. Schema.
2. 1. Table.
      
      1. 1.1. Indexes.
      2. 1.2. Triggers.
      3. 1.3. Constraints.
3. 2. Views.
4. 3. Stored Procedures
5. 4. Functions.

And so on…

Question 43

What are the database constraints?

Answer 43

PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK and DEFAULT.

Question 44

What’s the difference between column level constraint and table level constraint?

Answer 44

Column level constraint it’s when a constraint it’s declared while the column it’s being also declared. Whereas table level constraint it’s when the constraint it’s declared after all columns are being declared (or when the column which it’s being constrained it’s already defined)

Question 45

What’s the difference between unique constraint and non clustered index?

Answer 45

Both almost are the same, but an unique constraint it’s a nonclustered index, but with no duplicates. When a unique constraint it’s defined and created, a nonclustered index (but unique) it’s also created. A nonclustered index can be unique, just need to be specified.

Question 46

What does do the CHECK constraint?

Answer 46

It evaluates a column’s predicate specified before it’s being inserted the value in that column. For example, Quantity must be greater than one, so the predicate for Quantity column should be: Quantity > 1. If a quantity it’s zero or less than 0, then an error will be raised.

Question 47

What does to the DEFAULT constraint?

Answer 47

In case a column value isn’t being inserted, it’ll put a defualt value.

Question 48

When PRIMARY key it’s defined in a table, what does happen to the table?

Answer 48

Converts the table to a clustered index (when a table doesn’t have a primary key, it’s considered as an heap).

Question 49

What’s a lookup table?

Answer 49

It’s also called a reference table. It’s a table used for static data, and enforces the data integrity. A common design of a lookup table it’s having two columns: ID and Description (the lookup value). This table it’s always referecing another table (which it’s called a main table). For example: Grade and StudentGradeReport. There’s an one to many relationship from Grade to StudentGradeReport, that means there’s a foreign key in the StudentGradeReport table referencing to a row in the Grade table. The Grade rows are static and scallable. If there’s a new Grade, then it’s just added there. In this example, Grade it’s a lookup table (also called reference table).