SQL Server 2012, codename Denali – New Features
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.a. Windows PowerShell
2.b. MS.NET Framework
2.c. 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.a. SSMS, enhanced functionality for Transact-SQL debugging and IntelliSense (http://msdn.microsoft.com/en-us/library/cc645579(v=SQL.110).aspx)
2.b. Powershell, is no longer installed by SQL Server Setup, but is a pre-requisite for installing SQL Server Code-Named “Denali”.
2.c. 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.d. 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.a. 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.b. 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.c. 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.d. UTF-16 Supplementary Characters (SC) Collations
3.e. 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.f. Circular Arc Segment Support for Spatial Types
3.g. 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.a. 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.b. 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.c. Hashing Algorithms, The HASHBYTES function now supports the SHA2_256, and SHA2_512 algorithms.
5.d. Database Engine Access through Contained Databases
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.
Note: I’ve copied and collated the entire content from MS-BOL link above, just for keeping lot of things at one place and in concise.
- DBA Stuff (14)
- Interview Q (6)
- MS BI (8)
- Others (30)
- SQL Server Internals (54)
- SQL Server Versions (58)
- T SQL (84)
- March 2015 (2)
- February 2015 (1)
- January 2015 (1)
- December 2014 (2)
- November 2014 (1)
- October 2014 (1)
- September 2014 (1)
- August 2014 (3)
- July 2014 (4)
- April 2014 (1)
- March 2014 (3)
- December 2013 (4)
- November 2013 (1)
- October 2013 (2)
- September 2013 (2)
- July 2013 (3)
- June 2013 (3)
- May 2013 (3)
- April 2013 (2)
- February 2013 (2)
- January 2013 (2)
- December 2012 (4)
- November 2012 (3)
- October 2012 (3)
- September 2012 (5)
- August 2012 (1)
- July 2012 (5)
- June 2012 (1)
- May 2012 (4)
- April 2012 (7)
- March 2012 (1)
- February 2012 (2)
- January 2012 (3)
- December 2011 (11)
- November 2011 (1)
- October 2011 (6)
- September 2011 (3)
- August 2011 (2)
- July 2011 (12)
- June 2011 (2)
- May 2011 (3)
- April 2011 (1)
- March 2011 (5)
- February 2011 (9)
- January 2011 (7)
- December 2010 (9)
- November 2010 (6)
- October 2010 (4)
- September 2010 (3)
- August 2010 (3)
- July 2010 (1)
- June 2010 (1)
- May 2010 (1)
- March 2010 (1)
- February 2010 (1)
- January 2010 (1)
- September 2009 (2)
- August 2009 (1)
- April 2009 (2)
- March 2009 (2)
- February 2009 (3)
- Passed 70-461 Exam : Querying Microsoft SQL Server 2012
- SQL Server 2012 does not support Linked Server to SQL Server 2000, workaround
- CUBE, ROLLUP, COMPUTE, COMPUTE BY, GROUPING SETS
- DIRTY reads and PHANTOM reads
- Best Practices while creating Stored Procedures
- Using OUTPUT Parameters in Stored Procedures
- UPDATE statement with new .WRITE Clause
- SELECT or Query nodes in hierarchial or nested XML
- Difference between TRUNCATE, DELETE and DROP?
- more examples on querying XML ...with CROSS APPLY & XQuery
- 899,864 hits
Previous Years (2011-14) visitors
StatCounter …since April’2012
This is my personal blog site.
The opinions expressed here represent my own and not those of my employer. For accuracy and official reference refer to MS Books On Line and/or MSDN/TechNet.
My employer do not endorse any tools, applications, books, or concepts mentioned on the blog. I have documented my personal experience on this blog.