《Microsoft SQL Server 2005&2008技术内幕》代码示例

--------------------------------------------------------------------- -- Script that creates the sample database InsideTSQL2008 -- -- Supported versions of SQL Server: 2005, 2008 -- -- Based originally on the Northwind sample database -- with changes in both schema and data to fit the book's needs -- -- Last updated: 20080910 ---------------------------------------------------------------------- --------------------------------------------------------------------- -- Create Database --------------------------------------------------------------------- USE master; -- Drop database IF DB_ID('InsideTSQL2008') IS NOT NULL DROP DATABASE InsideTSQL2008; -- 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 InsideTSQL2008; GO USE InsideTSQL2008; GO --------------------------------------------------------------------- -- Create Schemas --------------------------------------------------------------------- CREATE SCHEMA HR AUTHORIZATION dbo; GO CREATE SCHEMA Production AUTHORIZATION dbo; GO CREATE SCHEMA Sales 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 CHK_birthdate CHECK(birthdate <= CURRENT_TIMESTAMP) ); CREATE NONCLUSTERED INDEX idx_nc_lastname ON HR.Employees(lastname); CREATE NONCLUSTERED INDEX idx_nc_postalcode ON HR.Employees(postalcode); -- Create table Production.Suppliers CREATE TABLE Production.Suppliers ( supplierid INT NOT NULL IDENTITY, companyname NVARCHAR(40) NOT NULL, contactname NVARCHAR(30) NOT NULL, contacttitle NVARCHAR(30) 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, fax NVARCHAR(24) NULL, CONSTRAINT PK_Suppliers PRIMARY KEY(supplierid) ); CREATE NONCLUSTERED INDEX idx_nc_companyname ON Production.Suppliers(companyname); CREATE NONCLUSTERED INDEX idx_nc_postalcode ON Production.Suppliers(postalcode); -- Create table Production.Categories CREATE TABLE Production.Categories ( categoryid INT NOT NULL IDENTITY, categoryname NVARCHAR(15) NOT NULL, description NVARCHAR(200) NOT NULL, CONSTRAINT PK_Categories PRIMARY KEY(categoryid) ); CREATE INDEX categoryname ON Production.Categories(categoryname); -- Create table Production.Products CREATE TABLE Production.Products ( productid INT NOT NULL IDENTITY, productname NVARCHAR(40) NOT NULL, supplierid INT NOT NULL, categoryid INT NOT NULL, unitprice MONEY NOT NULL CONSTRAINT DFT_Products_unitprice DEFAULT(0), discontinued BIT NOT NULL CONSTRAINT DFT_Products_discontinued DEFAULT(0), CONSTRAINT PK_Products PRIMARY KEY(productid), CONSTRAINT FK_Products_Categ