Index

A

• ACID characteristics
  • about / The fundamentals of transactions
• Activity Monitor
  • about / Activity Monitor
  • blocked processes, finding with / Activity Monitor
• ad hoc workloads
  • optimizing for / Optimizing SQL Server for ad hoc workloads
• aggregate functions
  • about / Aggregate functions
• alias data types
  • about / Alias data types
  • creating, with SSMS 2014 / Creating and dropping alias data types with SSMS 2014
  • dropping, with SSMS 2014 / Creating and dropping alias data types with SSMS 2014
  • creating, Transact-SQL DDL statement used / Creating and dropping alias data types using the Transact-SQL DDL statement
  • dropping, Transact-SQL DDL statement used / Creating and dropping alias data types using the Transact-SQL DDL statement
  • creating, CREATE TYPE used / Creating an alias data type using CREATE TYPE
  • dropping, DROP TYPE used / Dropping an alias data type using DROP TYPE
• ALTER DATABASE statement
  • syntax / Modifying a database with T-SQL DDL statements
  • arguments / Modifying a database with T-SQL DDL statements
• ALTER PROCEDURE statement
  • about / Modifying a stored procedure
  • example / Modifying a stored procedure
• ALTER VIEW statement
  • about / The ALTER VIEW statement
  • example / The ALTER VIEW statement
• analytic window functions, T-SQL
  • ranking functions / Ranking functions
  • PERCENT RANK / PERCENT RANK
  • CUME_DIST / CUME_DIST
  • PERCENTILE_CONT / PERCENTILE_CONT and PERCENTILE_DISC
  • PERCENTILE_DESC / PERCENTILE_CONT and PERCENTILE_DISC
  • LEAD / LEAD and LAG
  • LAG / LEAD and LAG
  • FIRST_VALUE / FIRST_VALUE and LAST_VALUE
  • LAST_VALUE / FIRST_VALUE and LAST_VALUE
• application programming interfaces (APIs) / The CREATE VIEW statement
• architecture, columnstore indexes / The architecture of columnstore indexes
• arguments, ALTER DATABASE statement
  • database_name / Modifying a database with T-SQL DDL statements
  • ADD FILE / Modifying a database with T-SQL DDL statements
  • TO FILEGROUP / Modifying a database with T-SQL DDL statements
  • REMOVE FILE / Modifying a database with T-SQL DDL statements
  • MODIFY FILE / Modifying a database with T-SQL DDL statements
• arguments, CREATE DATABASE command
  • database_name / Creating a database with T-SQL DDL statements
  • CONTAINMENT / Creating a database with T-SQL DDL statements
  • ON [PRIMARY] / Creating a database with T-SQL DDL statements
  • LOG ON / Creating a database with T-SQL DDL statements
  • filespec / Creating a database with T-SQL DDL statements
  • filespec, name parameter / Creating a database with T-SQL DDL statements
  • filespec, FILENAME parameter / Creating a database with T-SQL DDL statements
  • filespec, size parameter / Creating a database with T-SQL DDL statements
  • filespec, MAXSIZE parameter / Creating a database with T-SQL DDL statements
  • filespec, FILEGROWTH parameter / Creating a database with T-SQL DDL statements
  • COLLATE / Creating a database with T-SQL DDL statements
  • WITH <option> / Creating a database with T-SQL DDL statements
• arguments, CREATE FUNCTION statement
  • schema_name / Creating a user-defined scalar function, Creating DML triggers
  • function_name / Creating a user-defined scalar function
  • @parameter data_type / Creating a user-defined scalar function
  • WITH <function_option> / Creating a user-defined scalar function
  • RETURNS / Creating a user-defined scalar function
  • sql_statements / Creating a user-defined scalar function, Creating DML triggers
  • RETURN / Creating a user-defined scalar function
  • trigger_name / Creating DML triggers
  • ON {table | view} / Creating DML triggers
  • trigger_options / Creating DML triggers
  • FOR | AFTER | INSTEAD OF / Creating DML triggers
  • NOT FOR REPLICATION / Creating DML triggers
• arguments, CREATE PROCEDURE statement
  • schema_name / Creating a stored procedure
  • procedure_name / Creating a stored procedure
  • @parameter data_type / Creating a stored procedure
  • procedure_option / Creating a stored procedure
  • NOT FOR REPLICATION / Creating a stored procedure
  • AS / Creating a stored procedure
• arguments, CREATE VIEW statement
  • schema / The CREATE VIEW statement
  • view_name / The CREATE VIEW statement
  • column_list / The CREATE VIEW statement
  • WITH view_attributes / The CREATE VIEW statement
  • AS select_statement / The CREATE VIEW statement
  • WITH CHECK OPTION / The CREATE VIEW statement
• arguments, DELETE statement
  • WITH / Deleting data from SQL Server database tables
  • TOP / Deleting data from SQL Server database tables
  • table_name | view_name | rowset function | common_table_expression / Deleting data from SQL Server database tables
  • FROM / Deleting data from SQL Server database tables
  • WHERE / Deleting data from SQL Server database tables
  • WITH table_hints / Deleting data from SQL Server database tables
  • output_clause / Deleting data from SQL Server database tables
• arguments, INSERT statement
  • WITH / Inserting data into SQL Server database tables
  • TOP / Inserting data into SQL Server database tables
  • WITH table_hints / Inserting data into SQL Server database tables
  • output_clause / Inserting data into SQL Server database tables
  • VALUES / Inserting data into SQL Server database tables
  • value_list / Inserting data into SQL Server database tables
  • select_statement / Inserting data into SQL Server database tables
  • DEFAULT / Inserting data into SQL Server database tables
  • DEFAULT_VALUE / Inserting data into SQL Server database tables
• arguments, MERGE statement
  • WITH / Using the MERGE statement
  • TOP / Using the MERGE statement
  • ON / Using the MERGE statement
  • WHEN MATCHED / Using the MERGE statement
  • WHEN NOT MATCHED [BY TARGET] / Using the MERGE statement
  • WHEN NOT MATCHED BY SOURCE / Using the MERGE statement
  • output_clause / Using the MERGE statement
  • OPTION (<query_hint> [ ,...n ]) / Using the MERGE statement
