SQL Server 2012

O F F I C I A L

M I C R O S O F T

40007A

L E A R N I N G

P R O D U C T

First Look Clinic: Microsoft® SQL Server® 2012

ii First Look Clinic: Microsoft® SQL Server® 2012

Information in this document, including URL and other Internet Web site references, is subject to change without notice. Unless otherwise noted, the example companies, organizations, products, domain names, e-mail addresses, logos, people, places, and events depicted herein are fictitious, and no association with any real company, organization, product, domain name, e-mail address, logo, person, place or event is intended or should be inferred. Complying with all applicable copyright laws is the responsibility of the user. Without limiting the rights under copyright, no part of this document may be reproduced, stored in or introduced into a retrieval system, or transmitted in any form or by any means (electronic, mechanical, photocopying, recording, or otherwise), or for any purpose, without the express written permission of Microsoft Corporation. Microsoft may have patents, patent applications, trademarks, copyrights, or other intellectual property rights covering subject matter in this document. Except as expressly provided in any written license agreement from Microsoft, the furnishing of this document does not give you any license to these patents, trademarks, copyrights, or other intellectual property. The names of manufacturers, products, or URLs are provided for informational purposes only and Microsoft makes no representations and warranties, either expressed, implied, or statutory, regarding these manufacturers or the use of the products with any Microsoft technologies. The inclusion of a manufacturer or product does not imply endorsement of Microsoft of the manufacturer or product. Links may be provided to third party sites. Such sites are not under the control of Microsoft and Microsoft is not responsible for the contents of any linked site or any link contained in a linked site, or any changes or updates to such sites. Microsoft is not responsible for webcasting or any other form of transmission received from any linked site. Microsoft is providing these links to you only as a convenience, and the inclusion of any link does not imply endorsement of Microsoft of the site or the products contained therein. © 2012 Microsoft Corporation. All rights reserved. Microsoft and the trademarks listed at http://www.microsoft.com/about/legal/en/us/IntellectualProperty/Trademarks/ENUS.aspx are trademarks of the Microsoft group of companies. All other trademarks are property of their respective owners

Product Number: 40007A Released: 07/2012

Microsoft® SQL Server® 2012: First Look Clinic

iii

iv First Look Clinic: Microsoft® SQL Server® 2012

Acknowledgments Microsoft Learning would like to acknowledge and thank the following for their contribution towards developing this title. Their effort at various stages in the development has ensured that you have a good classroom experience.

Graeme Malcolm – Author Graeme Malcolm is a Microsoft SQL Server subject matter expert and professional content developer at Content Master—a division of CM Group Ltd. As a Microsoft Certified Trainer, Graeme has delivered training courses on SQL Server since version 4.2; as an author, Graeme has written numerous books, articles, and training courses on SQL Server; and as a consultant, Graeme has designed and implemented business solutions based on SQL Server for customers all over the world.

Microsoft® SQL Server® 2012: First Look Clinic

Contents Module 1: Introduction to SQL Server 2012 Lesson 1: Enterprise Data Scenarios and Trends Lesson 2: SQL Server 2012 Overview

1-3 1-11

Module 2: SQL Server 2012 as a Platform for Mission-Critical Data Lesson 1: Database Development Enhancements

2-3

Lesson 2: Database Manageability Enhancements

2-21

Lesson 3: Database Availability Enhancements

2-29

Module 3: Enterprise Integration Management and Data Warehousing Lesson 1: SQL Server 2012 Data Quality Services

3-3

Lesson 2: SQL Server 2012 Master Data Services

3-16

Lesson 3: SQL Server 2012 Integration Services

3-31

Lesson 4: SQL Server 2012 for Data Warehousing

3-41

Module 4: Business Intelligence Lesson 1: SQL Server 2012 Reporting Services

4-3

Lesson 2: PowerPivot and Analysis Services

4-10

Lesson 3: Power View

4-21

Module 5: Cloud-Based Data Solutions Lesson 1: Microsoft Cloud Solutions for Data

5-3

Lesson 2: SQL Server 2012 and the Cloud

5-8

v

1-1

Module 1 Introduction to SQL Server 2012 Contents: Module Overview

1-2

Lesson 1: Enterprise Data Scenarios and Trends

1-3

Lesson 2: SQL Server 2012 Overview

1-11

Module Review

1-19

1-2 Introduction to SQL Server 2012

Module Overview

Data is at the heart of practically all software applications and solutions; and consistent and credible data increasingly provides the basis for business decision making at all levels of an organization. Microsoft® SQL Server® 2012 is the latest release of an enterprise data platform from Microsoft that is designed to meet the challenges facing many businesses and respond to common trends in data management. In this module, you will learn about the key data management workloads and trends that SQL Server 2012 addresses, and examine the different editions and components that form the SQL Server 2012 platform. After completing this module, you will be able to: 

Describe enterprise data scenarios and trends.



Describe the key components of SQL Server 2012.

First Look Clinic: Microsoft® SQL Server® 2012 1-3

Lesson 1

Enterprise Data Scenarios and Trends

The business landscape is always changing, and business software solutions, such as database platforms, must evolve to meet the changing demands. Since the release of SQL Server 2008 R2, several trends in the way that businesses use technology, and in particular data management solutions, have emerged. SQL Server 2012 is designed to help organizations take advantage of new opportunities for data-based business decision making and management, and become more successful. In this lesson, you will learn about the core data workloads that SQL Server 2012 supports, as well as the key data management trends that it enables organizations to take advantage of. After completing this lesson, you will be able to: 

Describe common data workloads.



Describe common requirements for mission-critical data.



Describe common requirements for self-service business intelligence (BI).



Describe common requirements for “big data”.



Describe common requirements for private and public cloud.



Describe common requirements for database appliances.


Common Data Workloads

