Flashcards in Chapter 8: Creating Tables and Enforcing Data Integrity Deck (49):
What is a Base Table?
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.
What is a temporary table?
It's a base table stored in the tempdb. His purpose is to use it while a session or until the user want.
What's a table variable?
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).
What's a view?
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.
What's an index view?
Unlike a standard view, the index view is stored in the database as object. That's because has a index in his structure.
What are the ways which T SQL allows you create a table?
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.
What's a database schema?
It's a named container (like a namespace) which groups table and database objects.
What's the difference between a database schema and a table schema?
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).
Mention the built-in database schemas that cannot be dropped.
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).
4. 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).
Can a database schema be nested?
No, it can't.
How a database schema can be created?
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.
Can a table which is in a schema, can be trasfered or moved to another schema?
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).
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?
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).
Can a table have more than an IDENTITY column?
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.
What are the level of table compression?
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.
How a table modification can be achieved?
With the statement ALTER TABLE.
In which case the ALTER TABLE cannot be used?
To rename a column, to add IDENTITY property to a column or to drop IDENTITY property.
What's declarative data integrity?
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.
How is stored a constraint in a database?
As a database object, such as a table, a view, a stored procedure, a function, and so on.
What's a natural key?
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.
What's a surrogate key?
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).
What are the requirements to create a primary key on a column?
*. 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.
Exam tip about unique constraint and NULL.
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.
What's going to do SQL Server when this query run: ALTER TABLE ADD CONSTRAINT [Constraint Name] UNIQUE [Column Name?]
It'll create a unique non clustered index on the column specified.
What's the difference between a unique constraint and an unique index (non clustered)?
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.
How does SQL Server enforce uniqueness in both primary key and unique constraints?
SQL Server uses unique indexes to enforce uniqueness for both primary key and unique constraints.
Can a primary key on one table have the same name as the primary key in another table in the same database?
No, all table constraints must have unique names in a database.
What's the syntax to add a foreign key?
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])
EXAM TIP JOIN FOREING KEY
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.
What's a check constraint?
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
What's a default constraint?
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.
How I can create a table using T SQL?
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.
What are the principal schemas of a database?
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.
How I can create a database schema?
CREATE SCHEMA schema_name AUTHORIZATION schema_owner. A schema needs an owner, this owner grant permission to other users to use this schema.
How I can transfer a object which belongs to a schema to another schema?
ALTER SCHEMA destination_schema TRANSFER object_from_source_schema
How a variable and a temporal table needs to begin?
A variable needs to begin with "@" and a temporal table with "#".
What are the features of a regular identifier?
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).
What's a delimited identifier?
When it brokes one of the regular identifier rules.
How to choose properly a data type?
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.
What are database objects?
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.
What's the hierarchy of a database?
Database Server (Also called database cluster) -> Catalog (Database) -> Schema -> Table
Explain more detailed the hierarchy of a table?
1.3. Stored Procedures
And so on...
What are the database constraints?
PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK and DEFAULT.
What's the difference between column level constraint and table level constraint?
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)
What's the difference between unique constraint and non clustered index?
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.
What does do the CHECK constraint?
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.
What does to the DEFAULT constraint?
In case a column value isn't being inserted, it'll put a defualt value.
When PRIMARY key it's defined in a table, what does happen to the table?
Converts the table to a clustered index (when a table doesn't have a primary key, it's considered as an heap).