Query Tuning - Scalar Value Functions
Produced: 23/02/2014 18:16:00
Now, let me start by saying that if you have a scalar function which includes table access then you REALLY need to read this as they are bad... so, so bad that I never want you to use them. Seriously. These are horrible, horrible, horrible things when used in queries… please just abandon them now… there are better ways to achieve results.

This is a pet peeve of mine and I also know it’s the same with most DBAs who ever encounter this. In fact, not that I want to you go elsewhere, but a quick search on Google for “T-SQL Scalar Value Functions are evil” will return a plethora of results showing that it’s not just me who hates these abominations of the T-SQL language.

Query Tuning - "not in"
Produced: 16/02/2014 17:58:00
This is something I’ve seen a lot recently and rarely has it been anywhere near the best way to achieve a result. In essence I’m talking about this type of query:

select *
from largerTable
where id not in
select largerTableID
from subsetTable

Using OPENROWSET instead of a Linked Server
Produced: 09/02/2014 17:30:00
Ever been writing some code and found that you need some data from another SQL Server instance? You have security access to the other instance so what do you do?

Well you could easily use a linked server to obtain it, but what if there is no linked server set up and you lack the rights to (or maybe there’s a company rule forbidding) setting one up? Or maybe you just don’t want to use a linked server as these can have pitfalls (I’ve encountered a few).

This is where OPENROWSET comes to the fore. It provides a tidy, simple, and very effective way to obtain data across instances without requiring a linked server.

Applying Policies to Multiple Servers
Produced: 02/02/2014 19:28:00
The good thing about a Policy is that you don’t have to have a copy on every single server to be able to enforce it. This makes Policy Based Management very appealing to DBAs who have a central server which they can use to create and hold policies to then run against multiple other instances on the network in order to bring them into line without having to create numerous different policies on numerous different sets of servers.

Imagine we have the following setup…

Instance0 - DBA Data Collection & Monitoring Server

Instance1 and Instance2 - Reporting Servers

Instance3 and Instance4 - OLTP Servers

Based on this we may have a Policy enforcing the SIMPLE recovery model on the Reporting Servers but wish to have the FULL recovery in place on all databases in the OLTP servers.