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 […]
When restoring a database in different environments we may stumble across orphan users. This typically manifests through failed connections to the database. Here we’ll have a quick look at what the issue is and 3 ways to fix it. What are orphaned users When users are created in a database they’re mapped to the login […]
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 […]
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 […]
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 […]
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 […]
Last week we looked at identifying object dependencies through performing a wildcard search on the objects such as procedures. I noted another option could be to use the views such as sys.sql_expression_dependencies and Kevin also commented about using the supporting functions such as sys.dm_sql_referencing_entities. I wanted to briefly look at both of these options and look at how their results can […]
Andy Brownsword 