Here’s a practical script that I built to view how much space each database object takes up in the database. The script also show you information on how many rows a table (or indexed view) contains, as well as if it’s partitioned and/or compressed.
Category: T-SQL
User options and connection flags
Every user session in SQL Server comes with a number of “flags”, or settings, that you can alter to modify the way SQL Server behaves in different aspects. Some are fairly straight-forward, which others are old legacy options or even completely deprecated and have no actual use.
Identifiers in T-SQL
From time to time, you’re going to be confronted with a SQL Server solution where you need manage column or table names that contain non-alphanumeric characters, like for instance space or percent, or even reserved keywords like “table” or “select”.
How to build a histogram in T-SQL
Talk to SQL Server developers or DBAs about histograms, and they’ll inevitably think of index statistics. However, a task you may encounter some day is to calculate the distribution of numbers in a table. And although there’s no quick built-in function to do this, it’s not as difficult as you may think.
Using MERGE as a single INSERT or UPDATE
The MERGE statement is a very powerful way to combine INSERT, UPDATE and/or DELETE in a single statement. But there’s more than meets the eye. There are situations where you could use a MERGE statement to perform just one of those tasks.
Partitioned views over table partitioning
Today, we’re going to be looking at a kind of poor-mans’s-partitioning, using a view to union records from multiple tables. We’ll also take a look at when you would want this type of solution, some benefits and drawbacks, as well as ways to make things go faster.
Reloading fact tables with zero downtime
If you’re working with data warehousing or reporting, you’ll recognize this problem as a recurring headache whenever you’re designing an ETL process for fact tables: If you want to completely reload all the rows of a fact table, you would typically start by emptying (or truncating) the fact table, and then load new data into it. But during the loading process, depending on what your job does, there won’t be any data in the table, or worse, it will be half-filled and incorrect. Worst-case: If your ETL job crashes, the table will remain empty. Now, if your ETL job takes an hour to run, that’s a problem.
Calculating 30/360 day count convention
Calculating the number of days between two dates is a trivial matter in T-SQL if you use the DATEDIFF function. However, how many years (or rather, fractions of years) there are between two given dates is a matter of which method (day count convention) you apply. In financial mathematics, a lot of calculations use a 30/360 convention, where you apply certain rules in order to modify each month to contain exactly 30 days.
Building a calendar dimension with public holidays
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.
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.