• arguments, UPDATE statement
  • WITH / Updating data in SQL Server database tables
  • TOP / Updating data in SQL Server database tables
  • table_name | view_name | rowset function | common_table_expression / Updating data in SQL Server database tables
  • FROM / Updating data in SQL Server database tables
  • WHERE / Updating data in SQL Server database tables
  • WITH table_hints / Updating data in SQL Server database tables
  • output_clause / Updating data in SQL Server database tables
• atomic, consistent, isolated, and durable (ACID) / Creating and managing tables
• atomicity
  • about / The fundamentals of transactions
• attribute
  • about / Attributes
• attributes, procedure_option
  • ENCRYPITON / Creating a stored procedure
  • SCHEMABINDING / Creating a stored procedure
  • NATIVE_COMPILATION / Creating a stored procedure
  • EXECUTE AS / Creating a stored procedure
  • WITH RECOMPILE / Creating a stored procedure
• attributes, WITH <function_option>
  • ENCRYPITON / Creating a user-defined scalar function
  • SCHEMABINDING / Creating a user-defined scalar function
  • EXECUTE AS / Creating a user-defined scalar function
• attributes, WITH view_attributes
  • ENCRYPITON / The CREATE VIEW statement
  • SCHEMABINDING / The CREATE VIEW statement
  • VIEW_METADATA / The CREATE VIEW statement
• autocommit transaction mode / Transaction modes
• AUTO_CREATE_STATISTICS statement
  • about / Database-wide statistics options in SQL Server to automatically create and update statistics
• AUTO_UPDATE_STATISTICS statement
  • about / Database-wide statistics options in SQL Server to automatically create and update statistics
• AUTO_UPDATE_STATISTICS_ASYNC statement
  • about / Database-wide statistics options in SQL Server to automatically create and update statistics

B

• balanced tree (B-tree)
  • about / The structure of indexes
• batch-scoped transaction mode / Transaction modes
• BEGIN TRANSACTION statement
  • about / BEGIN TRANSACTION
  • syntax / BEGIN TRANSACTION
• BEGIN…END keywords
  • about / BEGIN…END keywords
  • syntax / BEGIN…END keywords
  • example / BEGIN…END keywords
• BETWEEN operator
  • about / The WHERE clause
• binding
  • about / Parsing and binding
• blocked processes
  • finding, with Activity Monitor / Activity Monitor
• BREAK statement
  • about / WHILE, BREAK, and CONTINUE statements
• bulk-logged recovery model / The bulk-logged recovery model
• bulk update locks (BU)
  • about / Basic locks

C

• cardinality
  • about / The improved design in SQL Server 2014 for the cardinality estimation
• cardinality estimator
  • URL, for information / The improved design in SQL Server 2014 for the cardinality estimation
• CASE statement
  • about / A CASE statement
• categories, data integrity
  • domain integrity / Data integrity
  • entity integrity / Data integrity
  • referential integrity / Data integrity
  • user-defined integrity / Data integrity
• change data capture (CDC) / The msdb database
• characteristics, transactions
  • atomicity / The fundamentals of transactions
  • consistency / The fundamentals of transactions
  • isolation / The fundamentals of transactions
  • durability / The fundamentals of transactions
• clauses, T-SQL
  • SELECT / The SELECT statement
  • FROM / The FROM clause
  • WHERE / The WHERE clause
• CLR stored procedures
  • about / Creating and using stored procedures
  • URL, for information / Creating and using stored procedures
• CLR triggers
  • URL, for information / Creating and using triggers
• CLR user-defined function
  • URL, for information / Creating and using user-defined functions
• CLR user-defined types
  • about / CLR user-defined types
  • URL, for articles / CLR user-defined types
• clustered indexes
  • about / Clustered indexes
• clustered indexes, on table
  • about / When should you have a clustered index on a table?
• columnstore indexes
  • about / Columnstore indexes
  • architecture / The architecture of columnstore indexes
  • managing / Creating and managing columnstore indexes
  • creating / Creating and managing columnstore indexes
  • considerations, for creating / Creating and managing columnstore indexes
• column_list parameter / Inserting data into SQL Server database tables
• COMMIT TRANSACTION statement
  • about / COMMIT TRANSACTION
  • syntax / COMMIT TRANSACTION
• components, SQL Server Database Engine
  • about / Components of SQL Server Database Engine
  • Storage Engine / Components of SQL Server Database Engine
  • Relational Engine / The SQL Server Relational Engine architecture
• composite indexes
  • about / Composite indexes
• conceptual design phase, database design process / The conceptual design phase
• configuration functions
  • about / Configuration functions
• consistency
  • about / The fundamentals of transactions
• CONTINUE statement
  • about / WHILE, BREAK, and CONTINUE statements
• control-of-flow keywords
  • about / Control-of-flow keywords
  • BEGIN…END / BEGIN…END keywords
  • IF…ELSE expression / The IF…ELSE expression
  • CASE statement / A CASE statement
  • WHILE / WHILE, BREAK, and CONTINUE statements
  • BREAK / WHILE, BREAK, and CONTINUE statements
  • CONTINUE / WHILE, BREAK, and CONTINUE statements
  • RETURN / RETURN, GOTO, and WAITFOR statements
  • GOTO / RETURN, GOTO, and WAITFOR statements
  • WAITFOR / RETURN, GOTO, and WAITFOR statements
• covering indexes
  • about / Covering indexes
• CREATE DATABASE command
  • syntax / Creating a database with T-SQL DDL statements
  • arguments / Creating a database with T-SQL DDL statements
• CREATE FUNCTION statement
  • arguments / Creating a user-defined scalar function, Creating DML triggers
• CREATE INDEX statement
  • about / Nonclustered indexes
  • URL, for article / Nonclustered indexes
• CREATE PROCEDURE statement
  • arguments / Creating a stored procedure
• CREATE STATISTICS (Transact-SQL) article
  • URL / Manually create and update statistics
• CREATE STATISTICS statement
  • about / Manually create and update statistics
• CREATE TABLE (SQL Server) article
  • URL, for information / Nonclustered indexes
• CREATE TYPE
  • used, for creating alias data types / Creating an alias data type using CREATE TYPE
