Clustered Index should not be used for Sorting


Howdy Everyone…

It is a common misconception that writing a clustered index on a table will get the table sorted on the index key columns. Although this is true for some extent, there are some exceptions to it, more over it is not documented and not supported by Microsoft, so they can remove this functionality without even letting you know about it (and not support it in the future versions of SQL server).

The below SQL script is a perfect example where we can see on why we should use order by clause every time.

use tempdb

create table emp

(

id  INT IDENTITY(1,1) PRIMARY KEY

,name varchar(200) NULL

)

GO

 

select object_name(object_id) table_name,name,type_desc  From sys.indexes where  object_id = object_id('emp')

table_name name type_desc
emp PK__emp__5FFB4DF9 CLUSTERED

insert into emp (name) values ('raj')

insert into emp (name) values ('suri')

insert into emp (name) values ('siva')

insert into emp (name) values ('murali')

 

select * from emp

id name

1

raj

2

suri

3

siva

4

murali

create unique index uix_empName ON emp (name)

select object_name(object_id) table_name,name,type_desc From sys.indexes where  object_id = object_id('emp')

table_name name type_desc
emp PK__emp__5FFB4DF9 CLUSTERED
emp uix_empName NONCLUSTERED

select * from emp

id name

4

murali

1

raj

3

siva

2

suri

As you can see the second select statement resulted in different order of rows even though there is a clustered index on the table.

Understanding Recursive queries


Howdy Everyone…

With the introduction of common table expressions (aka CTE), people started using it for building recursive and hierarchical solutions… At a first glance at the solution I am not able to understand what is happening there…. So here is the comeplete explanation of how it is happening for an application programmer than a database expert.

Let the scenario be printing all the numbers from 100 to 1, How we might have accomplished this task without the use of CTE is as below

declare @n int
set @n = 100
while(@n > 0)
BEGIN
	SELECT @n
	SET @n = @n-1
END

Simple one, I think there is no need of further explanation from my end to explain the above stuff….
Now coming to the recursive queries…. how we will do the same…

declare @n table ( a int)
insert into @n values (100)


;with CTE
AS
(
	SELECT a from @n        --- Anchor Query
	UNION ALL
	SELECT (a-1) from cte   --- Recursive Query
	where (a-1) > 0
)
select * from cte

What is happening over there… too much code…. You might be thinking using the earlier method is easier… but believe me using the recursive queries might help you to avoid a large of number cursors in your implementation.
Now going into the explanation bit,
We have defined a table variable named @n which will hold the values of 100 in a column a.
In a recursive CTE, you should always have a anchor query followed by union all or else you will get the error from SQL (Recursive common table expression ‘CTE’ does not contain a top-level UNION ALL operator.)
Next we have to build the recursion by querying on the common table expression itself (in this case CTE), but you have to use limit where the recursion should end, in this case the where clause of the second select statement will do that (where (a-1) > 0)
Incase if you didn’t mention the where clause you will get an error from SQL Server with the max recurssion exceeded. The reason is SQL Server dont know when to stop the recursion or where to stop the recursion.
So, by default it will loop for 100 times and if it is more than that we get and error like (The statement terminated. The maximum recursion 100 has been exhausted before statement completion.)
We can restrict the same by using the query hint OPTION (MAXRECURSION <number>), which will limit the number of loops.