The SQL Server security model, part 1: principals

2014-07-202014-07-28 / Daniel Hutmacher / 5 Comments

There are a number of layers in the SQL Server security model, giving you a nearly infinite number of ways to set up access control on your server and databases. Security is a huge topic, and there are literally entire books on it, so this series of articles is designed to give you just a quick overview of the SQL Server security model to get you started.

In this first installment, I’ll go through the different types of security principals that are available, as well as how they connect to each other.

Continue reading →

Schemabinding and table spools

2014-06-222014-06-09 / Daniel Hutmacher / 1 Comment

There are obvious advantages as well as challenges to schemabinding things, and whether you do or don’t is often a matter of preference. However, in some cases, you need schemabinding; when you’re designing indexed views, and to optimize user-defined functions. Here’s why:

Continue reading →

Blocking/non-blocking aggregate operators

2014-06-152014-06-05 / Daniel Hutmacher / 6 Comments

Some database operations can be performed in distinctly different manners, with different impacts on query performance. One important example of such an operation is calculating an aggregate. In this article, we’ll take a look at how aggregates can be “blocking” or “non-blocking”, how it affects memory allocation, and ultimately, what impact this has on your query.

Continue reading →

Datawarehouse modelling: Inmon vs Kimball

2014-06-082014-05-28 / Daniel Hutmacher / Leave a comment

If you’re into business intelligence, data warehousing and analytics, you will have heard an endless number of references to Bill Inmon and Ralph Kimball. These two figureheads in datawarehousing architecture have produced an immense number of books, articles, training seminars, etc. While many of their strategies and modelling approaches are similar, they have near-opposite views on other aspects.

Continue reading →

HASH JOIN deep-dive

2014-06-012014-05-13 / Daniel Hutmacher / 4 Comments

Among the three different types of join operators used by SQL Server, the HASH JOIN does some of the hardest work. It scales pretty well and is very suitable for parallel processing. As such, it can be very powerful in many applications, but hash joins can potentially consume quite a bit of memory, so seeing on in your query plan could be an indicator of a performance tuning issue in your query or data.

Continue reading →

An introduction to filtered indexes

2014-05-182014-04-07 / Daniel Hutmacher / Leave a comment

Filtered indexes are an excellent performance tuning feature found on SQL Server 2008 and later. Basically, by adding a WHERE clause to a CREATE INDEX statement, you can make the index smaller, more specific and faster.

Continue reading →

Minimally logged operations

2014-04-272014-06-29 / Daniel Hutmacher / 2 Comments

Today, we’re going to look at which database operations can be performed with what’s known as “minimal logging”, which can result in a considerable performance boost. Included is a quick overview of transaction logging and what it’s good for.

Continue reading →

Introduction to partitioning

2014-04-202014-03-28 / Daniel Hutmacher / 4 Comments

Partitioning is not only a great way to improve performance in large tables, but you can also use it to manage the physical storage of different chunks of data without keeping it in different tables.

Continue reading →

Error handling using TRY-CATCH

2014-04-132014-03-26 / Daniel Hutmacher / Leave a comment

As of SQL Server 2005, you can handle errors using a TRY-CATCH block, similar to “real” programming languages. This enables you to trap most common errors and handle them, instead of having your entire batch or procedure fail with an error message.

Continue reading →

Temp table myths

2014-03-102014-03-11 / Daniel Hutmacher / Leave a comment

Check out this deepdive on table variables that I found the other day:

SQL in the Wild: A trio of table variables

The article deals with some common misconceptions about table variables and it’s considerably more in-depth than my post on different types of temp tables.

sqlsunday.com

T-SQL tips and tricks, best practices and query plans from the field.

SQL Server concepts

Schemabinding and table spools

Blocking/non-blocking aggregate operators

Datawarehouse modelling: Inmon vs Kimball

HASH JOIN deep-dive

An introduction to filtered indexes

Minimally logged operations

Introduction to partitioning

Error handling using TRY-CATCH

Temp table myths