• Create View pane
  • Diagram pane / Creating views with SSMS 2014
  • Criteria pane / Creating views with SSMS 2014
  • SQL pane / Creating views with SSMS 2014
• CREATE VIEW statement
  • about / The CREATE VIEW statement
  • arguments / The CREATE VIEW statement
  • example / The CREATE VIEW statement
• CROSS JOIN operator
  • using / Using CROSS JOIN
• CTE
  • about / The SELECT statement, Common Table Expressions
  • creating / Common Table Expressions
  • structure / Common Table Expressions
• CUME_DIST function
  • about / CUME_DIST
• cursor functions
  • about / Cursor functions
• cursor variable
  • creating / Creating the cursor variable
  • syntax / Creating the cursor variable
  • example / Creating the cursor variable

D

• data
  • grouping / Organizing and grouping data
  • organizing / Organizing and grouping data
  • pivoting / Pivoting and unpivoting data
  • unpivoting / Pivoting and unpivoting data
  • inserting, into SQL Server database tables / Inserting data into SQL Server database tables
  • inserting, into IDENTITY column / Example 4 – explicitly inserting data into the IDENTITY column
  • updating, in SQL Server database tables / Updating data in SQL Server database tables
  • deleting, from SQL Server database tables / Deleting data from SQL Server database tables
  • accessing, without index / Access without an index
  • accessing, with index / Access with an index
• database
  • creating, with T-SQL DDL statements / Creating a database with T-SQL DDL statements
  • modifying, with T-SQL DDL statements / Modifying a database with T-SQL DDL statements
  • dropping, with T-SQL DDL statements / Dropping a database with T-SQL DDL statements
  • creating, with SSMS 2014 / Creating a database with SSMS 2014
  • modifying, with SSMS 2014 / Modifying a database with SSMS 2014
  • dropping, with SSMS 2014 / Dropping a database with SSMS 2014
• database-wide statistics options, SQL Server
  • AUTO_CREATE_STATISTICS / Database-wide statistics options in SQL Server to automatically create and update statistics
  • AUTO_UPDATE_STATISTICS / Database-wide statistics options in SQL Server to automatically create and update statistics
  • AUTO_UPDATE_STATISTICS_ASYNC / Database-wide statistics options in SQL Server to automatically create and update statistics
• database administrator (DBA) / Indexes
• database administrators (DBAs)
  • about / Database design
• database design process
  • about / Database design
  • phases / Database design
  • requirement collection and analysis phase / The requirement collection and analysis phase
  • conceptual design phase / The conceptual design phase
  • logical design phase / The logical design phase
  • physical design phase / The physical design phase
  • implementation and loading phase / The implementation and loading phase
  • testing and evaluation phase / The testing and evaluation phase
  • screenshot, for lifecycle / The database design life cycle recap
• database files, SQL Server
  • primary data file / The SQL Server database architecture
  • secondary data file / The SQL Server database architecture
  • transaction log file / The SQL Server database architecture
• Data Definition Language (DDL) / The implementation and loading phase
• data flow diagrams (DFDs)
  • about / The requirement collection and analysis phase
• data integrity
  • about / Data integrity
• Data Manipulation Language (DML) / Pages
• data type
  • about / The importance of choosing the appropriate data type
  • selecting, significance / The importance of choosing the appropriate data type
• data types, SQL Server
  • URL / SQL Server 2014 system data types
• date and time functions
  • about / Date and time functions
• DBCC SHOW_STATISTICS command
  • using / Using the DBCC SHOW_STATISTICS command
• DCL statements
  • executing / Data Control Language (DCL) statements
• DDL
  • about / Data Definition Language (DDL) triggers
• DDL trigger
  • about / Data Definition Language (DDL) triggers
  • EVENTDATA function / The EVENTDATA function
  • creating / Creating a DDL trigger
  • example / Creating a DDL trigger
  • modifying / Modifying a DDL trigger
  • dropping / Dropping a DDL trigger
• DDL triggers
  • about / Data Definition Language (DDL) triggers
• DECLARE statement
  • about / Creating and using variables
• DELETE statement
  • syntax / Deleting data from SQL Server database tables
  • arguments / Deleting data from SQL Server database tables
  • examples / The DELETE statement examples
• denormalization
  • about / Denormalization
• DENSE_RANK function
  • about / Ranking functions
• Devent classes, SQL Server Profiler
  • Deadlock_Graph_Event_Class / SQL Server Profiler
  • Lock*Acquired / SQL Server Profiler
  • Lock*Cancel / SQL Server Profiler
  • Lock*Deadlock / SQL Server Profiler
  • Lock*Escalation / SQL Server Profiler
  • Lock*Released / SQL Server Profiler
  • Lock*Timeout(timeout>0) / SQL Server Profiler
  • Lock*Timeout / SQL Server Profiler
• dirty reads
  • about / An overview of locking
• DISTINCT clause
  • about / The DISTINCT clause
  • syntax / The DISTINCT clause
• distribution database / The distribution database
• DLL
  • about / Creating and using stored procedures
• DML statements
  • executing / Data Manipulation Language (DML) statements
• DML triggers
  • about / DML triggers
  • types / DML triggers
  • inserted logical tables / Inserted and deleted logical tables
  • deleted logical tables / Inserted and deleted logical tables
  • creating / Creating DML triggers
  • example / Creating DML triggers
  • modifying / Modifying a DML trigger
  • dropping / Dropping a DML trigger
• DML triggers, types
  • AFTER / DML triggers
  • INSTEAD OF / DML triggers
• domain integrity
  • about / Data integrity
• DROP TYPE
  • used, for dropping alias data type / Dropping an alias data type using DROP TYPE
• DROP VIEW statement
  • about / The DROP VIEW statement
  • example / The DROP VIEW statement
• durability
  • about / The fundamentals of transactions
• dynamic lock management / An overview of locking
• Dynamic Management Views (DMVs)
  • URL, for information / Query optimization
  • about / Dynamic Management Views
  • sys.dm_exec_requests / Dynamic Management Views
  • sys.dm_tran_locks / Dynamic Management Views
  • sys.dm_os_waiting_tasks / Dynamic Management Views

E

• entities
  • about / Entities
• entity-relationship (ER) model
  • about / The conceptual design phase
• entity integrity
  • about / Data integrity
