Welcome to the SQL Server 2008 Development Course web page!

Here you will find useful links and information that I have gathered while teaching this course.

Setup

How to install the sample databases:

First, make sure to install the Northwind database from the CD that comes with the textbook (MCTS Exam 70-433 Training Kit).

Then, install the AdventureWorks2008 and AdventureWorksDW2008 databases from http://msftdbprodsamples.codeplex.com/Release/ProjectReleases.aspx?ReleaseId=18407.

Don’t bother to install the Adventureworks databases from the CD – it won’t work.

After you complete the installation you should see at least the following databases listed in SSMS: AdentureWorks2008, AdventureWorksDW2008, and Northwind.

SQL for Beginners

This course is based on the Microsoft SQL Server 2008 – Database Development Training Kit which can be purchased @ http://www.amazon.com/MCTS-Self-Paced-Training-Exam-70-433/dp/0735626391

This course covers a lot of content so if you are only starting to learn SQL, I highly recommend you follow an online SQL tutorial such as http://www.sqlcourse.com or better yet, read the book Microsoft SQL Server 2008 For Dummies which can purchased from Amazon @ http://www.amazon.com/Microsoft-Server-2008-Dummies-Computer/dp/0470224657. I have read this book myself and I always recommend it to my students – especially if the student has little or no prior SQL experience. Don’t let the “dummies” title fool you – this book is loaded with goodness – it covers, among other things: CRUD SQL operations, Stored Procedures, Aggregate Functions, Table Creation, Data types, Triggers, Replication, Mirroring, and much much more.

Course Material

The following are online resources that explain material much better than the explanation found in the training kit provided by Microsoft Press for certification exam 70-433.

• Group By/Having/Grouping/Grouping Sets
  • http://www.w3schools.com/sql/sql_groupby.asp
  • http://www.w3schools.com/sql/sql_having.asp & http://www.techonthenet.com/sql/having.php
  • http://msdn.microsoft.com/en-us/library/ms178544.aspx
  • http://blogs.conchango.com/jamiethomson/archive/2007/06/04/Katmai_3A00_-T_2D00_SQL-Grouping-sets.aspx & http://www.sql-server-performance.com/articles/dba/Grouping_Sets_in_SQL_Server_2008_p1.aspx 
• Join Examples
  • You can download a T-SQL script with all the different types of joins @ http://www.leonidsorokin.com/SQLServer2008Course/JoinExample.sql, and another one @ http://www.leonidsorokin.com/SQLServer2008Course/JoinExample2.sql, and also another one @ http://www.leonidsorokin.com/SQLServer2008Course/JoinExample3.sql
• GO Batch Command
  • http://msdn.microsoft.com/en-us/library/ms188037.aspx
  • http://www.mssqltips.com/tip.asp?tip=1216
• SQL Strings with a ‘N’ prefix
  • http://databases.aspfaq.com/general/why-do-some-sql-strings-have-an-n-prefix.html
• Analyzing data with ROLLUP and CUBE (PL/SQL examples but very similar to T-SQL)
  • http://www-camden.rutgers.edu/help/Documentation/Oracle/server.815/a68003/rollup_c.htm
• Recursive queries using CTEs (Common Table Expressions)
  • http://msdn.microsoft.com/en-us/library/ms186243.aspx
  • http://www.mssqltips.com/tip.asp?tip=1520
  • http://www.sqlservercurry.com/2009/06/simple-family-tree-query-using.html
• Subqueries
  • http://www.databasejournal.com/features/mssql/article.php/3464481/Using-a-Subquery-in-a-T-SQL-Statement.htm
  • http://www.databasejournal.com/features/mssql/article.php/3485291/Using-a-Correlated-Subquery-in-a-T-SQL-Statement.htm
• Ranking functions
  • http://pratchev.blogspot.com/2007/01/row-number-in-sql-server.html
  • http://drsql.spaces.live.com/Blog/cns!1pqLLlQgw9hZKLV1K4eLatKw!241.entry
  • http://drsql.spaces.live.com/blog/cns!80677FB08B3162E4!248.entry
• Apply Operator
  • http://www.databasejournal.com/features/mssql/article.php/3616286/Apply-Operator.htm
• Filestreams 
  • http://msdn.microsoft.com/en-us/magazine/dd695918.aspx
• Merge
  • http://blogs.microsoft.co.il/blogs/bursteg/archive/2007/12/06/sql-server-2008-t-sql-merge-statement.aspx (The T-SQL code samples contain old MERGE code syntax – you can download a sample script with the correct syntax that I have created based on Guy Burstein’s example @ http://www.leonidsorokin.com/SQLServer2008Course/MergeExample.sql)
• Temporary Tables & Table Variables
  • http://www.informit.com/articles/article.aspx?p=28497&seqNum=4
• Create a copy of a table quickly
  • http://technet.microsoft.com/en-us/magazine/dd401720.aspx (For example, use SELECT * INTO NewTableName FROM OriginalTableName WHERE 1=0 if you are interested only in copying the schema and not the data itself)
• Deadlocks
  • You can find a great example of typical database deadlock @ http://www.campbellassociates.ca/blog/PermaLink.aspx?guid=823bdca5-ec17-4689-ac0c-e653664164bf (I have created a sample T-SQL script based on Richard Campbell’s blog post, so that you can get up and running quickly – you can download it @ http://www.leonidsorokin.com/SQLServer2008Course/DeadlockExample.sql)
• Collations
  • Each SQL Server 2008 database has a collation defined on it. You can read more about collations @ http://blog.sqlauthority.com/2008/12/03/sql-server-default-collation-of-sql-server-2008 and http://msdn.microsoft.com/en-us/library/ms144260.aspx. You can also change the collation on-thy-fly per query. For example, by default the string comparisons are case-insensitive, but if you need to make sure a particular query uses a case-sensitive string comparison you can try this: SELECT * FROM [Table] WHERE [ColumnName] LIKE '_A%' Collate SQL_Latin1_General_CP1_CS_AS; (Try toggling between ‘CS’ and the default ‘CI’ and notice the difference). The default collation is SQL_Latin1_General_CP1_CI_AS.

Where do I take the certification exam test?

Go to the Prometric website @ http://www.prometric.com/Microsoft

You will be able to schedule your test and locate a test center near you.