SELECT时实现系列号的输出

今天一个同事问我如何实现SELECT时实现输出系列号，比如：

ID NAME
———– ——————-
1 sysobjects
2 sysindexes
3 syscolumns
4 systypes
5 syscomments
6 sysfiles1
7 syspermissions
8 sysusers
9 sysproperties

其中ID是SELECT的时候生成的。

我们知道 oracle 中有ROWNUM这个伪列(pseudocolumn),可以直接实现这个功能，如：

SELECT ROWNUM AS ID,TABLE_NAME FROM ALL_TABLES WHERE ROW<10;

ID TABLE_NAME

———- ——————————
1 DUAL

2 SYSTEM_PRIVILEGE_MAP
3 TABLE_PRIVILEGE_MAP
4 STMT_AUDIT_OPTION_MAP
5 AUDIT_ACTIONS
6 PSTUBTBL
7 ODCI_SECOBJ$
8 ODCI_WARNINGS$
9 DEF$_TEMP$LOB

在SQL SERVER中没有这样的功能，所以只能另想办法。察看了一下BookOnline，发现可以用IDENTITY函数和临时表来实现。关于IDENTITY函数，BookOnline是这样说明的：

IDENTITY（函数）
只用在带有 INTO table 子句的 SELECT 语句中，以将标识列插入到新表中。

语法
IDENTITY ( data_type [ , seed , increment ] ) AS column_name

参数
data_type
标识列的数据类型。标识列的有效数据类型可以是任何整数数据类型分类的数据类型（bit 数据类型除外），也可以是 decimal 数据类型。

seed
要指派给表中第一行的值。给每一个后续行指派下一个标识值，该值等于上一个 IDENTITY 值加上 increment 值。如果既没有指定 seed，也没有指定 increment，那么它们都默认为 1。

increment
用来添加到 seed 值以获得表中连续行的增量。

column_name
将插入到新表中的列的名称。

返回类型
返回与 data_type 相同的类型。

我们可以这样实现我们的功能，如下：

USE PUBS
SET NOCOUNT ON
SELECT IDENTITY(INT,1,1) AS ID,NAME INTO #TEMP FROM SYSOBJECTS
SELECT * FROM #TEMP
DROP TABLE #TEMP
SET NOCOUNT OFF

结果如下：

ID NAME
———– ———————————————–
1 authors
2 byroyalty
3 CK__authors__au_id__77BFCB91
4 CK__authors__zip__79A81403
5 CK__jobs__max_lvl__145C0A3F
6 CK__jobs__min_lvl__1367E606
7 CK__publisher__pub_i__7C8480AE
8 CK_emp_id
9 DF__authors__phone__78B3EFCA
10 DF__employee__hire_d__1FCDBCEB
11 DF__employee__job_id__1B0907CE
12 DF__employee__job_lv__1CF15040
13 DF__employee__pub_id__1DE57479
14 DF__jobs__job_desc__1273C1CD
15 DF__publisher__count__7D78A4E7
16 DF__titles__pubdate__023D5A04
17 DF__titles__type__00551192
18 discounts
19 employee

其实也可以这样实现：

USE PUBS
SET NOCOUNT ON
DECLARE @I INT
SET @I=0
SELECT 0 AS ID,NAME INTO #TEMP FROM SYSOBJECTS
UPDATE #TEMP SET @I=@I+1,ID=@I
SELECT * FROM #TEMP
DROP TABLE #TEMP
SET NOCOUNT OFF

结果如下：

ID name
———– ————————————————–
1 authors
2 byroyalty
3 CK__authors__au_id__77BFCB91
4 CK__authors__zip__79A81403
5 CK__jobs__max_lvl__145C0A3F
6 CK__jobs__min_lvl__1367E606
7 CK__publisher__pub_i__7C8480AE
8 CK_emp_id
9 DF__authors__phone__78B3EFCA
10 DF__employee__hire_d__1FCDBCEB
11 DF__employee__job_id__1B0907CE
12 DF__employee__job_lv__1CF15040
13 DF__employee__pub_id__1DE57479
14 DF__jobs__job_desc__1273C1CD
15 DF__publisher__count__7D78A4E7
16 DF__titles__pubdate__023D5A04
17 DF__titles__type__00551192
18 discounts
19 employee

结果一样，但相比较第一种方法简单，性能上也有很大的优势，第二种方法比较适合更新已经存在的TABLE里的ID字段，让其按照某种方式进行排序。