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.
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:
|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.|
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:
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.
May include but is not limited to: table width, sizing data types, IN_ROW_DATA (BLOBs), overflow data, sparse columns, computed columns, persisted computed columns
In the heart of every relational database system, we find tables. Lots of them. Everybody knows how they look – rows and columns, and in the intersection the data value itself and a nice header with the column titles.
Our first issue is table width. You can calculate it easily by the built-in dynamic management view sys.dm_db_index_physical_stats. The hard decision is how wide your table should be. A wide table is not necessarily the devil itself, and a very short table can be a bad solution, too. Of course it’s not the best idea to store ordered products in the customer table rendered as columns like Order1, Order2… I think the keyword here is clever design. Normalize your database system, ensure referential integrity, and you won’t have to worry about table width. You’d need to store that data anyway, don’t you?