Most organizations use database systems to manage business data. While the specific details of how each organization uses its data can vary enormously, there are four common data workloads that the data systems used by modern enterprises must support.

Online Transaction Processing Online transaction processing (OLTP) systems typically use a relational database to store details of business transactions and operations, such as sales orders or inventory management records. The data platform used for this type of workload must support both read and write operations for the data, with potentially huge numbers of transactions 24 hours a day, 7 days a week (24x7).

Enterprise Integration Management Many organizations use multiple OLTP databases, and potentially need to integrate internal data systems with data sources from outside the enterprise. To ensure the integrity of the data stored across diverse sources, and to help business users access and collate information from multiple systems, the data platform must support an enterprise integration management (EIM) workload. EIM includes the following key activities: 

Integration of data from multiple data sources through extract, transform, and load (ETL) capabilities.



Management of data quality that enables business data specialists to cleanse data and eliminate invalid or inconsistent data caused by data entry errors.



Master data management (MDM) that ensures consistency of key business entity definitions across multiple systems.

Data Warehousing When an organization has operational data in OLTP systems and a suitable EIM solution, it can bring data together into a data warehousing (DW) solution that enables business users to generate reports and perform analysis of historical data as a basis for decision making. Data warehouses are typically databases that are optimized to store large volumes of historical data in a structure that supports fast performance for read operations.


Business Intelligence Data warehouses typically form the basis for business intelligence (BI) solutions that enable executives, business analysts, and information workers to create and view reports, monitor key performance indicators (KPIs), and perform analysis of the data through online analytical processing (OLAP) cubes or other data models. Analytical data models typically provide a way to aggregate and summarize business measures by multiple aspects, or “dimensions”, of the business.


Key Trend – Mission-Critical Data

One of the common trends in the way that organizations use data systems is the increasing reliance on access to data at any time, and from anywhere. To support the typical data access requirements of today’s organizations, a data platform must support extremely high levels of availability and fast, effective disaster recovery.

High Availability Increasingly, business users need access to applications and their data on a 24x7 basis. Users not only access applications from PCs in the office, but also on a variety of devices during evenings, weekends, and other periods outside of traditional business hours. Additionally, in many organizations, application users are located in multiple time zones around the world. This expansion of application usage has led to increased requirements for high availability solutions that minimize or eliminate down-time, whether planned or unplanned.

Data Recoverability Such is the reliance on business information, that even in the event of a disaster most organizations can tolerate little or no data loss. A data platform for today’s businesses must support a disaster recovery solution that minimizes the time taken to restore data availability, while ensuring that data is recovered right up to the point of failure.


Key Trend – Self-Service Business Intelligence

Another key trend that has been identified in modern enterprises is the increasingly self-service nature of business intelligence and reporting solutions.

Empowering Information Workers Most business users today are significantly more familiar with technology than previous generations, and are comfortable using applications to find the information they need to do their jobs successfully. Selfservice BI technologies are designed to empower these users to explore data for themselves and create reports that they can use as the basis of business decisions or share with colleagues. This immediacy of access reduces the time it takes for business users to make effective decisions and enables dynamic and flexible business processes.

Reducing IT Workload By empowering business users to perform much of their own data analysis and reporting, requirements for IT specialists to create reports and complex analytical queries are reduced. This enables IT employees to focus on high-value activities such as maintaining the data infrastructure and servicing high-priority data requirements.


Key Trend – Big Data

One of the growing concerns shared by data specialists in many organizations is the phenomenon that has come to be known as “big data”. The key elements of this trend are the growing volumes of data used in most organizations, the proliferation of data sources, and the diversity of the data formats being used.

Large Volumes of Data As storage capacity and processing capabilities of modern hardware continue to expand, organizations and individuals are generating and storing increasing volumes of data. While in the past, users needed to access and process megabytes or gigabytes of data, some data scenarios now involve petabytes or even exabytes of data.

Many Data Sources As well as the sheer volume of data used in large organizations, data specialists need to consider the range of data sources in which that data is generated and stored. Most organizations now rely on multiple databases, file stores, and other data repositories; and may also consume data from external and cloud-based sources.

Diverse Data Formats Data exists in multiple formats, from relational database tables and text files in mainframe applications to rich documents, multimedia formats such as images and video, and real-time event streams emitted by sensors or social networking feeds. All of this data provides meaningful information that can help business users make effective decisions; so a modern data platform must be able to integrate and manage a wide variety of data formats.


Key Trend – Cloud Technologies

Cloud-based approaches to data infrastructure bring many benefits, including reduced infrastructure management, on-demand scalability, and better control of financial costs.

Public Cloud When most IT specialists think of the cloud, they think of Internet-based infrastructure and application services that they can purchase from external providers. For example, instead of purchasing, provisioning, and managing an on-premise database server, a company might purchase cloud-based data storage services that they can use to store data on a managed, Internet-accessible database. This approach eliminates the need for the company’s IT employees to install and manage database server hardware, reduces operational overheads for managing availability and data recoverability, and enables the company to pay only for the data storage that it actually uses.

Private Cloud While the benefits of using public cloud infrastructure are appreciated by many organizations, some companies need to maintain control of their data systems for operational, compliance, or security reasons. These organizations can still achieve many of the benefits of public cloud technologies by implementing a private cloud. A private cloud uses many of the same technologies used in the public cloud, such as server virtualization and self-service provisioning and management portals; but deploys servers and application services on company-dedicated hardware in on-premise data centers or private hosted environments. A private cloud can reduce the time it takes to provision a new database or application server while ensuring that hardware resources are used cost-effectively and managed centrally.

Hybrid Solutions Many organizations see the move to the cloud as an incremental process, or simply want to employ a “best of both worlds” approach to data management. In these scenarios, a hybrid approach can be taken in which some data is stored and managed in a public cloud data service, while other data is managed on-premise. A data synchronization solution is then used to enable applications to consume data from both public cloud data sources and private database systems.



