Object Catalog Views: Table Metadata
It’s handy to programmatically get metadata on tables (column names and datatypes), either to review implementation, or as part of a process automating code creation.
Partitions in SQL Server: A collection Of Thoughts
Like when I started writing about Dates in SQL Server, a couple of notes on partitioning has turned into a series… And not one I’m so happy with (I had a lot to cover and at the same time a lack of a bigger picture direction). Rather than ramble on about aspects of partitioning in…
Partitions in SQL Server: Partitioning a Table on a non Primary Key Column
In my series on partitioning, I’ve looked at creating a fact table partitioned by date. But I recently came across a case where I’d got data which had a date which should be used for the partition, however there was also a natural key in the data which should be maintained as a primary key.…
Partitions in SQL Server: Partition an Existing Fact Table (by date)
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…
Partitions in SQL Server: Creating a Partitioned Fact Table (by date)
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.
Partitions in SQL Server: Function Definitions, LEFT vs RIGHT
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 in SQL Server: Creating a Partitioned Table
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.
Wait For Data to be ready
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: When a value is neither IN or NOT IN a list.
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.
Warning: Null value is eliminated by an aggregate or other SET operation
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…