Delayed Durability - What is it?
Produced: 25/12/2016 16:06:00
This is one of my favourite new features of SQL Server 2014 as it can make an absolutely huge difference to your servers when performing inserts, updates, and deletes.

Be Very Careful with Sampled Statistics
Produced: 18/12/2016 16:03:00
Okay, we all know that Sampled Statistics are not perfect otherwise there would be no need for a Full Scan stats update. However, there’s an issue I encountered recently that has really bothered me in regard to these.

2014 Cardinality Estimator Exponential Backoff
Produced: 11/12/2016 11:38:00
This is going to be a very basic explanation as to the differences seen in my previous post (about TF 9481) in which we encountered the new and old cardinality estimators providing different estimates to what looked like a very simplistic query.

How To Turn Off the New 2014 Cardinality Estimator
Produced: 04/12/2016 11:33:00
Before I start, I want to point out that I like the new estimator and it does have its advantages over the old one, BUT because it does produce different estimates in certain circumstances it can cause you to get completely different execution plans and therefore, as part of upgrade testing, you will likely need to turn it on and off, hence this post.

Help for Undocumented DBCC Commands
Produced: 27/11/2016 19:55:00
This episode of Fun with Flags is used in conjunction with my previous post about TF 3604.

Undocumented DBCC commands are written about all over the place and, although undocumented and therefore also generally unsupported, people seem to love them. I use them myself in all manner of places, never in production code, but they still get used. The biggest problem though, is knowing how to use them.

Returning DBCC Messages to SSMS
Produced: 20/11/2016 19:33:00
There are some DBCC commands, such as DBCC PAGE which seem not to do anything because you see nothing in SSMS to tell you otherwise. As it turns out this isn’t the case just that SQL Server is suppressing the information messages.

Alternative to sp_rename
Produced: 13/11/2016 10:08:00
There are a few flaws with the sp_rename method when switching tables or even simply renaming them… note that these aren’t bugs, they’re simply annoyances encountered which can otherwise be avoided.

Lead and Lag Built In Functions
Produced: 06/11/2016 17:48:00
These aren’t new functions as they were brought in for SQL 2012, but I was using them the other day in a SQL 2014 environment and the person I was talking to at the time had never heard of them, so I thought I’d make mention of them here in case other people hadn’t heard of them either.

Obtaining the Max Values Across Columns
Produced: 30/10/2016 17:45:00
In some code I was writing recently I needed to perform this action which, having had a quick look on the internet, seems to be a topic that annoy people as it’s not something natively built in to SQL Server and to obtain it yourself can be a pain.

An Alternative to a Data Accessing Function
Produced: 23/10/2016 08:59:00
Once again I’m going to refer to previous posts here as this is actually an extension of my previous two posts.

This was something I came across recently when working on a tuning project… the original code was very slow and this was quickly traced to a good old function.

Passing a Table to a Stored Procedure
Produced: 16/10/2016 11:47:00
This is a direct follow on from my previous post really as one of the most common uses for the Table Type is that you can use it (in SQL Server 2008 onwards) to pass a table as a parameter into a Stored Procedure. This is incredibly useful as one of the things that I see most often asked on the internet is how to pass tabular data to a stored procedure.

Table Types
Produced: 09/10/2016 11:42:00
This was something which I used recently within my own personal work at home because I was writing a stored procedure which involved regularly creating tables with the same structure.

Proof A Failed Insert Increases the Value of an Identity Column
Produced: 02/10/2016 09:59:00
This is something I’ve seen cause plenty of confusion and so I thought I would investigate. It’s actually lucky that I did before decommissioning my old SQL 2008 R2 server as the same proof can’t be run on SQL Server 2014 (I’ve not tried SQL Server 2012).

Quick Warning about Variables and Truncation
Produced: 25/09/2016 08:48:00
Well, after a short break let’s start the new year with a nice and short post.

This one is simply because I was reviewing some code for someone the other day as they had an issue they couldn’t track down and, as it turned out, it was something that I’ve definitely been stung by in the past so I thought I’d make a note here.

Merry Christmas
Produced: 18/09/2016 08:52:00
Well, based on some relatively recent posts I’m pretty sure you can all guess what this will be… it’s Christmas and therefore we need a Christmas drawing!!!

Foreign Keys Are Allowed to be Null
Produced: 11/09/2016 06:31:00
Something that came up the other day when talking to a developer was that they were unsure as to whether or not they could have a foreign key relationship to a table when they didn’t necessarily have a foreign key value for all records.

Datepart DW Giving Inconsistent Results
Produced: 04/09/2016 22:13:00
I would imagine that most people tend to write “set nocount on” at the start of most code blocks and procedures as standard these days but, having been caught out with this recently, I have found a new “set” command that I’ve started to add to my code as well.

Shrink Log Without Breaking a Backup Chain
Produced: 28/08/2016 20:39:00
This is something I see asked all over the internet and it’s also something for which there are a LOT of incorrect responses, dodgy solutions, and poor advice (including on the MSDN website). Therefore as I’ve had to resize a few transaction logs recently I thought I’d share the code and process I’ve used.

Database Data and Log File Sizes and Growth
Produced: 21/08/2016 20:51:00
This isn’t going to anything in depth around the subject such as what you should or shouldn’t have as your standard settings etc. but instead it’s actually just a handy piece of code I knocked together which I happen to use quite a lot, especially when in a new company, just to see how SQL Server is set up.

Transactions and Table Variables
Produced: 14/08/2016 19:53:00
Now, anyone who’s read enough of my blog will probably know that I’m not too fond of the Table Variable as I see it being used too often and generally in a way that it’s not designed.

Temp Table Caching
Produced: 07/08/2016 08:09:00
I had a discussion with someone the other day who stated that all temp tables should be dropped at the end of a procedure because otherwise they never get cleaned up.

I could understand the statement, but it’s not actually how SQL Server works because within a stored procedure temp table caching generally makes dropping redundant.

Is SQL Server Showing Memory Pressure
Produced: 31/07/2016 07:50:00
This is something I’ve never really been able to prove but have now found, courtesy of 2 VERY good posts (Grant Fritchey and Jonathan Kehayais respectively - links to follow), that it’s actually quite simple to track and monitor in order to ascertain whether or not your SQL Server is experiencing memory pressure.

Some Artwork Using SSMS
Produced: 01/07/2016 07:11:00
I did a post a while back called “A Little Happiness in SSMS” in which I abused the Spatial Results tab in SSMS using the Geometry data type in order to make a smiley face.

At the time I did say that I’d delve a little deeper into this datatype but, as you can tell, I’ve never quite gotten around to it.