This is the second part in a series on storing and modelling data efficiently. A great way to add performance to your data is to partition it. Like the name implies, partitioning splits a table or index into multiple partitions, so the data can be stored across multiple physical files and drives. Partitioning is a feature of SQL Server Enterprise Edition, but if you have one, you’re in luck!
Category: Intermediate
Working with dependencies
Working with dependencies, particularly recursive dependencies, may not always be entirely intuitive, but it could be critical knowledge in your database development work. This article focuses primarily on different ways of visualizing dependencies and how to loop through them using recursive common table expressions.
Instant file initialization
When you create (or grow) the size of a database file, SQL Server will initialize the allocated space on the disk, i.e. fill it with zeroes. If you’re adding a large amount of space to your database file, this operation can take quite some time to complete. But just like there’s a “quick format” in most operating systems, you can allocate large chunks of database file space without initializing it. This is called “Instant file initialization” in the world of SQL Server.
The assert operator and different types of updates
When you update a column that is tied to a foreign key constraint, SQL Server needs to validate (called “assert“) the new value, in order to make sure that you haven’t added a value with no matching primary key. But in some situations, it’ll assert more than just the column(s) you updated.
Slowly changing dimensions (part 2)
In this second installment of the Slowly Changing Dimensions series (see part one here), we’ll take a look at how to practically create a slowly changing dimension table using T-SQL.
Slowly changing dimensions (part 1)
This is the first article in a series that will describe what slowly changing dimensions (SCD for short) are, how they work, and why you might need to take them into account in your database or datawarehouse solution.
Using TOP n WITH TIES
Here’s an interesting feature to that may greatly simplifie a few queries. TOP n WITH TIES works pretty much like TOP, except it orders the top records “densely” to include tied values.
Creating a top-10 list and aggregating the remainder
This article will show you how to create a “top 10” list of data, as well as providing an aggregate on the remainder of the data. There are a few different approaches to this.
Using compression
If you’re lucky enough to be working with an Enterprise Edition of SQL Server, you may be missing out on a really great feature that can boost your I/O performance considerably. And as an added bonus, it’ll save you disk space..
Weeks and weekday parts in dates
Here are two common pitfalls that you may encounter when it comes to working with dates, specifically with week numbers and weekdays. The first is to mistake week numbers in SQL Server for ISO week numbers (which are the weeknumbers used in Sweden, for instance). The other is to make assumptions about which day of the week is the first. The good news is that these are relatively easy fixes.