SQL Server Denali : Choose

CHOOSE function returns a value from the given array based on the specified index position.

Syntax : Choose ([Position], [Value1],[Value2],…,[ValueN])

Here,
Position : The position number of the value to return. Position number starts from 1
[Value1],[Value2],…,[ValueN] : List of values.

Example :

SELECT CHOOSE(1, 'a', 'b', 'c') AS First,

CHOOSE(2, 'a', 'b', 'c') AS Second;

/*
First Second
----- ------
a     b
*/
-- If the index value exceeds the bounds of the array of values,
-- then CHOOSE returns null.

SELECT CHOOSE(0, 'a', 'b', 'c') AS First,

CHOOSE(4, 'a', 'b', 'c') AS Second

/*
First Second
----- ------
NULL  NULL
*/
-- If the provided index value has a numeric data type other than int,
-- then the value is implicitly converted to an integer

SELECT CHOOSE(2.5, 'a', 'b', 'c') AS First,

CHOOSE(3.9, 'a', 'b', 'c') AS Second;

/*
First Second
----- ------
b     c
*/


Example 2:
/* 
We can use Choose function in Select List, Group By Clause and Order By Clause also. 
*/

SELECT p.Name, SUM(s.OrderQty) SellQuantity, CHOOSE(DATEPART(QUARTER, s.ModifiedDate), '1st', '2nd', '3rd', '4th') AS Quarter

FROM Sales.SalesOrderDetail s INNER JOIN Production.Product p ON p.ProductID = s.ProductID

WHERE s.ModifiedDate >= '2006-01-01' AND s.ModifiedDate < ' 2007-01-01'

GROUP BY p.Name,CHOOSE(DATEPART(QUARTER, s.ModifiedDate), '1st', '2nd', '3rd', '4th')

HAVING SUM(s.OrderQty) > 600

ORDER BY p.Name, CHOOSE(DATEPART(QUARTER, s.ModifiedDate), '1st', '2nd', '3rd', '4th')

If we look at the execution plan, we will see that Compute Scalar is used to support CHOOSE function. The engine has taken the CHOOSE function and converted it into a CASE.

SQL Server Denali: FORCESEEK and FORCESCAN

FORCESEEK and FORCESCAN are the new functions in SQL Server Denali. 
Before Denali, FORCESEEK hint exists and enhanced in Denali CTP3. FORCESCAN table hint is new addition. 
FORCESEEK: It forces optimizer to use index seek only. Sometimes optimizer does not user proper plan and use index scan which cause high reads on the system. We can use forceseek here to force otpimizer to use index seek which can give better performance.
FORCESCAN: Specifies that the query optimizer use only an index scan operation as the access path to the referenced table or view. The FORCESCAN hint can be useful for queries in which the optimizer underestimates the number of affected rows and chooses a seek operation rather than a scan operation. Here are few examples of queries with hints of FORCESEEK and FORCESCAN.


-- FORCESEEK
SELECT  ObjectName FROM ObjectItems WITH (FORCESEEK) WHERE ObjectType = 'SQL_STORED_PROCEDURE'


-- FORCESEEK and specified index
SELECT ObjectName FROM ObjectItems WITH (FORCESEEK,INDEX (IX_ObjectType_CreateDate)) WHERE ObjectType = 'SQL_STORED_PROCEDURE'


-- FORCESEEK and specified index using at least the specified index columns
SELECT ObjectName FROM ObjectItems WITH (FORCESEEK(IX_ObjectType_CreateDate(ObjectType,CreateDate))) WHERE ObjectType = 'SQL_STORED_PROCEDURE'


-- FORCESCAN
SELECT  ObjectName FROM ObjectItems WITH (FORCESCAN) WHERE ObjectType = 'SQL_STORED_PROCEDURE'


-- FORCESCAN and specified index
SELECT  ObjectName FROM ObjectItems WITH (FORCESCAN, INDEX(IX_ObjectId)) WHERE ObjectType = 'SQL_STORED_PROCEDURE'

