ch 05 - implementing the physical design.sql

《SQLServer2000宝典》—包括本书中的所有代码

-----------------------------------------------------------
-- SQL Server 2000 Bible 
-- Wiley Publishing 
-- Paul Nielsen

-- Chapter 5 Implementing the Physical Design

-----------------------------------------------------------
-----------------------------------------------------------

-- Simplest create database
CREATE DATABASE NewDB

DROP DATABASE NewDB

-- File Growth options
CREATE DATABASE NewDB
ON 
PRIMARY
  (NAME = NewDB,
    FILENAME = 'c:\SQLServerBible\NewDB.mdf',
      SIZE = 10MB,
      MAXSIZE = 2Gb,
      FILEGROWTH = 20)
LOG ON 
  (NAME = NewDBLog,
    FILENAME = 'c:\SQLServerBible\NewDBLog.ldf',
      SIZE = 5MB,
      MAXSIZE = 1Gb,
      FILEGROWTH = 10%)

-- Manually Grow a File
ALTER DATABASE NewDB
  MODIFY FILE 
    (Name = NewDB,
    SIZE = 25MB,
    MAXSIZE = 2Gb,
    FILEGROWTH = 0)
  
-- Create Multiple file db
DROP DATABASE NewDB

CREATE DATABASE NewDB
ON 
PRIMARY
  (NAME = NewDB,
    FILENAME = 'c:\SQLServerBible\NewDB.mdf'),
  (NAME = NewDB2,
    FILENAME = 'c:\SQLServerBible\NewDB2.ndf')
LOG ON 
  (NAME = NewDBLog,
    FILENAME = 'c:\SQLServerBible\NewDBLog.ldf'),
  (NAME = NewDBLog2,
    FILENAME = 'c:\SQLServerBible\NewDBLog2.ldf')

-- Modify Multiple Files 
ALTER DATABASE NewDB
  ADD FILE 
    (NAME = NewDB3,
      FILENAME = 'c:\SQLServerBible\NewDB3.ndf',
      SIZE = 10MB,
      MAXSIZE = 2Gb,
      FILEGROWTH = 20)

USE NewDB

CREATE TABLE Test (col1 INT IDENTITY)

-- insert some test data into NewDB
DECLARE @X INT
SET @X = 0
WHILE @X < 1000
  BEGIN
    INSERT Test DEFAULT VALUES
    SET @X = @X + 1
  END

SELECT COUNT(*) FROM Test

DBCC SHRINKFILE (NewDB3, EMPTYFILE)
ALTER DATABASE NewDB
  REMOVE FILE NewDB3

-- FileGroups
USE Master
DROP DATABASE NewDB
go
CREATE DATABASE NewDB
ON 
PRIMARY
  (NAME = NewDB,
    FILENAME = 'c:\SQLServerBible\NewDB.mdf',
      SIZE = 10MB,
      MAXSIZE = 2Gb,
      FILEGROWTH = 20),
FILEGROUP GroupTwo
  (NAME = NewDBGroup2,
    FILENAME = 'c:\SQLServerBible\NewDBTwo.ndf',
      SIZE = 10MB,
      MAXSIZE = 2Gb,
      FILEGROWTH = 20)
LOG ON 
  (NAME = NewDBLog,
    FILENAME = 'c:\SQLServerBible\NewDBLog.ndf',
      SIZE = 5MB,
      MAXSIZE = 1Gb,
      FILEGROWTH = 10%)

---------------------------------------------------
-- Creating Tables
-- sample code pulled from OBXKites database

CREATE TABLE dbo.ProductCategory (
  ProductCategoryID UNIQUEIDENTIFIER NOT NULL 
    ROWGUIDCOL DEFAULT (NEWID()) PRIMARY KEY NONCLUSTERED,
  ProductCategoryName NVARCHAR(50) NOT NULL,
  ProductCategoryDescription NVARCHAR(100) NULL
  )
  ON [Primary]

-- Filegroups
CREATE TABLE dbo.OrderPriority (
  OrderPriorityID UNIQUEIDENTIFIER NOT NULL 
    ROWGUIDCOL DEFAULT (NEWID()) PRIMARY KEY NONCLUSTERED,
  OrderPriorityName NVARCHAR (15) NOT NULL,
  OrderPriorityCode NVARCHAR (15) NOT NULL,
  Priority INT NOT NULL
  )
  ON [Static]

