SQL WHERE clauses: Avoid CASE, use Boolean logic

As some of you may know, I recommend to avoid using CASE expressions in the WHERE clause of a query to express conditional logic. I prefer to have everything translated to simple ANDs, ORs and NOTs to keep things a) portable, b) easier to read and c) efficient.

Learning some good boolean logic techniques will go a long way towards making your queries more efficient, and you won't need to rely on CASE's and other methods of doing conditional logic.

First off, when I say “conditional logic”, I am talking about something like this:

“If A then B”

where A and B are both conditions. For example, in a WHERE clause, you might want to implement a condition like this:

“If (@ReturnAll <>1) THEN (EmpID = @EmpID)”

In other words, if the @ReturnAll parameter is 1, then return all of the rows, but if @ReturnAll is not 1, then only return rows where EmpID is equal to the @EmpID parameter supplied.

To express this logic in the WHERE clause, many people might code it like this:

WHERE EmpID = CASE WHEN @ReturnAll<>1 THEN @EmpID ELSE EmpID END

However, this is kind of counter-intuitive (why should we check that EmpID = EmpID ?) and can be really tough to implement when the condition spans more than 1 column in the table (you need multiple CASE's). Also, if EmpID is null this will fail.

The alternative is to translate the condition into a regular boolean expression using only AND, OR and NOT. The logical translation of “IF A then B” is:

“(Not A) or B”

If you work it out on paper, you will see it makes sense. To translate our WHERE clause requirement using the above logic, it becomes:

WHERE (@ReturnAll =1) OR (EmpID = @EmpID)

We are saying if @ReturnAll is 1, don't bother evaluating more of the condition -- return the row because the expression is TRUE. But if @ReturnAll <> 1, then EmpID must be equal to @EmpID for the condition to be true and the row to be returned. Exactly the conditional logic we wish to express.

To show another example, suppose we wish to say:

“IF @Filter=1 THEN Date= @Date and Cust= @Cust and Emp= @Emp”

Expressing this in a CASE clause results in:

WHERE Date = CASE WHEN @Filter=1 THEN @Date ELSE Date END AND
Cust = CASE WHEN @Filter=1 THEN @Cust ELSE Cust END AND
Emp = CASE WHEN @Filter=1 THEN @Emp ELSE Emp END

A little hard to read and quite inefficient -- all 3 case expressions must be evaluated for each row in the result set. Without CASE, we get:

WHERE @Filter<>1 OR (Date= @Date and Cust= @Cust and Emp= @Emp)

Much easier to read and maintain, and faster -- if @Filter <>1, the rest of the expression can be ignored by the optimizer.
Another common situation is using a single variable to implement the optional filter. For example:

"IF @CustID is not null THEN CustID = @CustID"

This is often implemented using ISNULL() or COALESCE() like this:

WHERE CustID = ISNULL(@CustID, CustID)


This is basically the same as writing a CASE expression in that it will not use an index on our column and doesn't implement solid boolean logic.

Converting that IF to a simple boolean expression results in a nice WHERE clause of:

WHERE (@CustID is null OR CustID = @CustID)
which, again, is the preferred way to implement this type of logic in SQL. It is short, simple, portable, easy to read and maintain, and efficient.

Finally, to express:

IF A THEN B ELSE C


you would write it as:

((Not A) or B) AND (A or C)

a little harder, but it does the job! No need for CASE in the WHERE clause ... trust me !
(of course, you may need CASE to manipulate some columns or expressions in the WHERE, just don't use it for boolean logic).

UPDATE: Much more on optimizing your conditional WHERE clauses here, including implementing more complex conditional parameters.

Visual Representation of SQL Joins

Using the code

I am going to discuss seven different ways you can return data from two relational tables. I will be excluding cross Joins and self referencing Joins. The seven Joins I will discuss are shown below:

1. INNER JOIN
2. LEFT JOIN
3. RIGHT JOIN
4. OUTER JOIN
5. LEFT JOIN EXCLUDING INNER JOIN
6. RIGHT JOIN EXCLUDING INNER JOIN
7. OUTER JOIN EXCLUDING INNER JOIN

For the sake of this article, I'll refer to 5, 6, and 7 as LEFT EXCLUDING JOIN, RIGHT EXCLUDING JOIN, and OUTER EXCLUDING JOIN, respectively. Some may argue that 5, 6, and 7 are not really joining the two tables, but for simplicity, I will still refer to these as Joins because you use a SQL Join in each of these queries (but exclude some records with a WHERE clause).

Inner JOIN

This is the simplest, most understood Join and is the most common. This query will return all of the records in the left table (table A) that have a matching record in the right table (table B). This Join is written as follows:

SELECT 
FROM Table_A A
INNER JOIN Table_B B
ON A.Key = B.Key

Left JOIN

This query will return all of the records in the left table (table A) regardless if any of those records have a match in the right table (table B). It will also return any matching records from the right table. This Join is written as follows:

SELECT 
FROM Table_A A
LEFT JOIN Table_B B
ON A.Key = B.Key

Right JOIN

This query will return all of the records in the right table (table B)
regardless if any of those records have a match in the left table
(table A). It will also return any matching records from the left
table. This Join is written as follows:

SELECT 
FROM Table_A A
RIGHT JOIN Table_B B
ON A.Key = B.Key

Outer JOIN

This Join can also be referred to as a FULL OUTER JOIN or a FULL JOIN. This query will return all of the records from both tables, joining records from the left table (table A) that match records from the right table (table B). This Join is written as follows:

SELECT 
FROM Table_A A
FULL OUTER JOIN Table_B B
ON A.Key = B.Key

Left Excluding JOIN

This query will return all of the records in the left table (table A) that do not match any records in the right table (table B). This Join is written as follows:

SELECT 
FROM Table_A A
LEFT JOIN Table_B B
ON A.Key = B.Key
WHERE B.Key IS NULL

Right Excluding JOIN

This query will return all of the records in the right table (table B)
that do not match any records in the left table (table A). This Join is
written as follows:

SELECT 
FROM Table_A A
RIGHT JOIN Table_B B
ON A.Key = B.Key
WHERE A.Key IS NULL

Outer Excluding JOIN

This query will return all of the records in the left table (table A)
and all of the records in the right table (table B) that do not match.
I have yet to have a need for using this type of Join, but all of the
others, I use quite frequently. This Join is written as follows:

SELECT

FROM Table_A A
FULL OUTER JOIN Table_B B
ON A.Key = B.Key
WHERE A.Key IS NULL OR B.Key IS NULL

Examples

Suppose we have two tables, Table_A and Table_B. The data in these tables are shown below:

-- INNER JOIN
SELECT A.PK AS A_PK, A.Value AS A_Value,
     B.Value AS B_Value, B.PK AS B_PK
FROM Table_A A
INNER JOIN Table_B B
ON A.PK = B.PK

A_PK A_Value    B_Value    B_PK
---- ---------- ---------- ----
 1 FOX        TROT          1
 2 COP        CAR           2
 3 TAXI       CAB           3
 6 WASHINGTON MONUMENT      6
 7 DELL       PC            7

(5 row(s) affected)

-- LEFT JOIN
SELECT A.PK AS A_PK, A.Value AS A_Value,
B.Value AS B_Value, B.PK AS B_PK
FROM Table_A A
LEFT JOIN Table_B B
ON A.PK = B.PK

A_PK A_Value    B_Value    B_PK
---- ---------- ---------- ----
 1 FOX        TROT          1
 2 COP        CAR           2
 3 TAXI       CAB           3
 4 LINCOLN    NULL       NULL
 5 ARIZONA    NULL       NULL
 6 WASHINGTON MONUMENT      6
 7 DELL       PC            7
10 LUCENT     NULL       NULL

(8 row(s) affected)

-- RIGHT JOIN
SELECT A.PK AS A_PK, A.Value AS A_Value,
B.Value AS B_Value, B.PK AS B_PK
FROM Table_A A
RIGHT JOIN Table_B B
ON A.PK = B.PK

A_PK A_Value    B_Value    B_PK
---- ---------- ---------- ----
 1 FOX        TROT          1
 2 COP        CAR           2
 3 TAXI       CAB           3
 6 WASHINGTON MONUMENT      6
 7 DELL       PC            7
NULL NULL       MICROSOFT     8
NULL NULL       APPLE         9
NULL NULL       SCOTCH       11

(8 row(s) affected)

-- OUTER JOIN
SELECT A.PK AS A_PK, A.Value AS A_Value,
B.Value AS B_Value, B.PK AS B_PK
FROM Table_A A
FULL OUTER JOIN Table_B B
ON A.PK = B.PK

A_PK A_Value    B_Value    B_PK
---- ---------- ---------- ----
 1 FOX        TROT          1
 2 COP        CAR           2
 3 TAXI       CAB           3
 6 WASHINGTON MONUMENT      6
 7 DELL       PC            7
NULL NULL       MICROSOFT     8
NULL NULL       APPLE         9
NULL NULL       SCOTCH       11
 5 ARIZONA    NULL       NULL
 4 LINCOLN    NULL       NULL
10 LUCENT     NULL       NULL

(11 row(s) affected)

-- LEFT EXCLUDING JOIN
SELECT A.PK AS A_PK, A.Value AS A_Value,
B.Value AS B_Value, B.PK AS B_PK
FROM Table_A A
LEFT JOIN Table_B B
ON A.PK = B.PK
WHERE B.PK IS NULL

A_PK A_Value    B_Value    B_PK
---- ---------- ---------- ----
 4 LINCOLN    NULL       NULL
 5 ARIZONA    NULL       NULL
10 LUCENT     NULL       NULL
(3 row(s) affected)

-- RIGHT EXCLUDING JOIN
SELECT A.PK AS A_PK, A.Value AS A_Value,
B.Value AS B_Value, B.PK AS B_PK
FROM Table_A A
RIGHT JOIN Table_B B
ON A.PK = B.PK
WHERE A.PK IS NULL

A_PK A_Value    B_Value    B_PK
---- ---------- ---------- ----
NULL NULL       MICROSOFT     8
NULL NULL       APPLE         9
NULL NULL       SCOTCH       11

(3 row(s) affected)

-- OUTER EXCLUDING JOIN
SELECT A.PK AS A_PK, A.Value AS A_Value,
B.Value AS B_Value, B.PK AS B_PK
FROM Table_A A
FULL OUTER JOIN Table_B B
ON A.PK = B.PK
WHERE A.PK IS NULL
OR B.PK IS NULL

A_PK A_Value    B_Value    B_PK
---- ---------- ---------- ----
NULL NULL       MICROSOFT     8
NULL NULL       APPLE         9
NULL NULL       SCOTCH       11
 5 ARIZONA    NULL       NULL
 4 LINCOLN    NULL       NULL
10 LUCENT     NULL       NULL

(6 row(s) affected)

Note on the OUTER JOIN that the inner joined records are returned first, followed by the right joined records, and then finally the left joined records (at least, that's how my Microsoft SQL Server did it; this, of course, is without using any ORDER BY statement).

You can visit the Wikipedia article for more info here (however, the entry is not graphical).

I've also created a cheat sheet that you can print out if needed. If you right click on the image below and select "Save Target As...", you will download the full size image.

Scheduling automated backup using SQL server 2008

It is very important to take backups for the database files on regular basis. Microsoft SQL server 2008 made this task very easy. In this blog, I am going through step by step that will allow

• Users to schedule the backup to be taken on a particular interval
• Delete the backup copies after a certain period of time

Schedule the database backup

First I am going to tell you the steps required to schedule the backup. Login to Sql Management studio and connect to the required database. Now from the object explorer, make sure SQL server agent is running, if not start SQL server agent(Right click and press start).

Expand the Management Node from the object explorer, and then select the maintenance plan node. To schedule maintenance plan, you need to have “SYSADMIN” database role. If you dont see the maintenance node, make sure you have the necessary permission.

Right click the maintenance plan and then select “new maintenance plan”.

Enter the maintenance plan name in the popup box (This can be any name that identifies your task for ). This will identify your backup plan and you should choose a relevant name that suits your plan.

Now you will be in the configuration page for the maintenance plan. . Note the marked area, these are the two areas you need to use for setting up the maintenance plan. The marked area in the right top will be used to configure the time that the plan executes. Choose a time so that the database is least used. The bottom left pane shows the tasks that can be utilized to create an sql maintenance plan. since explaining all of them is not in the scope of this document, I am going to explore only two of them.

Click on the calendar item shown in the right side top. This will bring the job schedule properties popup window that configure the execution time/frequency of the tasks. Configure the data carefully so that it suits your requirement. Usually database backups are taken daily basis. Make sure you are selecting proper time so that your database is least used. Click ok once you finish.

From the maintenance plan tasks pane in the left side, select the backup database plan, this will be used to take backups for the databases. Drag and drop backup database task to the right side(as shown in the diagram).

Double click on the backup database task, it will open up a new window that allows you to configure the database configuration for the backup. Here you configure the databases that you need to backup, then specify a location for the backup, specify the extension for the backup files etc.

From the pop up modal window, by clicking on “Databases” dropdown, you will be able to select the required databases. Also configure the file location, extension for the backup file etc.

Click ok once finished. Now backup plan configuration is over. The backup files will be created on the scheduled time to the mentioned folder. The name of the file will be created by appending the date so that you can identify the back up for a particular date.

Since the backup files are created frequently,… it is a good practice that you delete backup files after a certain period of time. For this you need to execute clean up task along with the maintenance plan. You can configure the clean up task as follows.

From the left side pane, drag and drop maintenance cleanup task.

Double click on the dropped item inorder to edit the clean up properties. Here you need to specify the backup location, and file extension for the back up files and specify the age of the file. It is a good practice that you keep one month old data, and delete anything prior to one month.

Once you click ok, then save the maintenance plan. You can either wait till the next execution time or execute it manually inorder to check whether everything is working fine.

Hope this helps

How to Remove Restricted User in SQL Server 2005

The problem is that the database is in a single user mode, this usually happens after a restore process.

You have to execute the query below to change it back to multi user mode by executing the query below.

ALTER DATABASE DatabaseName SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE DatabaseName SET MULTI_USER
GO

Split Function in Sql Server to break Comma-Separated Strings into Table

Sql Server does not (on my knowledge) have in-build Split function.
Split function in general on all platforms would have comma-separated string value to be split into individual strings.
In sql server, the main objective or necessary of the Split function is to convert a comma-separated string value (‘abc,cde,fgh’) into a temp table with each string as rows.

The below Split function is Table-valued function which would help us splitting comma-separated (or any other delimiter value) string to individual string.

CREATE FUNCTION dbo.Split(@String varchar(8000), @Delimiter char(1))
returns @temptable TABLE (items varchar(8000))
as
begin
declare @idx int
declare @slice varchar(8000)

select @idx = 1
if len(@String)<1 or @String is null return

while @idx!= 0
begin
set @idx = charindex(@Delimiter,@String)
if @idx!=0
set @slice = left(@String,@idx - 1)
else
set @slice = @String

if(len(@slice)>0)
insert into @temptable(Items) values(@slice)

set @String = right(@String,len(@String) - @idx)
if len(@String) = 0 break
end
return
end



split function can be Used as

select top 10 * from dbo.split('Chennai,Bangalore,Mumbai',',')

would return


Above Split function can be used to pass parameter to IN clause in sql server.

complex UPDATE uses ANOTHER TABLE as the SOURCE of the data

A more complex update uses another table as the source of the data. This makes the UPDATE statement look like a combination of the UPDATE statement and the SELECT statement.

UPDATE TableName
SET Column2 = AnotherTable.Column3
FROM AnotherTable
WHERE TableName.Column1 = TableName.Column1

We can add joins into this as well, so that we can update more than one column from different tables at the same time.

UPDATE TableName
SET Column2 = AnotherTable.Column3,
Column3 = ThirdTable.Column2
FROM AnotherTable
JOIN ThirdTable ON AnotherTable.Column5 = ThirdTable.Column4
WHERE TableName.Column1 = TableName.Column1

I hope that you find this post useful. I encourage everyone to open up Books OnLine and read through the information on the UPDATE statement. It includes more examples, and some of the other options which are available to you.

CHEKING DUPLICATE ROWS

SELECT branchcode,
COUNT(branchcode) AS branchcode
FROM Blocking_Ids
GROUP BY branchcode
HAVING ( COUNT(branchcode) >1 )

in reference to:

"uch as we try and prevent it, duplicate data still fin"
- How to Delete SQL Server Database Duplicate Rows from a Table Using a Specified Column List and No Temp Tables (view on Google Sidewiki)