A Simple example of a Conditional Where Clause in SQL Server

I was recently preparing a query for a report where the client needed to find out the following information from a small table -

- Products that have been Discontinued but are receiving Orders

- Products that have not been Discontinued but do not generate Orders

I solved this requirement using Conditional WHERE Clause. The Products table of the Northwind database has fields that match this requirement and I will demonstrate the Conditional WHERE clause query using the same. Here’s the query:

SELECT ProductID, ProductName, UnitsOnOrder, Discontinued from
Products WHERE
(Discontinued = 1 AND UnitsOnOrder > 0)
 OR
(Discontinued  = 0 AND UnitsOnOrder = 0)

OUTPUT

You can also branch the statements and re-write the query using an IF-ELSE statement, however since this was a small table, I went in for the Conditional WHERE clause.

Feedback:

List all the Weekends of the Current Year using SQL Server

Sometime back, I had posted to Find the WeekEnds in this Quarter or Year using SQL Server 2005/2008. An anonymous user wrote back asking if there was a way to determine the first and last day of the current year and rewrite the same query. Well here it is:

DECLARE @StrtDate datetime
DECLARE @EndDate datetime
SELECT @StrtDate = DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)
SELECT @EndDate = DATEADD(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate()  )+1, 0))

;WITH CTE (weekends)
AS
(
SELECT @StrtDate
UNION ALL
SELECT DATEADD(d,1,weekends)
FROM CTE
WHERE weekends < @EndDate
)
SELECT weekends from CTE
WHERE DATENAME(dw,weekends)
In ('Saturday', 'Sunday')

OUTPUT

Feedback:

Intellisense does not work in SQL Server 2008

If for some reason, Intellisense does not work for you when using SQL Server Management Studio 2008, then check for the following:

1. Go to Tools > Options > Text Editor > Transact-SQL > Intellisense and make sure the Enable Intellisense checkbox is checked

Also go to Query > Intellisense Enabled to verify that Intellisense is Enabled.

2. If you are not able to see intellisense only for the latest schema changes , then you need to make sure you refresh the cache. The intellisense relies on a local cache which you need to manually refresh.

There are two ways to refresh the cache:

a. Go to Edit -> IntelliSense -> Refresh Local Cache and

b. Hit Ctrl+Shift+R

Once you have performed these steps, open a new query window and you should get Intellisense back on your SSMS 2008

Note: If you are using SSMS 2008 to connect to SQL Server 2000 databases on the network, then intellisense does not work for down version systems. Intellisense only works when you are connected to a 2008 server instance.

Feedback:

Dynamically Pass a Table Name to TRUNCATE TABLE

Here’s a simple query that shows you how to dynamically pass a Table Name to the TRUNCATE TABLE command

USE YOURDB
GO
DECLARE @tbl varchar(30) =  'YourTableName'
DECLARE @dynSQL varchar(50)

SET @dynSQL = 'TRUNCATE TABLE ' + @tbl

EXEC (@dynSQL)

You can even create a Stored Procedure and use this code. The only difference will be that ‘YourTableName’ will get replaced with a parameter of the StoredProcedure containing the Table Name

Feedback:

Auto Generate AlphaNumeric ID’s in a SQL Server Table

I was recently working on a query to auto generate a Column ID based on an IDENTITY column. I used computed columns to achieve this. If you too have a similar requirement, then here’s the query.

DECLARE @TT TABLE (
CandID as 'C-' + RIGHT('000' + CONVERT(varchar, CourseID),5),
CourseID int IDENTITY(1,1),
ReferrerName varchar(10)
)

INSERT INTO @TT VALUES ('Dhiraj')
INSERT INTO @TT VALUES ('Jack')
INSERT INTO @TT VALUES ('Kelly')
INSERT INTO @TT VALUES ('Kelly')
INSERT INTO @TT VALUES ('Jack')
INSERT INTO @TT VALUES ('Dhiraj')

SELECT * FROM @TT

OUTPUT

Observe how the values of the CandID column are autogenerated based on the values if the CourseID column.

Feedback:

Cumulative Update Package for SQL Server 2008 R2 RTM

Microsoft had recently released  Cumulative update package 8 for SQL Server 2008 Service Pack 1.

