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.

Read more »

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.

Read more »

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.

Read more »

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.

Read more »

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.

Read more »

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

Read more »

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.

Read more »