Obvious SQL Tip #1

I don't really know why I am starting this series, if it will be really useful for anyone and whether I will be able to keep it running for long. But, its here because I am convinced that sometimes the most obvious escapes our mind. And as someone rightly said "common sense is not so common".

I just intend to illustrate few common issues(one per post) where we try to come up with complicate queries and realise, in the end, that we have a painfully obvious alternative. But, the question is - Is it obvious and is it really an alternative? Read ahead...

Read more »

A Scenario to Ponder #10

Say,I have a requirement. My business demands an auto-generated key (or an identity) for a table called CharTbl. But, it needs to be a character identity column. The sequence generation should be as given in the figure below (partial result displayed).


How would I go about creating the table definition?

Pivot Query Generator for SQL Server 2000

This post is written with reference to the post Pivot Query Generator for SQL Server 2005. Please read through the post to get the context and the data setup before you read any further.

Now, that you have read through the other post, you will realise that in SQL Server 2005, we had the PIVOT operator to generate the crosstab reports. But in case of SQL Server 2000, we will have to use an indirect approach to generate the cross-tab. Here is the SQL Server 2000 counter part of the Pivot Query Generator of SQL Server 2005.

Read more »

Parameter Sniffing & Stored Procedures Execution Plan

This post is an attempt to explain what parameter sniffing is all about and how it affects the performance of a stored procedure. I would be walking through an example to demonstrate what the effect of parameter sniffing is. I will be showing the query execution plan generated for SQL Server 2005. To my knowledge, there is no change, in how this process works, between the two versions and you should be able to relate it to SQL Server 2000 as well.

Read more »

A Scenario to Ponder #9

This scenario is a slightly modifed (simplified) version of a question posted in a discussion forum.
Lets say I have a SQL Server 2000 box. And I have the following table.

create table #runningavg(id int identity(1,1), curval decimal(16,10))

And the following data:

Read more »

Recursive Self Join - A futile endeavor!!

This post is just a walkthrough of my attempt to come up with an elegant solution in SQL Server 2005 to a (yet) seemingly impossible problem. I don't really know if I am trying to reinvent the wheel but I did realise that the approach I followed was terribly flawed.


What I wanted (and yet want) to achieve might be best explained with an example. Take the table Employees in the Northwind database. With the recursive common table expressions in SQL Server 2005, I can get all the subordinates of an employee (say employeeID = 2) with this query.

Read more »

A Scenario to Ponder #8

This time its an interesting question. You will have to come up with a seemingly impossible query, which is pretty simple to implement in SQL Server 2005 :)
Lets say I have a table like this in a SQL Server 2000 box:

create table #answers (val1 char(1), val2 char(1))

And the following rows

Read more »