Friday 24 June 2011

SET and SELECT Differences

- SET is the ANSI standard for variable assignment, SELECT is not.

- SELECT can be used to assign values to more than one variable at a time. SET can only assign a value to one variable at a time.

- When using a query to populate a variable, SET will fail with an error, if the query returns more than one value. But SELECT will assign one of the returned rows and mask the fact that the query returned more than one row.

- When assigning a variable from a query if there is no value returned then SET will assign NULL, where SELECT will not make the assignment at all (the variable will have its' previous value if it has been assigned before).

- Be careful with SET and CASE WHEN statement. When any conditions do not match, CASE WHEN will return ‘NULL’ if ELSE is not specified. Therefore the variable will have a ‘NULL’ value.
DECLARE @errorCode AS INTEGER 
SET @errorCode = 0	
SET @errorCode = CASE
				 WHEN 'A'='B'
				 THEN 1000
				 END
SELECT @errorCode
-- @errorCode will return NULL

- Always use ‘SELECT’ instead of ‘SET’ to get @@ERROR and @@ROWCOUNT
SELECT @RowCount = @@ROWCOUNT, @Error = @@ERROR

Related article:
http://vyaskn.tripod.com/differences_between_set_and_select.htm

Friday 17 June 2011

CASE WHEN Statement Examples

-- this is similar to ' SELECT * FROM Customers WHERE Country = 'France' '
SELECT * FROM Customers
WHERE CASE WHEN Country = 'France' THEN 1 END = 1

Nested CASE WHEN examples:
declare @test varchar(10);
set @test = 'debug';
SELECT  * FROM Customers
WHERE 1 = (	CASE 
		WHEN @test = 'debug'
		THEN	CASE WHEN Country = 'France' AND City = 'Marseille' 
				THEN 1 
				END  
		END )	

declare @test varchar(10);
set @test = 'debug';
SELECT  * FROM Customers
WHERE 1 = (	CASE 
			WHEN @test = 'debug' 
			THEN	CASE WHEN Country = 'France'  
					THEN	CASE
							WHEN  City = 'Marseille'
							THEN 1
							ELSE NULL
							END
					END  
			END )	
This one actually will give the same result as the previous query (2nd example on this article). Below is the result:

Using LIKE clause:
declare @test as varchar(50)
set @test = 'abcdef'
SELECT	CASE 
		WHEN @test LIKE '%bc%' THEN 1
		WHEN @test LIKE '%de%' THEN 2
		ELSE 0
		END	
The result is '1' because the first condition is the first match.

An example of further filtering the rows' cities given their countries:
SELECT * FROM CUSTOMERS
WHERE City = (	CASE WHEN Country = 'France' THEN 'Marseille'
					 WHEN Country = 'UK' THEN 'London'
					 WHEN Country = 'Spain' THEN 'Madrid'
					 END  )
ORDER BY Country
The result is:

Monday 6 June 2011

Example of Using PARTITION Clause

SELECT ContactName, Country, COUNT(*) OVER (PARTITION BY Country) FROM Customers
ORDER BY Country
This will generate the same result as this query:
SELECT ContactName, Country, 
		(SELECT COUNT(*) FROM Customers C1 WHERE C2.Country = C1.Country) 
FROM Customers C2
ORDER BY Country


Thursday 2 June 2011

RANK( ) and DENSE_RANK( ) example

SELECT Country, 
		DENSE_RANK() OVER (ORDER BY Country) AS [Dense_Rank result],
		RANK() OVER (ORDER BY Country) AS [Rank result]
FROM Customers
DENSE_RANK - value is based on the item's uniqueness, same items have same values
RANK - value is based on the starting row number for the new item, same items have same values