When dealing with large volumes of data in SQL Server we may need to look for strategies to make querying and managing the data easier to handle. A few months back we looked at an approach where we partitioned our data into multiple segments by applying partition functions and schemes to our tables. This helps […]
In the previous post we looked at how to use the CROSS APPLY and OUTER APPLY operators as alternative to a JOIN in queries with some examples. As we wrapped that up I also mentioned that there can be drawbacks to using APPLY in our queries, particularly when we’re working with large data sets. The issue is due to the way that the right hand […]
The UPDATE and DELETE statements allow us to manage the data within our databases and being able to use them effectively is key. There was recently a question from a junior developer asking about how to perform data changes when table joins are required so I thought it a good opportunity to put forward some more thought out details […]
Back in our introduction to partitioning I’d mentioned that using partitions can also bring us benefits in terms of performance. That performance comes in the way of partition elimination with our queries. Before we get started let’s set the stage with a sample table and one million records of data which we’ve used before: What is partition elimination Partition […]
When using SQL Server Management Studio (SSMS) you have the option of creating or maintaining tables via regular SQL DDL statements or you can use the built in Designer tool which provides a GUI interface covering a selection of the same functionality such as adding fields, changing data types, maintaining foreign keys etc. Sometimes the […]
Sometimes when you’re querying a set of data for the first time it can be tricky to understand the results you see. What are the data types? How is the data keyed? Are there any constraints in place? Does the table have foreign keys relating to other tables? So you might then end up scanning […]
Continuing with our recent theme of partitioning I wanted to make sure that we looked at one of my personal favourite features – Partition Switching. Partition Switching is the ability to switch an entire partition of data from one table to another almost instantly. Where it gets interesting is that this is the same operation […]
Last time out, following up to our posts looking at an introduction to partitioning and how to implement it we started to look at how to manage partitions. In this post we’ll continue that theme by looking at how we’d go about removing (or merging) partitions as well as covering some general considerations when we’re performing operations on partitions and […]
Last time out we tried our hand at implementing partitioning on a table with some data inside it. We looked at creating the partition function, followed by the partition scheme, and then applied that to our table and looked at the results. Following up on that I now wanted to look at how we can manage […]
A little while back we looked at an introduction to table partitioning where we covered the concepts involved in the partitioning. This time out we’ll look at how to implement those concepts to create a partitioned table. As we’re going through the motions for this let’s start off by creating a table and fill it with some […]
Andy Brownsword 