70-433 – 000 – Introduction

Welcome to this series, where I will try to cover some of the most interesting and problematic topics in relation to this Microsoft Exam. The exam is a exam at the technology specialist exam, but to be honest, then it is an exam the divide people into two groups, where a lot of people tend to find the exam easy, and other people basically never pass.

With the proper preparation, then it should be quite easy to pass, but it requires a lot of hours of hard work.

this introduction is split into three parts:

1) Prerequisites

2) What is required for this exam

3) supplementary reading / material

4) Video material

A lot of the material for this course can used to pass the exam for both SQL server 2008R2 and SQL Server 2008.

PREREQUISITES

To be able to follow this blog, then you should have downloaded the adventureworks from codeplex.

then you should buy the book MCTS Self pased training exam 70-433. This is the core book for the exam, but let me be 100% honest. It will not make sure that you pass the exam.

WHAT IS REQUIRED FOR THIS EXAM

You can read Microsoft Official studyplan by clicking here, but you don’t get any idea about the level of what you are expected to learn. The level is quite technical, but not impossible.

To understand this exam, then we must start understanding why we have an SQL-server, and basically we use it to store data and to retrieve them again. We store information about a customer and we retrieve it again. It sounds simple, and it is. at some point then some guys figured out that we could store things in tables (Imagine a EXCEL-sheet) and that is the basic structure of a microsoft SQL Server.

Later on then someone figured out that it would be nice to store things in multiple tables and to normalize a database. The relational database was born.

Since that, the world has become a much more complex place to live, and that also goes for the Microsoft SQL server. Now we have XML-data, we want to be able to search like you do on Bing or google, and we want to do it faster and more effient.

During this course we will see how the relational database is created and how techniques to fullfill the requirements of today are used.

The book covers 9 chapters, and they are:

Chapter Description
1 – Data Retrieval This is were we begin, and this is were we start to understand how to retrieve data from a relational database. the most important words are:

  1. Select – From – Where – Order by
  2. Inner joins – Outer Joins – Cross Joins – Self Join
  3. Aggregation (Avg, Sum, count and so on) – Group by – Rollup – cube – grouping set – Having
  4. Union, Union All, Except, Intersect, Apply
  5. Built-in Scalar Functions
2 – Modifying data If you only know how to select, then it will be impossible to get data into the database, so you also need to know how to manipulate data. the most important words/techniques are:

  1. Insert, Update, Delete, Truncate Table
  2. Output clause, Merge
  3. Transactions, Locking, Transaction Isolation Levels
3 – Tables, data types, Integrity Now you can retrieve data, you can insert,update and delete data, but what if you want to create your own database, then you must be able to create databases and tables. In this module we look at:

  1. Datatypes. what datatypes are there, and why is it important to know the difference.
  2. How to create tables
  3. Integrity (Primary key, unique, Foreign key, check, default)
4 – Additional query techniques  Basically you now know everything to be a databasedeveloper, but if you want to be a good one, then you should continue with this chapter and the following once:

  1. CTE (common Table Expressions), Recursion
  2. Subqueries, running aggregates, Correlated and noncorrelated subqueries
  3. Rank functions (Row_number, rank, Dense_rank, NTILE)
5 – Stored Procedures, functions, triggers, views Not all SQL Statements are run on an Ad-Hoc basis, and the SQL Server is also build to use different techniques to perform better and do different tasks, and therefore this chapter is essential:

  1. Stored procedures, variables, parameters, return codes, Control Flow, Error messages, Error handling, execution context, compilation and recompilation.
  2. Types of User-defined functions (scalar, multi-line table function, inline table functin)
  3. DML-Triggers (only the triggers that are fired after insert, update and delete)
  4. DDL-triggers (only the triggers that are fired after a create, alter or drop)
  5. Logon-triggers
  6. Views
6 – Query Performance
  1. Understanding query costs
  2. Understanding pages
  3. Execution order
  4. Execution plans
  5. How to determine where to put in a lot of time 🙂
  6. Indexes (clustered, nonclustered, covering, filtered indexes)
  7. Tools to automatical index optimization
  8. Indexed views
  9. Partitioning
7 – XML, SQLCLR, Filestream
  1. Understanding CLR (Common Language Runtime).
  2. Understanding XML
  3. Transform  relational data to XML
  4. transform XML to relational data
  5. Query XML data
  6. Manage XML data
  7. Understanding Filestream
8 – Spatials, Full-Text Search, service broker In this chapter you learn how to work with

  1. Spatials (geography, geometry)
  2. Use the fulltext indexing
  3. Use Service Broker
9 – Manageability features This is a fluffy chapter, but there are four important things to learn:

  1. Databasemail
  2. Power shell
  3. Track data changes
  4. SQL Server Management Objects

This is the topics we will cover during the modules that I will upload over the next 30 days.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s