SQL Server 常見操作完整範例：資料庫、資料表、預存程序、檢視表、索引

本文彙整 SQL Server 日常開發與維護的核心操作範例，涵蓋資料庫建立、資料表定義、預存程式（含參數、動態 SQL、輸出/回傳值）、檢視表、索引建立與管理，適合開發人員快速查閱與複製套用。

1. 基礎環境建立

CREATE DATABASE Personnel_training_db;
GO
USE Personnel_training_db;
GO

CREATE TABLE dept (
    deptid int PRIMARY KEY IDENTITY(1,1),
    deptname varchar(20) NOT NULL,
    remark varchar(20) NOT NULL
);
GO

INSERT INTO dept VALUES ('教職部', '');

2. 預存程序進階範例

2.1 基本查詢程序

CREATE PROC proc_dept @deptname varchar(20)
AS
SELECT * FROM dept WHERE deptname = @deptname;
GO
EXEC proc_dept '教職部';

2.2 動態 SQL 查詢（防止 SQL 注入請改用參數化）

CREATE PROC proc_dept2 @deptname varchar(20)
AS
EXEC('SELECT * FROM dept WHERE deptname = ''' + @deptname + '''');
GO
EXEC proc_dept2 '教職部';

2.3 新增、刪除、多條件查詢

-- 新增
CREATE PROC proc_add_dept @deptname varchar(20), @remark varchar(20)
AS INSERT INTO dept VALUES (@deptname, @remark);
GO

-- 刪除（支援多 ID 以逗號分隔）
CREATE PROC proc_del_deptid @deptid varchar(20)
AS EXEC('DELETE FROM dept WHERE deptid IN (' + @deptid + ')');
GO
EXEC proc_del_deptid '1,2';

-- 多條件動態查詢
CREATE PROC proc_like_query_dept @deptname varchar(20), @remark varchar(20)
AS
BEGIN
    IF @deptname <> '' AND @remark <> ''
        SELECT * FROM dept WHERE deptname = @deptname AND remark = @remark;
    ELSE IF @remark <> '' AND @deptname = ''
        SELECT * FROM dept WHERE remark = @remark;
    ELSE IF @deptname <> '' AND @remark = ''
        SELECT * FROM dept WHERE deptname = @deptname;
    ELSE
        SELECT * FROM dept;
END
GO
EXEC proc_like_query_dept '', '';

2.4 輸出參數與回傳值

-- 輸出參數
CREATE PROC proc_set @deptid int OUTPUT
AS SELECT @deptid = deptid FROM dept WHERE deptname = '學術部' AND remark = '這個不能還有點意思';
GO
DECLARE @deptid int;
EXEC proc_set @deptid OUTPUT;
PRINT '獲取的 deptid 是：' + CAST(@deptid AS varchar(10));

-- 回傳值（僅限 int）
CREATE PROC proc_return
AS
DECLARE @deptcount int;
SELECT @deptcount = COUNT(*) FROM dept;
RETURN @deptcount;
GO
DECLARE @deptcount int;
EXEC @deptcount = proc_return;
PRINT '總筆數：' + CAST(@deptcount AS varchar(10));

2.5 LIKE 模糊查詢（動態 SQL 版）

CREATE PROC proc_query_like @like varchar(20)
AS
DECLARE @sql varchar(1024);
SET @sql = 'SELECT * FROM dept WHERE 1=1';
SET @sql = @sql + ' AND deptname LIKE ''%' + @like + '%''';
SET @sql = @sql + ' OR remark LIKE ''%' + @like + '%''';
PRINT @sql;
EXEC(@sql);
GO
EXEC proc_query_like '了';

3. 檢視表

-- 建立
CREATE VIEW view_dept AS SELECT * FROM dept;
GO
SELECT * FROM view_dept;

-- 刪除
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'view_dept')
    DROP VIEW view_dept;
GO

檢視表優點：資料聚焦、簡化複雜聯結、資料客製化、安全性控管。
注意：單一檢視表僅能包含一條 SELECT 陳述式。

4. 索引

索引類型對照

建立與使用

-- 建立非叢集索引，填滿因子 50%
CREATE NONCLUSTERED INDEX index_dept ON dept(deptname) WITH FILLFACTOR = 50;
GO

-- 強制使用索引
SELECT * FROM dept WITH (INDEX = index_dept);

-- 刪除
IF EXISTS (SELECT * FROM sysindexes WHERE name = 'index_dept')
    DROP INDEX dept.index_dept;
GO

索引優缺點

• 優點：大幅提升查詢速度。
• 缺點：佔用額外儲存空間；INSERT/UPDATE/DELETE 需同步維護索引，增加寫入成本。

善用上述範例模板，可加速日常開發並避免常見語法錯誤。