General Database Posts
I had a case where I needed to move objects to a new tablespace, quite a few objects. As there was a lot of objects (tables, indexes and partitions) I decided to automate the code creation from the database metadata. Moving Objects To A New Tablespace The first thing to understand is how to move…
I found a scenario where I wanted to identify groups of consecutive dates over a larger date range.
Most of us are familiar with the LIKE command in SQL Server (and other DBMS environments), but few venture beyond simple text matching and the ‘%’ wildcard. It’s ability to use some regular expression (a.k.a. regex or regexp) syntax is a surprise to many. For this article, I’m assuming that readers have already used the…
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.
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…
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.…
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.