After recently covering window functions I thought it would be a good opportunity to share one of my favourite use cases for them. The functions for ranking and aggregations have their own clear use cases but here I’d like to discuss a particular use for the LAG and LEAD functions. The scenario There are instances […]
In the previous posts we covered the ranking and aggregate window functions and this time we’ll be finishing the series covering the Analytic functions, seeing what an alternative to Window Functions might look like, and then wrapping up what we’ve covered in this series of posts. As with our previous examples we’ll set up our […]
Last time out we talked about the Ranking options which are available for Window Functions. This time we’ll be covering the Aggregate options which are available to us with them. We’ll also introduce a new feature to see how we can limit the rows the function is performed on, but different to the PARTITION method. […]
Introduction Window Functions in SQL Server are functions which can be applied in the SELECT portion of a statement and will return a value relative to other records in the same data set. These functions differ from standard functions such as aggregates where those would combine multiple records together to come up with a result […]
Defaulting values is a typical part of data ingestion and processing within SQL. Handling NULL values can cause issues in a number of ways so there are time when we want to get these out of our data. Recently I had a question about setting default values for a field which was being processed as […]
After being around SQL Server for the last 15 years I was surprised recently to see an example of Regular Expression (RegEx) syntax being used within a SQL query and knew it was something I wanted to try out for myself. What are Regular Expressions? If you haven’t head of them before, Regular Expressions are […]
This month’s invitation from Steve is asking about strategies for upgrades with SQL Server. We might be more familiar with patching a SQL environment semi-regularly – and those processes may be tried and trusted – however its much rarer that we have the opportunity to uplift an entire environment to a later version. Here’s a […]
Schemas within SQL server allow for logical separation of data within a single database. A schema is an extra layer which can allow objects in the database – including tables, views, procedures, etc. – to be separated. They could be used simply to group objects together in a more user-friendly way or they can be […]
In the previous introduction post we covered how to add a computed column to a table and that by default this becomes part of the table metadata when its created. Here we’re going to look at benefits we can get with the performance of these columns when applying an index to them. We’ll use the […]
In a previous post we covered some of the fundamentals about computed columns and we saw the benefits which they can bring for us. Once we’ve implemented them, one of the challenges we may have is if we need to make adjustments to their expression or the fields they reference. Lets start off with a […]
Andy Brownsword 