• EVENTDATA function
  • about / The EVENTDATA function
• examples, DELETE statement
  • single row, deleting / Example 1 – deleting a single row
  • rows, deleting / Example 2 – deleting all rows
• examples, INSERT statement
  • single row, inserting to / Example 1 – insert a single row into a SQL Server database table
  • INSERT statement, using with SELECT statement / Example 2 – INSERT with the SELECT statement
  • INSERT statement, using with EXEC statement / Example 3 – INSERT with the EXEC statement
  • data, inserting into IDENTITY column / Example 4 – explicitly inserting data into the IDENTITY column
• examples, MERGE statement / The MERGE statement examples
• examples, T-SQL DDL statements
  • model base database, creating / Example 1 – creating a database based on a model database
  • explicit data specification database, creating / Example 2 – creating a database that explicitly specifies the database data and the transaction log file's filespecs properties
  • database,creating on multiple filegroups / Example 3 – creating a database on multiple filegroups
  • secondary data file, adding to existing database / Example – adding a secondary data file to an existing database
• examples, TRUNCATE TABLE statement / The TRUNCATE TABLE statement
• examples, UPDATE statement
  • single row, updating / Example 1 – updating a single row
  • multiple rows, updating / Example 2 – updating multiple rows
• EXCEPT operator
  • about / The EXCEPT operator
  • syntax / The EXCEPT operator
• exclusive locks (X)
  • about / Basic locks
• EXEC statement
  • INSERT statement, using with / Example 3 – INSERT with the EXEC statement
• EXISTS keyword
  • about / The WHERE clause
• explicit transaction mode / Transaction modes
• extended user-defined stored procedures
  • about / Creating and using stored procedures
• extent
  • about / Extents

F

• features, unique indexes / Unique indexes
• fifth normal form (5NF)
  • about / The normal forms
• filegroups
  • about / Filegroups
  • advantages / Filegroups
• file tables / Creating and managing tables
• filtered indexes
  • about / Filtered indexes
• first normal form (1NF)
  • about / The normal forms, The first normal form (1NF)
• FIRST_VALUE function
  • about / FIRST_VALUE and LAST_VALUE
• forth normal form (4NF)
  • about / The normal forms
• FROM clause
  • about / The FROM clause
• full-text indexes
  • about / Full-text indexes
  • URL, for information / Full-text indexes
• FULL OUTER JOIN operator
  • using / Using FULL OUTER JOIN

G

• GOTO statement / RETURN, GOTO, and WAITFOR statements
• GROUP BY clause
  • about / The GROUP BY clause
  • syntax / The GROUP BY clause
  • HAVING clause / The HAVING clause

H

• HAVING clause
  • about / The HAVING clause

I

• IDENTITY column
  • data, inserting into / Example 4 – explicitly inserting data into the IDENTITY column
• IF…ELSE expression
  • about / The IF…ELSE expression
• implementation and loading phase, database design process / The implementation and loading phase
• implicit transaction mode / Transaction modes
• in-memory-optimized tables
  • limitations / The limitations of memory-optimized tables
• in-memory OLTP engine
  • about / The SQL Server 2014 in-memory OLTP engine
• in-memory optimized OLTP tables
  • SCHEMA_AND_DATA / The SQL Server 2014 in-memory OLTP engine
  • SCHEMA_ONLY / The SQL Server 2014 in-memory OLTP engine
• in-memory optimized tables
  • unsupported data types / The limitations of memory-optimized tables
• included columns
  • indexes, covering with / Covering indexes
• index
  • data, accessing with / Access with an index
• index, types
  • about / Index types
  • clustered indexes / Clustered indexes
  • nonclustered indexes / Nonclustered indexes
  • single-column indexes / Single-column indexes
  • composite indexes / Composite indexes
  • covering indexes / Covering indexes
  • unique indexes / Unique indexes
  • spatial indexes / Spatial indexes
  • partitioned indexes / Partitioned indexes
  • filtered indexes / Filtered indexes
  • full-text indexes / Full-text indexes
  • XML indexes / XML indexes
  • memory-optimized indexes / Memory-optimized indexes
  • columnstore indexes / Columnstore indexes
• indexed views
  • about / Indexed views
  • considerations, for creating / Indexed views
  • creating / Indexed view example
  • example / Indexed view example
• indexes
  • about / Indexes
  • cost associated with / The cost associated with indexes
  • using, in SQL Server / How SQL Server uses indexes
  • structure / The structure of indexes
  • covering, with included columns / Covering indexes
  • rebuilding, based on fragmentation level / Rebuild indexes based on the fragmentation level
• index fragmentation
  • about / Rebuild indexes based on the fragmentation level
• inline table-valued function
  • example / Inline table-valued function example
• INNER JOIN operator
  • using / Using INNER JOIN
  • about / Using INNER JOIN
• IN operator
  • about / The WHERE clause
• INSERT statement
  • about / Inserting data into SQL Server database tables
  • syntax / Inserting data into SQL Server database tables
  • arguments / Inserting data into SQL Server database tables
  • examples / The INSERT examples, Example 1 – insert a single row into a SQL Server database table, Example 2 – INSERT with the SELECT statement, Example 4 – explicitly inserting data into the IDENTITY column
  • using, with SELECT statement / Example 2 – INSERT with the SELECT statement
  • using, with EXEC statement / Example 3 – INSERT with the EXEC statement
• intent exclusive (IX) lock / Basic locks
• intent locks
  • about / Basic locks
  • intent shared (IS) / Basic locks
  • intent exclusive (IX) / Basic locks
  • shared with intent exclusive (SIX) / Basic locks
• intent shared (IS) lock / Basic locks
• INTERSECT operator
  • about / The INTERSECT operator
  • syntax / The INTERSECT operator
• IS NOT operator
  • about / The WHERE clause
• isolation
  • about / The fundamentals of transactions
• IS operator
  • about / The WHERE clause

J

• JOIN operator
  • about / The JOIN operator
  • syntax / The JOIN operator
  • inner joins / Using INNER JOIN
  • outer joins / Using outer joins
  • self joins / Using self joins

L

• LAG function
  • about / LEAD and LAG
  • syntax / LEAD and LAG
