SQL Server Blog

This is a bug bear of mine but something which I see all over the place in people’s code… if you’re going to create a clustered index on a table (including temp tables) and the key you’re using is unique, then let SQL Server know. You’ll be surprised how much difference it makes.

Something I’ve had to do a lot recently is to calculate financials which involved using an end of month date. This has always been a pain to calculate in SQL Server. There are a lot of ways of doing it, but all are annoying.

In this episode of Fun with Flags I'm going to discuss trace flag 9130. I found this flag when watching a video on performance tuning by Joe Sack… it’s something that I never knew existed, but that I’ve actually now used a few times because it turned out to be surprisingly handy.



SQL Server has a habit of hiding certain things in your execution plan and you might find that sometimes it’s not clear to see what’s going on, or why it’s chosen the plan it has. Well this can actually help with that.

Following on from my last post really, this was another aspect of the MI I was helping out with which benefitted from the use of some more modern code (SQL 2012 or above) in order to make code both clearer and more concise. It is also a lot faster and less strain on the server using the newer method.



This involves using Window functions. These are basically functions acting on a set of data rather than row by row. They’ve been around for a while in most other RDBMS systems, but only in SQL Server as of 2012. Probably why I’ve not seen them used very much as a good few companies I see are still on SQL 2008 R2.

Again, this is something that I decided to write about because I see code in a lot of places used in a lot of different ways and within most MI there is always a need to obtain a percentage figure across a dataset and, for some reason, no-one seems to use SUM with OVER but rather they choose an older, longer method.



Therefore I thought I would make note of this newer way to achieve this in the hope that more people realise how much easier it is and start to utilise it within their t-SQL.

This was an error that I hadn’t seen in a while and therefore I thought I would mention it. The error normally seen here is around the user owning a schema and so it cannot be dropped, but this was different:

So I’ve been a little quiet on here recently, well there’s a good reason… 3 weeks ago today I became a dad for the first time and therefore I’ve been a little too tied up to keep my website up to date.

This is basically an introduction into something I want to play with in some upcoming posts, but to start with I thought I would introduce the topic with a little bit of happiness.



Simply it’s the lesser known ability (it’s VERY rare to find anyone who knows about this) of SSMS to natively produce shapes and graphs based on data you provide it.

When looking through other people’s code I tend to find that people struggle with dynamic SQL and how to write it in the best way. Specifically when it comes to using variables within the dynamic string.



Generally people will start to concatenate all over the place, which tends to involve awkward conversions or casts and can start to look really messy and unreadable.