Round UP with CEILING

Rounding up in TSQL is bit tricky but if you want just to round up a decimal value to the nearest integer then you can use tsql Math function CEILING function to achieve this easily.

CEILING function return the next integer that is larger than the number entered.

SELECT CEILING(43.98) -- Returns 44
SELECT CEILING(43.18) -- Returns 44
SELECT CEILING(0.18) -- Returns 1
SELECT CEILING(-0.18) -- Returns 0
SELECT CEILING(-43.18) -- Returns -43

MSDN reference : http://msdn.microsoft.com/en-us/library/ms189818.aspx

Using WAITFOR to run a SQL Statement on a given time

Recently I came across a situation where I wanted to run the same SP at the same exact time from several sessions. The purpose of this was to simulate and see if there are any blocking or deadlock situations. As this is not possible to do by manually pressing F5 from few sessions, I looked out for a solution.

And I found this WAITFOR function in SQL Server very much do the trick. WAITFOR function has two behaviors one we can give a DELAY, a specific number of hours:minutes:seconds to wait till it executes the next statement(s) in the batch. Or by using TIME we can provide a specific time to run the next statement(s) in the batch.

Usage would be as follows;

WAITFOR DELAY '00:00:30'

EXECUTE spSampleSP @ID = 23945

When we run the above script the process will wait 30 seconds before it executes second statements which in this case the stored procedure.

WAITFOR TIME '13:11:30'

EXECUTE spSampleSP @ID = 23945

When we use the above script with the TIME function it will wait till the system time reaches 1 PM, 11 minutes and 30 seconds to execute the stored procedure.

So I used WAITFOR TIME in my situation where I could set same time for few sessions to execute the same stored procedure and could simulate the scenario. Hope you will also find this useful.

LEFT OUTER JOIN Vs RIGHT OUTER JOIN Vs FULL OUTER JOIN

There seems to be some confusion among some people about LEFT JOIN (Same as LEFT OUTER JOIN) , RIGHT JOIN (Same as RIGHT OUTER JOIN) and FULL JOIN (Same as FULL OUTER JOIN). Let me explain these three with a simple example

Lets say we have two tables called tblCustomer and tblInvoice where as tblInvoice holds a foriegn key for tblCustomer in fCustomerID column. Below are the data exists in our two sample tables;

tblCustomer



tblInvoice

You may be already aware of the most popular JOIN type we use INNER JOIN (Or just JOIN). In INNER JOIN what happens is on the given conditional column data it displays matching rows from both the tables. Below is an example of INNER JOIN results on our two sample tables.

Why we use ANSI_NULLS ON/OFF

You may have seen many times SET ANSI_NULLS ON statement on top of MS SQL Server stored procedures sometime on functions. Have you ever wondered what is it really means? Well if you havent you may be not reading this blog post.

So the main purpose of this option is to give SQL Server the ISO complient standard to its queries which uses Equal (=) and Not Equal (<>) operators on nullable columns. We should note using these two operators on nullable columns is not ANSII (or SQL 92) standartd. If you set this option ON then in SELECT statements with any conditions in the WHERE cluase which uses column = NULL or column <> NULL can get affected.. These conditions will not return any data if there are any NULL values in that column.

I know this is not that clear to you, so lets do a simple example to clear things out.

First we'll create a sample table and populate it with some test data;

CREATE TABLE tblTestNulls (ID INT IDENTITY NOT NULL PRIMARY KEY, [Name] VARCHAR(20) NULL) 

INSERT INTO tblTestNulls
SELECT 'Johon' UNION ALL
SELECT 'Mayor' UNION ALL
SELECT NULL UNION ALL
SELECT NULL Then we'll run below two queries with ANSI_NULLS ON.

SET ANSI_NULLS ON
SELECT  * FROM tblTestNulls WHERE [Name] = NULL
SELECT
  * FROM tblTestNulls WHERE [Name] <> NULL

As you can see it does not return anything since there are NULL values in Name column and we have set ANSI_NULLS ON.
So we'll run the same queries with ANSI_NULLS OFF
SET ANSI_NULLS OFF
SELECT  * FROM tblTestNulls WHERE [Name] = NULL SELECT * FROM tblTestNulls WHERE [Name] <> NULL 

So here we get the desired results. If we dont set any option for ANSI_NULLS default would be database default, usually OFF unless you have changed it. The database's default ANSI_NULLS settings you can find under database properties then going to options page.

Also if you use ANSII syntax for NULL comparison such is IS NULL you can get the desired results regardless of  whether ANSII_NULLS is ON or OFF. So you can write our two queries as below;

SELECT * FROM tblTestNulls WHERE [Name] IS NULL
SELECT * FROM tblTestNulls WHERE [Name] IS NOT NULL

This is one of the good example why it is always better to use ANSII syntax in Transact SQL.
Conclusion here is dont use SET ANSII_NULL ON simply because it is there in the SQL Server template when you try to create a Stored Procedure or Function. If it is ON adhere to the ANSII syntaxes. Also it is important if your queries run on hetrogenius databases.

Please feel free to leave your comments.