Overview:
This course will teach you how to build:
- basic queries using Transact-SQL, the language of SQL Server
- effective views, stored procedures, triggers, and user-defined functions, using Transact-SQL.
It will also cover the new enhancements to the Transact-SQL programming language including improved support for error handling and hierarchical queries, and programmers can now use .NET languages like C# and Visual Basic to build database objects.
Learning Method:
This three day course (approximately 7 hours per day) uses a combination of instructor presentation and student hands-on practical exercises.
Who will benefit from this course?
This course is designed for professionals who have the need to extract data of a specific type from Microsoft SQL Server 2005 / 2008. Many past students have come from financial and production management backgrounds with a need to retrieve data in a particular manner that is not readily available to them, necessitating the need to write custom queries.
Participants are not expected to be DBA’s or programmers.
Pre-requisites:
To get the most out of this course, you should have an understanding of relational databases.
No particular programming experience is required.
What can you expect to gain from this course?
After completing this course, you will be able to:
- build queries in Transact-SQL to insert, update, delete, and retrieve data
- create stored procedures to promote enhanced security and performance
- take advantage of triggers and user-defined functions
- utilise SQL server transactions for insure data integrity
- leverage their .NET programming skills with SQLCLR
- create highly optimised queries using advanced techniques.
Course Content:
Module 1:A Tour of SQL Server 2005
- SQL Server 2005 editions, components, and tools
- Using SQL Server Management Studio (SSMS)
- Working with tables and queries
- New Transact-SQL features
- Business intelligence services.
Module 2:Designing and Creating a Database
- Relational database design principles
- Implementing the design.
Module 3:Data Selection Queries
- Understanding Transact-SQL
- The SELECT statement
- The WHERE clause
- Using ORDER BY to sort data
- The GROUP BY clause
- Joining tables.
- Modifying data
- Inserting data
- Updating data
- Deleting data
- Understanding transaction isolation.
Module 5:Working with SQL Server Management Studio
- Getting started with SSMS
- Exploring the object explorer
- Working with the query editor
- Using SQL Server books online.
Module 6:Transact-SQL Programming
- Overview of Transact-SQL
- Using Built-In functions
- Controlling flow
- Ranking results.
Module 7:Transactions and Error Handling
- Transaction concepts
- Applications and transactions
- Creating explicit transactions
- Using TRY/CATCH error handling.
- What is a view?
- Creating views
- Updating data using a view
- Using computed columns
- Indexed views
- Partitioned views.
Module 9:Creating Stored Procedures and Triggers
- Creating stored procedures
- Creating triggers
Module 10:Creating User-Defined Functions
- User-defined function overview
- Scalar functions
- Inline table-valued functions
- Multi-statement table-valued functions
- Using functions, views, and stored procedures.
Module 11:Using .NET Code in SQL Server 2005
- Writing SQLCLR code
- SQLCLR code modules
- Managing code modules
- SQLCLR security
- T-SQL vs. .NET code
Module 12:Advanced Query Techniques
- Full-text search
- Generating XML with FOR XML
- Using APPLY
- Creating recursive queries
- Creating pivot queries
- Executing dynamic SQL