Select Range of Records Based on a Condition in SQL Server 2005/2008

In one of the reports, my clients wanted to retrieve a list of Employees who take Sick Leaves. The requirement was to retrieve Employees falling in 40% to 60% range of all Employees who take Sick Leaves. I found the HumanResources.Employee table of the AdventureWorks database to be a good test base to show you the query for the same.

Here’s the query to retrieve records falling in a range

SELECT EmployeeID, Title, Gender, SickLeaveHours
FROM HumanResources.Employee
WHERE SickLeaveHours IN
(
    SELECT TOP 60 PERCENT SickLeaveHours FROM HumanResources.Employee
    ORDER BY SickLeaveHours
)
AND SickLeaveHours NOT IN
(
    SELECT TOP 40 PERCENT SickLeaveHours FROM HumanResources.Employee
    ORDER BY SickLeaveHours
)

The result of running this query is that data for 54 Employees out of 290 Employees are retrieved who fall in between 40 to 60% of all Employee who take sick leaves

Feedback:

I am a Microsoft MVP again!

I thought of sharing an important news with all sqlservercurry.com readers. I am awarded the Microsoft Most Valuable Professional (MVP) title again and I am on Cloud 9. For those who are not aware of the MVP award, this award is given to exceptional technical community leaders who actively share their high quality, real world expertise with others. Microsoft’s appreciates your contributions in your Technical communities/Sites/Blogs during the past year and awards the best of the lot.

I have been contributing regularly to dotnetcurry , sqlservercurry, devcurry and the ASP.NET forums and am glad that my efforts were recognized.

I am so glad that two other authors of my site dotnetcurry -- Mr. Subodh Sohoni and Mr. Shoban Kumar were also awarded the prestigious MVP title.

It feels great to be recognized for your efforts and the MVP award does just the same. I want to thank a lot of people who I have worked with and have inspired me and continue to do so. Your comments and encouragement has played a very important role to keep me motivated and become eligible for this award. Thank You!

I am an MVP and I am proud of it!

Feedback:

Execute Stored Procedure when SQL Server starts

The sp_procoption system stored procedure is useful in setting the Stored Procedure for autoexecution – i.e it runs every time SQL Server service is started.

Here’s how to execute a Stored Procedure when SQL Server starts

EXEC sp_procoption @ProcName = 'usersp_CleanTables',
@OptionName = 'startup',
@OptionValue = 'true'

To disable the stored procedure again

EXEC sp_procoption @ProcName = 'usersp_CleanTables',
@OptionName = 'startup',
@OptionValue = 'false'

Note: For a Stored Procedure to be eligible to be executed when SQL Server starts, the stored procedure must be in the ‘master’ database and cannot contain INPUT or OUTPUT parameters.

Feedback:

SKIP and TAKE ‘N’ number of records in a SQL Server Select Query

How do you SKIP and TAKE ‘n’ number of records from a resultset? I have done this previously using LINQ as demonstrated over here Implementing Paging in a Generic List using LINQ

But how do you skip and take ‘n’ number of records in SQL Server 2005/2008? Here’s how:

DECLARE @TT table
(
ProductID int,
CategoryGroupID int,
CategoryGroupName varchar(10)
)

-- Create Sample Data
INSERT INTO @TT VALUES ( 101, 1, 'AA')
INSERT INTO @TT VALUES ( 203, 1, 'AA');
INSERT INTO @TT VALUES ( 305, 1, 'AA');
INSERT INTO @TT VALUES ( 403, 2, 'BB');
INSERT INTO @TT VALUES ( 553, 2, 'BB');
INSERT INTO @TT VALUES ( 634, 2, 'BB');
INSERT INTO @TT VALUES ( 744, 2, 'BB');
INSERT INTO @TT VALUES ( 838, 3, 'CC');
INSERT INTO @TT VALUES ( 939, 3, 'CC');
INSERT INTO @TT VALUES ( 1245, 3, 'CC');
INSERT INTO @TT VALUES ( 1341, 3, 'CC');
INSERT INTO @TT VALUES ( 1452, 3, 'CC');
INSERT INTO @TT VALUES ( 1565, 4, 'DD');

SELECT ProductID, CategoryGroupID
FROM
(
SELECT tbl.*, ROW_NUMBER() OVER (ORDER BY ProductID) rownum
FROM @TT as tbl
) seq
 WHERE seq.rownum BETWEEN 6 AND 10

As you can see in the query above, we are skipping the first 5 records on an ordered resultset and taking the next 5 records.

OUTPUT

Feedback:

Find the Missing Identity Numbers in SQL Server 2005/2008

We as Database Developers often come across tables that have identity columns. Many a times, there are missing rows in that table, which have probably been deleted during a database operation. Have you wondered how to find the missing rows? Well here’s a query written by Markwill that helps you find the missing identity numbers in a table. I found this query extremely useful and thought of sharing it with all of you.

Let’s create some sample data first. For demonstration purposes, please assume that this table has an identity column

-- Create Sample Table
DECLARE @TT table
( ID int,
CategoryGroupID int,
CategoryGroupName varchar(10)
)