Key Trend – Appliances

Provisioning server hardware and software for specific data workloads can be difficult, time-consuming, and expensive. Increasingly, IT customers are looking for vendors who can provide a single-purchase hardware appliance that can simply be plugged in, switched on, and used. Appliances provide the following benefits: 

Pre-configured hardware and software. An appliance usually consists of all required hardware components with software pre-installed. After some initial simple configuration, the appliance can be used – reducing the time taken to provision, deploy, and use a new server solution from weeks or months to days or even hours.



Optimization for specific workloads. The optimal hardware and software configuration for an OLTP database is generally significantly different from that of a data warehouse or BI server. Most database appliances are optimized for specific workloads based on experience gained from thousands of deployments by specialist consultants, reducing the need for specialist knowledge and configuration by IT administrators.



Single point of purchase and support. Organizations that purchase hardware and software from multiple vendors have great flexibility of choice, but often find it difficult to manage budgets effectively when dealing with multiple, separate purchases. Additionally, when a problem occurs, these organizations may find themselves having to deal with multiple support contracts for different aspects of the overall system, increasing the time it takes to resolve critical issues. Appliances are generally purchased as a package from a single vendor, and usually include a single point of support.


Lesson 2

SQL Server 2012 Overview

Now that you have learned about some of the key data management trends and challenges that provide the context for this release of SQL Server, it is time to explore some of the details of SQL Server 2012 and learn how it addresses those challenges. After completing this lesson, you will be able to: 

Describe SQL Server 2012 editions.



Describe SQL Server 2012 components.



Describe how SQL Server 2012 integrates with other Microsoft technologies.



Describe key options for SQL Server 2012 licensing.


SQL Server 2012 Editions

SQL Server is available in a wide variety of editions, with different price points and different levels of capability.

SQL Server Editions Each SQL Server edition is targeted to a specific business use case as shown in the following table: Edition

Business Use Case

Parallel Data Warehouse

Uses massively parallel processing (MPP) to execute queries against vast amount of data quickly. Parallel Data Warehouse systems are sold as a complete "appliance" rather than via standard software licenses.

Enterprise

Provides the highest levels of reliability for demanding workloads.

Business Intelligence

Adds Business Intelligence to the offerings from Standard Edition.

Standard

Delivers a reliable, complete data management platform.

Express

A free edition for lightweight web and small server-based applications.

Compact

A free edition for standalone and occasionally connected mobile applications, optimized for a very small memory footprint

Developer

An edition will of the features of Enterprise edition and licensed to allow developers to build, test, and demonstrate SQL Server applications.

Web

A secure, cost effective, and scalable platform for public web sites and applications

SQL Azure

A cloud-based database platform that is compatible with SQL Server.


Reference Links: For more information about SQL Server 2012 editions, visit www.microsoft.com/sqlserver/en/us/sql-2012-editions.aspx.


SQL Server 2012 Components

SQL Server is a very good relational database engine but as a data platform, it offers much more. SQL Server is a platform comprising many components. Component

Purpose

Database Engine

A relational database engine based on the SQL language

Analysis Services

An online analytical processing (OLAP) engine that works with analytic cubes

Integration Services

A tool used to orchestrate the movement of data between SQL Server components and external systems (in both directions)

Reporting Services

Offers a reporting engine based on web services and provides a web portal and end-user reporting tools

Master Data Services

Provides tooling and a hub for managing master or reference data

StreamInsight

A platform for building applications to process high-speed events

Data Mining

Provides tooling and an inference engine for deriving knowledge and insights from existing OLAP data or relational data

Full-Text Search

Enables the inclusion of sophisticated search options into applications. SQL Server 2012 adds sophisticated sematic search to the existing full-text search.

PowerPivot

Enables end-users, power users, and business analysts to quickly analyze large volumes of data from different locations

Replication

Enables synchronization of data between servers to suit data


Component

Purpose distribution needs

Data Quality Services

Enables data cleansing and matching to ensure optimal data quality

Power View

Enables interactive visualization of data by end-users


SQL Server 2012 and Other Microsoft Technologies

SQL Server provides a core platform for data management and BI. However, to fully benefit from SQL Server, you must understand how it integrates with other Microsoft technologies.

Microsoft Windows Server SQL Server is designed as an enterprise data platform, and while some editions can be installed on Windows client operating systems, enterprise database servers are generally installed on Windows Server. Windows server provides a robust, scalable operating system that provides services that SQL Server can leverage, including: 

Directory-based security and user authentication.



Network protocols and name resolution services.



Failover clustering services for high-availability.



File system services.



Memory and processor thread management.



Logging.

Microsoft SharePoint Server SharePoint Server provides a web-based collaboration environment through which users can publish and share documents and information. SQL Server can be integrated with SharePoint server in the following ways: 

SQL Server PowerPivot for SharePoint can be installed to enable users to share and edit PowerPivot workbooks in a document library within a SharePoint site.



SQL Server Reporting Services can be installed in SharePoint-Integrated mode, enabling reports to be published, viewed, and managed in a SharePoint document library.



SQL Server Reporting Services includes Power View, which is only available in SharePoint-Integrated mode and which enables users to create dynamic, interactive data visualizations from PowerPivot workbooks and tabular data models in SQL Server Analysis Services.




SQL Server Analysis Services KPIs can be visualized in SharePoint web parts.

Microsoft System Center Microsoft System Center is not a single product, but a suite of tools for managing server infrastructure. You can use System Center technologies to implement a private cloud solution in which database servers are provisioned as virtual servers based on Microsoft Hyper-V virtual machine templates.

Microsoft Office Microsoft Office is a widely used productivity suite for information workers, and provides an intuitive interface for consuming and managing data in a SQL Server database or data model. You can use Microsoft Office with SQL Server in the following ways: 

