Jul 27, 2006

Next version of SQL Server - Wish List

This is from the Dark Omni :) Just thinking out loud what all I would like to have in the next version of SQL Server. This is in no particular order and some might seem totally impossible to implement and some, downright stupid. But, I leave the decision to Microsoft. They are the ones who often yell "SURPRISE!!!"

Pattern matching - One or more occurances of a character

This was actually a question that popped in the discussion forum. I just want to keep all the good things that come out on the discussion forum (where I had SOME role to play) in this blog :)
Here is the issue.

Say, I have a table like this..

Jul 22, 2006

Resolution for concatenate column values issues

I would like to add a note before you go ahead and read this article. Please do not take this as an approach guaranteed to work. I have come across instances when the solution I suggested didn't work. Please read through this, purely from an academic perspective, and do not, I repeat, do not incorporate these suggestions in your production environment.

I am still keeping the article as I had originally written since I would like to maintain the arguements and the counter arguements.

Jul 15, 2006

Useful undocumented procedures in SQL Server 2000

Before I start, please do not use these procs in your production code since they are undocumented, which means Microsoft does not support these procs and might remove it in future versions. With that said, I am just writing about three procedures which I find very useful. Its more of a shortcut for the DBAs to get their work done faster :)

Jul 13, 2006

Pivot Query Generator for SQL Server 2005

SQL Server 2005 has a new feature called the PIVOT operator. It's useful in generating cross tab reports. Well, let me start by giving an example.

Lets say we have a table called the distance_tbl that gives the distance between any two cities.

Accessing non-deterministic functions in UDF

For those who didn't know, the following function definition will throw an error in SQL Server 2000.

create function fn1()
returns datetime
as
begin
return getdate()
end

The error thrown is "Invalid use of 'getdate' within a function".

Thats because built in non deterministic functions cannot be used inside UDF. Well there is a work around for this.

Generating temporary calendar tables in SQL Server 2000 and 2005

Many a times, we get a question as to get all the days or sundays or weeks between two given dates and join it with someother table and get the result and the suggestion given in the discussion forum is to use a calendar table.

Jul 12, 2006

Issue (seemingly) with the SET PARSEONLY option

This was actually a question raised in a discussion forum. Thought I will write about it anyways. In a database, lets say we don't have a table called orders.

Then the following script works fine, since syntactically its right and the select statement is just parsed.

Jul 11, 2006

Calculating the running total in a table

The scenario I am trying to discuss is to have a table which gives the running total of the values when you select from it. What I am not discussing is to generate the running total for a select query.

Okay, so here is the table definition.