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)