Basic model changes when you’ve built your solution can be tricky, because they can require redesigning or rebuilding an entire solution. Sometimes, though, the solution can be pretty easy. Like changing an object’s schema, a task that can be done using the ALTER SCHEMA statement.
Author: daniel
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.
Execution order of non-deterministic functions
Here’s a strange insight that I gained when building a test case where I needed some randomized values. In order to generate random values, you can use the NEWID() function, which creates a uniqueidentifier value for each row. But NEWID() comes with a strange behaviour, that some (including me) will consider a bug, while others (including the SQL Server development team) consider it to be “by design”.
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 3)
In the third installment of the series on slowly changing dimensions, we’re going to tackle the question of how to manage accumulated fact aggregates in a solution that uses SCD 2 dimensions. While SCD 2 dimensions solve a lot of problems with slowly changing dimensions, accumulated values can still make a mess of the aggregate data.
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.
What you didn’t know about GO
The GO keyword isn’t strictly speaking T-SQL – it’s really just a batch terminator used in Management Studio and other tools. But did you know that you can use GO to run a batch multiple times?
Turns out, GO has an optional numeric argument that specifies how many times you want to repeat the batch.
SELECT GETDATE() AS currentDateTime; GO 5
With a little bit of imagination, you could for instance use this to replace dreary old WHILE loops, to populate test values into tables or run a stored procedure multiple times to test performance.