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!!!"
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..
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.
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 :)
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.
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.
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.
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.
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.
Posts
All Comments