SQL Server Denali : Lead and Lag

LEAD() and LAG() are two interesting functions introduced in SQL Server Denali.


LEAD() can be used to access data from a subsequent row in the same result set and its counterpart LAG() function can be used to access data from a previous row in the result set without using a self-join. 


Syntax: LEAD/LAG (scalar_expression [,offset] [,default]) OVER ([partition_by_clause] order_by_clause)
/* Create Table */
CREATE TABLE ExamResults
(SubjectName varchar(25), Student  varchar(50), Number int)
-- Inserting sample records
INSERT INTO ExamResults 
VALUES
('Maths', 'Student 1', 45),
('Physics', 'Student 2', 45),
('Physics', 'Student 1', 50),
('Chemistry', 'Student 3', 20),
('Physics', 'Student 3', 35),
('Biology', 'Student 1', 20),
('Biology', 'Student 2', 60),
('Biology', 'Student 3', 65),
('Chemistry', 'Student 1', 75),
('Biology', 'Student 4', 30)
GO
/* Query using LEAD() and LAG() */
SELECT Student , SubjectName, Number, 
LAG(Number,1,0) OVER(PARTITION BY SubjectName ORDER BY Number) as LG,
LEAD(Number,1,0) OVER(PARTITION BY SubjectName ORDER BY Number) as LD
FROM ExamResults ORDER BY SubjectName, Number
GO
/*
OUTPUT :
Student      SubjectName   Number   LG  LD
-------------   --------------------  -----------   ----  ----
Student 1   Biology                20      0   30
Student 4   Biology        30    20   60
Student 2   Biology        60    30   65
Student 3   Biology        65    60     0
Student 3   Chemistry             20      0   75
Student 1   Chemistry        75    20     0
Student 1   Maths        45      0     0
Student 3   Physics        35      0   45
Student 2   Physics        45    35   50
Student 1   Physics        50    45     0
*/

SQL Server Denali: Select/Skip Top/Bottom N Rows

SQL Server Denali introduces query paging in which you can specify range of rows returned by a SELECT statement. This can be handy when you want to limit number of rows returned by the statement.
This is implemented in ORDER BY clause. two new keywords are added to ORDER BY clause:
1. OFFSET : Skips top N number of rows from the result set
2. FETCH : Fetch next N number of rows from the result set (after skipping rows specified by OFFSET)
1. Return bottom 15 rows only:
SELECT ProductCode, ProductName, Color FROM ProductMaster ORDER BY ProductCode DESC OFFSET 0 ROWS FETCH NEXT 15 ROWS ONLY
2. This will remove first 15 rows from the result:
SELECT ProductCode, ProductName, Color FROM ProductMaster ORDER BY ProductCode OFFSET 15 ROWS
3. Remove first 15 rows from the result, and return next 10 rows:
SELECT ProductCode, ProductName, Color FROM ProductMaster ORDER BY ProductCode OFFSET 15 ROWS FETCH NEXT 10 ROWS ONLY

SQL Server Denali: DateFromParts()

DateFromParts() is a new function introduced in SQL Server Denali. It takes three input parameters Year, Month and Day and returns value in Date format. 

Syntax: DateFromParts(Year, Month, Day)

There are 5 more functions related to DateTime introduced in SQL Server Denali.

TimeFromParts() function returns output in TIME format.

Syntax: TimeFromParts(Hour, Minute, Seconds, Fractions, Precision)

SmallDateTimeFromParts() function returns output in SmallDateTime2 format.

Syntax: SmallDateTimeFromParts(Year, Month, Day, Hour, Minute)

DateTimeFromParts() function returns output in DATETIME format.

Syntax: DateTimeFromParts( year, month, day, hour, minute, seconds, milliseconds)

DateTime2FromParts() function returns output in DATETIME2 format.

Syntax: DateTime2FromParts(Year, Month, Day, Hour, Minute, Seconds, Fractions, Precision)

DateTimeOffSetFromParts() function returns output in datetimeoffset format.

Syntax: DateTimeOffSetFromParts(Year, Month, Day, Hour, Minute, Seconds, Fractions, Hour_offset, Minute_offset, Precision)

SQL Server Denali – EOMONTH()

EOMONTH() returns the date value that represents the last day of the month for given date.
Syntax: EOMONTH( start_date [, months_to_add] )
Before Denali, to calculate the date value that represents the last day of the month we used:

SELECT DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 1, 0))

In Denali, we can use EOMONTH function.

SELECT EOMONTH (GETDATE()) AS LastDay1, EOMONTH('2011-09-05') AS LastDay2

If we specify “months_to_add” argument, then EOMONTH adds the specified number of months to start_date, and then returns the last day of the month for the resulting date.

SELECT EOMONTH(GETDATE(),-1) AS LastDayOfPreviousMonth, EOMONTH(GETDATE()) AS LastDayOfMonth, EOMONTH (GETDATE(),1)LastDayOfNextMonth

SQL Server Denali: ColumnStore() a new Index Function

It is special type of index which stored columns in separate set of data pages instead of rows stored in data pages.It is optimized for the improved and fast warehouse queries processing. In ordinary index rows are stored in disk pages but with columnstore index columns are stored in separate set of the disk pages, that why it is more faster then ordinary index. It is mostly optimized for queries which are used aggregation , warehouse processing and BI application. 


Syntax:
CREATE NONCLUSTERED COLUMNSTORE INDEX mycolumnstoreindex ON mytable (col1, col2, col3);


You can use the Object Explorer in Management Studio to create the index as follows:
Expand the tree structure for the table and then right click on the Indexes icon.
Select New Index and then Nonclustered columnstore index
Click Add in the wizard and it will give you a list of columns with check boxes.
You can either choose columns individually or click the box next to Name at the top, which will put checks next to all the columns. Click OK.
Click OK.

SQL Server Denali: CONCAT() Function

CONCAT() Function is new string function in SQL Server Denali. It returns a string that is the result of concatenating two or more string values (or values that can be converted to string).
Syntax: CONCAT(string1, string2, string3, ...)  
With CONCAT function, you can concatenate between 2 and 254 values. If you try to use CONCAT with only one value or with more than 254 values you will get an error.

Example:
select concat ('First' , ' ' ,'Second',' ','Three')
It will return "First Second Third"

CONCAT function has 2 advantages compared to the concatenating of strings using the “+” operator:
1. NULL values are implicitly converted to an empty string
2. All arguments are implicitly converted to string

SQL Server Denali– PARSE() and TRY_PARSE() conversion functions

PARSE() function converts expression (string value) to date/time and number data types if conversion is possible. If conversion isn’t possible it raises an error.

TRY_PARSE() function converts expression (string value) to date/time and number data types or return NULL if conversion isn’t possible. It basically identifies if the type specified is applicable for parsing or not and returns the appropriate status. 

We often see the error, "Conversion failed when converting the varchar value 'dsfds' to data type int.". Now we can avoid this error by using Try_Parse() function. If we are in doubt that there might be some invalid values, by using this TRY_PARSE() function, we can avoid this errors and it will generate NULL values for invalid values. You can convert data to different datatype using new try_parse() function introduced in SQL Server Denali. It returns data if parse is successful otherwise it returns null.

Example

SELECT PARSE('08' AS datetime)

-- Raised an error
--Msg 9819, Level 16, State 1, Line 1
--Error converting string value '15' into data type datetime using culture ''.

SELECT TRY_PARSE('08' AS datetime2)

--  Returned NULL

SELECT PARSE('8' AS NUMERIC(15,2))

-- Returned 8.00

SELECT TRY_PARSE('8' AS NUMERIC(15,2))

-- Returned 8.00

GO

SQL Server Denali: FIRST_VALUE() and LAST_VALUE()

In SQL Server Denali introduced these two analytic function. As the Name suggests First_Value() returns the first value in an ordered set of values and Last_Value() returns the Last value in an ordered set of values.


Example:First_Value
SELECT Cust_ID, Cust_Name, FIRST_VALUE(Cust_ID) OVER (ORDER BY Cust_ID) AS [First Value] FROM   Cust_Table Result:
Cust_ID   Cust_Name First Value
1               A                  1
2               B                  1
3               C                  1
4               D                  1


Example:Last_Value
SELECT Cust_ID, Cust_Name, LAST_VALUE(Cust_ID) OVER (ORDER BY Cust_ID) AS [Last Value] FROM   Cust_Table 
Result:
Cust_ID   Cust_Name Last Value
1               A                  4
2               B                   4
3               C                  4
4               D                  4

SQL Server Denali : Choose() Function

Choose() function returns an input argument from a list of input values at the desired index which is specified as an input arguement as well.
Syntax: CHOOSE (index, arg1, arg2 [, argN])
The first input argument to the Choose() function is "index" and it is in integer data type. The input arguments can be thought as an array with index starting from 1.
Example:
select choose (1,'a','b','c','d')
It will return 'a'.
If no Value exist at the specified index than it will return null.
select choose (5,'a','b','c','d')
It will return null.

SQL Server Denali: IIF Logical Function

If you have developed some sort of applications using Microsoft Visual Studio.net, then you are definitely familiar with “IIF” logical function. Prior to SQL Server Denali we had used “CASE” instead of “IIF” as this logical function was not available. But in SQL Server Denali CTP3, “IIF” is available with same ease and functionality.

According to BOL”IIF is a shorthand way for writing a CASE statement. It evaluates the Boolean expression passed as the first argument, and then returns either of the other two arguments based on the result of the evaluation. That is, the true_value is returned if the Boolean expression is true, and the false_value is returned if the Boolean expression is false or unknown. true_value and false_value can be of any type. The same rules that apply to the CASE statement for Boolean expressions, null handling, and return types also apply to IIF.

The fact that IIF is translated into CASE also has an impact on other aspects of the behavior of this function. Since CASE statements can nested only up to the level of 10, IIF statements can also be nested only up to the maximum level of 10. Also, IIF is remoted to other servers as a semantically equivalent CASE statement, with all the behaviors of a remoted CASE statement.” 

OFFSET and FETCH Feature of SQL Server Denali

The OFFSET and FETCH clause of SQL Server Denali provides you an option to fetch only a page or a window of the results from the complete result set. 



Using this feature of SQL Server Denali one can easily implement SQL Server Paging while displaying results to the client.  We will take a look at simple example and then also how you could construct a stored procedure to implement SQL paging.


Let’s go through a simple example which demonstrates how to use the OFFSET and FETCH feature of SQL Server Denali.  You can see below that the TSQL looks the same as what you write today except after the ORDER BY clause we have the OFFSET and FETCH commands.  One thing to note is that you have to use an ORDER BY to use this feature.  The OFFSET basically tells SQL to skip the first 100 rows and the FETCH will get the next 5 rows.

USE AdventureWorks2008R2
GO
SELECT 
  BusinessEntityID
  ,PersonType
 ,FirstName + ' ' + MiddleName + ' ' + LastName 
FROM Person.Person
 ORDER BY BusinessEntityID ASC
  OFFSET 100 ROWS 
  FETCH NEXT 5 ROWS ONLY
GO

The below snippet shows the output when running the above commands.  This shows that the first 100 rows were discarded and the query fetched the next 5 rows in the complete recordset.

Let’s go through another example where we will create a stored procedure which will use the OFFSET and FETCHfeature of SQL Server Denali to achieve sql paging while displaying results to client machines.  In this stored procedure we are passing in a page number and the number of rows to return.  These values are then computed to get the correct page and number of rows.

USE AdventureWorks2008R2
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = 
OBJECT_ID(N'[dbo].[ExampleUsageOfSQLServerDenaliPagingFeature]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[ExampleUsageOfSQLServerDenaliPagingFeature]
GO
CREATE PROCEDURE ExampleUsageOfSQLServerDenaliPagingFeature
 (
  @PageNo INT,
 @RowCountPerPage INT
 )
AS
SELECT
  BusinessEntityID
 ,PersonType
 ,FirstName + ' ' + MiddleName + ' ' + LastName 
FROM Person.Person
 ORDER BY BusinessEntityID
  OFFSET (@PageNo - 1) * @RowCountPerPage ROWS
  FETCH NEXT @RowCountPerPage ROWS ONLY
GO

Let’s go ahead and execute the stored procedure using the below command.  This will give us five records starting at page 21 where the records are ordered by BusinessEntityID.

/* Display Records Between 101 AND 105 BusinessEntityID */
EXECUTE ExampleUsageOfSQLServerDenaliPagingFeature 21, 05
GO

The below snippet shows the output once the above stored procedure is executed successfully. You can see that first 100 (20 pages * 5 rows per page = 100) rows were discarded and the stored procedure fetched only the next 5 rows thereby limiting the number of rows sent to the client.

You have seen in this tip how easily you can achieve SQL Server Paging using the OFFSET and FETCH feature of SQL Server Denali. SQL paging is not as hard as it used to be with this new feature.
Ref:http://www.mssqltips.com

WITH RESULT SETS Feature of SQL Server Denali

WITH RESULT SETS is a very useful feature especially when one needs to display a result set of a stored procedure by changing the names and data types of the returning result set. 


There was always a limitation in the previous versions of SQL Server that whenever you wanted to change a Column Name or a Data Type within the result set of a Stored Procedure you ended up making changes to all the references within a Stored Procedure. This limitation has been overcome with the release of WITH RESULT SETS feature in SQL Server Denali. 


USE tempdb
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Employee]') AND type in (N'U'))
DROP TABLE [dbo].[Employee]
GO
CREATE TABLE [dbo].[Employee]
(
 [ID]   [INT]   NOT NULL,
 [EmployeeType]  [NCHAR](15)  NOT NULL,
 [FirstName]  NVARCHAR(50) NOT NULL,
 [MiddleName]  NVARCHAR(50)  NULL,
 [LastName]  NVARCHAR(50)  NOT NULL,
) ON [PRIMARY]
GO
INSERT INTO dbo.Employee VALUES (1,'Cashier','Lee','L','Warne')
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[UsingWithResultSetsFeatureOfDenali]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[UsingWithResultSetsFeatureOfDenali]
GO
CREATE PROCEDURE UsingWithResultSetsFeatureOfDenali
AS
BEGIN
 SELECT
  ID, 
  FirstName + ' ' + MiddleName +' '+ LastName AS Name, 
  EmployeeType 
 FROM dbo.Employee
END
GO
/* Execute Stored Procedure */
EXEC UsingWithResultSetsFeatureOfDenali
GO
/* Using WITH Result Sets Feature Of Denali (SQL Server 2011) */
EXEC UsingWithResultSetsFeatureOfDenali 
WITH RESULT SETS
(
 ( 
  ID INT,
  EmployeeName VARCHAR(150),
  EmployeeType VARCHAR(15)
 ) 
) 
GO



In the above example, you can see that using WITH RESULTS SETS feature of SQL Server Denali we have changed the Column Name and Data Type to meet our needs irrespective of the Column Name and Data Type returned within the result set of the Stored Procedure.  The Column Name is changed from “Name” to “EmployeeName” and also Data Type for “Name” is changed from NVARCHAR to VARCHAR and Data Type for “EmployeeType” is changed from NCHAR to VARCHAR while displaying the result set. This feature can be very useful when executing a stored procedure in SSIS where you can execute the Stored Procedure with the required columns names and appropriate data types.