MICROSOFT SQL SERVER 2012 MANAGING DATABASE APPLICATIONS Last

MICROSOFT SQL SERVER 2012 - MANAGING DATABASE APPLICATIONS Last Updated:

MODULE OUTLINE • Database Development Enhancements • Database Manageability Enhancements • Database Availability Enhancements 2

LESSON 1: DATABASE DEVELOPMENT ENHANCEMENTS • Transact-SQL Enhancements • New Functions • Spatial Data Enhancements • Storing and Querying Documents

TRANSACT-SQL ENHANCEMENTS • The WITH RESULT SETS Clause EXECUTE Get. Order. Pick. List 'SO 59384' WITH RESULT SETS ( ([Sales. Order] nvarchar(20) NOT NULL, [Line. Item] int, [Product] int, [Quantity] int) ) • The THROW Statement THROW 50001, 'Customer doers not exist', 1 • Paging with the OFFSET and FETCH Keywords SELECT Sales. Order. Number, Order. Date, Customer. Name FROM Sales. Orders ORDER BY Sales. Order. Number ASC OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY • Sequence Objects CREATE SEQUENCE Order. Numbers START WITH 1000 INCREMENT BY 10. . . CREATE TABLE Orders (Order. Number int PRIMARY KEY DEFAULT(NEXT VALUE FOR Order. Numbers), Customer. Key int, Product. Key int, Quantity int) • The OVER Clause SELECT City, Order. Year, Order. Quantity, SUM(Order. Quantity) OVER (PARTITION BY City ORDER BY Order. Year ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS Running. Qty FROM City. Sales. By. Year

NEW FUNCTIONS Conversion Functions PARSE('£ 345. 98' AS money USING 'en-GB') TRY_PARSE('£ 345. 98' AS money USING 'en-US') TRY_CONVERT(int, 'One') Date and Time Functions DATEFROMPARTS (2010, 12, 31) DATETIMEFROMPARTS ( 2010, 12, 31, 23, 59, 0 ) SMALLDATETIMEFROMPARTS ( 2010, 12, 31, 23, 59 ) DATETIME 2 FROMPARTS ( 2010, 12, 31, 23, 59, 1, 7 ) TIMEFROMPARTS ( 23, 59, 1, 5 ) DATETIMEOFFSETFROMPARTS(2010, 12, 31, 14, 23, 1, 8, 0, 7) EOMONTH (GETDATE(), 1) Logical Functions CHOOSE (3, 'Cash', 'Credit Card', 'Debit Card', 'Invoice') IIF(@i % 2 = 0, 'Even', 'Odd') String Functions CONCAT(Firstname, ' ', Last. Name) FORMAT(Unit. Price, 'C', 'en-GB')

SPATIAL DATA ENHANCEMENTS • New Spatial Shapes CIRCULARSTRING COMPOUNDCURVE • Shapes larger than a Hemisphere • New FULLGLOBE Shape CURVEPOLYGON

STORING AND QUERYING DOCUMENTS CREATE TABLE File. Store AS File. Table WITH (File. Table_Directory = 'Documents') SELECT [name] As File. Name FROM File. Store WHERE CONTAINS(PROPERTY(file_stream, 'Title'), 'Bike OR Cycling') SELECT [name] As File. Name FROM File. Store WHERE CONTAINS(file_stream, 'NEAR((bicycle, race), 15)')

LESSON 2: DATABASE MANAGEABILITY ENHANCEMENTS • Management Tool Enhancements • Security Enhancements

POLLS

MANAGEMENT TOOL ENHANCEMENTS • Code Snippets • Enhanced Debugging

SECURITY ENHANCEMENTS • User-Defined Server Roles CREATE SERVER ROLE [AGAdmins] AUTHORIZATION [sa]; GRANT ALTER ANY AVAILABILITY GROUP TO [AGAdmins]; GRANT ALTER ANY ENDPOINT TO [AGAdmins]; GRANT CREATE AVAILABILITY GROUP TO [AGAdmins]; GRANT CREATE ENDPOINT TO [AGAdmins]; ALTER SERVER ROLE [AGAdmins] ADD MEMBER [John. Doe]; • Contained Databases CREATE DATABASE [My. Contained. DB] CONTAINMENT = PARTIAL GO USE [My. Contained. DB] CREATE USER [Sales. App. User] WITH PASSWORD = 'Pa$$w 0 rd' GO

LESSON 3: DATABASE AVAILABILITY ENHANCEMENTS • Backup and Restore Enhancements • Always. On Availability Groups

BACKUP AND RESTORE ENHANCEMENTS • Point-In-Time Restore • Page Restore

ALWAYSON AVAILABILITY GROUPS nc Sy Node 3 Windows Cluster Async Node 1 (Primary) Listener Node 2 (Read-Only)

MODULE REVIEW • Database Development Enhancements • Database Manageability Enhancements • Database Availability Enhancements For more information, attend the following courses: • • 10774 A: Querying Microsoft® SQL Server® 2012 10775 A: Administering Microsoft® SQL Server® 2012 Databases 10776 A: Developing Microsoft® SQL Server® 2012 Databases 40008 A: Updating your Database Skills to Microsoft® SQL Server® 2012