• Shuffle
    Toggle On
    Toggle Off
  • Alphabetize
    Toggle On
    Toggle Off
  • Front First
    Toggle On
    Toggle Off
  • Both Sides
    Toggle On
    Toggle Off
  • Read
    Toggle On
    Toggle Off
Reading...
Front

Card Range To Study

through

image

Play button

image

Play button

image

Progress

1/47

Click to flip

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
Table variables
Views
Indexed Views
Derived tables / Table Expressions
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.

By using the SELECT INTO statement, which creates a table automatically by using the output of a query for the basic table definition
Syntax: Write or type out the minimum required code to create a table using T-SQL
Create table
db_name.schema_name.table_name
(
column_name column_data_type
);

go
When creating a table, what MUST you specify?
The table name
The table columns, including:
Column names
Column data types
With respect to table columns, when creating a table what can you optionally specify?
The lengths of character data types

The precision of numeric and some date data types

Optional special types of columns (computed, sparse, IDENTITY, ROWGUIDCOL)

The collation of the column (normally used only if you need to specify a non-default
collation)
With respect to table constraints, when creating a table what can you optionally specify?
Nullability
Default and check constraints
Optional column collations
Primary key
Foreign key constraints
Unique constraints
With respect to table storage directions, when creating a table what can you optionally specify?
Filegroup
Partition schema
Table compression
Define: Database Schema
The database schema is a named container (anamespace) that you can use to group tables and
other database objects
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
guest schema
INFORMATION_SCHEMA
sys database schema
How are permissions granted with respect to database
schemas?
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;
GO
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
Delimited
What are the characteristics of a regular identifier in T-SQL?
First character

must be a letter defined in the Unicode Standard 3.2
or an underscore (_), and cannot be a digit

Second character

Letters as defined in the Unicode Standard 3.2.

Numerals from Basic Latin (0 through 9) or other collations

The at sign (@), the dollar sign ($), the number sign (#), and the underscore (_)

Cannot be a T-SQL reserved word and cannot include embedded spaces or special characters

Variables must begin with an at sign (@).

Temporary tables or procedures must begin with a number sign (#).
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
and adequately captures the data, and won't need to be changed later on when the table fills with data.
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
TIME
DATETIME2
Which datatypes should be used in place of the depreciated TEXT, NTEXT, and IMAGE data types?
VARCHAR(MAX)
NVARCHAR(MAX)
VARBINARY(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
familiar with possible rounding issues.
How would you specify whether a column allows NULL?
By stating NULL or NOT NULL right after
the column’s data type
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(
description NVARCHAR(200) NOT NULL DEFAULT ('')
) ON [PRIMARY];
GO
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(
categoryid INT IDENTITY(1,1) NOT NULL
)
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
Page
What is row level compression?
A more compact storage format
to each row of a table
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 (

orderid int Identity(1,1) not null

) with (DATA_COMPRESSION = ROW);
Syntax: Change the previous table's compression level from row to page.
ALTER TABLE Sales.OrderDetails
REBUILD WITH (DATA_COMPRESSION = PAGE);
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.)

Change the data type of a column

Change a column's nullability (that is, from NULL to NOT NULL, or vice versa).

Add or remove a constraint, including the following
Primary key constraint
Unique constraint
Foreign key constraint
Check constraint
Default constraint
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
Add an identity property
Remove an identity property