• large object (LOB) storage / SQL Server 2014 system data types
• LAST_VALUE function
  • about / FIRST_VALUE and LAST_VALUE
• LEAD function
  • about / LEAD and LAG
  • syntax / LEAD and LAG
• LEFT OUTER JOIN operator
  • using / Using LEFT OUTER JOIN
• LIKE operator
  • about / The WHERE clause
• local variable
  • creating / Creating a local variable
• locking
  • about / An overview of locking
• locking methods
  • pessimistic locking / Optimistic and pessimistic locking
  • optimistic locking / Optimistic and pessimistic locking
• locks, types
  • shared locks (S) / Basic locks
  • exclusive locks (X) / Basic locks
  • intent locks / Basic locks
  • update locks (U) / Basic locks
  • schema locks / Basic locks
  • bulk update locks (BU) / Basic locks
• logical design phase, database design process / The logical design phase
• logical filename / The SQL Server database architecture
• lost updates
  • about / An overview of locking

M

• many-to-many relationship / A many-to-many relationship
• master database
  • about / The master database
  • system-level configuration / The master database
• mathematical functions
  • about / Mathematical functions
• memory-optimized indexes
  • about / Memory-optimized indexes
  • URL, for information / Memory-optimized indexes
  • guidelines, for designing / Guidelines for designing and optimizing indexes, Create a clustered index before creating nonclustered indexes when using clustered indexes, Pad indexes and specify the fill factor to reduce page splits
  • guidelines, for optimizing / Guidelines for designing and optimizing indexes, Create a clustered index before creating nonclustered indexes when using clustered indexes, Pad indexes and specify the fill factor to reduce page splits
• memory-optimized tables / Creating and managing tables
• Memory Optimization Advisor article
  • URL / The SQL Server 2014 in-memory OLTP engine
• MERGE statement
  • using / Using the MERGE statement
  • syntax / Using the MERGE statement
  • arguments / Using the MERGE statement
  • examples / The MERGE statement examples
• metadata functions
  • about / Metadata functions
• model based database
  • creating / Example 1 – creating a database based on a model database
• model database / The model database
• msdb database / The msdb database
• Multiple Active Result Sets (MARS) / Transaction modes
• multiple rows
  • updating / Example 2 – updating multiple rows
• multiple table queries
  • about / Multiple table queries using UNION, EXCEPT, INTERSECT, and JOINs
  • with UNION operator / The UNION operator
  • with EXCEPT operator / The EXCEPT operator
  • with INTERSECT operator / The INTERSECT operator
  • with JOIN operator / The JOIN operator
• multistatement table-valued function
  • example / Multistatement table-valued function example
• MVCC
  • about / The SQL Server 2014 in-memory OLTP engine

N

• natively compiled stored procedures
  • about / Creating and using stored procedures
• nested triggers
  • about / Nested triggers
  • recursive triggers / Recursive triggers
  • DML triggers / DML triggers
• nonclustered columnstore index
  • creating / Creating and managing columnstore indexes
• nonclustered indexes
  • about / Nonclustered indexes
  • memory-optimized hash indexes / Memory-optimized indexes
  • memory-optimized non-hash indexes / Memory-optimized indexes
• nonrepeatable reads
  • about / An overview of locking
• normal forms
  • about / The normal forms
  • forth normal form (4NF) / The normal forms
  • fifth normal form (5NF) / The normal forms
  • URL, for information / The normal forms
  • first normal form (1NF) / The first normal form (1NF)
  • second normal form (2NF) / The second normal form (2NF)
  • third normal form (3NF) / The third normal form (3NF)
• normalization
  • about / The basics of data normalization
  • advantages / The basics of data normalization
  • disadvantages / The basics of data normalization
• NOT EXISTS keyword
  • about / The WHERE clause
• NOT IN operator
  • about / The WHERE clause
• NTILE function
  • about / Ranking functions

O

• one-to-many relationship / A one-to-many relationship
• one-to-one relationship / A one-to-one relationship
• online analytical processing (OLAP) / Denormalization
• online transaction processing (OLTP) / The basics of data normalization
  • about / The SQL Server 2014 in-memory OLTP engine
• operating system files, SQL Server
  • logical filename / The SQL Server database architecture
  • physical filename / The SQL Server database architecture
• optimistic locking
  • about / Optimistic and pessimistic locking
• ORDER BY clause
  • about / The ORDER BY clause
  • syntax / The ORDER BY clause
• outer joins
  • using / Using outer joins
  • LEFT OUTER JOIN / Using LEFT OUTER JOIN
  • RIGHT OUTER JOIN / Using RIGHT OUTER JOIN
  • FULL OUTER JOIN / Using FULL OUTER JOIN
  • CROSS JOIN / Using CROSS JOIN

P

• pages
  • about / Pages
  • URL, for information / Pages
• parsing
  • about / Parsing and binding
• PARTITION BY clause
  • about / Ranking functions
• partitioned indexes
  • about / Partitioned indexes
  • URL, for information / Partitioned indexes
• partitioned tables / Creating and managing tables
• PERCENTILE_CONT function
  • about / PERCENTILE_CONT and PERCENTILE_DISC
  • syntax / PERCENTILE_CONT and PERCENTILE_DISC
• PERCENTILE_DESC function
  • about / PERCENTILE_CONT and PERCENTILE_DISC
  • syntax / PERCENTILE_CONT and PERCENTILE_DISC
• PERCENT_RANK function
  • about / PERCENT RANK
• permission
  • granting, to securables with T-SQL DCL statements / Granting permissions to securables with T-SQL DCL statements
  • denying, to securables with T-SQL DCL statements / Denying permissions to securables with T-SQL DCL statements
  • revoking, to securables with T-SQL DCL statements / Revoking permissions to securables with T-SQL DCL statements
  • managing, with SSMS 2014 / Managing permissions using SSMS 2014
• pessimistic locking
  • about / Optimistic and pessimistic locking
• phantoms
  • about / An overview of locking
• physical design phase, database design process / The physical design phase
• physical filename / The SQL Server database architecture
• plan cache
  • about / Query optimization
  • clearing, manually / Manually clearing the plan cache
• plan caching
  • about / Query execution and plan caching
• primary data file / The SQL Server database architecture

Q

• query execution
  • about / Query execution and plan caching
