Whenever you’re building a data warehouse or similar solution, you’ll probably want to have a “calendar dimension”, a table that contains all days in a range of years. A challenge with this type of table is getting all the public holidays right, which could be particularly important if your business depends on this, like financial markets or logistics.
Author: daniel
Calculating the date of easter sunday
We’ve previously looked at how to calculate recurring public holidays. However, calculating the date of easter sunday is not as simple as you might think, because it involves calculations of lunar phases. This short post contains a T-SQL translation of the popularly used Meeus-Jones-Butcher formula.
Inline variable assignment in UPDATE statements
Ok, here’s a brain-twister. Not only can you assign values to a column in an UPDATE statement using variables, but you can assign values to variables as well. It’s really not as complicated as it may sound, but there are a few trapdoors to avoid.
Schemabinding and table spools
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:
Blocking/non-blocking aggregate operators
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.
Datawarehouse modelling: Inmon vs Kimball
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.
HASH JOIN deep-dive
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.
Directed acyclic graphs vs parent-child hierarchies
We’ve recently looked at ways to work with parent-child hierarchies, particularly in reporting scenarios. Regular parent-child hierarchies are great when working with dimensions that are ragged, but they have a critical limitation – any given node in the tree can only have a single parent node. A great solution to this problem is a DAG – directed acyclic graph.
An introduction to filtered indexes
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.
Changes in Management Studio keyboard shortcuts
A few keyboard shortcuts have changed in the code editor of the SQL Server 2014 Management Studio. Personally, I found this incredibly annoying because I’ve been using Ctrl+E for many years to execute a query, but now, all I got was
(Ctrl+E) was pressed. Waiting for the second key of chord...
.. in the status bar.