1 / 15

Microsoft SQL Server 2012 - Managing Database Applications

Microsoft SQL Server 2012 - Managing Database Applications. MODULE OUTLINE. Database Development Enhancements Database Manageability Enhancements Database Availability Enhancements. Lesson 1: Database Development Enhancements.

jaimin
Download Presentation

Microsoft SQL Server 2012 - Managing Database Applications

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Microsoft SQL Server 2012 - Managing Database Applications

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

  3. Lesson 1: Database Development Enhancements • Transact-SQL Enhancements New Functions Spatial Data Enhancements Storing and Querying Documents

  4. Transact-SQL Enhancements • The WITH RESULT SETS Clause EXECUTE GetOrderPickList'SO59384' WITH RESULT SETS ( ([SalesOrder] nvarchar(20) NOT NULL,[LineItem] int, [Product] int, [Quantity] int) ) • The THROW Statement THROW 50001, 'Customer doers not exist', 1 • Paging with the OFFSET and FETCH Keywords SELECT SalesOrderNumber, OrderDate, CustomerName FROM SalesOrders ORDER BY SalesOrderNumber ASC OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY • Sequence Objects CREATE SEQUENCE OrderNumbers START WITH 1000 INCREMENT BY 10 ... CREATE TABLE Orders (OrderNumberint PRIMARY KEY DEFAULT(NEXT VALUE FOR OrderNumbers), CustomerKeyint, ProductKeyint, Quantity int) • The OVER Clause • SELECT City, OrderYear, OrderQuantity, • SUM(OrderQuantity) OVER (PARTITION BY City ORDER BY OrderYear • ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS RunningQty • FROM CitySalesByYear

  5. New Functions

  6. Spatial Data Enhancements • New Spatial Shapes • Shapes larger than a Hemisphere • New FULLGLOBE Shape CIRCULARSTRING CURVEPOLYGON COMPOUNDCURVE

  7. Storing and Querying Documents CREATE TABLE FileStore AS FileTable WITH (FileTable_Directory = 'Documents') SELECT [name] As FileNameFROM FileStore WHERE CONTAINS(PROPERTY(file_stream,'Title'),'Bike OR Cycling') SELECT [name] As FileName FROM FileStore WHERE CONTAINS(file_stream, 'NEAR((bicycle, race), 15)')

  8. Lesson 2: Database Manageability Enhancements • Management Tool Enhancements Security Enhancements

  9. Polls

  10. Management Tool Enhancements • Code Snippets • Enhanced Debugging

  11. 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 [JohnDoe]; • Contained Databases • CREATE DATABASE [MyContainedDB] • CONTAINMENT = PARTIAL • GO • USE [MyContainedDB] • CREATE USER [SalesAppUser] WITH PASSWORD = 'Pa$$w0rd' • GO

  12. Lesson 3: Database Availability Enhancements • Backup and Restore Enhancements AlwaysOn Availability Groups

  13. Backup and Restore Enhancements • Point-In-Time Restore • Page Restore

  14. AlwaysOn Availability Groups Sync Node3 Windows Cluster Async Node1 (Primary) Node2 (Read-Only) Listener

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

More Related