----------------------------------------------------------
-- CreatingKeys
-- sample DDL code taken from CHA2 database
CREATE TABLE dbo.Guide (
  GuideID INT IDENTITY NOT NULL PRIMARY KEY NONCLUSTERED,
  LastName  VARCHAR(50) NOT NULL,
  FirstName  VARCHAR(50) NOT NULL,
  Qualifications  VARCHAR(2048) NULL,
  DateOfBirth  DATETIME NULL,
  DateHire  DATETIME NULL
  ) 
  ON [Primary]

ALTER TABLE dbo.Guide ADD CONSTRAINT
  PK_Guide PRIMARY KEY NONCLUSTERED(GuideID)
  ON [PRIMARY]

-- Identity Columns
CREATE TABLE dbo.Event (
	EventID INT IDENTITY NOT NULL PRIMARY KEY NONCLUSTERED,
	TourID INT NOT NULL FOREIGN KEY REFERENCES dbo.Tour,
	EventCode VARCHAR(10) NOT NULL,
	DateBegin DATETIME NULL,
	Comment NVARCHAR(255)
 	) 
	ON [Primary]

--GUIDs
-- sample code taken from OBXKites database
CREATE TABLE Product (
  ProductID UNIQUEIDENTIFIER NOT NULL 
    ROWGUIDCOL DEFAULT (NEWID()) 
    PRIMARY KEY NONCLUSTERED,
  ProductCategoryID UNIQUEIDENTIFIER NOT NULL 
    FOREIGN KEY REFERENCES dbo.ProductCategory, 
  ProductCode CHAR(15) NOT NULL,   
  ProductName NVARCHAR(50) NOT NULL,
  ProductDescription NVARCHAR(100) NULL,
  ActiveDate DATETIME NOT NULL DEFAULT GETDATE(),
  DiscountinueDate DATETIME NULL
  )
  ON [Static]

-- Foreign Keys
-- sample code pulled from CHA2 database
CREATE TABLE dbo.Tour_mm_Guide (
	TourGuideID INT 
    IDENTITY 
    NOT NULL 
    PRIMARY KEY NONCLUSTERED,
	TourID INT 
    NOT NULL 
    FOREIGN KEY REFERENCES dbo.Tour(TourID) 
    ON DELETE CASCADE,
	GuideID INT 
    NOT NULL 
    FOREIGN KEY REFERENCES dbo.Guide 
    ON DELETE CASCADE,
	QualDate DATETIME NOT NULL,
	RevokeDate DATETIME NULL	
	)
	ON [Primary]

-- sample code pulled from Family database
CREATE TABLE dbo.Person (
  PersonID  INT NOT NULL PRIMARY KEY NONCLUSTERED,
  LastName  VARCHAR(15) NOT NULL,
  FirstName  VARCHAR(15) NOT NULL,
  SrJr  VARCHAR(3) NULL,
  MaidenName VARCHAR(15) NULL,
  Gender CHAR(1) NOT NULL, 
  FatherID INT NULL,
  MotherID INT NULL,
  DateOfBirth  DATETIME  NULL,
  DateOfDeath  DATETIME  NULL
  )
go
ALTER TABLE dbo.Person 
  ADD CONSTRAINT FK_Person_Father 
    FOREIGN KEY(FatherID) REFERENCES dbo.Person (PersonID)
ALTER TABLE dbo.Person 
  ADD CONSTRAINT FK_Person_Mother 
    FOREIGN KEY(MotherID) REFERENCES dbo.Person (PersonID)