Create a PowerPivot workbook that uses SQL Server Analysis Services technologies for highperformance data analysis in Excel.



Consume KPIs and dimensional structures from Analysis Services OLAP and tabular data models in an Excel workbook.



Create and use SQL Server Analysis Services data mining models for predictive analysis in an Excel workbook.



Manage master data models and work with SQL Server Master Data Services models in Excel.



Use Microsoft Excel as a source for SQL Server Data Quality Services cleansing or matching operations, and export the results of these operations as Excel workbooks.



Render SQL Server Reporting Services reports in Microsoft Excel or Word format.



Save Power View data visualizations as interactive Microsoft PowerPoint presentations.

The .NET Framework The Microsoft .NET Framework is a software runtime and class library for developing applications. Developers can use classes provided by the .NET framework to access data in SQL Server database. Commonly used data access technologies in the .NET framework include: 

ADO.NET



LINQ



The .NET Entity Framework

Windows Azure Windows Azure is a cloud platform that developers can use to create cloud-based services and applications. The Windows Azure platform includes database and reporting services that are based on SQL Server technologies, and can be used to provide data services for cloud applications.


SQL Server 2012 Licensing

SQL Server 2012 offers a range of licensing options for the three main editions.

Core-Based Licensing You can license SQL Server Enterprise and Standard editions based on computing power by purchasing a license per CPU core. For example, a server with two dual-core processors would require four licenses.

Server + Client Access License (CAL) You can license Business Intelligence and Standard editions by purchasing a server license and a client access license (CAL) for each user who will access the server.

Virtual Machine Licensing When deploying SQL Server Enterprise or Standard edition on virtual machines, you can use core-based licensing and purchase a license for each virtual core allocated to the virtual machine. If you are deploying Business Intelligence edition on a virtual machine, you must use the server + CAL model as described previously. Alternatively, if you purchase an Enterprise edition license for each core on the physical host server, you can deploy an unlimited number of virtual machines running SQL Server without purchasing any additional licenses. Reference Links: For more information about SQL Server licensing, visit www.microsoft.com/sqlserver/en/us/get-sql-server/licensing.aspx


Module Review

Module Summary In this module, you have learned about some of the key industry trends and challenges that SQL Server 2012 is designed to address. Additional Reading: To learn more about SQL Server 2012, visit the SQL Server web site at www.microsoft.com/sqlserver.

2-1

Module 2 SQL Server 2012 as a Platform for Mission-Critical Data Contents: Module Overview

2-2

Lesson 1: Database Development Enhancements

2-3

Lesson 2: Database Manageability Enhancements

2-21

Lesson 3: Database Availability Enhancements

2-29

Module Review

2-40

2-2 SQL Server 2012 as a Platform for Mission-Critical data

Module Overview

SQL Server is one of the most commonly used database platforms for enterprise applications. SQL Server 2012 builds on the capabilities of previous releases to provide a world-class data management platform that can be used for mission-critical enterprise data in organizations of any size. In this module, you will learn about the enhancements on SQL Server 2012 that make it easier than ever to develop and manage database applications, and to ensure high availability for your mission-critical data. After completing this module, you will be able to: 

Describe database development enhancements in SQL Server 2012.



Describe database manageability enhancements in SQL Server 2012.



Describe database availability enhancements in SQL Server 2012.


Lesson 1

Database Development Enhancements

SQL Server 2012 makes it easier to develop database solutions through enhancements to the TransactSQL language, new functions, enhancements to spatial data support, and new capabilities for working with documents and text. In this lesson, you will learn how to use these enhancements to store and query data in a SQL Server database. After completing this lesson, you will be able to: 

Describe Transact-SQL enhancements in SQL Server 2012.



Describe new Transact-SQL functions in SQL Server 2012.



Describe spatial data enhancements in SQL Server 2012.



Describe how to work with documents and text in SQL Server 2012.


Transact-SQL Enhancements

Transact-SQL is the query language used to define and manipulate data in a SQL Server database. The language is based on ANSI standards for Structured Query Language (SQL) syntax and includes several extensions that make it easier to work with SQL Server-specific features. SQL Server 2012 introduces some enhancements to the Transact-SQL language that you can use when querying data.

The WITH RESULT SETS Clause When a stored procedure returns rows of data, the names and data types of the columns in the results are determined by the query defined in the stored procedure. By using the WITH RESULTS clause of the EXECUTE statement, you can override the schema of the results to change column names and data types. This example shows how to use the WITH RESULT SETS clause for a stored procedure that returns a single rowset. Using the WITH RESULT SETS clause EXECUTE GetOrderPickList ‘SO59384’ WITH RESULT SETS ( ([SalesOrder] nvarchar(20) NOT NULL, [LineItem] int, [Product] int, [Quantity] int) )

When a stored procedure returns multiple rowsets, you can specify a schema for each result set in the WITH RESULT SETS clause, separated by a comma.

The THROW Statement You can use the TRY and CATCH keywords in Transact-SQL code to catch exceptions that occur during execution, such as the violation of a primary key constraint. SQL Server 2012 introduces the THROW statement, which you can use to throw an exception that has been caught in a CATCH block but which cannot be handled gracefully (in order to log the error), or to throw custom exceptions of your own that consist of an error number, an error message, and a tiny integer state value (between 0 and 255).


This code sample shows how to use the THROW keyword to throw a custom exception. Using the THROW statement CREATE PROCEDURE GetCustomerDetails @CustomerKey int AS SELECT * FROM Customers WHERE CustomerKey = @CustomerKey IF @@ROWCOUNT = 0 THROW 50001, ‘Customer does not exist’, 1

