Study Guide for Certification Exam 70-433

Here's a study guide for exam takers who will take the certification Exam 70-433 Microsoft SQL Server 2008, Database Development. I prepared this 70-433 certification exam guide after my 70-433 exam experience for SQL Server database developers. Be sure that before you start studying for the 70-433 certification exam, you have visited and review the exam content at Microsoft Learning web portal. An other guide for SQL database developers who want to be certified at Exam 70-433 Microsoft SQL Server 2008, Database Development skills is the resources.

Partition Tables : Yes this is one of the hot topics of MS SQL Server 2008. You should work with a sample table and an archive table of that table. Than try moving some of the data from main table to the archive table. Believe me, you will have to do this in your production environment many times. It is easy with partitioned tables and Switch, Merge and Split commands on Microsoft SQL Server 2008 databases now.

Identify Deadlocks using SQL Profiler: Try to catch the context of a deadlock problem in a SQL 2008 database. I feel locky myself since I had done it before. I can recommend you the article at http://www.simple-talk.com/sql/learn-sql-server/how-to-track-down-deadlocks-using-sql-server-2005-profiler/ for tracking deadlocks in SQL Server Profiler tool.

New SQL data types : Do you know the new sql data types introduced with the MS SQL Server 2008 version. I can recommand you to work on new data types in detail, new sql date and time data types, as well ass the new spatial data types. I had such questions on beta exams of SQL Server 2008 certification exams more than once.

Execution Plans and query statistics : Do you ever use the SHOWPLAN command to see how the SQL Server engine is running your query. You can optimize your sql queries if you work on the execution plans and reach better performance from yout queries as a sql developer. Also you can measure your queries performance comparing the IO statistics of each different execution of different queries by using the SET STATISTICS IO t-sql command. Work on SET STATISTICS IO, SET SHOWPLAN_ALL, etc t-sql commands that will help you gather sql performance data from query analyzer.

Powershell : Powershell component is new with MS SQL Server 2008. So have idea about how you can call a ps command, etc.

SQL Server collations : Yes have detailed idea about instance default collation, database collations, tempdb database collation. Work with collations different than the default database collation and get idea how indexes and udf's are effected from such collation variations.

Table-Valued Parameters : TVPs are new with MS SQL Server 2008, so as a sql developer, you should get enough idea about how table-valued parameters are used in SQL Server 2008 databases. During SQL Server development tasks as sql programmers you will find alot use of TVPs. Please check the SQL tutorial Table Valued Parameters in SQL Server 2008 for more detail.

Transactions : Transactions are not new with SQL2008. So if you have worked with sql server transactions before in MS SQL Server 2005 or even in MS SQL Server 2000, you have enough experience and information on sql transactions. But you can study basic transaction commands, BEGIN TRAN, ROLLBACK, COMMIT and nested transactions, etc.

Fulltext indexing : Yes, Fulltext index in Microsoft SQL Server 2008 has great improvements. So work with MS SQL2008 fulltext and use features of fulltext search, and have hand on experience on fulltext search t-sql commands.

DDL Triggers : You know DDL (Data Definition Language) first introduced with MS SQL Server 2005. So you have probably worked with some samples of DDL triggers already. If not you'll find samples at Prevent DROP Table using SQL Server DDL Trigger and DDL Triggers (Data Definition Language Triggers)

Error Handling : SQL developers should study on error handling in SQL Server, like TRY-CATCH blocks, registering error messages and raising errors in T-SQL code blocks. Please check the SQL tutorial SQL Try Catch for a sample case.

Group By Grouping Sets : As a sql developer, the new Grouping Sets extentions to Group By sql command is now giving more power to us. So you should have understood Group By Grouping Sets and had worked with. Please refer to T-SQL tutorial Group By Grouping Sets for samples.

Merge : The new Merge t-sql command is also new and it gives the ability to sql developers to decide to update, insert or delete on one I/O. Be sure you know this new t-sql command MERGE very well. For samples and more information on SQL Merge command please refer to T-SQL tutorial SQL Server 2008 T-SQL MERGE Statement and MERGE T-SQL Command Enhancement

XML in MS SQL Server : You will have a quite number of XML related questions in 70-433. Querying XML data in SQL 2008, indexing XML columns, XQuery, XPath, For XML, etc should be well known if you want to pass the XML questions in 70-433.

CLR : CLR (Common Language Runtime) and permission sets are issues to know for sql development using CLR objects.

Ranking functions : You know SQL Server 2005 has introduced the new ranking functions for sql developers. I'm sure , ranking functions like Row_Number(), Rank(), Dense_Rank(), NTile() are practiced a lot by sql programmers during the sql developments done since SQL Server 2005. Here is a brief entry for SQL Server Ranking Functions

CTE : CTE 's (Common Table Expression) should be well understood by every SQL developer. But again since this feature CTE is first introduced with MS SQL Server 2005, for a certification exam of SQL2008, I believe experienced sql programmers will not get confused in CTE questions. Programmers can refer to TSQL tutorial Common Table Expression (SQL CTE) in SQL Server and SQL Recursive CTE Query for more information.

Subqueries : As well as CTEs subqueries are used a lot since the first versions of MS SQL Server. So nothing to worry about subqueries I believe.

Database Mail : SQL Server Database mail component has helped sql development and t-sql programming tasks and simplified SQL Server administration tasks by enabling automated email sending within SQL Server within T-SQL codes and SQL Server Management Studio wizards. Not only developers but SQL Server database administrators should know how to configure Database Mail in SQL Server 2008 instance, and how to send email using Database Mail in T-SQL codes.