Data Engineer and Nerd
When joining data in SQL we have a number of options to us including INNER and OUTER joins, but one of the ones I tend to use less frequently is the APPLY operator, specifically the CROSS APPLY and OUTER APPLY operators. As with other types of joins they operate slightly differently and have considerations which should be taken into account when deciding on the output […]
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 […]
Happy new year folks! I hope everyone enjoyed the festive period and was able to get some sort of a break to spend time with those closest to you. This past year has been a busy one personally and quite a journey with this blog too. With that in mind I thought it was worth […]
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 […]
This month’s invitation from Garry asks us to talk about what SQL related end of year activities we have to look forward to. Garry has included a few typical DBA examples such as archiving or purging out old data, or updating calendars such as dimension tables or even partition schemes for the new year to come. However […]
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 […]
Andy Brownsword 