Paging with the OFFSET and FETCH Keywords A common task in a data-bound application is to display data in a “page-able” user interface – often some sort of data grid. For example, you might want to create a Web application that shows a list of available products, but which limits the list to ten products at a time. The user then “pages” through the data by clicking “Next Page” or something similar. There are many user interface design and implementation patterns that enable this kind of functionality, but they usually either involve fetching all of the data from the database and caching it locally in the application, or implementing some sort of “current page” tracking functionality to adjust the query used to retrieve the data on each page. SQL Server 2012 introduces a new way to manage paging within the SELECT statement itself by using the new OFFSET and FETCH keywords in the ORDER BY clause to limit the query results to a specific page of data. The OFFSET keyword is used to indicate the “starting row” of the results (the number of rows to skip before this page), and the FETCH keyword is used to indicate the number of pages to be returned (the page size). The following code sample skips 20 rows in the underlying dataset and then returns the next 10 rows Using the OFFSET and FETCH keywords to return a specific page of data SELECT so.SalesOrderNumber, so.OrderDate, c.FirstName + ' ' + c.LastName CustomerName FROM SalesOrderHeader so JOIN Customers c ON so.CustomerKey = c.CustomerKey ORDER BY SalesOrderNumber ASC OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY

Sequence Objects You can use IDENTITY columns to assign an incrementing numeric value as an identifier in a single table. However, in some cases you might want to use the same sequence of numbers across multiple tables, or generate a key value before actually performing the insertion. SQL Server 2012 introduces sequence objects to help you generate sequential numbers independently of column definitions. To use a sequence object, you must first create a sequence (specifying start and increment values), and then use the NEXT VALUE FOR statement to use the next available number in the sequence. The following code example creates a sequence that starts at 1000 and increments by 10, and then uses the next available number in the sequence as a default value for new order numbers.


Using a sequence object CREATE SEQUENCE OrderNumbers START WITH 1000 INCREMENT BY 10 GO CREATE TABLE Orders ( OrderNumber int PRIMARY KEY DEFAULT(NEXT VALUE FOR OrderNumbers), CustomerKey int, ProductKey int, Quantity int ) GO

