源码T-SQL性能调优秘笈 基于SQL Server 2012窗口函数

--------------------------------------------------------------------- -- Script that creates the sample database TSQL2012 -- -- Supported versions of SQL Server: 2005, 2008, 2008 R2, 2012, Windows Azure SQL Database -- -- Based originally on the Northwind sample database -- with changes in both schema and data -- -- Last updated: 20130525 -- -- © Itzik Ben-Gan --------------------------------------------------------------------- --------------------------------------------------------------------- -- Create empty database TSQL2012 --------------------------------------------------------------------- -- For on-premises SQL Server use the steps in section A and then proceed to section C -- For SQL Azure use the steps in section B and then proceed to section C --------------------------------------------------------------------- -- Section A - for on-premises SQL Server only --------------------------------------------------------------------- -- 1. Connect to your on-premises SQL Server instance, master database -- 2. Run the following code to create an empty database called TSQL2012 USE master; -- Drop database IF DB_ID('TSQL2012') IS NOT NULL DROP DATABASE TSQL2012; -- If database could not be created due to open connections, abort IF @@ERROR = 3702 RAISERROR('Database cannot be dropped because there are still open connections.', 127, 127) WITH NOWAIT, LOG; -- Create database CREATE DATABASE TSQL2012; GO USE TSQL2012; GO -- 3. Proceed to section C --------------------------------------------------------------------- -- Section B - for SQL Azure only --------------------------------------------------------------------- /* -- 1. Connect to SQL Azure, master database USE master; -- used only as a test; will fail if not connected to master -- 2. Run following if TSQL2012 database already exists, otherwise skip DROP DATABASE TSQL2012; GO -- 3. Run the following code to create an empty database called TSQL2012 CREATE DATABASE TSQL2012; GO -- 4. Connect to TSQL2012 before running the rest of the code USE TSQL2012; -- used only as a test; will fail if not connected to TSQL2012 GO -- 5. Proceed to section C */ --------------------------------------------------------------------- -- Populate database TSQL2012 with sample data --------------------------------------------------------------------- --------------------------------------------------------------------- -- Section C - for both on-premises SQL Server and SQL Azure --------------------------------------------------------------------- -- 1. Highlight the remaining code in the script file and execute --------------------------------------------------------------------- -- Create Schemas --------------------------------------------------------------------- CREATE SCHEMA HR AUTHORIZATION dbo; GO CREATE SCHEMA Production AUTHORIZATION dbo; GO CREATE SCHEMA Sales AUTHORIZATION dbo; GO CREATE SCHEMA Stats AUTHORIZATION dbo; GO --------------------------------------------------------------------- -- Create Tables --------------------------------------------------------------------- -- Create table HR.Employees CREATE TABLE HR.Employees ( empid INT NOT NULL IDENTITY, lastname NVARCHAR(20) NOT NULL, firstname NVARCHAR(10) NOT NULL, title NVARCHAR(30) NOT NULL, titleofcourtesy NVARCHAR(25) NOT NULL, birthdate DATETIME NOT NULL, hiredate DATETIME NOT NULL, address NVARCHAR(60) NOT NULL, city NVARCHAR(15) NOT NULL, region NVARCHAR(15) NULL, postalcode NVARCHAR(10) NULL, country NVARCHAR(15) NOT NULL, phone NVARCHAR(24) NOT NULL, mgrid INT NULL, CONSTRAINT PK_Employees PRIMARY KEY(empid), CONSTRAINT FK_Employees_Employees FOREIGN KEY(mgrid) REFERENCES HR.Employees(empid), CONSTRAINT �