Home > Articles > Data

What's New in SQL Server 2012

This chapter introduces the major new features provided in SQL Server 2012 and covers a number of the enhancements to previously available features.
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 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.”

InformIT Promotional Mailings & Special Offers

I would like to receive exclusive offers and hear about products from InformIT and its family of brands. I can unsubscribe at any time.


Pearson Education, Inc., 221 River Street, Hoboken, New Jersey 07030, (Pearson) presents this site to provide information about products and services that can be purchased through this site.

This privacy notice provides an overview of our commitment to privacy and describes how we collect, protect, use and share personal information collected through this site. Please note that other Pearson websites and online products and services have their own separate privacy policies.

Collection and Use of Information

To conduct business and deliver products and services, Pearson collects and uses personal information in several ways in connection with this site, including:

Questions and Inquiries

For inquiries and questions, we collect the inquiry or question, together with name, contact details (email address, phone number and mailing address) and any other additional information voluntarily submitted to us through a Contact Us form or an email. We use this information to address the inquiry and respond to the question.

Online Store

For orders and purchases placed through our online store on this site, we collect order details, name, institution name and address (if applicable), email address, phone number, shipping and billing addresses, credit/debit card information, shipping options and any instructions. We use this information to complete transactions, fulfill orders, communicate with individuals placing orders or visiting the online store, and for related purposes.


Pearson may offer opportunities to provide feedback or participate in surveys, including surveys evaluating Pearson products, services or sites. Participation is voluntary. Pearson collects information requested in the survey questions and uses the information to evaluate, support, maintain and improve products, services or sites, develop new products and services, conduct educational research and for other purposes specified in the survey.

Contests and Drawings

Occasionally, we may sponsor a contest or drawing. Participation is optional. Pearson collects name, contact information and other information specified on the entry form for the contest or drawing to conduct the contest or drawing. Pearson may collect additional personal information from the winners of a contest or drawing in order to award the prize and for tax reporting purposes, as required by law.


If you have elected to receive email newsletters or promotional mailings and special offers but want to unsubscribe, simply email information@informit.com.

Service Announcements

On rare occasions it is necessary to send out a strictly service related announcement. For instance, if our service is temporarily suspended for maintenance we might send users an email. Generally, users may not opt-out of these communications, though they can deactivate their account information. However, these communications are not promotional in nature.

Customer Service

We communicate with users on a regular basis to provide requested services and in regard to issues relating to their account we reply via email or phone in accordance with the users' wishes when a user submits their information through our Contact Us form.

Other Collection and Use of Information

Application and System Logs

Pearson automatically collects log data to help ensure the delivery, availability and security of this site. Log data may include technical information about how a user or visitor connected to this site, such as browser type, type of computer/device, operating system, internet service provider and IP address. We use this information for support purposes and to monitor the health of the site, identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents and appropriately scale computing resources.

Web Analytics

Pearson may use third party web trend analytical services, including Google Analytics, to collect visitor information, such as IP addresses, browser types, referring pages, pages visited and time spent on a particular site. While these analytical services collect and report information on an anonymous basis, they may use cookies to gather web trend information. The information gathered may enable Pearson (but not the third party web trend services) to link information with application and system log data. Pearson uses this information for system administration and to identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents, appropriately scale computing resources and otherwise support and deliver this site and its services.

Cookies and Related Technologies

This site uses cookies and similar technologies to personalize content, measure traffic patterns, control security, track use and access of information on this site, and provide interest-based messages and advertising. Users can manage and block the use of cookies through their browser. Disabling or blocking certain cookies may limit the functionality of this site.

Do Not Track

This site currently does not respond to Do Not Track signals.


Pearson uses appropriate physical, administrative and technical security measures to protect personal information from unauthorized access, use and disclosure.


This site is not directed to children under the age of 13.


Pearson may send or direct marketing communications to users, provided that

  • Pearson will not use personal information collected or processed as a K-12 school service provider for the purpose of directed or targeted advertising.
  • Such marketing is consistent with applicable law and Pearson's legal obligations.
  • Pearson will not knowingly direct or send marketing communications to an individual who has expressed a preference not to receive marketing.
  • Where required by applicable law, express or implied consent to marketing exists and has not been withdrawn.

Pearson may provide personal information to a third party service provider on a restricted basis to provide marketing solely on behalf of Pearson or an affiliate or customer for whom Pearson is a service provider. Marketing preferences may be changed at any time.

Correcting/Updating Personal Information

If a user's personally identifiable information changes (such as your postal address or email address), we provide a way to correct or update that user's personal data provided to us. This can be done on the Account page. If a user no longer desires our service and desires to delete his or her account, please contact us at customer-service@informit.com and we will process the deletion of a user's account.


Users can always make an informed choice as to whether they should proceed with certain services offered by InformIT. If you choose to remove yourself from our mailing list(s) simply visit the following page and uncheck any communication you no longer want to receive: www.informit.com/u.aspx.

Sale of Personal Information

Pearson does not rent or sell personal information in exchange for any payment of money.

While Pearson does not sell personal information, as defined in Nevada law, Nevada residents may email a request for no sale of their personal information to NevadaDesignatedRequest@pearson.com.

Supplemental Privacy Statement for California Residents

California residents should read our Supplemental privacy statement for California residents in conjunction with this Privacy Notice. The Supplemental privacy statement for California residents explains Pearson's commitment to comply with California law and applies to personal information of California residents collected in connection with this site and the Services.

Sharing and Disclosure

Pearson may disclose personal information, as follows:

  • As required by law.
  • With the consent of the individual (or their parent, if the individual is a minor)
  • In response to a subpoena, court order or legal process, to the extent permitted or required by law
  • To protect the security and safety of individuals, data, assets and systems, consistent with applicable law
  • In connection the sale, joint venture or other transfer of some or all of its company or assets, subject to the provisions of this Privacy Notice
  • To investigate or address actual or suspected fraud or other illegal activities
  • To exercise its legal rights, including enforcement of the Terms of Use for this site or another contract
  • To affiliated Pearson companies and other companies and organizations who perform work for Pearson and are obligated to protect the privacy of personal information consistent with this Privacy Notice
  • To a school, organization, company or government agency, where Pearson collects or processes the personal information in a school setting or on behalf of such organization, company or government agency.


This web site contains links to other sites. Please be aware that we are not responsible for the privacy practices of such other sites. We encourage our users to be aware when they leave our site and to read the privacy statements of each and every web site that collects Personal Information. This privacy statement applies solely to information collected by this web site.

Requests and Contact

Please contact us about this Privacy Notice or if you have any requests or questions relating to the privacy of your personal information.

Changes to this Privacy Notice

We may revise this Privacy Notice through an updated posting. We will identify the effective date of the revision in the posting. Often, updates are made to provide greater clarity or to comply with changes in regulatory requirements. If the updates involve material changes to the collection, protection, use or disclosure of Personal Information, Pearson will provide notice of the change through a conspicuous notice on this site or other appropriate way. Continued use of the site after the effective date of a posted revision evidences acceptance. Please contact us if you have questions or concerns about the Privacy Notice or any objection to any revisions.

Last Update: November 17, 2020