Protean

Many of you will have heard the mantra that loops are bad, set based is good. But how do you get around them?

The fact of the matter is that there are very few circumstances where a loop is the only way to achieve your objective. A look through the SQL forums will show you many a technique to turn iterative row by row processing into single transaction set based processing. I’m refering to hierarchical query techniques (see nested set theory) and of course the subject of this post, the numbers table.

(more…)

A very frequent requirement in a database, especially in an OLTP system is to be able to capture the identity and values of the last inserted record in a table so that we can use it to populate the foreign key in a child table and any other tables that require it.

It is easy enough to capture the last inserted identity if we are only dealing one record at a time using @@IDENTITY, but what happens when we need to do batch inserts? Well, the old way of doing things would mean you had the following options:

• Execute a loop (either a cursor or a WHILE statement), and for each iteration we’d write a record in each of the child tables
• You’d have to select the same data twice, once to insert the audit records and once to update the data
• You’d rely on a trigger to write to the audit table

Well that should now not be necessary as using the OUTPUT clause we can capture all the data we’ve inserted, updated or deleted in a temporary area for use later on, or say in an auditing scenario, we can output the data directly to the auditing table. More importantly we can use that data in a set based transaction instead of row based. The advantages here are obvious. 

(more…)

This script might be the best SQL insert generator I’ve seen yet. It’s flexible and deals quite comfortably with the majority of problems a dev can come across when attempting to write one of these. Why re-invent the wheel.

Take a look at the website, there are plenty of neat tips and tricks.

Cheers

Frank

Amazon Link: Microsoft SQL Server 2008 T-SQL Fundamentals (PRO-Developer)

I have a SQL generation package that I am using to create sql files that will later be executed against the production environment. It is necessary to do this for corporate IT policy reasons which I won’t go into, however, suffice to say it isn’t the most efficient way to get new data into the database!

The data is input into Excel by the users and then passed to me the run the SSIS package. This worked fine until recently when one of the Excel spreadsheet I recieved had cells in it that were in excess of 8000 characters wide (containing Legal T&Cs).

Amazon Link: The Rational Guide to Extending SSIS 2005 with Script (Rational Guides)

(more…)

In my never ending quest to simplify administration tasks on SQL Server, I recently came up with a method to automate the creation of new publications with a one script fits all piece of SQL Code.

Considering that in most enterprises, the default configuration options for replication will remain the same, the only thing that changes are the destination servers and the publications/articles themselves.

(more…)

We try to avoid SQL Server user-defined functions unless we can be certain that there is no other way to achieve what it is we’re asking of them. They aren’t very performant as they calculate on a row by row basis rather than the set based operations that our esteemed RDMS is renowned for.

Having said this I am going to post a table function I created recently for use as part of an SSIS import package that transforms an incoming single row CSV into roughly 100 records each time it runs.

The content team in our organisation faces the soul-destroying task of entering records as well as comma delimited strings on to an Excel spreadsheet which in turn is imported into 2 SQL tables.

There is no straight forward way to split the csv string into a SELECT list, but here is what I have done…

(more…)

Ever had a multitude of SQL Agent jobs with dependencies on one another in order for your nightly batch to succeed? Ever had a situation where you forgot to set the steps correctly within the job resulting in the batch failing or not even running at all?

Me too… here’s what I did in order to protect myself from human error.

(more…)

I have just finished the code for a new transactional replication setup.

Requirements were to be able to filter rows according to the destinations that formed part of the fullfilment of the order. This could theoretically include up to 10 destinations however in reality it tended to be 2 or 3. The data would be real-time messages flowing out of the mainframe into the SQL Server via BizTalk.

(more…)

Ever been stuck trying to figure out what people are doing on your server, but unable to tell from DBCC InputBuffer what statements the users are running? This is one of the most useful scripts I’ve come accross and helps enormously when trying to analyse what is happening on your SQL Server.

It is often the case that users will query your database with tools like MS Access or Business Objects which are quite difficult to track. Well this useful tool helps to solve that problem… 

(more…)