不要在SQL Server中盲目地追求一句处理 （2）

USE tempdb

GO

 

SET NOCOUNT ON

--======================================

--创建测试环境

--======================================

RAISERROR('创建测试环境', 10, 1) WITH NOWAIT

-- Table A

CREATE TABLE [dbo].A(

    [TranNumber] [int] IDENTITY(1, 1) NOT NULL,

    [INVNO] [char](8) NOT NULL,

    [ITEM] [char](15) NULL DEFAULT (''),

    PRIMARY KEY([TranNumber])

)

 

CREATE INDEX [indexONinvno] ON [dbo].A([INVNO])

CREATE INDEX [indexOnitem] ON [dbo].A ([ITEM])

CREATE INDEX [indexONiteminnvo] ON [dbo].A([INVNO], [ITEM])

GO

 

-- Table B

CREATE TABLE [dbo].B(

    [ItemNumber] [char](15) NOT NULL DEFAULT (''),

    [CompanyCode] [char] (4) NOT NULL,

    [OwnerCompanyCode] [char](4) NULL,

    PRIMARY KEY([ItemNumber], [CompanyCode])

)

 

CREATE INDEX [ItemNumber] ON [dbo].B([ItemNumber])

CREATE INDEX [CompanyCode] ON [dbo].B([CompanyCode])

CREATE INDEX [OwnerCompanyCode] ON [dbo].B([OwnerCompanyCode])

GO

 

--======================================

--生成测试数据

--======================================

RAISERROR('生成测试数据', 10, 1) WITH NOWAIT

INSERT [dbo].A([INVNO], [ITEM])

SELECT LEFT(NEWID(), 8), RIGHT(NEWID(), 15)

FROM syscolumns A, syscolumns B

 

INSERT [dbo].B([ItemNumber], [CompanyCode], [OwnerCompanyCode])

SELECT RIGHT(NEWID(), 15), LEFT(NEWID(), 4), LEFT(NEWID(), 4)

FROM syscolumns A, syscolumns B

GO