SET STATISTICS TIME OFF
GO

USE TEMPDB
GO

SET NOCOUNT ON
GO

CREATE TABLE DBO.I1(
I1 INT NOT NULL
)

CREATE TABLE DBO.C1(
C1 VARCHAR( 10 ) NOT NULL
)

print 'Tables created'
print ''
GO

DECLARE @I INT,
        @C VARCHAR( 10 )

SET @I =  1 
WHILE @I <=  2000000 
BEGIN
	INSERT INTO I1 VALUES(@I)
	INSERT INTO C1 VALUES(SUBSTRING('AB00000000', 1 , 10 -LEN(CONVERT(VARCHAR( 8 ),@I)))+CONVERT(VARCHAR( 8 ),@I))
	SET @I = @I+ 1 
END

print 'Tables filled with data'
print '--------------------------------------------------------'
print ''
SET NOCOUNT OFF
GO

print 'Select data from non indexed I1'
print ''
SET STATISTICS TIME ON 
SELECT I1 FROM I1 WHERE I1 BETWEEN  199  AND  27654  OR I1 BETWEEN  1045564  AND  1050031 
SET STATISTICS TIME OFF 
print '--------------------------------------------------------'
GO

print 'Select data from non indexed C1'
print ''
SET STATISTICS TIME ON 
SELECT C1 FROM C1 WHERE C1 BETWEEN 'AB00000199' AND 'AB00027654' OR C1 BETWEEN 'AB01045564' AND 'AB01050031'
SET STATISTICS TIME OFF 
print '--------------------------------------------------------'
GO

print 'Creating UNIQUE CLUSTERED INDEX ON I1'
print ''
SET STATISTICS TIME ON 
CREATE UNIQUE CLUSTERED INDEX  PK_I1 ON I1(I1)
SET STATISTICS TIME OFF 
print '--------------------------------------------------------'
GO

print 'Creating UNIQUE CLUSTERED INDEX ON C1'
print ''
SET STATISTICS TIME ON 
CREATE UNIQUE CLUSTERED INDEX  PK_C1 ON C1(C1)
SET STATISTICS TIME OFF 
print '--------------------------------------------------------'
GO

print 'Select data from indexed I1'
print ''
SET STATISTICS TIME ON 
SELECT I1 FROM I1 WHERE I1 BETWEEN  199  AND  27654  OR I1 BETWEEN  1045564  AND  1050031 
SET STATISTICS TIME OFF 
print '--------------------------------------------------------'
GO

print 'Select data from indexed C1'
print ''
SET STATISTICS TIME ON 
SELECT C1 FROM C1 WHERE C1 BETWEEN 'AB00000199' AND 'AB00027654' OR C1 BETWEEN 'AB01045564' AND 'AB01050031'
SET STATISTICS TIME OFF 
print '--------------------------------------------------------'
GO

print 'Rebuilding index for I1'
print ''
SET STATISTICS TIME ON 
DBCC DBREINDEX(I1)
SET STATISTICS TIME OFF 
print '--------------------------------------------------------'
GO

print 'Rebuilding index for C1'
print ''
SET STATISTICS TIME ON 
DBCC DBREINDEX(C1)
SET STATISTICS TIME OFF 
print '--------------------------------------------------------'
GO

print 'Select data from indexed I1'
print ''
SET STATISTICS TIME ON 
SELECT I1 FROM I1 WHERE I1 BETWEEN  10199  AND  127654  OR I1 BETWEEN  1345093  AND  1500031 
SET STATISTICS TIME OFF 
print '--------------------------------------------------------'
GO

print 'Select data from indexed C1'
print ''
SET STATISTICS TIME ON 
SELECT C1 FROM C1 WHERE C1 BETWEEN 'AB00010199' AND 'AB00127654' OR C1 BETWEEN 'AB01345093' AND 'AB01500031'

SET STATISTICS TIME OFF 
print '--------------------------------------------------------'
GO

print 'Droping tables'
print ''

DROP TABLE I1, C1
print '-------------------DONE---------------------------------'
GO