Design for implicit and explicit transactions

May include but is not limited to: nested transactions, savepoints, TRY/CATCH

A transaction is an atomic, consistent, isolated, durable operation – this is best known as the acronym ACID. There are two kinds of transactions: explicit – this is what you refer as a transaction. It starts with the BEGIN TRANSACTION clause, and ends with a COMMIT / ROLL BACK – what you prefer. There are also implicit transactions. These are enabled when you connect SQL Server with implicit transactions enabled. By this, some clauses automatically start transactions (CRUD operations, DDL statements, etc.) which you can commit or roll back immediately.

To enable implicit transactions, you call the SET IMPLICIT_TRANSCATIONS ON statement. Remember – a transaction either succeeds or fails, so this mode holds some surprises to you.

Nothing can hold you back from nesting transactions. There are some rules, although. The first rule is that the outmost transaction always wins. So if you commit every nested transaction, but roll back the outmost one, every inner transaction is rolled back. The reverse is true.

Continue reading


Design for concurrency

May include but is not limited to: hints, transaction isolation level, effect of database option READ_COMMITTED_SNAPSHOT, rowversion and timestamp datatypes

Most of this objective is covered in the previous post there’s simply too much overlapping in this part of the exam, as far as I can grab it. Anyway, there are some other interesting issues with locking and concurrency, so here’s a brief post on the remainders.

There are two data types related to concurrency – rowversion and timestamp. Rowversion is a binary data type which stores eight bytes, and increments automatically on every insert and update. Note that each table can have only one rowversion column, and you must give a name for it (you don’t have to specify a name for a timestamp value). Rowversion type is useful when you expecting multiple updates in roughly the same time, but I’ve stumbled into it when used LINQ to SQL along with WCF, too.

Continue reading

Design a locking granularity level

May include but is not limited to: locking hints, memory consumption

SQL Server has a multigranural locking feature, which lets you lock resources on different levels. Here’s a brief table of them:

Resource Description
RID A row identifier to lock rows within a heap.
KEY A row lock within an index.
PAGE A whole page locked in the database (8 KB).
EXTENT 8 continuous pages.
HoBT A heap or B-tree, depending on the existence of a clustered index.
TABLE An entire table locked down.
FILE A whole database file.
APPLICATION An application-specified resource.
METADATA Locks in metadata.
ALLOCATION_UNIT An allocation unit.
DATABASE An entire database.

Continue reading

Design T-SQL table-valued and scalar functions

May include but is not limited to: inline table-valued functions vs. views, multi-statement table-valued functions, determinism

Another feature of SQL Server which is very useful, but I’m not using it daily – User Defined Functions (UDFs). A UDF is like any other function. It takes parameters, works on them, and then return some results. There are three types of UDFs: inline table-valued functions, multistatement table-valued functions and scalar functions.

Let’s start with scalar UDFs, because they can be described very easily. They are functions that accepts zero or more parameters, and returns a single scalar value at the end. There are some built-in scalar functions in SQL Server, like ABS or SUM or COUNT, so you can have a good grasp on what I’m talking about. UDFs have a more rigid syntax than stored procedures, so here’s a brief one:
Continue reading

Design views

May include but is not limited to: common table expressions, partitioned views, WITH CHECK OPTION, WITH SCHEMABINDING

A view is a piece of metadata (a simple SELECT query) stored by the database server. It can be used like a table, thus you can query it, update the underlying tables, delete from them through the view, etc. What is the point of a view (you really shouldn’t ask this question preparing to be MCTIP)? We all have common select queries we run every day with joins, functions, etc. involved, and it takes a lot of time to enter the exact same query every time. Create a view for it, and you can even control the access for it with great granularity.

With a view, you can restrict access to even specified columns of a table, or just given rows (with a WHERE clause). Now with the advent of Common Table Expressions (CTEs) – views got a strong opponent. But you can even use a CTE in a view, making the best of both worlds (the CTE can recursively call itself). There’s a catch, however – views can’t get parameters.

Continue reading

Design a database model

May include but is not limited to: normalization, entities, entity relationships

Much could be said and has been said already about this subject. I don’t really think that I could show anything new, but here is some topology.

Normalization is the process of making an effective database solution. There are five normal forms, of which we’ll discuss the first four:

  • 1NF: quite basic rules, eliminate duplicate columns from the very same table (a rule which isn’t hard to implement) and separate related data into different tables, and identify them with a primary key.
  • 2NF: remove data that apply to multiple rows of a table from that, and create separate tables for them, linking to the predecessors with a foreign key. And of course, 1NF.
  • 3NF: the first two plus removing the columns which aren’t dependent on the primary key.
  • 4NF: the first three and a new definition for relations: a relation is in 4NF when it has no multi-valued dependencies.

Now the concept of entities. I must confess that the word entity is too abstract for me. I’d like to map English words to their Hungarian counterparts, but entity lacks one. So I can’t really describe what an entity is- I can only say that it is an entity.

Continue reading

Design an application strategy to support security

May include but is not limited to: application roles, schema ownership, execution context, Windows vs. SQL authentication, permissions and database roles

Let’s start with the boring definitions: authentication is the act of identifying yourself, while authorization is that one when you gain access to resources, based on your identity.

SQL Server allows two types of authentication methods: Windows authentication allows you to connect SQL Server with an existing Windows account , while SQL Server authentication allows connections from anywhere – as long as you set it up this way (which is a bad idea). You should use Windows Authentication, because this way you make your life easier (don’t have to store passwords in config files to connect), but if it isn’t possible, SQL Server authentication is the way to go.

Now how to build up your authentication model – there’s an easy way to go – connect with a fixed application credential. This way you can control what the app can do in the database server. However, sometimes you need more granularity – let’s say you are interested in who did what. If you connect with a single application credential, you’ll won’t get user-detailed information. Then you should use built-in user accounts.

Continue reading