The OVER Clause SQL Server 2012 provides windows as a method for applying functions to sets of rows. There are many applications of this technique which solve common problems in writing Transact-SQL queries. For example, using windows allows the easy generation of row numbers in a result set, and the calculation of running totals. In order to use windows and window functions in Transact-SQL, you will always use the OVER subclause, which creates and manipulates windows. Additionally, you may need to create partitions with the PARTITION BY option, and even further restrict which rows are applied to a function with framing options. This example shows how to use the OVER subclause to create running total of order quantities for city by year. Using the ORDER subclause to create a running total over a window SELECT City, OrderYear, OrderQuantity, SUM(OrderQuantity) OVER (PARTITION BY City ORDER BY OrderYear ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS RunningQty FROM CitySalesByYear


Demonstration: Using Transact-SQL Enhancements

In this demonstration, you will see how to use Transact-SQL enhancements in SQL Server 2012.  Use the WITH RESULT SETS Clause 1.

Start the MIA-DC1 and MIA-SQLDB virtual machines, and log into the MIA-SQLDB virtual machine as ADVENTUREWORKS\Student with the password Pa$$w0rd.

2.

In the D:\Demofiles folder, right-click Setup.cmd and click Run as administrator. When prompted, click Yes.

3.

Start SQL Server Management Studio, and use Windows authentication to connect to the localhost instance of the database engine.

4.

In SQL Server Management Studio, open the Exec Result Sets.sql script file in the D:\Demofiles\Mod02 folder.

5.

Select and execute the code under the comment Create a stored procedure. This creates a stored procedure that returns some data.

6.

Select and execute the code under the comment Execute with default results. This executes the stored procedure and returns a resultset with default column names and data types.

7.

Select and execute the code under the comment Execute with RESULT SETS. This executes the stored procedure and returns a resultset with custom column names and data types.

 Use the THROW Statement 1.

Open the Throw.sql script file in the D:\Demofiles\Mod02 folder.

2.

Select and execute the code under the comment Create a stored procedure that uses THROW. This creates a stored procedure that throws an exception if a specified customer is not found.

3.

Select and execute the code under the comment Find an existing customer. This executes the stored procedure and returns a resultset containing details for the specified customer.

4.

Select and execute the code under the comment Try to find a non-existent customer. This executes the stored procedure and throws an exception when the customer is not found.


 Implement Paging 1.

Open the Paging.sql script file in the D:\Demofiles\Mod02 folder.

2.

Select and execute the code under the comment Create a stored procedure to retrieve a page of data. This creates a stored procedure that retrieves a page of data based on the specified page size and offset.

3.

Select and execute the code under the comment Use the stored procedure to page through the data. This executes the stored procedure four times, returning the first, second, third, and second (again) pages of data based on a page size of 10 rows.

 Use a Sequence Object 1.

Open the Sequence Objects.sql script file in the D:\Demofiles\Mod02 folder.

2.

Select and execute the code under the comment Create a sequence. This creates a sequence named OrderNumbers that starts at 1000 and increments by 10.

3.

Select and execute the code under the comment Create a table that uses the sequence. This creates a table named Orders in which the OrderNumber field has a default value that specifies the next available number from the OrderNumbers sequence.

4.

Select and execute the code under the comment Use the sequence. This inserts data into the Orders table.

5.

Select and execute the code under the comment View the assigned sequence numbers. This retrieves data from the Orders table, showing that the sequence numbers have been assigned.

 Use the OVER Subclause 1.

Open the Over Window.sql script file in the D:\Demofiles\Mod02 folder.

2.

Select and execute the code under the comment Create a view that sums sales per city per year. This creates a view named CitySalesByYear that contains the sum of order quantities for each year in each city.

3.

Select and execute the code under the comment View the data in the view. This shows the contents of the CitySalesByYear view.

4.

Select and execute the code under the comment Use OVER to show a running total by year. This uses the OVER subclause to create a window for each city with the running total of order quantity for each year.

5.

Keep SQL Server Management Studio open for the next demonstration.


New Functions

In addition to enhancements to the Transact-SQL language, SQL Server 2012 introduces some new Transact-SQL functions that make it easier to query data.

Conversion Functions SQL Server 2012 introduces the following conversion functions: 

PARSE – Converts a string expression to a specified data type, optionally using a specified culture to parse a formatted string.



TRY_PARSE – Provides the same functionality as PARSE, but returns NULL if the string expression is not valid.



TRY_CONVERT – Casts a value to a specified data type, returning NULL if the cast fails.

Data and Time Functions SQL Server 2012 introduces the following date and time functions: 

DATEFROMPARTS – Creates a DATE value from specified year, month, and day values.



DATETIMEFROMPARTS – Creates a DATETIME value from specified year, month, day, hour, minute, second, and millisecond values.



SMALLDATETIMEFROMPARTS – Creates a SMALLDATETIME value from specified year, month, day, hour, and minute.



DATETIME2FROMPARTS – Creates a DATETIME2 value from specified year, month, day, hour, minute, second, and fractional values at a specified precision.



TIMEFROMPARTS – Creates a TIME value from specified hour, minute, second, and fractional values at a specified precision.



DATETIMEOFFSETFROMPARTS - Creates a DATETIMEOFFSET value from specified year, month, day, hour, minute, second, and fractional values for a given time zone represented by hour and minute offset values at a specified precision.




EOMONTH - Returns the last day of the month that contains the specified date, with an optional offset.

Logical Functions SQL Server 2012 introduces the following logical functions: 

CHOOSE – Selects the item in the specified ordinal position in a list of possible values.



IIF – Returns a specified value depending on the result of a Boolean expression.

String Functions SQL Server 2012 introduces the following string functions: 

CONCAT – Concatenates two or more string values.



FORMAT – Returns a culture-specific formatted string for a specified value.


Demonstration: Using New Functions

In this demonstration, you will see how to use new and enhanced functions in SQL Server 2012.  Use Conversion Functions 1.

Ensure that you have completed the previous demonstration in this module.

2.

In SQL Server Management Studio, open the Functions.sql script file in the D:\Demofiles\Mod02 folder.

3.

Select and execute the code under the comment PARSE. This uses the PARSE function to convert string values with formatting for the en-GB culture (United Kingdom) into date and money data types.

4.

Select and execute the code under the comment TRY_PARSE. This uses the TRY_PARSE function to attempt to convert string values with formatting for the en-GB culture (United Kingdom) into date and money data types formatted for the en-US culture (United States). The date fails to convert because it contains an invalid value (December 32nd), and the money fails because it contains the currency symbol for pounds sterling, which is invalid in the en-US culture.

5.

Select and execute the code under the comment TRY_CONVERT. This uses the TRY_CONVERT function to attempt to convert data values to numbers. The first succeeds because 1.2 is a valid value for the float data type. The second fails because the text “one” is not a valid integer.

 Use Date and Time Functions 1.

Select and execute the code under the comment DATEFROMPARTS. This generates a date value from a year, month and day.

2.

Select and execute the code under the comment DATETIMEFROMPARTS. This generates a datetime value from a year, month, day, hour, minute, second, and millisecond.

3.

Select and execute the code under the comment SMALLDATETIMEFROMPARTS. This generates a smalldatetime value from a year, month, day, hour, and minute.


4.

Select and execute the code under the comment DATETIME2FROMPARTS. This generates a datetime2 value from a year, month, day, hour, minute, second, and a fraction with a precision of 7 (i.e. the fraction .0000001).

5.

Select and execute the code under the comment TIMEFROMPARTS. This generates a time value from an hour, minute, second, and a fraction with a precision of 5 (i.e. the fraction .00001).

6.

Select and execute the code under the comment DATETIMEOFFSETFROMPARTS. This generates a datetimeoffset value from a year, month, day, hour, minute, second, and a fraction with a precision of 7 (i.e. the fraction .0000001) with an offset of 8 hours.

7.

Select and execute the code under the comment EOMONTH. This retrieves the date of the last day in the current month, next month, and the previous month.

 Use Logical Functions 1.

Select and execute the code under the comment CHOOSE. This returns the third item (Debit Card) from a list of payment options.

2.

Select and execute the code under the comment IIF. This returns ‘Even’ if the modulo (remainder) of a specified variable when divided by 2 is 0, and ‘Odd’ if it is not.

 Use String Functions 1.

Select and execute the code under the comment CONCAT. This concatenates the FirstName and LastName columns in the Customers table.

2.

Select and execute the code under the comment FORMAT. This formats the UnitPrice column in the SalesOrderDetail table as currency for the en-GB culture (United Kingdom).

3.



Spatial Data Enhancements

Spatial data support for geometric and geographic data was introduced in SQL Server 2008 and has been enhanced in SQL Server 2012.

New Spatial Shapes SQL Server 2012 introduces a few new spatial shapes, including CIRCULARSTRING, COMPOUNDCURVE, and CURVEPOLYGON. A CIRCULARSTRING line is a sequence of an odd number of at least three points, which are connected to form a curved arc. The following code sample defines a CIRCULARSTRING shape Creating a CIRCULARSTRING shape DECLARE @g geography = 'CIRCULARSTRING(-4.115 55.778, -3.399 56.990, -2.237 54.009)'

You can create a closed CIRCULARSTRING line by defining at least five points and making the final point in the line the same as the first. A COMPOUNDCURVE is a curved shape that is composed of one or more CIRCULARSTRING arcs and linear sections that are contiguously joined by having the final point in each segment the same as the first point in the next segment. The following code sample creates a COMPOUNDCURVE shape from two CIRCULARSTRING arcs and a linear section. Note that you do not specify a keyword for the linear sections. Creating a COMPUNDCURVE shape DECLARE @g geography = 'COMPOUNDCURVE( CIRCULARSTRING(-4.000 55.000, -4.500 54.500, -4.000 54.000), (-4.000 54.000, 1.000 54.000), CIRCULARSTRING(1.000 54.000, 1.500 54.500, 1.000 55.000))'


A CURVEPOLYGON is a surface area that is formed by a closed curved line, which can be defined by a CIRCULARSTRING or a COMPOUNDCURVE. When working with the geometry data type, the points in the curved area can be defined in any order, but when using he geography type, you must observe the “left foot rule”, which dictates that you must describe the shape as if you were pacing it out on the ground and the “inside” of the shape is always on your left. The following code sample defines a CURVEPOLYGON based on a CIRCULARSTRING. Creating a CURVEPOLYGON shape SELECT geography::Parse('CURVEPOLYGON( CIRCULARSTRING(-4.889 55.844, -3.924 55.738, -2.731 56.058,-4.201 56.134,-4.889 55.844))')

Support for Shapes Larger than a Hemisphere In previous releases of SQL Server, the geography data type did not support shapes that are bigger than a hemisphere. Trying to create a shape bigger than a hemisphere resulted in an error. In SQL Server 2012, this rule is relaxed, and shapes larger than a hemisphere are supported. You must be careful to apply the “left foot rule” when defining the points of an enclosed shape. For example, reversing the points in the previous CURVEPOLYGON example defines a shape that covers the entire surface of the Earth other than the small “hole” enclosed by the specified points.

New FULLGLOBE Shape SQL Server 2012 introduces the FULLGLOBE shape, which defines the entire surface of the Earth. The following code sample returns the area of the planet’s surface in square kilometers Using the FULLGLOBE shape DECLARE @theEarth geography = geography::STGeomFromText('FULLGLOBE', 4326) SELECT @theEarth.STArea()/1000000


Demonstration: Using Spatial Data Enhancements

In this demonstration, you will see how to use new spatial data features in SQL Server 2012.  Create a CIRCULARSTRING Shape 1.

Ensure that you have completed the previous demonstrations in this module.

2.

In SQL Server Management Studio, open the Spatial Data.sql script file in the D:\Demofiles\Mod02 folder.

3.

Select and execute the code under the comment CIRCULARSTRING. This returns a resultset containing:

4.



An open CIRCULARSTRING



An open LINESTRING with the same points as the open CIRCULARSTRING.



A closed CIRCULARSTRING.



A closed LINESTRING with the same points as the closed CIRCULARSTRING.

In the query results pane, on the Spatial Results tab, view the image representing each of the spatial columns returned by the query; adjusting the zoom and displaying or hiding grid lines as necessary. Note how the CIRCULARSTRING shape creates a line based on a circular arc defined by the points.

 Create a COMPOUNDCURVE Shape 1.

Select and execute the code under the comment COMPOUNDCURVE. This returns a COMPOUNDCURVE shape that consists of a CIRCULARSTRING, and LINESTRING (for which the shape type does not need to be specified), and a second CIRCULARSTRING.

2.

In the query results pane, on the Spatial Results tab, view the image representing COMPOUNDCURVE shape returned by the query; adjusting the zoom and displaying or hiding grid lines as necessary.


 Create a CURVEPOLYGON Shape 1.

Select and execute the code under the comment CURVEPOLYGON. This returns a CURVEPOLYGON shape that consist of a single CIRCULARSTRING.

2.

In the query results pane, on the Spatial Results tab, view the image representing CURVEPOLYGON shape returned by the query; adjusting the zoom and displaying or hiding grid lines as necessary.

 Create a Shape that is Larger than a Hemisphere 1.

Select and execute the code under the comment Larger than a hemisphere. This returns a CURVEPOLYGON shape that consist of a single CIRCULARSTRING with the points in the reverse order of the one used in the previous example.

2.

In the query results pane, on the Spatial Results tab, view the image representing CURVEPOLYGON shape returned by the query; adjusting the zoom and displaying or hiding grid lines as necessary. Zoom very closely into the image at around -4o longitude (horizontal) and 55o latitude (vertical) to see the “hole” on the surface of the earth created by the shape.

 Use the FULLGLOBE Shape 1.

Select and execute the code under the comment FULLGLOBE. This returns the surface area of the Earth in square kilometers.

2.



Storing and Querying Documents

SQL Server has supported full-text search since version 7.0, and in SQL Server 2008, the ability to store and query documents on the file system was introduced through the FILESTREAM feature. In SQL Server 2012, support for complex text documents has evolved further through the following new and enhanced features.

FileTables In previous releases of SQL Server, the FILESTREAM feature has made it possible to query and manipulate documents on the file system with Transact-SQL. SQL Server 2012 introduces the FileTable as a database structure that makes documents on the file systems first class citizens in a SQL Server database, and enables you to query files and folders using the full-text search capabilities of SQL Server. The following example shows how to create a FileTable that maps to a physical folder named Documents in the SQL Server FILESTREAM share. Creating a FileTable CREATE TABLE FileStore AS FileTable WITH (FileTable_Directory = ‘Documents’)

Full-Text-Related Enhancements Full-text support in SQL Server 2012 has been enhanced to support new languages (Greek and Czech), and benefits from updated word breakers and stemmers. Additionally, support for property-scoped searching that takes advantage of properties emitted by iFilters (such as those for Office documents) makes it possible to search based on document properties such as Author or Title. You can include document properties in a CONTAINS clause as shown in this example. Searching based on document properties SELECT [name] As FileName FROM FileStore WHERE CONTAINS(PROPERTY(file_stream,'Title'),'Bike OR Cycling');


Customizable Proximity Full-text search in SQL Server 2012 includes enhancements to the NEAR operator that enable you to specify the proximity scope for searches that try to find one search term near another. The following code sample shows how to find documents that contain the word “bicycle” within 15 search terms of the word “race”. Using NEAR in a full-text query SELECT [name] As FileName FROM FileStore WHERE CONTAINS(file_stream, ‘NEAR((bicycle, race), 15)’)

Statistical Semantic Search Statistical semantic search extends the existing full-text search feature in SQL Server to enable you to query the meaning of a document. For example, you can find the top key phrases in a document to discern its semantic meaning, or find documents that are similar to one another. To use statistical semantic search, you must install the semantic language statistics database, which contains the statistical language models required for semantic search. You can then use the structures and data in this database to perform semantic queries.


Demonstration: Working with Documents

In this demonstration, you will see how to create a FileTable and use full-text search enhancements in SQL Server 2012.  Create a FileTable 1.


2.

In SQL Server Management Studio, open the Files.sql script file in the D:\Demofiles\Mod02 folder.

3.

Select and execute the code under the comment Create a filestream database. This creates a database named FilesDemo that supports filestream data.

4.

Select and execute the code under the comment Create a filetable. This creates a FileTable named FileStore that maps to a directory named Documents.

5.

Minimize SQL Server Management Studio and view the contents of the D:\Demofiles\Mod02 folder. Note that it contains three rich text document (.rtf) files named Document1, Document2, and Document3).

