creative developer(SQL-DOTNET)

I will be covering some of the common but tricky queries like:-  (i) Finding the nth highest salary of an employee. (ii) Finding TOP X records from each group. (iii) Deleting duplicate rows from a table. NOTE :  All the  SQL  mentioned in this article has been tested under  SQL  Server 2005.  (i) Finding the nth highest salary of an employee. Create a table named Employee_Test and insert some test data as:- CREATE TABLE Employee_Test ( Emp_ID INT Identity, Emp_name Varchar( 100 ), Emp_Sal Decimal ( 10 , 2 ) ) INSERT INTO Employee_Test VALUES ( ' Anees' , 1000 ); INSERT INTO Employee_Test VALUES ( ' Rick' , 1200 ); INSERT INTO Employee_Test VALUES ( ' John' , 1100 ); INSERT INTO Employee_Test VALUES ( ' Stephen' , 1300 ); INSERT INTO Employee_Test VALUES ( ' Maria' , 1400 ); It is very easy to find the highest salary as:- -- Highest Salary select max(Emp_Sal) from Employee_Test Now, if you are asked to find the 3rd highest sala...

SQL SERVER – Group by Rows and Columns using XML PATH – Efficient Concating Trick I hardly get hard time to come up with the title of the blog post. This was one of the blog post even though simple, I believe I have not come up with appropriate title. Any way here is the question I received. “I have a table of students and the courses they are enrolled with the name of the professor besides it. I would like to group the result with course and instructor name. For example here is my table: How can I generate result as following? “ Now you can see how easy the question is but so hard to come up with either solution or  title of this blog post. We can use XML PATH and come up with the solution where we combine two or more columns together and display desired result. Here is the quick script which does the task ask, I have used temporary tables so you can just take this script and quickly run on your machine and see how it returns results. Let me know i...

SQL SERVER – 2005 – List All Stored Procedure Modified in Last N Days I usually run following script to check if any stored procedure was deployed on live server without proper authorization in last 7 days. If SQL Server suddenly start behaving in un-expectable behavior and if stored procedure were changed recently, following script can be used to check recently modified stored procedure. If stored procedure was created but never modified afterwards modified date and create date for that stored procedure are same. SELECT  name FROM  sys.objects WHERE  type  =  'P' AND  DATEDIFF ( D , modify_date ,  GETDATE ()) <  7 ----Change 7 to any other day value Following script will provide name of all the stored procedure which were created in last 7 days, they may or may not be modified after that. SELECT  name FROM  sys.objects WHERE  type  =  'P' AND  DATEDIFF ( D , create_date ,  GETDATE ()) ...

Create A Comma Delimited List From a Column In this article I will try to show you a simple example on how to Create a Comma Delimited List from a column also called as CSV (Comma Separated Values). For example here is our Sample Table - Id Names 1 A 1 B 1 C 2 A 2 B 3 X 3 Y 3 Z And here is the expected output - Id AllNames 1 A, B, C 2 A, B 3 X, Y, Z Though in above example I have used Comma, sometimes it may required to use other delimiters as well, like Single space (A B C) or Vertical bar (A | B | C) or may be some other delimiter. Create Sample Data - -- Create sample table CREATE   TABLE   Test  ( Id   INT , Names   VARCHAR (100) ) GO   -- Load sample data   INSERT   INTO   Test   SELECT 1, 'A'   UNION   ALL   SELECT 1, 'B'   UNION   ALL   SELECT 1, 'C'   UNION   ALL   SELECT 2, 'A'   UNION   ALL   SELECT 2, 'B'   UNION   ALL   S...

How To Split A Comma Delimited String In one of my previous posts I wrote about “ How to Create a Comma Delimited List ”.  Now I’ll show you an example with reverse action, that is Splitting a Comma Delimited String or array() of values. There can be different types of Delimiters also : like Comma ' , ', vertical bar ' | ', Single space or a Tab. For example here is our Sample Table - Id AllNames 1 A,B,C 2 A,B 3 X,Y,Z And here is the expected output - Id Names 1 A 1 B 1 C 2 A 2 B 3 X 3 Y 3 Z Create Sample Data : -- Create Table for  Sample Data CREATE TABLE  Test ( ID  INT , AllNames  VARCHAR (100) ) GO -- Load Sample Data  INSERT INTO  test  SELECT 1, 'A,B,C'  UNION ALL SELECT 2, 'A,B'   UNION ALL SELECT 3, 'X,Y,Z' GO -- Verify the Sample Data SELECT  Id, AllNames FROM  Test And here is the query for How to split a comma delimited string : ; WITH  Cte  AS (    ...

Script All the Stored Procedures in The Database In Sql Server 2005 and 2008 you can script the stored procedure in Management Studio by right clicking on Store Procedure name and clicking on “Script Store Procedure as” and then “Create To”. But if you want to script all the Stored Procedures in the database programmatically, then here is the simple T-SQL query for it - To script All the Stored Procedures in the Database : SELECT     O.Name  as  ProcName         ,M.Definition  as  CreateScript         ,O.Create_Date         ,O.Modify_Date FROM   sys.sql_modules   as  M INNER JOIN  sys.objects   as  O ON  M.object_id = O.object_id WHERE  O.type =  'P' If the Stored Procedure is created with ENCRYPTION option then you will get the NULL in the definition column. Similarly, To script All the Vi...

SQL Myth: Primary Key and Clustered Index After remaining quiet for almost a year I’m back with what I enjoy the most, talking about SQL and sharing whatever little knowledge I have.   Many SQL Developers have this misconception:  “Primary key => Clustered Index: Only a Clustered Index can exist on a Primary key column”.     On numerous occasions I had tough times explaining that this is not the case every time, you can create a Non-Clustered Index on a primary key column.  But if this hot discussion is going on across a coffee table and I’m away from Computer I get helpless.  So finally I decided to write about this. You can create a Non-Clustered Index on primary key column.    Or if I try to put this in Myth Buster words “A primary key column can exist/survive without a Clustered Index”  Yes it is a fact that PRIMARY KEY constraints default to CLUSTERED Index.   But it doesn’t mean that you CAN’T create ...