Conditionally add a column in the table

Sometimes we need to add a column in an already existing table but we need to make sure that this column should not be present already. This conditional column creation requires a way to check column existence in the given table. SQL Server 2005 INFORMATION_SCHEMA.COLUMNS view is an standard way to do this. Following script gives you the ability to conditionally add a column in the table, if that column is not already present.

Use tempdb
Go

Create table testTable (id int, city varchar(50))
Go

—Check for column existence
If not exists
(
Select * from INFORMATION_SCHEMA.COLUMNS
Where
TABLE_CATALOG=‘tempdb’
AND TABLE_SCHEMA=‘dbo’
AND TABLE_NAME=‘testTable’
AND COLUMN_NAME=‘id’
)
Begin
–Add new column
ALTER Table dbo.testTable Add id int Null
Print ‘Column added successfuly’
End
Else
Begin
–Column already exists. leave the table as it is.
Print ‘Column already exists.’
End
Go

Drop table testTable
Go

Dynamic Date Search without writing dynamic SQL

Many of us sometimes got stuck when our application requires flexible search mechanism where one or all of the search parameters can be NULL. These type of queries requires you to implement a mechanism which takes care of all possible combinations of input to the stored procedure.
Following is a quick,simple and efficient way of implementing flexible date search. Technique used here is applicable for any type of parameter.

We will create a stored procedure which will take two parameters FromDate and ToDate. Both, any or none of these parameters can be Null. I am using AdventureWorks sample database and the table we are searching on is Sales.SalesOrderHeader. This table contains a column called OrderDate. The objective here is to return
1. all orders whose OrderDate falls in the given range if both From and To Dates are provided.
2. all orders whose OrderDate is greater than or equal to the given FromDate if ToDate is NULL.
3. all orders whose OrderDate is less than or equal to the given ToDate if FromDate is NULL.
4. and finally all orders if both From and To Dates are Null.

Following is the stored procedure:

Use AdventureWorks
Go
Create proc usp_GetSalesOrderHeaderInfo
(
@p_FromDate datetime,
@p_ToDate datetime
)
as
Begin
–Set date format to Day/Month/Year
Set dateformat ‘dmy’

Select * from Sales.SalesOrderHeader
Where OrderDate between
–If respective parameter is Null then OrderDate is equal to itself which is always true
Isnull(@p_FromDate,OrderDate) and isnull(@p_ToDate,OrderDate)

End

and Following are some sample calls to the above stored procedure

–Set date format to Day/Month/Year
Set dateformat ‘dmy’

—Get all orders of one day i.e. 17-March-2004
exec usp_GetSalesOrderHeaderInfo ‘17/03/2004′,‘17/03/2004′

—Get all order uptill 17-March-2004
exec usp_GetSalesOrderHeaderInfo NULL,‘17/03/2004′

—Get all order from 17-March-2004 and after
exec usp_GetSalesOrderHeaderInfo ‘17/03/2004′,NULL

—Get all orders
exec usp_GetSalesOrderHeaderInfo NULL,NULL

How it works?
The crux of the logic is If the parameter is Null then compare the value with itself which will always results TRUE so practically you have removed the where clause condition for that parameter without creating dynamic SQL for different combinations.

Hash Join: The clear indicator of Index requirement

Many people ask me What is Hash join? When SQL Server uses HASH join algorithm? The answer is, if you do not have index on either column of a join statement then SQL Server has to scan both the tables to match the rows. Is it really feasible? Is there can be anything better than this?
Yes, there is one algorithm which is Hash algorithm. If you recall from your school days of Data Structure course you will easily understand the importance of data hashing here. The idea is simple:
1. Take a sample set of data values from the column participating in Join (Each value represents a data bucket)
2. Distribute the rows in respective buckets according to their values (Rows will get the bucket number by applying the hash function on the value and the result will be the bucket number)
3. Check the existence of any data value by applying the hash on the value, getting the bucket number as a result and searching on that bucket only.

It looks complex but it is much more efficient than a simple table scan of a huge table.

Always remember, the quickest algorithm for searching is the B-Tree which is the default algorithm for SQL Server but it is applicable on indexed columns or SQL Server builds it dynamically sometimes for very small tables.

Please note: Whenever you see a Hash join operator in your query plan than most of the time it is a clear indicator of a Index requirement on that column but beware, if it is an ad hoc query and you are not willing to run this query on production then do not make excessive indexes just because of Hash Join operator.

Search columns in SQL Server 2005 database

Since SQL Server 2005 Management Studio lacks the Object Search feature, here is the simple query to find any column in a database

Select O.name objectName, C.name ColumnName from sys.columns C inner join sys.objects O ON C.object_id=O.object_idwhere C.name like ‘%ColumntoFind%’order by O.name,C.name

This query works for SQL Server 20005. Just replace “ColumnToFind” with your required column name.