在数据库管理中，经常需要为某些列生成唯一的索引，例如任务编号或步骤序列。本文将介绍两种方法：第一种是使用用户定义的函数在表插入操作时自动生成一个唯一代码；第二种是使用存储过程来插入带有指定或默认索引号的行。
首先，需要打开SQLServer Management Studio (SSMS) 并创建一个名为DemoTaskList的示例数据库。然后在新的查询窗口中，创建第一个表：
USE DemoTaskList;
GO
CREATE TABLE Person(
ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
[Name] VARCHAR(100) UNIQUE NOT NULL
);
-- 添加一些示例数据...
INSERT Person([Name]) VALUES ('Richard Nixon');
INSERT Person([Name]) VALUES ('Bill Clinton');
GO
接下来，需要创建一个用户定义的函数，用于自动创建唯一值，这个值是由Person表中的Person.Name列和Job表中Job的ID（即将创建...）组合而成的字符串操作：
CREATE FUNCTION udf_SET_JobCode(@PersonID INT, @ID INT) RETURNS VARCHAR(10) AS BEGIN
-- 获取由@PersonID引用的Person
DECLARE @Person VARCHAR(100) = (SELECT [Name] FROM Person WHERE ID = @PersonID);
-- @ID输入的VARCHAR副本
DECLARE @RET VARCHAR(10) = CAST(@ID AS VARCHAR(10));
-- @RET变量的CHAR长度
DECLARE @LEN INT = LEN(@RET);
SET @RET = UPPER(LEFT(@Person, 3)) +
-- 魔术（参见BOL以了解STUFF函数的工作方式）
STUFF(@RET, 1, 0, LEFT('0000000', 7 - @LEN));
-- 函数定义/语法
RETURN @RET;
END;
现在，非常重要的Job表调用UDF作为引用：
CREATE TABLE Job(
ID INT Identity(1,1) NOT NULL PRIMARY KEY,
PersonID INT NOT NULL FOREIGN KEY REFERENCES Person(ID),
JobCode AS dbo.udf_SET_JobCode(PersonID, ID),
JobDescription VARCHAR(200) NOT NULL
);
-- 如所见，JobCode列定义引用了用户定义的函数，并传递了两个参数：PersonID和ID，为返回自定义值。
自定义值由UDF返回，由人名的前三个字母和自动生成的Identity值组成，填充到7位数字。阅读BOL文档以了解STUFF函数的工作原理 - 简而言之，它将一个字符串插入到另一个字符串中。
让通过为Richard Nixon添加几个新工作来测试这一点：
INSERT Job(PersonID, JobDescription) VALUES (1, '成为美国总统');
INSERT Job(PersonID, JobDescription) VALUES (1, '变得臭名昭著');
-- 选择数据...
SELECT * FROM Job;
GO
结果：
| ID | PersonID | JobCode | JobDescription |
|---|---|---|---|
| 1 | 1 | RIC0000001 | 成为美国总统 |
| 2 | 1 | RIC0000002 | 变得臭名昭著 |
正如预期的那样，每次INSERT操作都会为人自动生成一个JobCode。让为Bill Clinton添加一个工作：
INSERT Job(PersonID, JobDescription) VALUES (2, '获得恶名');
GO
-- 选择数据...
SELECT * FROM Job;
GO
结果：
| ID | PersonID | JobCode | JobDescription |
|---|---|---|---|
| 1 | 1 | RIC0000001 | 成为美国总统 |
| 2 | 1 | RIC0000002 | 变得臭名昭著 |
| 3 | 2 | BIL0000003 | 获得恶名 |
现在，想构建一个表，用于保存分配给个人的工作所需的任务。希望在查询表时按顺序列出任务，并且希望能够在列表中指定的位置插入新任务。例如，如果不存在任务，则在任务编号1处添加新任务。如果任务存在，可以根据标准在列表中指定的位置或列表末尾插入任务。让看看脚本：
CREATE TABLE Task(
ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
JobID INT NOT NULL FOREIGN KEY REFERENCES Job(ID),
TaskNo INT NOT NULL,
TaskDescription VARCHAR(200)
);
GO
CREATE PROCEDURE usp_INSERT_Task(
@JobCode VARCHAR(10),
@TaskNo INT = NULL,
@TaskDescr VARCHAR(200)
)
AS BEGIN
SET NOCOUNT ON;
-- 获取JobID
DECLARE @JobID INT = (SELECT ID FROM Job WHERE JobCode = @JobCode);
-- 获取工作的最大TaskNo
DECLARE @MAX INT = (SELECT MAX(TaskNo) FROM Task t JOIN Job j ON t.JobID = j.ID WHERE j.JobCode = @JobCode);
-- 花哨的错误处理块
BEGIN TRY
BEGIN TRANSACTION;
-- 检查TaskNo是否有效，否则设置默认值
SET @TaskNo = (
SELECT CASE ISNULL(@TaskNo, 0) WHEN 0 THEN CASE ISNULL(@MAX, 0) WHEN 0 THEN 1 ELSE @MAX + 1 END ELSE CASE WHEN @TaskNo > @MAX THEN @MAX + 1 ELSE @TaskNo END END
);
DECLARE @Count INT = @MAX;
WHILE @Count >= @TaskNo BEGIN
-- 如果@MAX >= @TaskNo 更新TaskNos >=
UPDATE Task SET TaskNo = TaskNo + 1 WHERE JobID = @JobID AND TaskNo = @Count SET @Count = @Count - 1 -- 递减计数器 END INSERT Task(JobID, TaskNo, TaskDescription) VALUES (@JobID, @TaskNo, @TaskDescr) COMMIT TRANSACTION; -- 提交事务 END TRY BEGIN CATCH ROLLBACK; -- 执行错误处理 BEGIN RAISERROR('错误处理INSERT语句或Task表。', 15, 1) RETURN -100 END END CATCH RETURN END;
就是这样 - 迭代TaskNo，将它们增加1，然后像这样在指定位置插入新记录：
EXEC dbo.usp_INSERT_Task 'RIC0000001', NULL, '竞选班长';
GO
EXEC dbo.usp_INSERT_Task 'RIC0000001', 1, '加入学生会';
GO
EXEC dbo.usp_INSERT_Task 'RIC0000001', 5, '竞选美国总统';
GO
SELECT p.[Name], j.JobCode, t.TaskNo, t.TaskDescription FROM Task t JOIN Job j ON t.JobID = j.ID JOIN Person p ON j.PersonID = p.ID WHERE p.ID = 1 ORDER BY p.[Name], j.JobCode, t.TaskNo;
GO
结果：
| Name | JobCode | TaskNo | TaskDescription |
|---|---|---|---|
| Richard Nixon | RIC0000001 | 1 | 加入学生会 |
| Richard Nixon | RIC0000001 | 2 | 竞选班长 |
| Richard Nixon | RIC0000001 | 3 | 竞选美国总统 |
正如所见，存储过程能够根据@MAX的值确定默认位置，并根据规则插入具有正确TaskNo值的新行。
感兴趣的要点：
UDF使用各种字符串函数：CAST、LEN、LEFT和STUFF。请参阅Books On Line以详细了解如何使用这些内置函数。