表变量、临时表的自增序列

1.MSSQL 2000

DECLARE @tmpDepartments TABLE(SerialID INT IDENTITY(1,1),DepartmentsID INT,DepartmentsCode VARCHAR(50)) --事业部表

CREATE TABLE #tmpAgencies(SerialID INT IDENTITY(1,1),AgenciesID INT,DepartmentsID INT)  --机构表

 

 注：临时表可以进行TRUNCATE TABLE操作，表变量不可以。TRUNCATE TABLE操作后，自增序列又会从1开始计数。

2.MSSQL2005

ALTER PROCEDURE [dbo].[GetDealCustomerCountByUser]
AS
BEGIN
      DECLARE @result TABLE(UserID INT,DealCustomerCount INT,Serial INT)--添加Serial字段作为自增序列
       INSERT INTO @result --ROW_NUMBER() OVER(ORDER BY UserID) 按UserID排序由1开始自增    
       SELECT DISTINCT UserID,0 AS DealCustomerCount,Serial=row_number() over(ORDER BY UserID)  　　   
      FROM CRM_Log_CheckTempCustomer WHERE UpdateTime>=@fromDate AND UpdateTime<@toDate GROUP BY UserID ORDER BY UserID;
END