SQL Server 2012 管理

1.新增資料庫

新增資料表：輸入相關欄位名稱與資料類型，ProductUid的資料類型為int，識別規格為是，並將其設定為主索引鍵。依此方法分別建立Product、Device、Manufacture及Supplier共四個資料表。

欄位資料
Product 資訊設備項目
===============================================
ProductUid 產品Uid
Type 產品類型：個人電腦、筆記型電腦、平板電腦
ManufacturerUid 製造商：Acer、Asus、HP、D-link
Name 產品品稱：個人電腦
Description 特徵及說明：ACER VERITON L480
PurchaseDate 購置日期：’99/02/10
Valid 使用年限：4
Price 現值金額：18996
Memo1 備註一：98年度多功能E化專科教室…
Memo2 備註二：依據..號函辦理
GetSource 撥入、採購
BudgetarySource 公務預算、新北市地方教育發展基金
GetDate ’99/02/10
Property 1.動產、5.非消耗品
ImgUrl 產品圖片
WebUrl 產品參考網頁
Detail 產品說明
SupplierUid 供應商、維護商
[ProductUid] [int] IDENTITY(1,1) NOT NULL,
[Type] [nchar](3) NULL,
[ManufacturerUid] [int] NULL,
[Name] [nvarchar](50) NULL,
[Description] [nvarchar](50) NULL,
[PurchaseDate] [smalldatetime] NULL,
[Valid] [int] NULL,
[Price] [int] NULL,
[Memo1] [ntext] NULL,
[Memo2] [ntext] NULL,
[GetSource] [nchar](10) NULL,
[BudgetarySource] [nchar](10) NULL,
[GetDate] [smalldatetime] NULL,
[Property] [nchar](2) NULL,
[ImgUrl] [nchar](50) NULL,
[WebUrl] [nchar](150) NULL,
[Detail] [ntext] NULL,
[SupplierUid] [int] NULL

Device資訊設備財產資料
===============================================
DeviceUid DeviceUid
ProductUid ProductUid
Classification 財產編號分類號
SequenceNo 財產編號流水號
StorageUnits 保管單位：教務處
Custodian 保管人：XXX
EndUser 使用人
StoragePos 存放地點一：資訊中心
StoragePosNo 存放地點一：328
Status 使用狀態
InventoryResult 盤點結果
InventoryDate 盤點日期
UpdateUser 更新人員
UpdateDate 更新日期
Memo
[DeviceUid] [int] IDENTITY(1,1) NOT NULL,
[ProductUid] [int] NULL,
[Classification] [nchar](10) NULL,
[SequenceNo] [nchar](10) NULL,
[StorageUnits] [nchar](2) NULL,
[Custodian] [nchar](30) NULL,
[EndUser] [int] NULL,
[StoragePos] [nvarchar](20) NULL,
[StoragePosNo] [nchar](10) NULL,
[Status] [nchar](2) NULL,
[InventoryResult] [nchar](2) NULL,
[InventoryDate] [smalldatetime] NULL,
[UpdateUser] [int] NULL,
[UpdateDate] [smalldatetime] NULL,
[Memo] [ntext] NULL

Manufacturer 製造商
===============================================
ManufacturerUid uid
CName 中文名稱
EName 英文名稱
WebUrl 官網
Tel 電話
Fax 傳真
Email Email
Address 住址

[ManufacturerUid] [int] IDENTITY(1,1) NOT NULL,
[CName] [nvarchar](50) NULL,
[EName] [nchar](30) NULL,
[WebUrl] [nchar](50) NULL,
[Tel] [nchar](15) NULL,
[Fax] [nchar](15) NULL,
[Email] [nchar](30) NULL,
[Address] [nvarchar](50) NULL

Supplier 供應商
===============================================
SupplierUid Uid
Name 供應商名稱
Contact 聯絡人
ComTel 電話
Mobile 行動電話
Address 住址
WebUrl 官網

[SupplierUid] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](50) NULL,
[Contact] [nvarchar](10) NULL,
[ComTel] [nchar](15) NULL,
[Mobile] [nchar](15) NULL,
[Address] [nvarchar](100) NULL,
[WebUrl] [nchar](100) NULL

