Use LEFT and RIGHT arrow keys to navigate between flashcards;
Use UP and DOWN arrow keys to flip the card;
H to show hint;
A reads text to speech;
47 Cards in this Set
- Front
- Back
Define: Base Table |
In SQL Server, tables containing data are often called base tables |
|
Name 5 types of table variations |
Temporary Tables |
|
Define: Temporary Table |
Temporary tables are base tables that exist in tempdb and last only as long as a session or scope referencing them endures. |
|
Define: Table Variable |
Table variables are variables that can store data but only for the duration of a T-SQL batch |
|
Define: View |
Views, which are not base tables but are derived from queries against base tables, appear just like tables but do not store data |
|
Define: Indexed Views |
Indexed views store data but are defined as views and are updated whenever the base tables are updated |
|
Define: Derived Tables & Table Expressions |
Derived tables and table expressions are subqueries that are referenced like tables in queries |
|
Name two ways a table can be created in T-SQL |
By using the CREATE TABLE statement, where you explicitly define the components of the table. |
|
Syntax: Write or type out the minimum required code to create a table using T-SQL |
Create table |
|
When creating a table, what MUST you specify? |
The table name |
|
With respect to table columns, when creating a table what can you optionally specify? |
The lengths of character data types |
|
With respect to table constraints, when creating a table what can you optionally specify? |
Nullability |
|
With respect to table storage directions, when creating a table what can you optionally specify? |
Filegroup |
|
Define: Database Schema |
The database schema is a named container (anamespace) that you can use to group tables and |
|
What purpose does a database schema serve? |
The primary purpose of a database schema is to group many database objects, such as tables, together. In the case of tables, a database schema also allows many tables with the same table name to belong to different schemas. |
|
Name the 4 database schemas that cannot be dropped. |
dbo database schema |
|
How are permissions granted with respect to database |
Every database schema must be owned by exactly one authorized database user. That database schema owner can then grant permissions to other users regarding the objects in this schema. |
|
Syntax: Create a database schema called production and grant ownership to the dbo user. |
CREATE SCHEMA Production AUTHORIZATION dbo; |
|
Syntax: How would you move a table from schema to another one? |
ALTER SCHEMA TRANSFER |
|
Syntax: Move a table named Categories from the Production schema to the Sales schema |
ALTER SCHEMA Sales TRANSFER Production.Categories; |
|
What constitutes a valid identifier in SQL Sever? |
At least one character long and no longer than 128 characters |
|
Name two types of identifiers in T-SQL |
Regular |
|
What are the characteristics of a regular identifier in T-SQL? |
First character |
|
What special characters are used to delimit delimited identifiers in T-SQL? |
" " |
|
With respect to column datatypes in T-SQL, what is the most efficient data type? |
One that requires the least amount of disk storage |
|
Character string that vary in length should be placed under which column datatype? |
NVARCHAR or VARCHAR |
|
What datatypes store data more efficiently and with better precision than DATETIME and SMALLDATETIME? |
DATE |
|
Which datatypes should be used in place of the depreciated TEXT, NTEXT, and IMAGE data types? |
VARCHAR(MAX) |
|
What datatype should be used in place of the depreciated TIMESTAMP? |
ROWVERSION |
|
When should FLOAT or REAL be used in place of DECIMAL or NUMERIC? |
When you really need floating-point precision and are |
|
How would you specify whether a column allows NULL? |
By stating NULL or NOT NULL right after |
|
If you don't want to allow NULL in the column, but you do want to specify some default value to indicate that the column has not yet been populated you would________ |
Specify a DEFAULT constraint by adding the DEFAULT clause right after saying NOT NULL |
|
Syntax: Create a table in the Production schema named Categories. Add one column called description that should hold max of 200 characters, does not allow nulls, and has a default value of ' '. |
CREATE TABLE Production.Categories( |
|
What is the purpose of the identity property? |
A property assigned to one column in a table that automatically generates sequence numbers. Often used for primary keys. |
|
Syntax: Create a table called Categories in the Production schema. Add one column called categoryid that will hold an integer value, be an identity column with a seed value of 1 and incremental value of 1, and not allow nulls. |
CREATE TABLE Production.Categories( |
|
What is an optional way to generate sequence numbers in a table? |
Sequence Objects. |
|
Define: Computed Column |
Column values based on expressions which are based on the values of other columns in the same row or T-SQL functions. |
|
What purpose does table compression serve? |
Gives you more efficient storage |
|
What are the two levels of table compression? |
Row |
|
What is row level compression? |
A more compact storage format |
|
What is page level compression? |
Page-level compression includes row-level plus additional compression algorithms that can be performed at the page level |
|
Syntax: Create a table with row level compression |
Create table Sales.OderDetails ( |
|
Syntax: Change the previous table's compression level from row to page. |
ALTER TABLE Sales.OrderDetails |
|
After you have created a table, what command do you use to change the table's structure and add or remove certain table properties? |
ALTER TABLE |
|
What can the ALTER TABLE command be used to do? |
Add or remove a column, including a computed column. (New columns are placed at the end of the table's column order.) |
|
How do you change the definition of a constraint or a computed column with alter table |
Drop the constraint or column with the old definition and add the constraint or computed column back in with the new definition |
|
What can you not use ALTER TABLE to do? |
Change a column name |