SQL Server Blog

This is a simple script that’s held on my website mostly for reference as it simply takes any table and then compresses it with PAGE level compression.



If you want ROW level then simply substitute in the code, but this was a script I needed recently and therefore wanted accessible via my website and therefore it’s on here but for PAGE level by default. It really isn’t hard to change to a row compression version.

At some point I will explain the nuances of the Columnstore index, how they work, when and where you should use them, and what they’re for… but in the mean time I have recently had situations in which I have upgraded to SQL 2014 and have needed to Columnstore a table.

One of the best features of a database snapshot is the ability to roll back your changes and revert to the snapshot.



Therefore what you can do is take a snapshot, make a load of changes (whether testing new code, trying out something new etc) and then if it all goes pear shaped you can simply revert back to the snapshot and all will be well.

This is pretty simple really, but it’s useful code and therefore I wanted it on my website mainly for my reference on those days in which I can’t remember the syntax and want a simple copy and paste option. We all have our forgetful moments after all :o)

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.



And best of all, this is an ALL Edition feature rather than Enterprise only.

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.

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.



For a much more in depth explanation of this I would recommend looking at Paul White’s blog page here.

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.

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.