6.

On the Start menu, click Run, type \\localhost\mssqlserver, and press Enter. Then, in the folder window that opens, double-click the FilestreamData folder and double-click the Documents folder.

7.

Drag the rich text document files from the Mod02 folder window to the Documents folder window to copy the files into the Documents folder. Then close all folder windows.

8.

Maximize SQL Server Management Studio, and then select and execute the code under the comment Query FileTable. This queries the FileStore table and retrieves the names of the files in the folder to which it is mapped.


 Use the NEAR Operator 1.

Select and execute the code under the comment Create full-text catalog. This creates a full-text catalog named documents_catalog in the database.

2.

Select and execute the code under the comment Get index ID for FileTable PK. This retrieves the ID for the primary key index in the FileStore table.

3.

In the code under the comment Create full-text index, replace the index ID in the KEY INDEX clause with the ID retrieved in the previous step. Then select and execute the code under the comment Create full-text index to create a full-text index on the FileStore table.

4.

Select and execute the code under the comment Find documents containing "imperdiet" near "vivamus" (within 15 search terms). This returns all documents that contain the text “imperdiet” near the text “vivamus” (only Document1.rtf contains these items within 15 search terms of one another).

5.



Lesson 2

Database Manageability Enhancements

Database management is easier in SQL Server 2012 because of enhancements that make it easier to work with databases in management tools, and new security features. In this lesson, you will learn how to use new features in SQL Server 2012 management tools, delegate management tasks through user-defined server roles, and manage database-level security with contained databases. After completing this lesson, you will be able to: 

