We’ve previously looked at how to implement vertical partitioning and the benefits it can bring within our data. Here I’d like to dive a little further into improving the performance even further with a couple of modifications to our schema. We’ll use the same setup as we had previously to create the table, populate it and partition it. This results in […]
Tag: SQL Server
Categories
Benefits of Vertical Partitioning
We recently looked at implementing vertical partitioning on a set of data. In an earlier introduction post we also mentioned the benefits which can be seen as a result of the partitioning in both our maintenance and querying of the data. Here we’ll take a look at those benefits with a little more evidence. We’ll be using the data […]
Categories
Boolean Value Storage
Within SQL Server we can store Boolean data using the BIT data type. This value is a single bit which will store a True or False (or null) value. However if you dive a little deeper with the data type it’s actually a little less clear cut in terms of the storage needed for this data type. […]
Categories
Implementing Vertical Partitioning
We previously looked at an introduction to Vertical Partitioning where we covered what it is and the potential benefits and drawbacks of using it. Here I wanted to build up some data and show how we may vertically partition it to help make it more manageable for us. So that we’ve got some relevant data to use […]
Categories
Introduction to Vertical Partitioning
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 […]
Categories
Performance Considerations using APPLY
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 […]
Categories
Complex Updates and Deletes
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 […]
Categories
Partition Elimination in SQL Server
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 […]
Categories
SSMS Tip: Saving Schema Changes
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 […]
Categories
SSMS Tip: Show Table Details
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 […]
Andy Brownsword 