SQL with Manoj

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.