Describe management tool enhancements in SQL Server 2012.



Describe security enhancements in SQL Server 2012.


Management Tool Enhancements

SQL Server Management Studio is the primary tool for managing SQL Server instances and databases, and provides an environment for performing administrative tasks graphically or by executing TransactSQL statements. In SQL Server 2012, a number of enhancements have been introduced to make it easier to use Transact-SQL.

Code Snippets Code snippets enable you to quickly enter common Transact-SQL statements. You can insert a snippet to perform a common task such as creating a login or a table, and then simply replace the placeholder text in the snippet with the values you want to use. You can also surround existing Transact-SQL code in your script with a snippet such as a BEGIN…END block or a WHILE loop. Snippets are implemented as XML files in the Tools\Binn\ManagementStudio\SQL\Snippets folder where SQL Server Management Studio is installed (usually in the C:\Program Files (x86)\Microsoft SQL Server folder). You can use these XML files as the basis for your own custom snippets, which you can save as .snippet files in the SQL Server Management Studio\Code Snippets\SQL\My Code Snippets folder in the Documents library.

Enhanced Debugging SQL Server 2012 provides enhancements to interactive debugging in SQL Server Management Studio, including the ability to: 

Specify break conditions, filters, and actions.



Monitor Transact-SQL expressions in the Watch and Quick Watch windows.



Display a Quick Info pop up by holding the mouse over a Transact-SQL identifier.


Demonstration: Using SQL Server Management Studio

In this demonstration, you will see how to use code snippets and debug Transact-SQL code in SQL Server 2012.  Use Code Snippets 1.


2.

In SQL Server Management Studio, open the Snippets.sql script file in the D:\Demofiles\Mod02 folder.

3.

With the insertion point at the beginning of the script file, on the Edit menu, point to IntelliSense, and click Insert Snippet. Then, in the Insert Snippet shortcut menu, double-click Table, and double-click Create Table. This inserts a CREATE TABLE statement with placeholders for values you need to provide when creating a table.

4.

Delete the entire CREATE TABLE statement.

5.

Minimize SQL Server Management Studio and view the contents of the D:\Demofiles\Mod02 folder. Note that it contains a file named Variable Declaration.snippet).

6.

Right-click Variable Declaration.snippet and click Open With. Then in the Open with dialog box, expand Other Programs and double-click Notepad.

7.

View the XML definition for the snippet, noting the elements used to define placeholders. Then close Notepad and copy the Variable Declaration.snippet file to the SQL Server Management Studio\Code Snippets\SQL\My Code Snippets folder in the My Documents library folder.

8.

Maximize SQL Server Management Studio, right-click the blank line under the comment Insert custom snippet to declare a variable, and click Insert Snippet. Then, in the Insert Snippet shortcut menu, double-click My Code Snippets, and double-click Declare Variable. This inserts a DECLARE statement with the placeholders defined in the snippet file.

9.

Change the @x placeholder to @i.


10. Select the two lines of code under the comment Surround with a WHILE snippet. Then right-click the selected lines and click Surround With, and then click While. 11. In the WHILE statement, change the Condition to @i