General Database Posts
Picking up on my series on partitioning, previously I looked at Partitions in SQL Server: Creating a Partitioned Fact Table (by date) now I’m going to look into splitting an existing table into partitions. A key thing to remember is that the moving of data on disk can be time consuming and cause locks. The approaches…
I want to demonstrate a simple, yet common style partitioned fact table. I’ll also include here some handy bits of SQL to help along the way.
In my first article Partitions in SQL Server: Creating a Partitioned Table, I briefly covered the difference between LEFT and RIGHT in partition function definitions. Here I have a comparison of the two.
Partitions are now a common feature in most DBMS environments, although the syntax and functionality varies between implementations. Here I’m looking at the fundamentals of partitioned tables in SQL Server.
Here are two SQL Server based methods for waiting for data, and raising an alert if it’s not ready. I’ve designed them as simple templates which I can easily re-use and customise.
NULL is always a challenge for equality, I’ve written about it before. I recently produced some samples for training/explanation of NULL when it comes to IN and NOT IN, and thought I’d include it here.
In data warehousing where aggregation (GROUP BY) is one of the most common things done in queries, we often get the warning: Warning: Null value is eliminated by an aggregate or other SET operation In most cases what is happening is harmless. But what is happening? NULL in aggregation First we have to better understand…
I recently had a challenge mapping attributes using a lookup table. I had the same customer name in two tables “bobs widgets”, but when I tried to join on the names, there was no match. To cut a long story short, there is more than one way to encode a space. This picks up from the…
Today I’m looking at solutions to Magic Numbers. Magic numbers are hard coded numbers (or references) which appear in scripts and procedures, they creep in very easily. I don’t think that magic numbers are entirely bad. If you have a stored procedure which modifies data for a specific customer, having the one customer ID in…
If an ID or reference is used more than once in a process, it’s best to store it in a variable and reference that. This can slow down debugging, but makes a process far more portable. It also reduces the number of magic numbers in a process (or at least times they are defined). I…