-- Example:
-- Ben Brugman
-- 20130107
--

-- Altered from the first Posting, NOW heap is default.
-- First few anwsered to this mail had the cluster as default.
--
-- Example to test index behavior.
-- Runs as supplied. (Heap table is measured).
-- Comment out or run relevant parts.
--

/* BUILD CLUSTER */
/* CLUSTER */
/* CLUSTER */
/* To prevent any potential data loss issues, you should review this script in detail before running it outside the context of the database designer.*/
BEGIN TRANSACTION
GO
CREATE TABLE dbo.A_Table
	(
	A int NOT NULL,
	B int NOT NULL,
	C int NOT NULL,
	Text_field varchar(300) NULL,
	Number_off_other_fields varchar(300) NULL
	)  ON [PRIMARY]
GO
ALTER TABLE dbo.A_Table ADD CONSTRAINT
	PK_A_Table PRIMARY KEY CLUSTERED 
	(
	A,
	B,
	C
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO
ALTER TABLE dbo.A_Table SET (LOCK_ESCALATION = TABLE)
GO
COMMIT

--
-- Script creates the A_table twice first as heap second as clustered table. 
-- Run the appropriete parts of the script. (Select parts or comment out parts).
--
drop table A_Table

/* BUILD HEAP */
/* HEAP */
/* HEAP */
/* To prevent any potential data loss issues, you should review this script in detail before running it outside the context of the database designer.*/

BEGIN TRANSACTION
GO
CREATE TABLE dbo.A_Table
	(
	A int NOT NULL,
	B int NOT NULL,
	C int NOT NULL,
	Text_field varchar(300) NULL,
	Number_off_other_fields varchar(300) NULL
	)  ON [PRIMARY]
GO
ALTER TABLE dbo.A_Table ADD CONSTRAINT
	PK_A_Table PRIMARY KEY NONCLUSTERED 
	(
	A,
	B,
	C
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO
ALTER TABLE dbo.A_Table SET (LOCK_ESCALATION = TABLE)
GO
COMMIT

/*GENERATE DATA*/
/*GENERATE DATA*/
/*GENERATE DATA*/
;
WITH
  L0 AS(SELECT 0 AS c UNION ALL SELECT 0),   -- 2
  L1 AS(select 0 as x from L0 A, L0 b),      -- 2 ^2 = 4
  L2 AS(select 0 as x from L1 A, L1 b),      -- 4 ^2 = 16
  L3 AS(select 0 as x from L2 A, L2 b),      -- 16 ^2 = 256
  L4 AS(select 0 as x from L3 A, L3 b),      -- 256 ^2 = 65536
  L5 AS(select 0 as x from L4 A, L4 b),      -- 65536 ^2 = ruim 4 E9
  L9 AS(Select *, row_number() OVER(PARTITION BY x order by x ) as nr from L5) -- voeg rijnummers toe
insert into A_Table 
SELECT 
   TA.NR,
   TB.NR,
   TC.NR,
   CONVERT(VARCHAR(300),TA.NR)+'   '+
   CONVERT(VARCHAR(300),TB.NR)+'   '+
   CONVERT(VARCHAR(300),TC.NR)+'   ',
   'sum  '+CONVERT(VARCHAR(300),TA.NR+TB.nr+TC.nr)+'   product  '+   CONVERT(VARCHAR(300),TA.NR*TB.nr*TC.nr)
   from L9 TA, L9 TB, L9 TC where 
                           TA.nr*TB.nr <1000 and    -- change numbers to
                           TA.nr*TC.nr <1000 and    -- change number of rows
                           Tb.nr*TC.nr <1000 and    -- or influence distribution
                           TA.nr <1000 and 
                           TB.nr <1000 and 
                           TC.nr <1000
-- the overdetermined where clause is more efficient than 'short' where clause.

select top 3000 * from A_Table 

SET STATISTICS TIME ON
SET STATISTICS IO  ON

/*BENCHMARK OR TEST*/
/*BENCHMARK OR TEST*/
/*BENCHMARK OR TEST*/

dbcc dropcleanbuffers
dbcc freeproccache
--
-- selection in order of the fields.
--
select * from A_Table where A = 33 and B = 26    -- Optimised for the index.

dbcc dropcleanbuffers
dbcc freeproccache
--
-- selection of fields which are not in the order of the index
--
select * from A_Table where A = 33 and C = 26    -- Not optimised for th index

drop table A_Table                               -- Clean up

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

We walk in the dark places no others will enter
We stand on the bridge and no one may pass