LISTING 2: Performs Three Bulk Load Tests with and Without TABLOCK

USE Northwind
GO

-- Copy the data to a text file to be loaded in with BULK INSERT.
EXEC master..xp_cmdshell "bcp Northwind..od out c:\OrderDetails500K.txt /T /c "

-- Method 1: Copy the data, then build all the indexes.

-- Remove comments from the DROP if necessary.
-- DROP TABLE load_od

-- Create new empty table.
CREATE TABLE [load_od] (
	[counter] [int] NOT NULL ,
	[OrderID] [int] NOT NULL ,
	[ProductID] [int] NOT NULL ,
	[UnitPrice] [money] NOT NULL ,
	[Quantity] [smallint] NOT NULL ,
	[Discount] [real] NOT NULL ) 
GO

DECLARE @start datetime, @end datetime
SELECT @start = getdate()

-- Remove comment from TABLOCK hint if desired.
BULK INSERT Northwind.dbo.[load_od]
   FROM 'c:\OrderDetails500K.txt'
--   WITH (TABLOCK )

-- Build a clustered index and four nonclustered indexes.
CREATE UNIQUE CLUSTERED INDEX od_PK ON load_od(counter)
CREATE INDEX od_ProductID ON load_od(ProductID)
CREATE INDEX od_UnitPrice ON load_od(UnitPrice)
CREATE INDEX od_Quantity ON load_od(Quantity)
CREATE INDEX od_Discount ON load_od(Discount)
-- Get end time and difference from start time.
SELECT @end = getdate()
SELECT 'Time to copy, then build indexes is ', datediff(ss, @start, @end), 'seconds.'

-- Method 2: Build clustered index, copy the data, 
-- then build all the nonclustered indexes.

-- First rebuild the table.
DROP TABLE load_od
CREATE TABLE [load_od] (
	[counter] [int] NOT NULL ,
	[OrderID] [int] NOT NULL ,
	[ProductID] [int] NOT NULL ,
	[UnitPrice] [money] NOT NULL ,
	[Quantity] [smallint] NOT NULL ,
	[Discount] [real] NOT NULL ) 
GO
CREATE UNIQUE CLUSTERED INDEX od_PK ON load_od(counter)
GO

DECLARE @start datetime, @end datetime
SELECT @start = getdate()

-- Remove comment from TABLOCK hint if desired.
BULK INSERT Northwind.dbo.[load_od]
   FROM 'c:\OrderDetails500K.txt'
 --   WITH (TABLOCK )

-- Build four nonclustered indexes.
CREATE INDEX od_ProductID ON load_od(ProductID)
CREATE INDEX od_UnitPrice ON load_od(UnitPrice)
CREATE INDEX od_Quantity ON load_od(Quantity)
CREATE INDEX od_Discount ON load_od(Discount)

-- Get end time and difference from start time.
SELECT @end = getdate()
SELECT 'Time to build clustered index, copy data, then build nonclustered indexes is ', datediff(ss, 
@start, @end), 'seconds.'

-- Method 3: Build one clustered and four nonclustered indexes, then copy the data.

-- First rebuild the table.
DROP TABLE load_od
CREATE TABLE [load_od] (
	[counter] [int] NOT NULL ,
	[OrderID] [int] NOT NULL ,
	[ProductID] [int] NOT NULL ,
	[UnitPrice] [money] NOT NULL ,
	[Quantity] [smallint] NOT NULL ,
	[Discount] [real] NOT NULL ) 
GO
CREATE UNIQUE CLUSTERED INDEX od_PK ON load_od(counter)
GO

-- Build four nonclustered indexes.
CREATE INDEX od_ProductID ON load_od(ProductID)
CREATE INDEX od_UnitPrice ON load_od(UnitPrice)
CREATE INDEX od_Quantity ON load_od(Quantity)
CREATE INDEX od_Discount ON load_od(Discount)

DECLARE @start datetime, @end datetime
SELECT @start = getdate()

-- Remove comment from TABLOCK hint if desired.
BULK INSERT Northwind.dbo.[load_od]
   FROM 'c:\OrderDetails500K.txt'
--    WITH (TABLOCK )

-- Get end time and difference from start time.
SELECT @end = getdate()
SELECT 'Time to build all indexes, then copy data is ', datediff(ss, @start, @end), 'seconds.'