T-SQL查询笔记2：生成一个100万测试数据的表

快速生成一个从1到1000000数据的序列表

USE master;
GO

IF OBJECT_ID('dbo.Nums', 'U') IS NOT NULL
  DROP TABLE dbo.Nums;
CREATE TABLE dbo.Nums(n INT NOT NULL PRIMARY KEY);

DECLARE @max AS INT, @rc AS INT;
SET @max = 1000000;
SET @rc = 1;

INSERT INTO dbo.Nums(n) VALUES(1);
WHILE @rc * 2 <= @max
BEGIN
  INSERT INTO dbo.Nums(n) SELECT n + @rc FROM dbo.Nums;
  SET @rc = @rc * 2;
END

INSERT INTO dbo.Nums(n)
  SELECT n + @rc FROM dbo.Nums WHERE n + @rc <= @max;
GO

 利用上面的表，生成一个从20050101至20081231的测试数据的表(包含部分2004年12月数据)

DECLARE
  @numorders   AS INT,
  @numcusts    AS INT,
  @numemps     AS INT,
  @numshippers AS INT,
  @numyears    AS INT,
  @startdate   AS DATETIME;

SELECT
  @numorders   =   1000000,
  @numcusts    =     20000,
  @numemps     =       500,
  @numshippers =         5,
  @numyears    =         4,
  @startdate   = '20050101';
  
IF OBJECT_ID('dbo.Orders', 'U') IS NOT NULL
  DROP TABLE dbo.Orders;
CREATE TABLE dbo.Orders
(
  orderid   INT        NOT NULL,
  custid    CHAR(11)   NOT NULL,
  empid     INT        NOT NULL,
  shipperid VARCHAR(5) NOT NULL,
  orderdate DATETIME   NOT NULL,
  filler    CHAR(155)  NOT NULL DEFAULT('a')
);

INSERT INTO dbo.Orders(orderid, custid, empid, shipperid, orderdate)
  SELECT n AS orderid,
    'C' + RIGHT('000000000'
            + CAST(
                1 + ABS(CHECKSUM(NEWID())) % @numcusts
                AS VARCHAR(10)), 10) AS custid,
    1 + ABS(CHECKSUM(NEWID())) % @numemps AS empid,
    CHAR(ASCII('A') - 2
           + 2 * (1 + ABS(CHECKSUM(NEWID())) % @numshippers)) AS shipperid,
      DATEADD(day, n / (@numorders / (@numyears * 365.25)), @startdate)
        - CASE WHEN n % 10 = 0
            THEN 1 + ABS(CHECKSUM(NEWID())) % 30
            ELSE 0 
          END AS orderdate
  FROM master.dbo.Nums
  WHERE n <= @numorders
  ORDER BY CHECKSUM(NEWID());

 

DATEADD(day, n / (@numorders / (@numyears * 365.25)), @startdate)
- CASE WHEN n % 10 = 0
            THEN 1 + ABS(CHECKSUM(NEWID())) % 30
            ELSE 0
          END

DATEADD的第二个参数会忽略掉小数点后面的数，DATEADD(day,0.1,'20050101')和DATEADD(day,0.9,'20050101')的结果一样是2005-01-01，所以上面有多条相同日期的记录，
@numorders / (@numyears * 365.25) 为每个相同日期的记录数1000000 / (4 * 365.25) = 684
即20050101有684条记录，20050102有684条记录，以此类推

NEWID 返回的是uniqueidentifier类型的唯一值
CHECKSUM 返回按照表的某一行或一组表达式计算出来的校验和值，结果可为负也可为正
ABS 返回指定数值表达式的绝对值（正值）

例如执行下面语句：
DECLARE  @s AS uniqueidentifier  
SET @s = NEWID()
select @s,CHECKSUM(@s),ABS(CHECKSUM(@s))
结果为：
------------------------------------ ----------- -----------
D336A32E-C211-4B2B-8F3B-008314D40024 -1959770923 1959770923

ABS(CHECKSUM(NEWID())) % 30 返回0-29范围的随机数
1 + ABS(CHECKSUM(NEWID())) % 30 即返回1-30范围内的随机数

- CASE WHEN n % 10 = 0
            THEN 1 + ABS(CHECKSUM(NEWID())) % 30
            ELSE 0
          END
则是当n % 10 = 0时，减去1至30内的范围数，所以表dbo.Orders除了20050101至20081231范围的数据，还有一小部分2004年12月的数据。
由于满足n % 10 = 0条件的数据684/10=68，因此>20050101的相同日期的记录数在684加减68即616-752范围内，可查询
select orderdate, COUNT(*) from dbo.Orders
group by orderdate