• query optimization
  • about / Query optimization
• query optimization process
  • about / The SQL Server Relational Engine architecture
• query optimization statistics
  • about / Query optimization statistics
• query plan aging
  • about / Query plan aging

R

• RANK function
  • about / Ranking functions
• ranking functions
  • about / Ranking functions
  • ROW_NUMBER / Ranking functions
  • RANK / Ranking functions
  • DENSE_RANK / Ranking functions
  • NTILE / Ranking functions
• READ COMMITTED transaction isolation level / Transaction isolation
• READ UNCOMMITTED transaction isolation level / Transaction isolation
• recovery model, database
  • about / An overview of database recovery models
  • simple / The simple recovery model
  • bulk-logged / The bulk-logged recovery model
  • full recovery / Full recovery
• recursive triggers
  • about / Recursive triggers
• referential integrity
  • about / Data integrity
• Relational Engine
  • about / Components of SQL Server Database Engine, The SQL Server Relational Engine architecture
  • parsing / Parsing and binding
  • binding / Parsing and binding
  • query optimization / Query optimization
  • query execution / Query execution and plan caching
  • plan caching / Query execution and plan caching
  • query plan aging / Query plan aging
  • improved design, in SQL Server 2014 for cardinality estimation / The improved design in SQL Server 2014 for the cardinality estimation
  • optimizing, for ad hoc workloads / Optimizing SQL Server for ad hoc workloads
  • plan cache, clearing manually / Manually clearing the plan cache
• relationships
  • about / Relationships
  • one-to-one relationship / A one-to-one relationship
  • one-to-many relationship / A one-to-many relationship
  • many-to-many relationship / A many-to-many relationship
• Reorganize and Rebuild Indexes article
  • URL, for information / Rebuild indexes based on the fragmentation level
• REPEATABLE READ transaction isolation level / Transaction isolation
• requirement collection and analysis phase, database design process / The requirement collection and analysis phase
• resource database / The resource database
• RETURN statement / RETURN, GOTO, and WAITFOR statements
• RIGHT OUTER JOIN operator
  • using / Using RIGHT OUTER JOIN
• ROLLBACK TRANSACTION statement
  • about / ROLLBACK TRANSACTION
  • syntax / ROLLBACK TRANSACTION
• row
  • inserting, to SQL Server database table / Example 1 – insert a single row into a SQL Server database table
  • updating / Example 1 – updating a single row
  • deleting / Example 1 – deleting a single row
• rows
  • deleting / Example 2 – deleting all rows
• rowset functions
  • about / Rowset functions
• row versioning
  • about / Transaction isolation
• ROW_NUMBER function
  • about / Ranking functions

S

• SAVE TRANSACTION statement
  • about / SAVE TRANSACTION
  • syntax / SAVE TRANSACTION
• scalar functions, within CATCH block
  • ERROR_NUMBER() / Handling Transact-SQL errors
  • ERROR_MESSAGE() / Handling Transact-SQL errors
  • ERROR_SEVERITY() / Handling Transact-SQL errors
  • ERROR_STATE() / Handling Transact-SQL errors
  • ERROR_LINE() / Handling Transact-SQL errors
  • ERROR_PROCEDURE() / Handling Transact-SQL errors
• schema
  • about / Creating and managing database schemas
  • listing, in SSMS 2014 / Creating and managing database schemas
  • managing, with T-SQL DDL statements / Managing schemas using T-SQL DDL statements
  • managing, with SSMS 2014 / Managing schemas using SSMS 2014
• schema locks
  • about / Basic locks
• SCHEMA_AND_DATA in-memory optimized OLTP tables
  • about / The SQL Server 2014 in-memory OLTP engine
• SCHEMA_ONLY in-memory optimized OLTP tables
  • about / The SQL Server 2014 in-memory OLTP engine
• Search Arguments (SARG)
  • about / Query optimization
• secondary data file / The SQL Server database architecture
• second normal form (2NF)
  • about / The normal forms, The second normal form (2NF)
• securables
  • permissions, granting to / Granting permissions to securables with T-SQL DCL statements
  • permissions, denying to / Denying permissions to securables with T-SQL DCL statements
  • permissions, revoking to / Revoking permissions to securables with T-SQL DCL statements
• security functions
  • about / Security functions
• SELECT INTO statement
  • about / The SELECT INTO statement
• SELECT statement
  • about / The SELECT statement, Creating a local variable
  • purposes / The SELECT statement
  • syntax / The SELECT statement
  • INSERT statement, using with / Example 2 – INSERT with the SELECT statement
• select_list parameter / The SELECT statement
• self joins
  • using / Using self joins
• SERIALIZABLE transaction isolation level / Transaction isolation
• Server 2014 system databases
  • master database / The master database
  • model database / The model database
  • msdb database / The msdb database
  • tempdb database / The tempdb database
  • resource database / The resource database
  • distribution database / The distribution database
• service-level agreement (SLA) / Indexes
• SET keyword
  • about / Creating a local variable
• shared locks (S)
  • about / Basic locks
• shared with intent exclusive (SIX) lock / Basic locks
• simple recovery model / The simple recovery model
• single-column indexes
  • about / Single-column indexes
• SNAPSHOT transaction isolation level / Transaction isolation
• spatial indexes
  • about / Spatial indexes
• Specify Fill Factor for an Index article
  • URL, for information / Pad indexes and specify the fill factor to reduce page splits
• sp_who2 system stored procedure / The sp_who and sp_who2 system stored procedures
• sp_who system stored procedure / The sp_who and sp_who2 system stored procedures
• SQL Server
  • indexes, using / How SQL Server uses indexes
  • transaction modes / Transaction modes
  • lock, issuing for row identifier / An overview of locking
  • lock, issuing for key / An overview of locking
  • lock, issuing for table / An overview of locking
  • lock, issuing for database / An overview of locking
  • lock, issuing for page / An overview of locking
  • lock, issuing for extent / An overview of locking
• SQLServer*Locks performance object
  • about / The SQLServer:Locks performance object
  • monitoring / The SQLServer:Locks performance object
• SQL Server 2014
  • system data types / SQL Server 2014 system data types
  • transaction isolation levels / Transaction isolation
