Flashcards in Chapter 8 Creating Tables and Enforcing Data Integrity Deck (78)
What is the main method used for storing data?
Tables (Base tables). When you query a database for data, ultimately, that data is located in tables.
How do tables work with the SQL Server backup/restore process?
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.
What are the system tables?
They store system data for SQL Server in specially reserved tables called system tables.
What is a temporary table?
Tables that exist in tempdb and last only as long as a session or scope referencing them endures.
What are table variables?
Variables that can store table data but only for the duration of a T-SQL batch.
What are views?
Views appear just like tables but they do not store any data. The are derived from queries against tables.
What are indexed views?
Indexed views store data but are defined as views and are updated whenever the base tables are updated.
What are derived tables and table expressions?
Subqueries that are referenced like tables in queries.
What are the two ways to create a table in T-SQL?
(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.
What options are allowed in the CREATE TABLE statement?
(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).
What is a database schema?
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).
What happens if you create a table without specifying the database schema?
SQL Server will fill in the database schema with your user name's default schema.
What are four built in database schemas that cannot be dropped?
(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.
Can you create schemas that aren't linked to users?
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.
Can a database schema contain another database schema?
No. There can only be one level of database schema; one schema cannot contain another.
How many users can own a database schema?
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.
How do you create a database schema and define who owns it?
CREATE SCHEMA Production AUTHORIZATION dbo;
How can you move a table from one schema to another?
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;
What are the length restrictions on SQL Server identifiers?
Identifiers must be one character long and no longer than 128 characters.
What are the two types of SQL Server identifiers?
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.
What are the rules for regular identifiers?
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.
What are the rules for delimited identifiers?
There is no restriction on what characters can be embedded in them as long as they are delimited, e.g. [Yesterday's News].
What's the difference between the brackets and quotes delimiters?
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
Why is it a best practice to use regular identifiers?
If one of your users does not use the delimiters in a query, their query will still succeed.
When should you use N/VARCHAR vs N/CHAR data types?
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.
When should you use DATETIME vs DATETIME2 data types?
The DATE, TIME, and DATETIME2 data types can store data more efficiently and with better precision than DATETIME and SMALLDATETIME.
When should you use TEXT, NTEXT, and IMAGE data types?
Never. They are deprecated. Instead, use the VARCHAR(MAX), NVARCHAR(MAX), and VARBINARY(MAX).
When should you use DECIMAL/NUMERIC vs FLOAT/REAL?
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.
When should you use ROWVERSION vs TIMESTAMP?
TIMESTAMP is deprecated. Use ROWVERSION to version-stamp table rows. It is just an incrementing number and does not preserve a date or time.