This week the Cumulative Update package 1 for SQL Server 2008 R2 RTM was released. This package is for SQL Server 2008 SP1 customers who want to upgrade to the latest version of SQL Server 2008 R2 and still keep the hotfixes from Cumulative Update 5 to 7 for the release version of SQL Server 2008 SP1

Microsoft said  “Cumulative Update 1 for Microsoft SQL Server 2008 R2 RTM contains only hotfixes that were released in Cumulative Update 5, 6, and 7 for SQL Server 2008 Service Pack 1 (SP1). Cumulative Update 1 for SQL 2008 R2 RTM is only intended as a post-RTM rollup for Cumulative Update 5-7 for the release version of SQL Server 2008 SP1 customers who plan to upgrade to SQL Server 2008 R2 and still keep the hotfixes from Cumulative Update 5-7 for the release version of SQL Server 2008 SP1. No new hotfixes have been included in this cumulative update”

Feedback:

List of Database Engine Error Messages

All system and user-defined error messages in an instance of the Database Engine are contained in the sys.messages catalog view.

Here’s how to query this catalog view to list the Error Messages based on their severity

SELECT * FROM sys.messages  
WHERE language_id = 1033 
ORDER BY severity desc

OUTPUT

The error message describes the cause of the error whereas the severity indicates how serious the error is.You can read more on the different Level of Severities over here Database Engine Error Severities

Feedback:

Group and Count Records in SQL Server

I had recently written a query to Select the Latest Record based on a Condition in SQL Server. A SQLServerCurry.com reader Mazz wrote back asking how to use the same query to count how many times a student took an exam for each course. Here’s the modified query that works on SQL Server 2005/2008.

SAMPLE DATA:

DECLARE @TT TABLE (CandID int, CourseID smallint)

INSERT INTO @TT VALUES (1, 101)
INSERT INTO @TT VALUES (3, 102)
INSERT INTO @TT VALUES (3, 110)
INSERT INTO @TT VALUES (3, 102)
INSERT INTO @TT VALUES (1, 101)
INSERT INTO @TT VALUES (2, 101)
INSERT INTO @TT VALUES (1, 110)
INSERT INTO @TT VALUES (1, 109)
INSERT INTO @TT VALUES (2, 102)
INSERT INTO @TT VALUES (1, 110)
INSERT INTO @TT VALUES (1, 101)
INSERT INTO @TT VALUES (3, 110)
INSERT INTO @TT VALUES (2, 102)
INSERT INTO @TT VALUES (2, 102)

QUERY

;WITH CTE
AS
(
SELECT
ROW_NUMBER() OVER (PARTITION BY CANDID, CourseID ORDER BY CandID) as ord,
CandID, CourseID
FROM @TT
)
SELECT CandID, CourseID, Count(CourseID) as TotalAttempts
FROM CTE
GROUP BY CandID, CourseID
ORDER BY CandID

OUTPUT

Feedback:

Taking BackUp of SQL Server Database on a USB Drive

While carrying out a normal database BackUp operation, I thought of taking my database backup on a USB Drive too. So I opened up my SQL Server Management Studio > expanded ‘Server Objects’ > Right-clicked ‘Backup Devices’, and then clicked on ‘New Backup Device’. This is what I saw. The Tape option was grayed out.

Note: Microsoft says “Support for tape backup devices will be removed in a future version of SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.”

Here’s what I did to take the backup of my database on a USB. I used the sp_adddumpdevice to register the USB based tape device. What this stored proc does it adds a backup device to the sys.backup_devices catalog view. Once done, the device can then be referred to logically in BACKUP statements

QUERY

-- Add a Tape BackUp Device
USE master;
GO
EXEC sp_addumpdevice 'tape', 'TpDumpOne', '\\.\Tp0';

-- Back Up DB to the newly create Tape
USE <YourDB>
GO
Backup Database <YourDB>
TO TpDumpOne

Feedback:

Select the Latest Record based on a Condition in SQL Server

I was working on a query recently and thought of sharing the solution with all my readers. Here’s the problem statement - An institute allows its students to appear for an exam twice, for each course they attend. While generating the report, the institute wanted that only the latest exam date should be shown, listing the CandidateID, CourseID and latest ExamDate for that Student in each Course. The query works on SQL Server 2005/2008.

