The UNION and UNION ALL operators allow us to combine results, but there’s no guarantee that each set of results uses the same data types. So what data types are returned? For the longest time I thought the data types from the first set of results were used for the final results. That’s not the case. Understanding how this […]
Window functions allow us to perform a function across a set of rows in a result set, rather than how we might typically group them. In SQL Server 2022 we have a new clause available for our queries which can help tidy up how these are defined. We’ll use the sample table from the introduction to […]
Uniqueness is something we like to reinforce within data. Sometimes the uniqueness will mean we reject data, but other times we want to make it unique. Let’s consider an account name or an order reference. We wouldn’t want to simply reject an account or an order, we want to ensure they’re unique. A typical approach […]
Data quality and consistency is key to the services we support and solutions we deliver. A gremlin which can undermine that is duplicate data. Let’s start the new year dealing with duplicate data and having a good clear-out. For our example we’ll consider an Order Product table which contains an OrderID and ProductID, and the combination of these should […]
This past week I stumbled across an ODBC Scalar Function for the first time. What was this which lay before me? Is that SQL with curly braces?! It returned the current date like this: It’s a function, but not quite as we know it. They’re surrounded by { curly braces } and prefixed with fn. The kind of thing I’d […]
When adding a new column and wanting to default the value for existing records, it used to be a painful task. As of SQL Server 2012 that became much easier. But nobody told me, until Simon casually mentioned it in conversation recently. I had to see it for myself, so I thought I’d share for those who […]
Last week I shared a script to identify foreign keys which weren’t indexed. What we didn’t discuss in detail was why it can be important to index these columns. Here we’ll demonstrate the two areas we can see great benefits by introducing the indexes. Query optimisation We’ll start with the typical benefit of indexing – query optimisation. The index […]
Foreign keys provide us with confidence in the integrity of the code we develop. In this post we identify indexing opportunities around foreign keys to increase performance of their usage. Creating an index on a foreign key column (or columns) can provide additional performance in two key situations: That’s all well and good if we’re […]
Bad code smells can run through a system, and one instance can quickly spread when code is recycled. Simon recently looked at a non-sargable example and was able to fix it by using an index and refactoring the query. I wanted to consider an alternative approach if we saw the same issue repeated consistently. We don’t want to […]
In Management Studio we can view object details by hitting F7 in Object Explorer. It gives us basic metrics but I find it very slow to load for the details I typically need. For that reason I though I’d share a script to turn to for metrics I commonly need. This query returns: Results for the AdventureWorks2022 […]
Andy Brownsword 