SQL Server Blog

To be honest I couldn’t think of a good title for this particular post so I’ve gone with the above. Basically what I’m referring to is that sometimes a simple text email isn’t enough and you want to include some output with it… therefore what you need is a table.

This is a handy little “trick” which I use all the time when I need to turn the column of a table into a comma separated list. It’s incredibly quick, has very little overhead, and it’s effective.



One of the main uses I have for this is when I want to dynamically create a select statement across a table of unknown width. With just a little help from sys.objects and sys.columns it can quickly become an invaluable piece of code to have up your sleeve.

There are numerous times in which I, and many devs in the company, need to merge new data into existing tables and prior to the MERGE command this tended to be done in a procedure which created a “dev” table (table prefixed with “dev_”), executed multiple comparisons and joins, and then proceeded to perform an sp_rename to swap the dev table for the live one.



In comparisons, the MERGE statement out performed this proc by a country mile and therefore the recommendation was to move to this style of code. However, the code is quite long winded and people weren’t using it. Hence sp_merge.

Today I was asked a question that left me baffled for a good few minutes whilst trying to come up with an answer. Sadly my mistake all along was that I trusted a developer. Ah well, happens to everyone sometimes.

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.

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:

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?

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.

In most SQL Server environments with fast moving data we want to ensure that we will suffer the least amount of data loss in an outage or disaster. This means using the FULL recovery model. There are some circumstances in which someone might wish to change the database to a SIMPLE mode in order to, for example, shrink a runaway transaction log.