MSSQL Server 2005: Database Administration

SQL Server 2005: Database Administration

SQL Server Architecture

• SQL Server edition overview
• Introducing the tools
• SQL Server Management Studio

Managing Tables with DDL

Creating schemas

• Managing 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

Retrieving Data with Transact-SQL Stored Procedures

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
• Combining tabular results with UNION, INTERSECT and EXCEPT
• Executing remote procedures
• Producing cross tabulations using PIVOT

Declaring variables and parameters

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

Calling built-in scalar functions

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

Maintaining Data

Modifying data

• Inserting, updating and deleting 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
• Managing locks using hints

Programming procedural statements

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

Handling errors

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

Producing server-side result sets

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

Developing Views, Functions and Triggers

Storing queries on the server

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

Creating user-defined functions

• Calculating values with scalar functions
• Processing multiple rows returned from a table-valued function
• Taking advantage of schema binding

Formulating triggers

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

Top