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 […]
When looking to migrate, consolidate or deprovision parts of a SQL solution it’s key to understand the dependencies on the objects inside. Identifying dependencies can be challenging and I wanted to demonstrate one way to approach this. We’ll start with some objects across a couple of databases: The simplest option to find references within a […]
I’ve recently been reviewing SSIS packages to make some performance fixes and needed a way to validate the results of those changes. I thought I’d share the scripts as they may be useful for others. Rather than relying on run times from the SQL Agent running the packages I wanted to dive deeper into the […]
Within SSIS you can make use of custom components which aren’t present out of the box. An example of some would be the Azure Feature Pack if you’re working with cloud resources. These will let us use features not available natively. They can also provide a challenge down the line when we come to deploy changes to […]
Shifting from handling data on premises to Azure has been a real change of mindset. Whilst what I want to build may be similar, the how part is completely different. There’s a learning curve not just to the tooling but how you use it too. This is one of those instances. I had a storage container with files which […]
Dynamic SQL has many uses and one of these can help us fix Parameter Sniffing issues. Here we’ll look at how it can be used to generate multiple execution plans for the same query. Parameter sniffing is a common issue. Even for simple queries we can run into suboptimal plans being produced. There are multiple ways we […]
When building dynamic SQL, safety is crucial. As we saw last week, we have the QUOTENAME function which can help when referencing object names. Another aspect to consider is use of parameters. Integrating them incorrectly can leave us vulnerable to SQL injection attacks. Let’s take a look at how to handle them the wrong way, followed by the right […]
I’m a big fan of dynamic SQL in the right conditions. One key to crafting safe dynamic query of the use of the QUOTENAME function. The issue Using dynamic SQL can leave us vulnerable without proper safeguard. Let’s see an example of this: This is a cut down example of something I’ve seen previously in […]
In the last post we looked at how TOP and MAX operators compared. We saw the execution plan for a MAX function used a Stream Aggregate operator which is one of two which we can use for aggregation I wanted to look at the two operators and how they perform the same tasks in different ways. The way they function is key […]
Both TOP (1) and MAX can be used to identify the largest value in a data set. Whilst they get the same result it isn’t necessarily in the same way. Firstly, what is the difference between the two? The TOP clause limits the number of results which are returned from a query, in this instance we’re focussing on a single result. […]
Andy Brownsword 