SAMPLE DATA

DECLARE @TT TABLE (CandID int, CourseID smallint, DateOfExam datetime)

INSERT INTO @TT VALUES (1, 101, 'April 02, 2010 11:25am')
INSERT INTO @TT VALUES (3, 102, 'April 03, 2010 9:55am')
INSERT INTO @TT VALUES (5, 110, 'April 07, 2010 11:24am')
INSERT INTO @TT VALUES (3, 102, 'April 10, 2010 11:22am')
INSERT INTO @TT VALUES (4, 109, 'April 15, 2010 5:27am')
INSERT INTO @TT VALUES (2, 101, 'April 16, 2010 8:21am')
INSERT INTO @TT VALUES (1, 110, 'April 17, 2010 11:55am')
INSERT INTO @TT VALUES (4, 109, 'April 22, 2010 10:16am')
INSERT INTO @TT VALUES (6, 102, 'April 24, 2010 11:35am')
INSERT INTO @TT VALUES (1, 110, 'April 30, 2010 9:49pm')
INSERT INTO @TT VALUES (1, 101, 'May 01, 2010 9:49am')
INSERT INTO @TT VALUES (5, 110, 'May 02, 2010 10:43am')
INSERT INTO @TT VALUES (2, 101, 'May 03, 2010 11:29am')
INSERT INTO @TT VALUES (6, 102, 'May 03, 2010 11:29am')

QUERY

;WITH CTE
AS
(
SELECT
ROW_NUMBER() OVER (PARTITION BY CandID, CourseID order by DateOfExam DESC) as ord,
CandID, CourseID, DateOfExam
FROM @TT
)
SELECT CandID, CourseID, DateOfExam
FROM CTE
WHERE ord = 1;
GO

As you can see, we use the ROW_NUMBER() function to calculate the sequential number of a row within a partition of each CandidateID and CourseID and then use ord=1 to select the latest date

OUTPUT

Feedback:

Create Date Ranges for Consecutive dates in a SQL Server Table

I had recently posted a SQL script to SQL Query to Find out who attended office on Saturday

Shashi commented asking it was possible to create Date Range for all consecutive dates and group them together in such queries. Here is a query to group all the consecutive dates together and create a range out of them.

SAMPLE DATA

DECLARE @TT TABLE (EID int, CheckIn DATETIME)
INSERT INTO @TT VALUES (2, 'April 02, 2010 11:25am')
INSERT INTO @TT VALUES (4, 'April 03, 2010 9:55am')
INSERT INTO @TT VALUES (5, 'April 07, 2010 11:24am')
INSERT INTO @TT VALUES (2, 'April 10, 2010 11:22am')
INSERT INTO @TT VALUES (3, 'April 15, 2010 5:27am')
INSERT INTO @TT VALUES (6, 'April 16, 2010 8:21am')
INSERT INTO @TT VALUES (7, 'April 17, 2010 11:55am')
INSERT INTO @TT VALUES (3, 'April 22, 2010 10:16am')
INSERT INTO @TT VALUES (4, 'April 24, 2010 11:35am')
INSERT INTO @TT VALUES (7, 'April 30, 2010 9:49pm')
INSERT INTO @TT VALUES (2, 'May 01, 2010 9:49am')
INSERT INTO @TT VALUES (5, 'May 02, 2010 10:43am')
INSERT INTO @TT VALUES (3, 'May 03, 2010 11:29am')

QUERY

SELECT MIN(CheckIn) AS BeginRange,
       MAX(CheckIn) AS EndRange
FROM (
SELECT CheckIn,
    DATEDIFF(D, ROW_NUMBER() OVER(ORDER BY CheckIn), CheckIn) AS DtRange
FROM @TT) AS dt
GROUP BY DtRange;

OUTPUT

Feedback:

SQL Query to find out who attended office on Saturday

A client of mine had a report requirement to find out the employees who attended office on Saturday in the last 30 days and their Check-In Check-Out times.

Here is some sample data that does that. For convenience, I have not added the CheckOut time, but hopefully you will get the idea

SAMPLE Data

