Month: November 2012

Now you can use IIF in SQL Server 2012 instead of CASE (again this only if you have 2 possible values that you want to evaluate)

–This will return Apple
SELECT IIF(‘APPLE’ = ‘APPLE’, ‘Apple’, ‘Banana’)

–This will return Banana
SELECT IIF(‘APPLE’ = ‘Banana’, ‘Apple’, ‘Banana’)

OFFSET FETCH in SQL Server 2012 – Paging

In SQL Server 2005, normally when we do paging we need to use CTE or nested SELECT Statement, but in SQL Server 2012 we can use OFFSET and FETCH to do the paging

Let’s start of how we normally do paging in SQL Server 2005/2008 – this will return the first 10 rows ordered by REF

WITH Fields AS
(
SELECT REF, NAME, DISPLAY, ROW_NUMBER() OVER (ORDER BY REF) AS RowNumber
FROM SU_FIELD
)
SELECT REF, NAME, DISPLAY
FROM Fields
WHERE RowNumber BETWEEN 1 AND 10
ORDER BY RowNumber ASC;

To move to next page we do

WITH Fields AS
(
SELECT REF, NAME, DISPLAY, ROW_NUMBER() OVER (ORDER BY REF) AS RowNumber
FROM SU_FIELD
)
SELECT REF, NAME, DISPLAY
FROM Fields
WHERE RowNumber BETWEEN 11 AND 20
ORDER BY RowNumber ASC;

In SQL Server 2012 – it looks much more simpler to write the query. OFFSET is used for skipping the first x rows ad FETCH NEXT is used to control how many records to be returned

–This will return 10 records without skipping any row (REF from 0 – 9)
SELECT REF, NAME, DISPLAY
FROM SU_FIELD
ORDER BY REF
OFFSET 0 ROWS
FETCH NEXT 10 ROWS ONLY;

to move to the next page

–This will return 10 records with skipping first 10 rows (REF from 10 – 19)
SELECT REF, NAME, DISPLAY
FROM SU_FIELD
ORDER BY REF
OFFSET 10 ROWS
FETCH NEXT 10 ROWS ONLY;

TRY_CONVERT in SQL Server 2012

One of the new TSQL feature in SQL Server 2012 is TRY_CONVERT, basically it’s a function that will return null if the object passed is not compatible with the expected casting. In earlier version of SQL Server we don’t have this feature which means you need to make sure your data is cast-able to the expected format

Earlier Version of SQL Server – you can run this

SELECT CONVERT(INT, ‘abc’)

and it will throw an error

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value ‘abc’ to data type int.

Then you will normally do work around with this to avoid the error which means no record will be returned when the object is not a valid data type

SELECT CONVERT(INT, ‘abc’) AS TEST WHERE ISNUMERIC(‘abc’) = 1

SQL Server 2012

SELECT TRY_CONVERT(INT, ‘abc’) AS TEST

This will still return a record but with NULL value when the data is not cast-able

SQL Server udf Join

http://sqlblog.com/blogs/rob_farley/archive/2011/11/08/when-is-a-sql-function-not-a-function.aspx

I found some interesting article to share in regards of joining the UDF into your join which ends up in executing the UDF as many as number of records being joined to. The solution is to put it into a temp table before joining it

Tracing Dynamic SQL in the SQL Profiler

On November 21, 2012