Date handling in SQL Server tends to accumulate tried and trusted combinations of DATEPART(), DATEADD(), and DATEDIFF() – with nested variations. The challenge with these isn’t raw performance, but more with conveying intent and readability. So let’s look at some simpler patterns to try and avoid some of these and be clearer with what we’re […]
For August’s T-SQL Tuesday ask, Jeff has thrown temp tables to the wolves. Does the community like or loathe their usage? I’ll predict a few ‘it depends’ responses, and I lean towards Jeff’s position that they’re more of the exception than the rule. I’d argue there’s one particular scenario where this exception becomes exceptional (heh). Building optimisation boundaries […]
Computed columns allow us to bake logic into our schema. I find particular use for these when reviewing or optimising established solutions where you spot the quirky ways the data is being used. Here I want to demonstrate 3 symptoms and show how different implementations of computed columns solve them. Baking the expression Common expressions […]
Fragmented data estates usually share a trait – there’s no consistent representation of core business entities. It’s a common thread I’ve seen – system-centric entities and attributes, then another solution which recreates these in its own unique way. Different systems talking different languages make integrations messy and bespoke. It’s a technical mess, and it doesn’t […]
When developing Power BI models, we don’t always want to refresh the entire dataset before we start working with the data. We can speed up the refresh – and therefore our development – by reducing the volume we’re working with. Here we’ll look at a couple of ways to use parameters to adapt production-ready models to reduce […]
Schema drift is an inevitable part of environments where database changes are applied manually. Sometimes it’s dev-ing in production, other times it’s lax change control. Either way you’ve got a problem, and SSDT comparisons may be the solution. In this post I want to look at how using the Schema Compare and Data Compare features […]
For the bicentennial edition of T-SQL Tuesday, Brent has thrown down the topic of “When I’m looking at a query, I bet it’s bad if I see ____.”. I’m expecting the Brits out in force for this game of Blankety Blank. There are some things which terrify me when troubleshooting: Not necessarily in that order. But there’s […]
As a database developer, when I started using Power BI, I was concerned about it retrieving reams of data only to perform transformations downstream. The Power Query editor misleads us into thinking the retrieval and transformations are applied sequentially. Thanks to Query Folding, that’s not usually the case. And that gives us more power to […]
If you’re building a large INSERT statement using VALUES, you might find yourself hitting the limit of 1000 rows, for example: INSERT statement exceeds the maximum allowed number of 1000 row values Folks may tell you there are better ways to solve the issue, but the likelihood is that you’re doing it this way for a particular reason. […]
Dealing with endless formats for SQL scripts is a special type of pain you get to enjoy as a data developer. With the arrival of SSMS v22.7 last week, we have a solution in the form of a T-SQL formatter which is now available in preview. What’s new? The ability to format scripts in the […]
Andy Brownsword 