70-433 Passed

Today I passed the MCTS 70-433 Microsoft SQL Server 2008 Database Development exam, which completes my first (and probably last) SQL Server MCTS. The exam itself was a little bit hard, and I passed only with 775 points, although it took less than an hour.

If I’d restart preparing for this exam now, I’d look more closely on the topics of Gathering Performance Information, and Working with Additional SQL Server Components, I haven’t performed well in both of them.

The next target is WPF 4.0, but I ‘m considering the MCTIP in Database Development, too.

Implement error handling

May include but is not limited to: TRY/CATCH, RAISERROR, retrieving error information, custom error messages, @ERROR.

Unfortunately, we cannot write bullet-proof code in any language, T-SQL included. Fortunately, most languages provide ways to deal with errors within your applications, T-SQL included (at least, Microsoft’s implementation).

The easiest but most limited way to deal with errors is to query the @@ERROR global variable. If any error occurs, @@ERRROR returns its number. If not, it returns 0. The severe limitation is that it resets its value after each successful statement. And a simple SELECT @@ERROR counts as a successfully executed statement, so you won’t be able to pass the error information any further.

But I went too far. Let’s see what builds up an error in SQL Server:

  • Error number: a unique number which identifies the current error message. SQL Server reserves the error numbers 0-50000, so you can build custom errors from number 50001.
  • Severity level: from 0 to 25, indicates the severity of what has happened.
  • Error message: a textual representation of the error.

Continue reading “Implement error handling”

Implement partitioning solutions

May include but is not limited to: partitioned tables and indexes (constraints, partition functions, partition schemes, MERGE, SPLIT, SWITCH); distributed partitioned views (constraints, linked servers).

Partitioning means that you store table data in different file groups, instead of using only one file group. By default, SQL Server treats every table as if it were partitioned, but having only one partition.

Partitioning is implemented horizontally, so that you store collections of rows in different file groups, not columns. You should consider partitioning a table when it is excepted to contain lots of data, which is being used in different ways. MSDN comes up with the example of a sales table, with the current month used primarily for inserts, and the previous ones for selects. This is a good candidate for partitioning solutions, because of the different operations performed on the different parts of the table.

To create a partitioned table, you need to do the following:

  • Create a partition function
  • Create a partition scheme
  • Create the table using the scheme

Continue reading “Implement partitioning solutions”

Track data changes

May include but is not limited to: change tracking, database audit specification, CHANGETABLE

There are two tools in SQL Server serving the purpose of auditing data changes. The first is Change Tracking, which you can find in every version, the other is Change Data Tracking, can be found in Enterprise Edition.

The difference in a nutshell: using CDC, you can see what’s changed and how, in an asynchronous manner (there’s a latency involved). With CT, you can see that there’s a change occurred, but can’t see the changes themselves,  and do so in a synchronous manner.

You can imagine the benefits and drawbacks: CDC uses much more resources, while providing more information. Change Tracking doesn’t provide historical data, while still indicate the type of change made. Now let’s see both techniques in detail.

Change Tracking

The first thing to do with CT is to enable it. You must define that you need this functionality at the database level. You’d write a syntax like this:

ALTER DATABASE MyDataBase
SET CHANGE_TRACKING = ON

Continue reading “Track data changes”

Manage transactions

May include but is not limited to: BEGIN TRANSACTION, COMMIT, ROLLBACK, SET TRANSACTION ISOLATION LEVEL

A transaction is a group of related tasks, which must succeed or fail as a unit. A transaction is referred as an ACID operation. ACID stands for Atomicity, Consistency, Isolation and Durability.

To start a transaction, simply write BEGIN TRANSACTION. You can optionally specify a transaction name for it. To end it successfully, use the COMMIT clause, to undo the changes, use the ROLLBACK clause, along with the optional name of the transaction. Anything between the BEGIN TRAN(SACTION) and the ending clause is considered to be part of it.

In SQL Server, we distinct some kinds of transactions. There are explicit transactions, which are explicitly marked with the BEGIN TRAN keyword. There are also implicit transactions, which can be used after calling the SET IMPLICIT_TRANSACTIONS ON statement. After this, any SQL statement will be executed as a transaction.

A nice feature to use is SET XACT_ABORT. By default, if a run-time statement error occurs in a SQL statement, only that statement is rolled back. If you specify SET XACT_ABORT, the whole batch will be rolled back instead of just the one causing the error.

Continue reading “Manage transactions”

Apply built-in scalar functions

May include but is not limited to: CAST and CONVERT; REPLACE; LEN and DATALENGTH; PATINDEX and CHARINDEX

You can define functions in SQL Server for enhancing your select or where clauses. These functions are called user-defined functions (a shocking name for them). However, there are many-many predefined functions you can work with, and they are called built-in functions. Now we’ll take a closer look on them.

The main purpose of these scalar functions is to enhance your select statements. You can also use them in your where clause, but with a considerable performance overhead, so try to restrict yourself here.

Because there are so many built-in functions, it’s a good idea to categorize them. Microsoft did this for us, so we know date/time functions,  system functions, string functions, and additional functions.
Continue reading “Apply built-in scalar functions”

Collect information from system metadata

May include but is not limited to: Dynamic Management Views (DMVs), catalog views

SQL Server always provided ways to access metadata describing your database objects. The SQL Server 2008 ways to do this is to query Dynamic Management Views and catalog views.

Before we move further, let’s describe metadata. Metadata is meta that describes data.

Catalog views provide a way to query database objects, and server configuration options in a very similar way you are doing it with actual tables. The obsolete method doing this was querying INFORMATION_SCHEMA views. Catalog views are far more superior, because they let you query results and use them in joins, offers server-specific information, and provides much more information.

We’ll query the following: table, column and index metadata. To query table metadata, use the sys.tables, sys.columns, sys.schemas and sys.types views. The syntax is the standard SQL:

SELECT * FROM sys.tables t
JOIN sys.columns c
ON t.object_id = c.object_id

The code above retrieves all tables with their columns residing in the current database.

Information about indexes can be retrieved by using the sys.indexes, sys.index_columns catalog views.

Dynamic Management Views give you access to internal server state information. There are more than eighty of them, and you can easily identify them, because they are members of the sys schema, and their names start with the prefix dm_.

You can retrieve a great deal of information by using them, regarding session, connection, executing SQL, server resources, and unused indexes, just to name a few.

I don’t think it would do any good if I’d provide a list of these views, because MSDN did this job in a more effective way already. Just play around with these views, and you’ll gain enough experience on them for the exam, too.