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;
24 Cards in this Set
- Front
- Back
|
First Section of Create Table Command
|
CREATE TABLE [ database_name . [ schema_name ] . | schema_name . ] table_name [ AS FileTable ]
({||column_set_definition>|[] [,...n]}) [ ON { partition_scheme_name ( partition_column_name ) | filegroup | "default" } ] [ { TEXTIMAGE_ON { filegroup | "default" } ] [ FILESTREAM_ON { partition_scheme_name | filegroup | "default" } ] [ WITH ( [ ,...n ] ) ][ ; ] |
|
|
You must specify when create a table |
- table name - table columns; including column name, column data type |
|
|
Can specify (not required) when create table |
- length of character data types - precision of numberic and some date data types - special types i.e. computed, sparse, IDENTITY, ROWGUIDCOL - collation - Constraints; Nullability, Default, Check, Primary Key, Foreign Key, Unique - Table Storage Directions; Filegroup, Partition schema, Table compression |
|
|
Database Schema |
Every table belongs to a grouping of objects within a database called a database schema. If you don't supply a database schema when create a table SQL Server will use the user name's default schema. Database wide container of objects. - cannot be nested -only owned by one authorized database user - authorized user can grant permission to other user. |
|
|
Table Schema |
definition of a table that includes the CREATE TABLE statement with all the column definitions |
|
|
Base Tables |
Tables containing data and are permanent. Remain in database even if SQL server is shut down. |
|
|
Temporary Tables |
Base tables that exist in tempdb and last only as long as a session or scope referencing them endures. |
|
|
Table Variables |
Variables that can store data but only for the duration of a T-SQL batch |
|
|
View |
Are not base tables but derived from queries against tables, appear just like tables but do not store data |
|
|
Indexed View |
Store data but are defined as views and are updated whenever the base tables are updated. |
|
|
Derived tables and Table expressions |
Subqueries that are referenced like tales in queries. |
|
|
Four built-in database schemas |
Build in views, cannot be dropped dbo - default schema for new objects created by users having the db_owner or db_ddl_admin roles. guest schema- for guest user, rarely used. INFROMATION_SCHEMA - used by Information Schema views, which provide ANSI standard access to metadata sys - reserved for system objects like system tables and views. |
|
|
Move one table to another schema |
ALTER SCHEMA TRANSFER - target schema cannot have object with same name as source table name. |
|
|
Naming Tables and Columns |
- at least one character long, less than 128 character - cannot be reserved words - if not regulars must enclose in [ ] or ' '. - ' ' is ANSI standard, but SET QUOTED_IDENTIFIER to ON which is default. If set OFF ' ' won't work to delimit |
|
|
Naming Tables and Columns - Regular |
- Letters, decimal numbers, first character is a letter or and underscore (two exceptions) - variables must begin with @ - temporary tables or procedures must begin with # - subsequent identifier characters can include - letters, numbers, @, $, #, _ - cannot include embedded spaces or non-alphanumeric except listed above. |
|
|
Naming Best Practice |
- not too long. - descriptive |
|
|
Column Data Types |
- Characters: if vary in length use NVARCHAR OR VARCHAR; if update often and especially if fixed use NCHAR OR CHAR - Use DATE, TIME & DATETIME2 more efficient and precise than DATETIME & SMALLDATETIME - Use VARCHAR(MAX), NVARCHAR(MAX) & VARBINARY(MAX) instead of TEXT, NTEXT & IMAGE - Use ROWVERSION not TIMESTAMP - DECIMAL = NUMERIC use instead of FLOAT & REAL unless need floating point precision and familiar with possible rounding issues |
|
|
NULL |
can specify if a column allows NULLs by stating NULL or NOT NULL after data type |
|
|
DEFAULT Values |
Add DEFAULT() right after saying NOT NULL |
|
|
Identity Property |
- only one column per table, - can specify seed and increment value - often used with primary key - add INDENTITY(, ) after data type |
|
|
Computed Columns |
- for column to persist, not have to be computed on the fly, cannot use functions that are not determistic (can't reference dynamic functions like GETDATE(), CURRENT_TIMESTAMP - can be based on other columns or functions |
|
|
Table Compression |
Row - apply compact format to each row of a table Page - includes row level compression and page level compression add WITH (DATA_COMPRESSION = ROW|PAGE after column definition closed paranthesis |
|
|
sp_estimate_data_compression_savings |
stored procedure used to see if a table with data would benefit from compression |
|
|
ALTER TABLE |
ALTER TABLE ADD | DROP - use to add, remove column, change column data type, change nullability of a colum, add or remove constraint (primary key, unique, foreign key, check, default) - change a constraint or computed column, drop old add back with change - cannot use to change column name, add identity property, remove identity property |
