关于sqlserver physical schema的一些SQL

/*create datafile*/
CREATE DATABASE NewDB
ON
PRIMARY
(NAME = NewDB,
FILENAME = ‘c:/SQLData/NewDB.mdf’,
SIZE = 10MB,
MAXSIZE = 200Gb,
FILEGROWTH = 100)
LOG ON
(NAME = NewDBLog,
FILENAME = ‘d:/SQLLog/NewDBLog.ldf’,
SIZE = 5MB,
MAXSIZE = 10Gb,
FILEGROWTH = 100);


/*调整数据库文件*/
ALTER DATABASE NewDB
MODIFY FILE
(Name = NewDB,
SIZE = 25MB,
MAXSIZE = 2Gb,
FILEGROWTH = 0);


/*创建多文件数据库*/
CREATE DATABASE NewDB
ON
PRIMARY(NAME = NewDB,
FILENAME = ‘e:/SQLData/NewDB.mdf’),
(NAME = NewDB2,
FILENAME = ‘f:/SQLData/NewDB2.ndf’)
LOG ON
(NAME = NewDBLog,
FILENAME = ‘g:/SQLLog/NewDBLog.ldf’),
(NAME = NewDBLog2,
FILENAME = ‘h:/SQLLog/NewDBLog2.ldf’);


/*向数据库添加文件*/
ALTER DATABASE NewDB
ADD FILE
(NAME = NewDB3,
FILENAME = ‘i:/SQLData/NewDB3.ndf’,
SIZE = 10MB,
MAXSIZE = 2Gb,
FILEGROWTH = 20);


/*移除数据为文件*/
DBCC SHRINKFILE (NewDB3, EMPTYFILE)
ALTER DATABASE NewDB
REMOVE FILE NewDB3;


/*创建文件组的数据库*/
CREATE DATABASE NewDB
ON
PRIMARY
(NAME = NewDB,
FILENAME = ‘d:/SQLData/NewDB.mdf’,
SIZE = 50MB,
MAXSIZE = 5Gb,
FILEGROWTH = 25MB),
FILEGROUP Data DEFAULT
(NAME = NewDBData,
FILENAME = ‘e:/SQLData/NewDBData.ndf’,
SIZE = 100MB,
MAXSIZE = 50Gb,
FILEGROWTH = 100MB)
LOG ON
(NAME = NewDBLog,
FILENAME = ‘f:/SQLLog/NewDBLog.ndf’,
SIZE = 100MB,
MAXSIZE = 25Gb,
FILEGROWTH = 25MB);



/*删除数据库*/
DROP DATABASE NewDB;


/*创建表*/
CREATE TABLE dbo.ProductCategory (
ProductCategoryID UNIQUEIDENTIFIER NOT NULL
ROWGUIDCOL DEFAULT (NEWID()) PRIMARY KEY NONCLUSTERED,
ProductCategoryName NVARCHAR(50) NOT NULL,
ProductCategoryDescription NVARCHAR(100) NULL
)
ON [Data];


/*显示定义主键限制名*/
CREATE TABLE dbo.Guide (
GuideID INT IDENTITY NOT NULL CONSTRAINT PK_Guide PRIMARY KEY (GuideID),
....)


/*为表添加主键*/
ALTER TABLE dbo.Guide ADD CONSTRAINT
PK_Guide PRIMARY KEY(GuideID)
ON [PRIMARY];

/*创建一个guid主键*/
CREATE TABLE dbo.Product (
ProductID UNIQUEIDENTIFIER NOT NULL
ROWGUIDCOL DEFAULT (NEWSEQUNTIALID())
PRIMARY KEY CLUSTERED,...)


/*创建含有外键的表*/
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];

/*添加外键*/
ALTER TABLE SecondaryTableName
ADD CONSTRAINT ConstraintName
FOREIGN KEY (ForeignKeyColumns)
REFERENCES dbo.PrimaryTable (PrimaryKeyColumnName);


/*CASCADE 删除定义*/
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)

/*创建列*/
CREATE TABLE TableName (
ColumnName DATATYPE Attributes,
ColumnName DATATYPE Attributes
);

/*修改表添加列*/
ALTER TABLE TableName
ADD ColumnName DATATYPE Attributes;

/*修改列*/
ALTER TABLE TableName
ALTER COLUMN ColumnName
NEWDATATYPE Attributes;


/*创建有计算列的表*/
CREATE TABLE dbo.OrderDetail (
. . .
Quantity NUMERIC(7,2) NOT NULL,
UnitPrice MONEY NOT NULL,
ExtendedPrice AS Quantity * UnitPrice Persisted,
. . .
)
ON [Primary];
Go

/*创建sparse列*/
CREATE TABLE Foo (
FooPK INT NOT NULL IDENTITY PRIMARY KEY,
Name VARCHAR(25) NOT NULL,
ExtraData VARCHAR(50) SPARSE NULL
);


/*设置列默认不为空，这是sqlserver的默认情况*/
USE TempDB;
EXEC sp_dboption ‘TempDB’, ANSI_NULL_DEFAULT, ‘false’;
SET ANSI_NULL_DFLT_OFF ON;

/*设置列默认为空，这是ansi标准*/
EXEC sp_dboption ‘TempDB’, ANSI_NULL_DEFAULT, ‘true’;
SET ANSI_NULL_DFLT_ON ON;

/*创建索引示例*/
CREATE CLUSTERED INDEX IxOrderID
ON dbo.OrderDetail (OrderID)
[on FILEGROUPname];


/*创建复合索引*/
CREATE CLUSTERED INDEX IxGuideName
ON dbo.Guide (LastName, FirstName);

/*unique索引创建*/
CREATE UNIQUE INDEX OrderNumber
ON [Order] (OrderNumber);

/*创建有包含列的索引*/
CREATE INDEX ixGuideCovering
ON dbo.Guide (LastName, FirstName)
INCLUDE (Title)

/*创建filtered index*/
CREATE INDEX IxActiveProduction
ON Production.WorkOrders (WorkOrderID, ProductID)
WHERE Status = ‘Active’

/*创建带填充因子的index*/
CREATE NONCLUSTERED INDEX IxOrderNumber
ON dbo.[Order] (OrderNumber)
WITH FILLFACTOR = 85, PAD_INDEX = ON;

/*创建忽略重复键索引*/
CREATE UNIQUE INDEX OrderNumber
ON [Order] (OrderNumber)
WITH IGNORE_DUP_KEY = ON

/*disable index*/
ALTER INDEX [IxContact] ON [dbo].[Contact] DISABLE


/*To re-enable an index, use the ALTER INDEX... REBUILD WITH command*/
ALTER INDEX [PKContact0BC6C43E]
ON [dbo].[Contact]
REBUILD WITH
( PAD_INDEX = OFF,
STATISTICS_NORECOMPUTE = OFF,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON,
SORT_IN_TEMPDB = OFF,
ONLINE = OFF )