Implicit conversion happens in SQL Server when the engine detects a mismatch in data types and automatically converts from one type to another. This can be helpful as it makes different types interchangeable and is generally transparent to the client, but it can come with issues. Usually the downside from implicit conversion is seen through […]
When running queries, reviewing execution plans, or diving into Query Store it’s not uncommon to see missing index recommendations, particularly for more complex queries. But are they any good? There are many factors when considering an index. However if you’re a situation where these missing index recommendations are enticing, I wanted to cover 3 key areas you […]
When looking to increase performance for a database it can be the smaller and faster queries which can be improved easier and more effectively than their slower counterparts. We can be drawn to tuning long running and complex queries, but these can be a challenge due to their size and complexity. Sometimes though we can reclaim just […]
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 […]
Andy Brownsword 