SQL Server Transact-SQL Programming

• SQL Server edition overview
• SQL Server Management Studio

Creating schemas

• Referencing schemas versus using the default schema
• Hiding schemas with synonyms

Building tables

• Selecting appropriate SQL Server data types
• Constructing tables with CREATE TABLE

Adding constraints

• Enforcing uniqueness using PRIMARY KEY and UNIQUE constraints
• Validating relationships using FOREIGN KEY

Batch and stored procedure processing

• Minimising network traffic using batches and procedures
• Stored procedure compilation and execution
• Using scalar functions

Selecting data

• Developing stored procedures that extract data from multiple servers
• Executing dynamic queries using OPENROWSET and OPENQUERY
• Executing remote procedures
• Capturing RETURN values from stored procedures

Declaring variables and parameters

• Creating and utilising local variables
• Passing input and output parameters

Calling built-in scalar functions

• Converting data using CAST and CONVERT
• Ordering data with ranking functions

Modifying data

• Ensuring data consistency with transactions and distributed transactions
• Managing concurrency with isolation levels
• SQL Server locking fundamentals
• Avoiding blocking problems with read-committed snapshot isolation

Programming procedural statements

• Implementing conditions with IF...ELSE
• Looping with WHILE and GOTO
• Creating code blocks with BEGIN...END
• Debugging T-SQL in Management Studio

Handling errors

• Communicating problems to the client with RAISERROR
• Intercepting errors with TRY ... CATCH
• Dealing with open transactions

Producing server-side result sets

• Building and using temporary tables
• Processing rows on the server with a cursor
• Taking advantage of table variables

Storing queries on the server

• Concealing complexity with views
• Solving business problems using multi-statement table-valued functions

Creating user-defined functions

• Calculating values with scalar functions
• Taking advantage of schema binding

Formulating triggers

• INSTEAD OF vs. AFTER triggers
• Detecting row changes using the inserted/deleted tables
• Tracing metadata changes with DDL triggers
• Auditing user access using a LOGON trigger
• Tracking data changes with the OUTPUT clause