3.由查詢視窗以SQL語法建立資料庫

建立以上四個資料表的SQL指令碼
CREATE TABLE [dbo].[Product](
[ProductUid] [int] IDENTITY(1,1) NOT NULL,
[Type] [nchar](3) NULL,
[ManufacturerUid] [int] NULL,
[Name] [nvarchar](50) NULL,
[Description] [nvarchar](50) NULL,
[PurchaseDate] [smalldatetime] NULL,
[Valid] [int] NULL,
[Price] [int] NULL,
[Memo1] [ntext] NULL,
[Memo2] [ntext] NULL,
[GetSource] [nchar](10) NULL,
[BudgetarySource] [nchar](10) NULL,
[GetDate] [smalldatetime] NULL,
[Property] [nchar](2) NULL,
[ImgUrl] [nchar](50) NULL,
[WebUrl] [nchar](150) NULL,
[Detail] [ntext] NULL,
[SupplierUid] [int] NULL,
CONSTRAINT [PK_Product] PRIMARY KEY CLUSTERED ( [ProductUid] ASC )
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
CREATE TABLE [dbo].[Device](
[DeviceUid] [int] IDENTITY(1,1) NOT NULL,
[ProductUid] [int] NULL,
[Classification] [nchar](10) NULL,
[SequenceNo] [nchar](10) NULL,
[StorageUnits] [nchar](2) NULL,
[Custodian] [nchar](30) NULL,
[EndUser] [int] NULL,
[StoragePos] [nvarchar](20) NULL,
[StoragePosNo] [nchar](10) NULL,
[Status] [nchar](2) NULL,
[InventoryResult] [nchar](2) NULL,
[InventoryDate] [smalldatetime] NULL,
[UpdateUser] [int] NULL,
[UpdateDate] [smalldatetime] NULL,
[Memo] [ntext] NULL,
CONSTRAINT [PK_Device] PRIMARY KEY CLUSTERED ( [DeviceUid] ASC )
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

CREATE TABLE [dbo].[Manufacturer](
[ManufacturerUid] [int] IDENTITY(1,1) NOT NULL,
[CName] [nvarchar](50) NULL,
[EName] [nchar](30) NULL,
[WebUrl] [nchar](50) NULL,
[Tel] [nchar](15) NULL,
[Fax] [nchar](15) NULL,
[Email] [nchar](30) NULL,
[Address] [nvarchar](50) NULL,
CONSTRAINT [PK_Manufacturer] PRIMARY KEY CLUSTERED ( [ManufacturerUid] ASC )
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [dbo].[Supplier](
[SupplierUid] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](50) NULL,
[Contact] [nvarchar](10) NULL,
[ComTel] [nchar](15) NULL,
[Mobile] [nchar](15) NULL,
[Address] [nvarchar](100) NULL,
[WebUrl] [nchar](100) NULL,
CONSTRAINT [PK_Supplier] PRIMARY KEY CLUSTERED ( [SupplierUid] ASC )
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON [PRIMARY]) ON [PRIMARY]

4.新增資料庫圖表：建立各資料表間的關連，如出現無法安裝資料庫圖表支援物件，請參考

5.修改資料表內容：在要修改的資料表上右鍵 / 設計 / 插入或刪除資料行 / 儲存，也可以利拖曳的方式改變欄位的上下順序，變更資料欄位的寬度時要考慮到是否會將超出寬度的原有資料遺失。

資料表修改後，存檔時可能會出現「不允許儲存變更」的錯誤，從 SQL Server 2008 開始 Management Studio 管理工具預設會防止儲存需要資料表重建的變更(Prevent saving changes that require table re-creation)，意思是說，當你在修改資料庫表格結構時，當修正的幅度過大，例如移除欄位、變更欄位順序、變更欄位型態、變更欄位長度、變更是否允許 NULL等狀況，預設都會拒絕你變更資料表結構，導致按下儲存按鈕之後並沒有真正的變更資料結構(Schema)。

解決此問題的方法就是去變更 SQL Server Management Studio (SSMS) 的選項設定，步驟如下：開啟「工具」→「選項」，切換到「設計師」類別，將「防止儲存需要資料表重建的變更」的核取項目取消勾選即可，並按下確定按鈕即可。