-- Cascading Deletes
-- sample code pulled from OBXKites database
CREATE TABLE dbo.OrderDetail (
  OrderDetailID UNIQUEIDENTIFIER 
    NOT NULL 
    ROWGUIDCOL 
    DEFAULT (NEWID()) 
    PRIMARY KEY NONCLUSTERED,
  OrderID UNIQUEIDENTIFIER   
    NOT NULL 
    FOREIGN KEY REFERENCES dbo.[Order] 
      ON DELETE CASCADE, 
  ProductID UNIQUEIDENTIFIER   
    NULL 
    FOREIGN KEY REFERENCES dbo.Product,


-------------------------------------------------------
-- Creating Data Columns
ALTER TABLE TableName
  ADD ColumnName DATATYPE Attributes

-- ANSI Nullability

USE TempDB

--ANSI Default Column Nullability Column DEFAULT
SELECT DATABASEPROPERTYEX('TempDB','IsAnsiNullDefault')

EXEC sp_dboption 'TempDB', ANSI_NULL_DEFAULT, 'false'

SET ANSI_NULL_DFLT_OFF ON 

DROP TABLE NullTest

CREATE TABLE NullTest(
  PK INT IDENTITY,
  One VARCHAR(50)
  )

INSERT NullTest(One)
  VALUES (NULL)  -- should receive null error

EXEC sp_dboption 'TempDB', ANSI_NULL_DEFAULT, 'true'
SET ANSI_NULL_DFLT_ON ON 

DROP TABLE NullTest

CREATE TABLE NullTest(
  PK INT IDENTITY,
  One VARCHAR(50)
  )

INSERT NullTest(One)
  VALUES (NULL) -- allows nulls

--------------------------------------------------------
-- Unique Constraints

USE TempDB

CREATE TABLE Employee (
  EmployeeID INT PRIMARY KEY NONCLUSTERED,
  EmployeeNumber CHAR(8) UNIQUE (EmployeeNumber),
  LastName NVARCHAR(35),
  FirstName NVARCHAR(35)
  )

ALTER TABLE Employee
  ADD CONSTRAINT EmpNumUnique
    UNIQUE (EmployeeNumber)

Insert Employee (EmployeeID, EmployeeNumber, LastName, FirstName)
  Values( 1, '1', 'Wilson', 'Bob')

Insert Employee (EmployeeID, EmployeeNumber, LastName, FirstName)
  Values( 2, '1', 'Smith', 'Joe') -- unique constraint error

--------------------------------------------------------
-- Check Constraints
Drop Table Employee

CREATE TABLE Employee (
  EmployeeID INT PRIMARY KEY NONCLUSTERED,
  EmployeeNumber CHAR(8) CHECK (EmployeeNumber <> '1'),
  LastName NVARCHAR(35),
  FirstName NVARCHAR(35)
  )

Insert Employee (EmployeeID, EmployeeNumber, LastName, FirstName)
  Values( 2, '1', 'Smith', 'Joe') -- violates check constraint

ALTER TABLE Employee
  ADD CONSTRAINT NoHireSmith
    CHECK (Lastname <> 'SMITH')

Insert Employee (EmployeeID, EmployeeNumber, LastName, FirstName)
  Values( 4, '4', 'Smith', 'Joe') -- violates check constraint

---------------------------------------------------------
-- Default Constraints

USE OBXKites

ALTER TABLE Product
  DROP CONSTRAINT DF__Product__ActiveD__7F60ED59

ALTER TABLE Product
  ADD CONSTRAINT  ActiveDefault 
  DEFAULT GetDate() FOR ActiveDate 

---------------------------------------------------------
-- Data Catalog

sp_help person

-- User Defined Rules (example only - not to be applied to any sample database)
CREATE RULE BirthdateRule AS @Birthdate <= Getdate()
go
EXEC sp_bindrule 
  @rulename = 'BirthdateRule', 
  @objname =  'Person.DateOfBirth'
 
go

-- User Defined Default (example only - not to be applied to any sample database)
CREATE DEFAULT HireDefault AS Getdate()
go
sp_bindefault 'HireDefault', 'Contact.Hiredate'
go

-- User Defined Data Type (example only - not to be applied to any sample database)
EXEC sp_addtype 
  @typename = Birthdate,
  @phystype = SmallDateTime,
  @nulltype = 'NOT NULL'
go
sp_bindefault 
  @defname = 'BirthdateDefault', 
  @objname =  'Birthdate', 
  @futureonly =  'futureonly'

sp
EXEC sp_bindrule 
  @rulename = 'BirthdateRule', 
  @objname =  'Birthdate' 

------------------------------------------------
-- Creating Indexes

USE OBXKites

CREATE NONCLUSTERED INDEX IxOrderNumber
  ON dbo.[Order] (OrderNumber)

CREATE CLUSTERED INDEX IxOrderID
  ON dbo.OrderDetail (OrderID)

DROP INDEX OrderDetail.IxOrderID 

-- Composite Index
USE CHA2
CREATE CLUSTERED INDEX IxGuideName 
  ON dbo.Guide (LastName, FirstName)

-- Unique Index
USE OBXKites
CREATE UNIQUE INDEX OrderNumber 
  ON [Order] (OrderNumber)

-- Index Fill Factor and Index Pad
CREATE NONCLUSTERED INDEX IxOrderNumber
  ON dbo.[Order] (OrderNumber)
  WITH FILLFACTOR = 85, PAD_INDEX

-- Ignore Duplicate Key
DROP INDEX [Order].OrderNumber
CREATE UNIQUE INDEX OrderNumber 
  ON [Order] (OrderNumber)
  WITH IGNORE_DUP_KEY