• SQL Server database architecture
  • about / The SQL Server database architecture
  • pages / Pages
  • extent / Extents
  • transaction log file architecture / The transaction log file architecture
  • filegroups / Filegroups
• SQL Server Database Engine
  • components / Components of SQL Server Database Engine
• SQL Server database table
  • row, inserting to / Example 1 – insert a single row into a SQL Server database table
• SQL Server database tables
  • data, inserting into / Inserting data into SQL Server database tables
  • data, updating in / Updating data in SQL Server database tables
  • data, deleting from / Deleting data from SQL Server database tables
• SQL Server Extended Events
  • about / SQL Server Extended Events
  • URL, for article / SQL Server Extended Events
• SQL Server performance
  • troubleshooting tools / SQL Server 2014 tools for monitoring and troubleshooting SQL Server performance, The SQLServer:Locks performance object, SQL Server Profiler, SQL Server Extended Events
  • monitoring tools / SQL Server 2014 tools for monitoring and troubleshooting SQL Server performance, The SQLServer:Locks performance object, SQL Server Profiler
• SQL Server Profiler
  • about / SQL Server Profiler
  • event classes / SQL Server Profiler
• SQL Server query optimizer
  • about / Query optimization
• SSMS 2014
  • alias data types, creating with / Creating and dropping alias data types with SSMS 2014
  • alias data types, dropping with / Creating and dropping alias data types with SSMS 2014
  • used, for creating database / Creating a database with SSMS 2014
  • used, for modifying database / Modifying a database with SSMS 2014
  • used, for dropping database / Dropping a database with SSMS 2014
  • used, for managing schema / Managing schemas using SSMS 2014
  • used, for creating tables / Creating a table with SSMS 2014
  • used, for modifying tables / Modifying a table with SSMS 2014
  • used, for deleting tables / Deleting a table with SSMS 2014
  • used, for managing permission / Managing permissions using SSMS 2014
  • views, creating with / Creating views with Transact-SQL and SSMS 2014, Creating views with SSMS 2014
  • views, altering with / Creating, altering, and dropping views with SSMS 2014, Altering and dropping views with SSMS 2014
  • views, dropping with / Creating, altering, and dropping views with SSMS 2014, Altering and dropping views with SSMS 2014
• statistics
  • creating, manually / Manually create and update statistics
  • updating / Manually create and update statistics
  • last update date, determining / Determine the date when the statistics were last updated
• STATS_DATE() function
  • sys.stats catalog view, using with / Using the sys.stats catalog view with the STATS_DATE() function
• Storage Engine
  • about / Components of SQL Server Database Engine
• stored procedure
  • about / Creating and using stored procedures
  • creating / Creating and using stored procedures, Creating a stored procedure
  • using / Creating and using stored procedures
  • example / Creating a stored procedure
  • modifying / Modifying a stored procedure
  • dropping / Dropping a stored procedure
  • viewing / Viewing stored procedures
  • executing / Executing stored procedures
• stored procedures, types
  • user-defined stored procedures / Creating and using stored procedures
  • natively compiled stored procedures / Creating and using stored procedures
  • system stored procedures / Creating and using stored procedures
  • temporary stored procedures / Creating and using stored procedures
  • extended user-defined stored procedures / Creating and using stored procedures
  • CLR stored procedures / Creating and using stored procedures
• string functions
  • about / String functions
• subqueries
  • about / Subqueries
  • examples / Examples of subqueries
• sys.stats catalog view
  • using, with STATS_DATE() function / Using the sys.stats catalog view with the STATS_DATE() function
• system databases, SQL Server 2014
  • SQL Server 2014 / SQL Server 2014 system databases
• system data types, SQL Server 2014
  • about / SQL Server 2014 system data types
  • exact numeric data types / SQL Server 2014 system data types
  • approximate numeric data types / SQL Server 2014 system data types
  • character string data types / SQL Server 2014 system data types
  • unicode character string data types / SQL Server 2014 system data types
  • date and time data types / SQL Server 2014 system data types
  • binary string data types / SQL Server 2014 system data types
• system statistical functions
  • about / System statistical functions
• system stored procedures
  • about / Creating and using stored procedures
• system tables / Creating and managing tables

T

• T-SQL
  • clauses / Understanding Transact-SQL SELECT, FROM, and WHERE clauses
  • URL, for deterministic and nondeterministic functions / System statistical functions
  • analytic window functions / Using the Transact-SQL analytic window functions
  • views, creating with / Creating views with Transact-SQL and SSMS 2014
• T-SQL DCL statements
  • used, for granting permissions to securables / Granting permissions to securables with T-SQL DCL statements
  • used, for denying permissions to securables / Denying permissions to securables with T-SQL DCL statements
  • used, for revoking permissions to securables / Revoking permissions to securables with T-SQL DCL statements
• T-SQL DDL statements
  • used, for creating database / Creating a database with T-SQL DDL statements
  • used, for modifying database / Modifying a database with T-SQL DDL statements
  • used, for dropping database / Dropping a database with T-SQL DDL statements
  • used, for managing schema / Managing schemas using T-SQL DDL statements
  • used, for creating tables / Creating a table with T-SQL DDL statements
  • used, for modifying tables / Modifying a table with T-SQL DDL statements
  • used, for dropping tables / Dropping a table with T-SQL DDL statements
  • views, creating with / Creating, altering, and dropping views with Transact-SQL DDL statements
  • views, altering with / Creating, altering, and dropping views with Transact-SQL DDL statements
  • views, dropping with / Creating, altering, and dropping views with Transact-SQL DDL statements
• T-SQL errors
  • handling / Handling Transact-SQL errors
• T-SQL functions, in query
  • about / Using T-SQL functions in the query
  • aggregate functions / Aggregate functions
  • configuration functions / Configuration functions
  • cursor functions / Cursor functions
  • date and time functions / Date and time functions
  • mathematical functions / Mathematical functions
  • metadata functions / Metadata functions
  • rowset functions / Rowset functions
  • security functions / Security functions
  • string functions / String functions
  • system statistical functions / System statistical functions
• T-SQL statements
  • DDL statements / Data Definition Language (DDL) statements
  • DML statements / Data Manipulation Language (DML) statements
  • DCL statements / Data Control Language (DCL) statements
