Last time out we started to look at optimising SSIS packages by showing how to identify bottlenecks with a handy script. This time we’re turning insights into action to solve those pain points. The solutions are grouped into 3 areas: Data Flows, as they do a lot of heavy lifting; the Execute SQL task, which can […]
Code is an ever moving target. Version control and documentation only go so far, if they even exist. Sometimes all you have is the code in front of you. This is why I always start stored procedures with a header. Here’s a template as a starter: Name seems redundant as we have the proc name below […]
Sometimes you want to segment records. It may be splitting a customer base for marketing purposes, or segmenting a user base for a new feature. Good segmentation makes clean divisions in the data. In this post we’ll see a way to achieve that with a great deal of help from Window Functions. This post was […]
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 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 […]
Andy Brownsword 