What's New in SQL Server 2012
- New SQL Server 2012 Features
- SQL Server 2012 Enhancements
- Deprecated Features
- Summary
This chapter is from the book
This chapter is from the book
This chapter is from the book
With SQL Server currently on a 2-year release cycle, each release is more of an evolutionary change from the previous release than a revolutionary change. Therefore, Microsoft SQL Server 2012 is not a quantum leap forward from SQL Server 2008 R2, but it does provide a number of interesting new features and enhancements that further extend the performance, reliability, availability, programmability, and ease of use of SQL Server. This chapter introduces the major new features provided in SQL Server 2012 and covers a number of the enhancements to previously available features.
New SQL Server 2012 Features
So what does SQL Server 2012 have to offer over SQL Server 2008 R2? Following is an overview of the new features provided in SQL Server 2012:
- New storage features—Columnstore indexes, FileTable storage.
- New Transact-SQL (T-SQL) constructs—Sequence objects, THROW statement, new conversion, logical, string, and date and time functions, and ad hoc query paging.
- New scalability and performance features—Indirect checkpoints, FORCESCAN table hint, number of table partitions increased to 15,000.
- New security features—Database Audit, user-defined server roles, contained databases.
- New availability features—A number of high-availability enhancements known as AlwaysOn, which include AlwaysOn Availability Groups and AlwaysOn Failover Cluster Instances.
- Statistical Semantic Search—Statistical Semantic Search builds upon the existing full-text search feature in SQL Server by querying the contextual meaning of terms within a document repository.
- Data Quality Services—This new feature allows you to build a knowledge base of data rules and use those to perform a variety of critical data quality tasks, including correction, enrichment, standardization, and de-duplication of your data.
The following sections take a closer look at each of these new features and, where appropriate, provide references to subsequent chapters where you can find more information and detail about the new features.
New and Improved Storage Features
SQL Server 2012 provides a few new features related to data storage, primarily intended to improve performance.
SQL Server 2012 provides an enhancement to FILESTREAM storage by allowing more than one filegroup to be used to store FILESTREAM data. This can improve I/O performance and scalability for FILESTREAM data by providing the ability to store the data on multiple drives. FILESTREAM storage, which was introduced in SQL Server 2008, integrates the SQL Server Database Engine with the NTFS file system, providing a means for storing unstructured data (such as documents, images, and videos) with the database storing a pointer to the data. Although the actual data resides outside the database in the NTFS file system, you can still use T-SQL statements to insert, update, query, and back up FILESTREAM data, while maintaining transactional consistency between the unstructured data and corresponding structured data with same level of security. For more information on using FILESTREAM storage, see Chapter 20, “Creating and Managing Tables.”
A new storage features introduced in SQL Server 2012 is FileTable storage. FileTable is the new type of table that was added in SQL Server 2012. The FileTable builds upon the FILESTREAM capability that was introduced in SQL Server 2008. Like FILESTREAM, FileTable storage provides SQL Server access to files that are stored in the Windows file system. FileTable differs from FILESTREAM in that FileTable allows for Windows applications to access the files that are part of the FileTable definition. This file access is nontransactional, and it comes without having to make changes to the Windows program that is accessing the files. For more information on FileTable storage, see Chapter 44, “What’s New for Transact-SQL in SQL Server 2012.”
SQL Server 2012 also introduces a new data warehouse query acceleration feature based on a new type of index called an xVelocity memory optimized columnstore. This new index, combined with enhanced query processing features, improves data warehouse query performance by hundreds to thousands of times in some cases and can routinely give a tenfold speedup for a broad range of decision support queries. Columnstore indexes can limit or eliminate the need to rely on prebuilt aggregates, including user-defined summary tables and indexed (materialized) views. Unlike typical row storage, which stores multiple rows of data per page, a columnstore index stores the values for a single column in its own set of disk pages. The advantage of columnar storage is the ability to read the values of a specific column of a table without having to read the values of all the other columns. For more information on the structure of columnstore indexes, see Chapter 30, “Data Structures, Indexes, and Performance.” For more information on creating and using columnstore indexes, see Chapter 57, “Parallel Data Warehouse Appliance.”
New Transact-SQL Constructs
What would a new SQL Server release be without new T-SQL commands and constructs to further expand the power and capabilities of the T-SQL language? SQL Server 2012 is no exception. The new constructs provided in SQL Server 2012 include the following:
- Sequence objects—Sequence objects can be used to generate a sequence of numeric values according to the definition of the object. Sequence objects provide an alternative for generating autosequencing values, similar to an Identity column but independent of any specific table. Sequence objects provide more flexibility and allow applications to apply a sequence value across multiple tables.
- THROW statement—The THROW statement can be used to raise an error condition and transfer execution to a CATCH block of a TRY...CATCH construct or to re-raise the error condition that invoked the CATCH block.
- New conversion functions—PARSE, TRY_PARSE, and TRY_CONVERT.
- New logical functions—CHOOSE and IIF.
- New string functions—CONCAT and FORMAT.
- New date and time functions—DATEFROMPARTS, DATETIME2FROMPARTS, DATETIMEFROMPARTS, DATETIMEOFFSETFROMPARTS, SMALLDATETIMEFROMPARTS, TIMEFROMPARTS.
- New ROWS and RANGE Clauses—Provides support for support for windows framing, which can be used to restrict the results to a subset of rows by specifying the start and end points within the partition of the OVER clause.
- New window offset functions—LAG and LEAD functions used in conjunction with OVER clause let you return a value from a specific row that’s a certain offset from the current row.
- New window rank distribution functions—PERCENT_RANK, PERCENTILE_CONT, PERCENTILE_DISC, CUME_DIST.
- Ad hoc query paging—Provides a mechanism to implement paging using the relatively simple syntax provided by the ANSI standard ORDER BY ... OFFSET / FETCH clause.
For more information and examples of the new T-SQL features and enhancements, see Chapter 44.
New Scalability and Performance Features
SQL Server provides some new scalability and performance features including indirect checkpoints and the FORCESCAN table hint.
Indirect checkpoints provide a mechanism for configuring checkpoint intervals at the individual database level. Indirect checkpoints can provide potentially faster, more predictable recovery times for your critical databases than automatic checkpoints. For more information on indirect checkpoints, see Chapter 27, “Transaction Management and the Transaction Log.”
The new FORCESCAN table hint complements the FORCESEEK hint by allowing you to specify that the query optimizer use an index scan operation as the access path to the table or view referenced in the query. For more information on the FORCESCAN table hint, see Chapter 31, “Understanding Query Optimization.”
New Security Features
SQL Server 2012 introduces Database Audit. Similar to SQL Server Audit, Database Audit is based on the new Extended Events feature and enables you to audit database-level events or groups of events. For more information on Database Audit, see Chapter 16, “Security and Compliance.”
SQL Server 2012 also adds user-defined server roles. These new server roles provide the same type of flexibility that is available with user-defined database roles, but they are instead defined at the server level and improve manageability of the permissions at the server level, giving administrators an option that goes well beyond the rigid fixed server roles. For more information on creating user-defined server roles, see Chapter 17, “Security and User Administration.”
Contained database support was added in SQL Server 2012 along with the related changes to support authentication to this type of database. Users can now be added to a database without having a corresponding SQL Server login. Authentication instead takes place at the database level. Contained databases can be easily moved from one server to another without the authentication issues that make noncontained database migrations more difficult. For more information on configuring contained databases, see Chapter 19, “Creating and Managing Databases.”
New Availability Features
One of the key new features in SQL Server 2012 is the AlwaysOn availability features. The new AlwaysOn features provide SQL Server administrators more power and flexibility in their efforts toward providing both high availability and disaster recovery. The AlwaysOn features consist of AlwaysOn Availability Groups and AlwaysOn Failover Cluster Instances.
AlwaysOn Availability Groups support a failover environment for a discrete set of user databases that fail over together. A single SQL Server instance can host multiple availability groups. In the event of a failure, each availability group can be configured to fail over to different SQL Server instances. For example, one availability group can fail over to instance 2, another availability group to instance 3, and so on. You no longer need to have a standby server that is capable of handling the full load of your primary server. An availability group consists of a set of one or more read-write primary databases and from one to four remote secondary copies. The remote secondary databases can be set up as read-only copies that you can run certain backup operations and reporting activity against, taking significant load off the primary server without the maintenance and overhead of creating snapshots of the secondary databases.
The other feature that is part of the AlwaysOn Availability offering is AlwaysOn Failover Cluster Instances. This feature is an enhancement to the existing SQL Server failover clustering, which is based on Windows Server Failover Cluster (WSFC). AlwaysOn Failover Clustering provides higher availability of SQL Server instance after failover. Some of the improvements of AlwaysOn Failover Clustering over the existing SQL Server failover clustering include the ability to set up multisite failover clustering for improved site protection, more-flexible failover policies, and improved diagnostics capabilities.
For more information on the AlwaysOn Availability features of SQL Server 2012, see Chapter 43, “SQL Server 2012 AlwaysOn Features.”
Statistical Semantic Search
Statistical Semantic Search builds upon the existing full-text search feature in SQL Server but enables new scenarios that extend beyond keyword searches. While full-text search lets you query the words in a document, Semantic Search lets you query the meaning of the document. Semantic Search attempts to improve document searches by understanding the contextual meaning of the terms and tries to provide the most accurate answer from a given document repository. If you use a web search engine like Google, you are already familiar with Semantic Search technology.
SQL Server Semantic Search builds its indexes using the indexes created by full-text search. With Semantic Search, you can go beyond just searching for specific words or strings in a document. Solutions are possible that include automatic tag extraction, related content discovery, and hierarchical navigation across similar content. For example, you can query the index of key phrases to build the taxonomy for an organization, or you can query the document similarity index to identify resumés that match a particular job description.
For more information on Statistical Semantic Search, see Chapter 51, “SQL Server Full-Text Search.”
Data Quality Services
Within Master Data Services (MDS), SQL Server 2012 adds Data Quality Services. Data Quality Services complements MDS and is usable by other key data manipulation components within the SQL Server environment. This feature enables you to build a knowledge base of data rules and use those to perform a variety of critical data quality tasks, including correction, enrichment, standardization, and de-duplication of your data. This also includes the ability to do some basic data profiling to better understand the integrity and overall data quality state of your core data.
Data Quality Services (DQS) consists of a Data Quality Server and a Data Quality Client. The DQS Server consists of three SQL Server catalogs with data-quality functionality and storage. The Data Quality Client is a SQL Server shared feature that business users, information workers, and IT professionals can use to perform computer-assisted data quality analyses and manage their data quality interactively. These DQS Cleansing components can also be used in Integration Services and MDS.
For more information on working with Data Quality Services, see Chapter 56, “Master Data Services and Data Quality Services.”