-- Create Sample Data
INSERT INTO @TT VALUES ( 1, 1, 'AA')
INSERT INTO @TT VALUES ( 2, 1, 'AA');
INSERT INTO @TT VALUES ( 3, 1, 'AA');
INSERT INTO @TT VALUES ( 4, 2, 'BB');
INSERT INTO @TT VALUES ( 5, 2, 'BB');
INSERT INTO @TT VALUES ( 6, 2, 'BB');
INSERT INTO @TT VALUES ( 7, 2, 'BB');
INSERT INTO @TT VALUES ( 8, 3, 'CC');
INSERT INTO @TT VALUES ( 9, 3, 'CC');
INSERT INTO @TT VALUES ( 10, 3, 'CC');
INSERT INTO @TT VALUES ( 11, 3, 'CC');
INSERT INTO @TT VALUES ( 12, 3, 'CC');
INSERT INTO @TT VALUES ( 13, 4, 'DD');

We will now go ahead and delete a few rows from this table using the following query

DELETE @TT WHERE ID IN(3,6,11);

SELECT ID FROM @TT

On selecting the records, we will see the missing identity numbers as shown below:

As you can observe from the screenshot above, ID 3, 6 and 11 are missing.

So how do we programmatically determine missing numbers? Here’s the query to detect missing identity numbers

;WITH Missing (missnum, maxid)
AS
(
 SELECT 1 AS missnum, (select max(id) from @TT)
 UNION ALL
 SELECT missnum + 1, maxid FROM Missing
 WHERE missnum < maxid
)
SELECT missnum
FROM Missing
LEFT OUTER JOIN @TT tt on tt.id = Missing.missnum
WHERE tt.id is NULL
OPTION (MAXRECURSION 0); 

OUTPUT

Feedback:

Find the Next and Previous Records in a SQL Server Table

I was solving a strange requirement a few days ago – well not strange for my client though. The client had a Product table. They wanted a query which would accept a Product ID and display the Next and Previous Products of that Product ID listed in that table or a result set. Now I may be wrong, but queries like this are not really solved in T-SQL; rather a front end UI like the GridView is capable of navigating through the records.

However if you intend doing it in T-SQL, then here’s how we can retrieve the Next and Previous Records of a given record

-- Create Sample Table
DECLARE @TT table
(
ProductID int,
CategoryGroupID int,
CategoryGroupName varchar(10)
)

-- Create Sample Data
INSERT INTO @TT VALUES ( 101, 1, 'AA')
INSERT INTO @TT VALUES ( 203, 1, 'AA');
INSERT INTO @TT VALUES ( 305, 1, 'AA');
INSERT INTO @TT VALUES ( 403, 2, 'BB');
INSERT INTO @TT VALUES ( 553, 2, 'BB');
INSERT INTO @TT VALUES ( 634, 2, 'BB');
INSERT INTO @TT VALUES ( 744, 2, 'BB');
INSERT INTO @TT VALUES ( 838, 3, 'CC');
INSERT INTO @TT VALUES ( 939, 3, 'CC');
INSERT INTO @TT VALUES ( 1245, 3, 'CC');
INSERT INTO @TT VALUES ( 1341, 3, 'CC');
INSERT INTO @TT VALUES ( 1452, 3, 'CC');
INSERT INTO @TT VALUES ( 1565, 4, 'DD');

SELECT
  (SELECT MAX(ProductID)
        FROM @TT Prod1
        WHERE     Prod1.ProductID <  Prod2.ProductID ) as PreviousProductID,
  (SELECT MIN(ProductID)
        FROM @TT Prod1
        WHERE     Prod1.ProductID >  Prod2.ProductID ) as NextProductID
FROM @TT  Prod2
WHERE  ProductID = 744

OUTPUT

Feedback:

Recompiling Stored Procedures in SQL Server – All, Few or One at a time

Amongst other uses, recompiling your stored procedures can be useful especially when you want to ensure that you haven't deleted/renamed any columns/tables.

The following queries shown below recompile all stored procedures the next time they are run.

Recompiling all the Stored Procedures in a Database

-- Recompile all Stored Procedures and Triggers on a Database
USE AdventureWorks;
GO
EXEC sp_MSforeachtable @command1="EXEC sp_recompile '?'";
GO

Note: See my post over here for other uses of sp_MSforeachtable 8 Common Uses of the undocumented Stored Procedure sp_MSforeachtable

Recompile all the Stored Procedures in a Table

-- Recompile all Stored Procedures that act on the Customer table
USE AdventureWorks;
GO
EXEC sp_recompile N'Sales.Customer';
GO

Recompile a specific Stored Procedure

-- Recompile a specific Stored Procedure uspGetEmployeeManagers
USE AdventureWorks;
GO
EXEC sp_recompile 'uspGetEmployeeManagers';
GO

Feedback:

Find out the Binary, ASCII and Character of a Given String in SQL Server

When you are storing data inside fields like ‘address’, there are bound to be unusual characters in it which make way due to poor validation rules. A good way to look for them is to convert your string to varbinary.

I came across this cool query from Peter which shows how to convert a string to varbinary and find out the Binary, ASCII and Char of each character in the string. Here’s the query:

DECLARE @MyAddress varchar(35)
SET @MyAddress = 'CANTB RY EA%T P.O.Box 55343'

DECLARE    @BIN AS VARBINARY(100)
SET    @BIN = convert(varbinary(100),@MyAddress)

SELECT    SUBSTRING(@BIN, Number, 1) AS Binary,
ASCII(SUBSTRING(@BIN, Number, 1)) AS ASCII,
CHAR(ASCII(SUBSTRING(@BIN, Number, 1))) AS Character
FROM    master..spt_values
WHERE    Type = 'p'
AND Number BETWEEN 1 AND DATALENGTH(@BIN)

OUTPUT

Feedback: