Denali | SQL with Manoj

SQL Server 2012 (DENALI) TSQL – New features | Built-in Functions

July 27, 2011 6 comments

Here is a list of some new function introduced in SQL Server Denali, some examples are taken from MSDN and here is the link for more details.

Logical Functions:

-- CHOOSE()
SELECT CHOOSE ( 3, 'Manager', 'Director', 'Developer', 'Tester' ) AS Result; -- Developer
SELECT CHOOSE ( 2, 'Manoj', 'Saurabh', 'Andy', 'Dave' ) AS Result; -- Saurabh
GO

-- IIF()
DECLARE @a int = 45;
DECLARE @b int = 40;
SELECT IIF ( @a > @b, 'TRUE', 'FALSE' ) AS Result; -- TRUE
GO

Note: The IIF() function was important for the developers that migrated from ACCESS to SQL Server. Introduction of these 2 functions will make their life easy.

Check the video Tutorial on CHOOSE() and IIF() functions.

Conversion Functions:

-- PARSE()
SELECT PARSE ('Monday, 13 December 2010' AS datetime2 USING 'en-US') AS Result;
SELECT PARSE ('€345,98' AS money USING 'de-DE') AS Result;
GO

-- TRY_CAST()
SELECT
CASE WHEN TRY_CAST ('test' as float) IS NULL
	THEN 'Cast failed'
	ELSE 'Cast succeeded'
END AS Result;
GO

-- TRY_CONVERT()
SELECT
CASE WHEN TRY_CONVERT (float,'test') IS NULL
THEN 'Cast failed'
ELSE 'Cast succeeded'
END AS Result;
GO

-- TRY_PARSE()
SELECT
CASE WHEN TRY_PARSE ('Aragorn' AS decimal, 'sr-Latn-CS') IS NULL
THEN 'True'
ELSE 'False'
END
AS Result;
GO

Note: The TRY_CAST(), TRY_CONVERT() & TRY_PARSE() functions added exception handling to the existing CAST(), CONVERT() and new PARSE() function respectively. Now developers can fully rely on these 2 functions while type converting values and defaulting them to something else on any execption.

Analytic Functions:

USE AdventureWorks2008R2;
GO

-- FIRST_VALUE() and LAST_VALUE()
SELECT 
	Name, ListPrice, ProductSubcategoryID,
	FIRST_VALUE (Name) OVER (PARTITION BY ProductSubcategoryID ORDER BY ListPrice ASC) AS LeastExpensive,
	LAST_VALUE (Name) OVER (PARTITION BY ProductSubcategoryID ORDER BY ListPrice ASC) AS LeastExpensive
FROM Production.Product;

-- LAG()
SELECT 
	BusinessEntityID, YEAR(QuotaDate) AS SalesYear, SalesQuota AS CurrentQuota,
	LAG (SalesQuota, 1, 0) OVER (ORDER BY YEAR(QuotaDate)) AS PreviousQuota
FROM Sales.SalesPersonQuotaHistory
WHERE BusinessEntityID = 275 and YEAR(QuotaDate) IN ('2005','2006');

-- LEAD()
SELECT 
	TerritoryName, BusinessEntityID, SalesYTD,
	LEAD (SalesYTD, 1, 0) OVER (PARTITION BY TerritoryName ORDER BY SalesYTD DESC) AS PrevRepSales
FROM Sales.vSalesPerson
WHERE TerritoryName IN (N'Northwest', N'Canada')
ORDER BY TerritoryName;

Note: The LAG() and LEAD() functions are already in Oracle and by introducing them in MS SQL Server’s Denali version is very much welcomed by TSQL developers. Plus the addition of FIRST_VALUE(), LAST_VALUE(), PERCENTILE_XXX(), etc will really add value and provide zeal to programmers in TSQL development, just like Ranking functions and Cube/Rollup did in 2005(08).

Some other new functions introduced are:
– CUME_DIST()
– PERCENTILE_CONT()
– PERCENTILE_DISC()
– PERCENT_RANK()

More details here on MS BOL: http://technet.microsoft.com/en-us/library/hh213234%28SQL.110%29.aspx

Categories: SQL Server 2012 Tags: Denali, FIRST_VALUE, LAG, LAST_VALUE, LEAD, SQL Server 2012, TRY_CAST, TRY_CONVERT, TRY_PARSE

SQL Server 2012 (DENALI) TSQL – New Feature | SEQUENCES

July 26, 2011 2 comments

As per MS BOL a Sequence is a user-defined schema-bound object that generates a sequence of numeric values according to the specification with which the sequence was created. The sequence of numeric values is generated in an ascending or descending order at a defined interval and may cycle (repeat) as requested.

--// Create a simple Sequence
CREATE SEQUENCE CountBy1
	START WITH 1
	INCREMENT BY 1 ;
GO

-- Assign new ID sorted by First Name:
SELECT BusinessEntityID, FirstName, MiddleName, LastName,
	NEXT VALUE FOR CountBy1 OVER(ORDER BY FirstName) as New_ID
FROM Person.Person;
GO
-- If you run the above query again, it won't start from 1 but starts after the max value it ended in its first run.

--// Create a Cyclic Sequence
CREATE SEQUENCE CountBy5 AS tinyint
	START WITH 1
	INCREMENT BY 1
	MINVALUE 1
	MAXVALUE 5
	CYCLE ;
GO

-- Assign Persons to 5 different sections:
SELECT BusinessEntityID, FirstName, MiddleName, LastName,
	NEXT VALUE FOR CountBy5 OVER(ORDER BY FirstName, LastName) as New_Section
FROM Person.Person;
GO

--// Final cleanup
DROP SEQUENCE CountBy1
DROP SEQUENCE CountBy5
GO

More on MSDN: http://technet.microsoft.com/en-us/library/ff878058%28SQL.110%29.aspx

Categories: SQL Server 2012 Tags: CREATE SEQUENCE, Cyclic Sequence, Denali, NEXT VALUE FOR, OVER, SQL Server 2012

SQL Server 2012, codename Denali – New Features

November 24, 2010 2 comments

Microsoft released out the first Community Technology Preview (CTP) of the next generation of its SQL Server Enterprise Database product codenamed Denali.

I found very interesting information on MS BOL and want to brief it here as we developers are interested in new features, like:

A. New Server Installation:

1. SQL Server Multi-Subnet Clustering (One can now configure a SQL Server failover cluster using clustered nodes on different subnets, link: http://msdn.microsoft.com/en-us/library/ff878716(v=SQL.110).aspx)

2. Prerequisite Installation during SQL Server Setup (You must download the following and install them before you start SQL Server Setup, link: http://msdn.microsoft.com/en-us/library/ms143219(v=SQL.110).aspx)

2.1. Windows PowerShell

2.2. MS.NET Framework

2.3. No-Reboot package for .NET Framework 4.0

3. No support for SQL Server Itanium editions

B. New (Database Engine):

1. Availability Enhancements – “HADR” is a high-availability and disaster recovery solution introduced in SQL Server Code-Named “Denali” to enable you to maximize availability for one or more of your user databases. Deploying “HADR” involves configuring one or more availability groups. Each availability group establishes the context within which a selected set of user databases can fail over as a single unit. (http://msdn.microsoft.com/en-us/library/ff877884(v=SQL.110).aspx)

2. Manageability Enhancements – New Improved & enhanced tools & monitiring features.

2.1. SSMS, enhanced functionality for Transact-SQL debugging and IntelliSense (http://msdn.microsoft.com/en-us/library/cc645579(v=SQL.110).aspx)

2.2. Powershell, is no longer installed by SQL Server Setup, but is a pre-requisite for installing SQL Server Code-Named “Denali”.

2.3. Contained Databases, Moving databases from one instance of the SQL Server Database Engine to another instance of the Database Engine is simplified by introducing contained databases. Users in a contained database are no longer associated with logins on the instance of SQL Server. Many other dependencies on the instance are also removed. (http://msdn.microsoft.com/en-us/library/ff929071(v=SQL.110).aspx)

2.4. Startup Options, Database Engine startup options are now configured by using a new Startup Parameters tab of SQL Server Configuration Manager. (http://msdn.microsoft.com/en-us/library/ms345416(v=SQL.110).aspx)

3. Programmability Enhancements – includes property-scoped full-text search and customizable proximity search, ad-hoc query paging, circular arc segment support for spatial types, support for sequence objects, and numerous improvements and additions to Transact-SQL.

3.1. Full-Text Search, You can configure a full-text index to support property-scoped searching on properties, such as Author and Title, which are emitted by IFilters. (http://msdn.microsoft.com/en-us/library/ee677637(v=SQL.110).aspx)
You can customize a proximity search by using the new custom NEAR option of the CONTAINS predicate or CONTAINSTABLE function. Custom NEAR enables you to optionally specify the maximum number of non-search terms that separate the first and last search terms in a match. Custom NEAR also enables you to optionally specify that words and phrases are matched only if they occur in the order in which you specify them. (http://msdn.microsoft.com/en-us/library/ms142568(v=SQL.110).aspx)

3.2. Metadata Discovery, The SET FMTONLY option for determining the format of a response without actually running the query is replaced with sp_describe_first_result_set, sp_describe_undeclared_parameters, sys.dm_exec_describe_first_result_set, and sys.dm_exec_describe_first_result_set_for_object. (http://msdn.microsoft.com/en-us/library/ms173839(v=SQL.110).aspx)

3.3. EXECUTE Statement, can now specify the metadata returned from the statement by using the WITH RESULT SETS argument. (http://msdn.microsoft.com/en-us/library/ms188332(v=SQL.110).aspx)

3.4. UTF-16 Supplementary Characters (SC) Collations

3.5. Ad-hoc Query Paging Implementation, You can specify a range of rows returned by a SELECT statement based on row offset and row count values that you provide. This is useful when you want to control the number of rows sent to a client application for a given query. (http://msdn.microsoft.com/en-us/library/ms188385(v=SQL.110).aspx)

3.6. Circular Arc Segment Support for Spatial Types

3.7. Sequence Objects, is a user-defined schema-bound object that generates a sequence of numeric values according to the specification with which the sequence was created. It operates similar to an identity column, but sequence numbers are not restricted to use in a single table. (http://msdn.microsoft.com/en-us/library/ff878058(v=SQL.110).aspx)

4. Scalability and Performance Enhancements (http://msdn.microsoft.com/en-us/library/cc645580(v=SQL.110).aspx)

5. Security Enhancements, include new SEARCH PROPERTY LIST permissions, new user-defined server roles, and new ways of managing server and database roles.

5.1. New Permissions, New GRANT, REVOKE, and DENY permissions to a SEARCH PROPERTY LIST are available. New GRANT, REVOKE, and DENY permissions to CREATE SERVER ROLE and ALTER ANY SERVER ROLE.

5.2. New Role Management, User-defined server roles are now available. To manage user-defined server roles and to add and remove members from all server roles, use CREATE SERVER ROLE, ALTER SERVER ROLE, and DROP SERVER ROLE.

5.3. Hashing Algorithms, The HASHBYTES function now supports the SHA2_256, and SHA2_512 algorithms.

5.4. Database Engine Access through Contained Databases

6. ColumnStore Index, new technology for storing, retrieving and managing data by using a columnar data format, called a ColumnStore, [video].

C. New (Integration Services): (http://msdn.microsoft.com/en-us/library/bb522534(v=SQL.110).aspx)

1. Deployment and Administration Enhancements, Build your project, packages, and parameters to a project deployment file (.ispac extension) and deploy it directly to the SQL Server database engine. New dialog boxes in SQL Server Management Studio and a comprehensive set of Transact-SQL views and stored procedures help you manage, execute, and monitor your packages in real time.

2. Object Impact and Data Lineage Analysis

3. Usability Enhancements

4. Reduced Memory Usage by the Merge and Merge Join Transformations, Merge and Merge Join transformations are more robust and reliable now.

5. New Data Correction Component, that enables you to more easily and accurately improve the quality of data.

Categories: SQL Server 2012 Tags: Contained Databases, Denali, SQL Server 2012

Newer Entries