DECLARE @TT TABLE (EID int, CheckIn DATETIME)
INSERT INTO @TT VALUES (2, 'April 02, 2010 11:25am')
INSERT INTO @TT VALUES (4, 'April 03, 2010 9:55am')
INSERT INTO @TT VALUES (5, 'April 07, 2010 11:24am')
INSERT INTO @TT VALUES (2, 'April 10, 2010 11:22am')
INSERT INTO @TT VALUES (3, 'April 15, 2010 5:27am')
INSERT INTO @TT VALUES (6, 'April 16, 2010 8:21am')
INSERT INTO @TT VALUES (7, 'April 17, 2010 11:55am')
INSERT INTO @TT VALUES (3, 'April 22, 2010 10:16am')
INSERT INTO @TT VALUES (4, 'April 24, 2010 11:35am')
INSERT INTO @TT VALUES (7, 'April 30, 2010 9:49pm')
INSERT INTO @TT VALUES (2, 'May 01, 2010 9:49am')
INSERT INTO @TT VALUES (5, 'May 02, 2010 10:43am')
INSERT INTO @TT VALUES (3, 'May 03, 2010 11:29am')

QUERY

SELECT * FROM @TT
WHERE DATENAME(weekday, CheckIn) = 'SATURDAY'
AND DATEDIFF(DD, CheckIn, GETDATE()) < 30

OUTPUT

Hopefully these people get the bonus they deserve!

Feedback:

SQL Server 2008 R2 Express Database Size Limit increased from 4GB to10 GB

I had recently posted about SQL Server 2008 R2 RTM Released.

Well the excitement does not stop here. I was glad to read this official announcement by Microsoft which said that they increased the maximum database size in SQL Server 2008 R2 Express from 4GB to 10GB

The 10 GB size limit also applies to SQL Server 2008 R2 Express with Tools and SQL Server 2008 R2 Express with Advanced Services. The CPU and RAM limits remain the same i.e. 1 CPU and 1 GB of RAM.

Note: In case, you missed it out, SQL Server 2008 R2 Express was also released shortly after the SQL Server 2008 R2 RTM. Here are some important download links:

Microsoft SQL Server 2008 R2 Express
For X86 For X64

Microsoft SQL Server 2008 R2 Express with Tools
For X86 For X64

Microsoft SQL Server 2008 R2 Express with Advanced Services
For X86 For X64

Microsoft SQL Server 2008 R2 Management Studio Express
For X86 For X64

Read more over here ‘Installing SQL Server 2008 R2 Express’

Feedback:

Win a copy of ‘SQL Server MVP Deep Dives’

My site DotNetCurry.com is celebrating 3 years and we have kept a Mega Giveaway of gifts worth $18K. There are some EBooks from Manning to be won including 2 copies of the SQL Server MVP Deep Dives worth $34.99 each.

From Manning “This is no ordinary SQL Server book. In SQL Server MVP Deep Dives, the world's leading experts and practitioners offer a masterful collection of techniques and best practices for SQL Server development and administration. 53 MVPs each pick an area of passionate interest to them and then share their insights and practical know-how with you.

SQL Server MVP Deep Dives is organized into five parts: Design and Architecture, Development, Administration, Performance Tuning and Optimization, and Business Intelligence. In each, you'll find concise, brilliantly clear chapters that take on key topics like mobile data strategies, Dynamic Management Views, or query performance.”

How do I Win a Copy of ‘SQL Server MVP Deep Dives‘?

It’s quite simple. Just follow @DotNetCurry on twitter and after May 24th, we will be selecting winners of this EBook as well as the other products. Check out the entire GiveAway over here

Feedback:

Convert Date to String in SQL Server

Whenever I see a question on converting a Date to String in SQL Server, I see developers nesting various REPLACE functions to get the desired output. However with some knowledge of formatting date and time, this requirement can be achieved in a simple manner as shown below.

Here’s a query that converts a Date to a String in SQL Server:

DECLARE @Dt as DateTime
SET @Dt = '2010-04-27 11:30:17'

SELECT CONVERT(CHAR(8), @Dt, 112)
+ REPLACE(CONVERT(CHAR(8), @Dt, 114), ':', '')

In the query shown above, the style value 112 gives an output of yymmdd and a style value 114, gives an output of hh:mi:ss:mmm(24h). To display the milliseconds too, change Char(8) to Char(12).

OUTPUT

Further Reading: Cast and Convert

Feedback: