SQL Garbage Collector: 2006

Dec 4, 2006

Obvious SQL Tip #2

Thinking in sets:
The thought process that goes into set- based programming is slightly different from writing procedural code.

Say, I have 2 tables -one holding order header information and the other holding the details of the products ordered. I want to get the items ordered for all the orders:

The procedural way of looking at it is "for each order in the orders table get the order details from the [order details] table".
In DB terms, its "for all orders in the orders table get the order details from the [order details] table".

A Scenario to Ponder #11

This time it's a pretty interesting scenario to solve. I have a table called the distance_tbl holding distance between different cities.
Here is the table definition:

create table #distance_tbl (city1 char(1), city2 char(1), distance int)

And the sample data:

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...

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?

Nov 20, 2006

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.

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.

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:

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.

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

A Scenario to Ponder #7

A totally hypothetical and insane requirement, incontestably prone to excessive criticism (to put it mildly) from Joe Celko, if he ever happens to visit my blog :)

But, lets just assume that the requirement is warranted and we need a solution in SQL Server 2000.
I have a table with this definition.

CREATE TABLE BAD_ENCRYPTION(VAL_ID INT, VALUE VARCHAR(100))

Templates in SQL Server

I asked a few of my colleagues whether they knew that there were templates in SQL Server. Their responses were typically either "Is it?" or "Yeah. Saw it once when I was trying to create a new query. So?"

When Microsoft tries to pack in so many features into SQL Server 2005 aimed at improving the life of developers, why haven't they done much on the templates, which I feel, is a powerful tool for speeding up the development process.

A Scenario to Ponder #6

Here is a question which I find to be pretty interesting. Can anyone come-up with a query or a batch which, when executed, will print itself? I would like to see the lengthiest batch anyone can come up with. The solution can be in SQL Server 2000 or 2005. For example, here is a no-brainer solution that works for SQL Server 2005

SELECT (select text from sys.dm_exec_sql_text(sql_handle))
FROM sys.dm_exec_requests
WHERE session_id = @@spid

A Scenario to Ponder #5

This should be quite simple. But, I would like to find the best approach to this problem.
The question is to get the binary values for positive integers.

Lets take this table called numbers that has one column called id holding positive integer values. Now, I need to write a query (or set of queries) to select from the table "numbers" and return me the id and the binary value of that id.

Alternative approach to splitting a comma separated list

I had already blogged on how we can split a comma separated list using CTEs in my other post on Interesting queries using recursive Common Table Expressions , the idea of which was a slight modification to the one conceived and presented by Erland Sommarskog in Arrays and Lists in SQL Server.

After reading through the blog of Hugo Kornelis on using stuff for string manipulation, I realised that the STUFF function, indeed, made splitting a comma separated values string (CSV) pretty much easy.

Calculating the value of pi

I accept that this post looks a bit out of context for SQL, but its defenitely an interesting "garbage" to collect. For all having some aptitude in maths, you would wonder how the value of pi is derieved? Here is a small attempt in finding the value of pi from what we already know.

A Scenario to Ponder #4

This scenario doesn't (and hopefully shouldn't) relate to any requirements, you will ever see, to be implemented at the database tier. But just for the fun of solving it, I am putting it here.
Please have a look at the image. Its a sine curve, that is generated from a query selecting 2 columns (both computed, may be from number table). One column gives the angle in degrees and the other column is the corresponding sine value, graphically represented.

A Scenario to Ponder #3

Now this is pretty similar to the previous scenario (#2) , but a bit more interesting, I hope.

Here is the question.

I have a table maxnum with the following data:

create table maxnum (a int, b int, c int)

insert into maxnum values (1,2,3)
insert into maxnum values (4,3,5)
insert into maxnum values (23,44,23)
insert into maxnum values (23,45,45)

Now I need to get the result with 4 columns. a,b,c and the intermediate (neither max nor min) value of a,b,c. So, the result will be this way:

a b c intermed
1 2 3 2
4 3 5 4
23 44 23 23
23 45 45 45

How do I get the result?
Take it, that all three values (a,b,c) in the row cannot be the same.

Oct 7, 2006

A scenario to ponder #2

Unlike the previous scenario, I am not tying this up with any SQL Server version. Here is the question.
I have a table maxnum with the following data:

create table maxnum (a int, b int, c int)

insert into maxnum values (1,2,3)
insert into maxnum values (4,3,5)
insert into maxnum values (23,44,23)
insert into maxnum values (23,45,45)

Now I need to get the result with 4 columns. a,b,c and the max value of a,b,c. So, the result will be this way:

a b c maxval
1 2 3 3
4 3 5 5
23 44 23 44
23 45 45 45

How do I get the result?

Oct 5, 2006

A scenario to ponder #1

For all those using SQL Server 2005, here is a scenario, see if you can come up with a solution:
I thought I will check out the different implementations possible and find the best out of it.

Say you have a table:

Customers (CustomerID int primary key, CustomerName varchar(50))

A pretty simple table structure. And it has 1000 rows.
Now, I am conducting a contest for the customers where I will randomly pick up 5 to 20 customers every week and give away prizes.

How will I go about doing it?

I need to create a stored procedure/query/function that will accept no parameters but will return random list of customers and random number of customers (between 5 and 20)

Sep 15, 2006

Missing sp_who

The issue I am going to write on is something I had seen on a website quite sometime back. I don't remember any explaination given about the behavior. I tried to search for that site now but in vain. So, I am posting it anyways. And I will also be writing about the attitude I get from the guys who are Anti-Microsoft. My thoughts on it will be intertwined in my post and hence might seem that I am digressing from the topic at times :)

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..

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.

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 :)

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.

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.

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.

Interesting queries using recursive Common Table Expressions

I have just given an attempt to show how the recursive member of the common table expression (CTE) can be used.

A recursive CTE has 2 parts, the anchor member and the recursive member. The anchor member will first put in a set of rows into the CTE and the recursive member will use the newly added rows in the CTE to get a few more rows and adds it to the CTE. Now the recursive member will act upon the newly added rows again and this will go on till the recursive member does not produce any rows by acting upon the penultimate rows added to it.

Generate Unique User IDs

This requirement was actually a question in the discussion forum. I thought I will keep a copy of my solution here for reference.

Okay, so here it goes.

I am taking the narration from the actual post (thanks to him), with a few changes.

Lets say I have a table like this.

Function to get the fraction given a decimal value

This User-defined function, written in SQL Server 2000, which uses Euclid’s algorithm to convert the decimal to a fraction. The function has an accuracy of 0.0001 i.e. the input decimal value is rounded off beyond this when it’s converted to a fraction. If you need a better accuracy change the function accordingly.

Understanding and Using the NOT operator in SQL Server

Using a NOT operator, be it NOT IN or NOT EXISTS had been a performance bottleneck in most of the queries.

No knowledgeable SQL prgrammer will ever say that he has achieved the best performing query that can ever be written for a given functionality. Because, performance doesn't just depend on the query we write, but goes far beyond that. It includes (but not limited to) the indexes on the table, the data, fragmentation of the data and indexes, server load and server settings.

Date Functions in SQL Server - FAQ

There are lots of queries on date operations like getting the first day of the month or week or quarter, last day given some date. So I thought I will put over here some basic implementations.
I will keep updating the post as and when I get to solve more queries on this.

Eliminating duplicate rows in a table - SQL Server 2000

There are quite a few number of ways by which we can delete the duplicate records from a table. I am just discussing 3 methods which I find interesting. I don't take ownership for any of these methods. I had seen these implementations put to use at different places. And I am just compiling it so that you get the different options in one place and chose the best one for yourself..

Feel the power of the new T-SQL

Just thinking if I could come up with a query that will use most, if not all, of the T-SQL enhancements of SQL Server 2005, using the adventureworks database here. And this was the best I could come up with.

Understanding Outer Joins

Most of the times when we talk about the ANSI join syntax, we say that we use the ON clause for the joining columns and where for filter. Though there is no rule that we need to include joining columns in the on clause or the filter in the where clause. It is only for readability.

Funny behavior in SQL Server 2000 UDF

This is one interesting observation in User defined functions (UDF) in SQL Server 2000.
Be careful when you use a group by and have a scalar UDF in the select. What you get out of a query depends on what the query execution plan is. Try this out..

I am creating a very simple function.

Creating unique constraint on columns with multiple null values

Before you read further: A more detailed and original post of this scenario is in the msdn blog: Achieving Selective Uniqueness in SQL Server Tables . Thanks to the comment from an anonymous reader. Please read through that if you need to understand the mechanics behind the suggestion. And it obviously is a better read as it comes from the creators of SQL Server :)
-Omni
-------------------------------------------------------------

Okay. The scenario is this. I have a table tlb1 and a nullable int column col1. I want to create an unique constraint on col1 satisfying the following requirements.
1. Col1 can have multiple nulls.
2. All non-null values in Col1 should be unique.

Concatenate the values in a column in SQL Server 2000 and 2005

Please Note: Before you go through this article, the approach I have given for SQL Server 2000 is unpredicatable and might fail. The only predictable way is to use a cursor and loop through it and concatenate the values. Please go through the links I had given in the post to understand the potential issues. I would suggest you not to use this approach in production environment. The suggestions for SQL Server 2005 (both XML and CLR), however, shouldn't have any problem.
-Omni
---------------------------------------------------------------

This is the most common question that comes in most discussion forums that I feel its high time Microsoft brings in an inbuilt function for it.

Case sensitive search in SQL Server

I cannot find a real necessity to do a case sensitive search in the database. And if it was needed the column or the database should have had the proper collation.

But beyond all these you want to do a case sensitive search on a column, then you can do it the following ways.

Recursive function with cross apply vs CTE in SQL Server 2005

Common Table Expressions (CTE) may not be the best way, performance wise, (in all cases) for recursive output. I don't want to come out too strong.
But I guess it can be proved with a small test.

Lets take the database AdventureWorks.
Say, I want to find all the subordinates for a given manager (here I take it as null for the top most manager) from the table HumanResources.Employee.

Auto suggest words - nearest match with soundex

What I am trying to acheive here is to find out the the word in a dictionary table that is the closest match to the word entered by the user. I will be using the "difference" function, that actually gives the difference between the soundex values of the two words compared (More info will be there in BOL)

Dec 4, 2006

Nov 29, 2006

Nov 27, 2006

Nov 22, 2006

Nov 20, 2006

Nov 17, 2006

Nov 15, 2006

Nov 13, 2006

Nov 2, 2006

Oct 30, 2006

Oct 28, 2006

Oct 25, 2006

Oct 18, 2006

Oct 17, 2006

Oct 16, 2006

Oct 15, 2006

Oct 14, 2006

Oct 7, 2006

Oct 5, 2006

Sep 15, 2006

Jul 27, 2006

Jul 22, 2006

Jul 15, 2006

Jul 13, 2006

Jul 12, 2006

Jul 11, 2006

Jun 29, 2006

Jun 28, 2006

Jun 27, 2006

Jun 25, 2006

Jun 13, 2006

Jun 7, 2006

Jun 6, 2006

Jun 5, 2006

Search My Site

Categories

Fun with SQL - Puzzles

Blog Archive

Favorite SQL Server links