• table design
  • about / Table design
  • tables / Tables
  • entities / Entities
  • attributes / Attributes
• tables
  • about / Tables, Creating and managing tables
  • temporary tables / Creating and managing tables
  • system tables / Creating and managing tables
  • user-defined tables / Creating and managing tables
  • partitioned tables / Creating and managing tables
  • file tables / Creating and managing tables
  • memory-optimized tables / Creating and managing tables
  • creating, with T-SQL DDL statements / Creating a table with T-SQL DDL statements
  • modifying, with T-SQL DDL statements / Modifying a table with T-SQL DDL statements
  • dropping, with T-SQL DDL statements / Dropping a table with T-SQL DDL statements
  • creating, with SSMS 2014 / Creating a table with SSMS 2014
  • modifying, with SSMS 2014 / Modifying a table with SSMS 2014
  • overindexing, avoiding / Avoid overindexing tables
• table variable
  • creating / Creating the table variable
  • syntax / Creating the table variable
  • example / Creating the table variable
• tempdb database / The tempdb database
• temporary stored procedures
  • about / Creating and using stored procedures
• temporary tables / Creating and managing tables
• testing and evaluation phase, database design process / The testing and evaluation phase
• third normal form (3NF)
  • about / The normal forms, The third normal form (3NF)
• TOP clause
  • about / The TOP clause
  • syntax / The TOP clause
• Transact-SQL DDL statement
  • used, for creating alias data types / Creating and dropping alias data types using the Transact-SQL DDL statement
  • used, for dropping alias data types / Creating and dropping alias data types using the Transact-SQL DDL statement
• transaction isolation
  • about / Transaction isolation
• transaction isolation levels, SQL Server 2014
  • READ UNCOMMITTED / Transaction isolation
  • READ COMMITTED / Transaction isolation
  • REPEATABLE READ / Transaction isolation
  • SNAPSHOT / Transaction isolation
  • SERIALIZABLE / Transaction isolation
• transaction log file / The SQL Server database architecture
• transaction log file architecture
  • about / The transaction log file architecture
  • operation / The operation and workings of a transaction log
  • working / The operation and workings of a transaction log
• transaction modes
  • explicit / Transaction modes
  • implicit / Transaction modes
  • autocommit / Transaction modes
  • batch-scoped / Transaction modes
• transactions
  • about / The fundamentals of transactions
  • characteristics / The fundamentals of transactions
  • implementing / Implementing transactions
• triggers
  • about / Creating and using triggers
  • uses / Creating and using triggers
  • nested triggers / Nested triggers
  • disabling / Disabling and enabling triggers
  • enabling / Disabling and enabling triggers
  • viewing / Viewing triggers
• TRUNCATE TABLE statement
  • about / The TRUNCATE TABLE statement
  • example / The TRUNCATE TABLE statement
• TRY…CATCH block
  • example / An example of TRY...CATCH
• TRY…CATCH with RAISERROR block
  • example / An example of TRY...CATCH with RAISERROR
• TRY…CATCH with THROW block
  • example / An example of TRY...CATCH with THROW

U

• UNION ALL operator
  • about / The UNION operator
• UNION operator
  • about / The UNION operator
  • syntax / The UNION operator
• unique indexes
  • about / Unique indexes
  • features / Unique indexes
  • URL, for information / Unique indexes
• update locks (U)
  • about / Basic locks
• UPDATE statement
  • about / Updating data in SQL Server database tables
  • syntax / Updating data in SQL Server database tables
  • arguments / Updating data in SQL Server database tables
  • examples / The UPDATE statement examples
• UPDATE STATISTICS (Transact-SQL) article
  • URL / Manually create and update statistics
• UPDATE STATISTICS statement
  • about / Manually create and update statistics
• user-defined functions
  • about / Creating and using user-defined functions
  • creating / Creating user-defined functions
  • modifying / Modifying user-defined functions
  • scalar-value functions, syntax / Modifying user-defined functions
  • inline table-valued function, syntax / Modifying user-defined functions
  • multistatement table-valued functions, syntax / Modifying user-defined functions
  • dropping / Dropping user-defined functions
  • viewing / Viewing user-defined functions
• user-defined integrity
  • about / Data integrity
• user-defined scalar function
  • creating / Creating a user-defined scalar function
  • using / Using a user-defined scalar function
• user-defined stored procedures
  • about / Creating and using stored procedures
• user-defined table-valued function
  • creating / Creating a user-defined table-valued function
  • inline table-valued function, syntax / Creating a user-defined table-valued function
  • multistatement table-valued function, syntax / Creating a user-defined table-valued function
  • using / Using a user-defined table-valued function
• user-defined tables / Creating and managing tables

V

• variables
  • creating / Creating and using variables
  • using / Creating and using variables
  • local variable, creating / Creating a local variable
  • cursor variable, creating / Creating the cursor variable
  • table variable, creating / Creating the table variable
• Venn diagram
  • for UNION ALL operator / The UNION operator
  • for UNION operator / The UNION operator
• views
  • about / Creating and using views
  • creating / Creating and using views
  • using / Creating and using views
  • uses / Creating and using views
  • creating, with T-SQL / Creating views with Transact-SQL and SSMS 2014
  • creating, with SSMS 2014 / Creating views with Transact-SQL and SSMS 2014, Creating views with SSMS 2014
  • creating, with T-SQL DDL statements / Creating, altering, and dropping views with Transact-SQL DDL statements
  • altering, with T-SQL DDL statements / Creating, altering, and dropping views with Transact-SQL DDL statements
  • dropping, with T-SQL DDL statements / Creating, altering, and dropping views with Transact-SQL DDL statements
  • altering, with SSMS 2014 / Creating, altering, and dropping views with SSMS 2014, Altering and dropping views with SSMS 2014
  • dropping, with SSMS 2014 / Creating, altering, and dropping views with SSMS 2014, Altering and dropping views with SSMS 2014
• Virtual Log Files (VLFs) / The transaction log file architecture

W

• WAITFOR statement / RETURN, GOTO, and WAITFOR statements
• WHERE clause
  • about / The WHERE clause
• WHILE statement
  • about / WHILE, BREAK, and CONTINUE statements

X

• XML indexes
  • about / XML indexes
  • URL, for information / XML indexes