-- a) CREATE CLUSTERED INDEX ci_k ON dbo.T1(k);
--    SELECT * FROM dbo.T1				   - scanning in (ORDERED Cs.) index order
--    SELECT * FROM dbo.T1 WITH (NOLOCK);  - scanning in allocation order
--    SELECT * FROM dbo.T1 WITH (TABLOCK); - scanning in allocation order

-- b) CREATE NONCLUSTERED INDEX ci_k ON dbo.T1(k);
--    SELECT * FROM dbo.T1				   - scanning in index order
--    SELECT * FROM dbo.T1 WITH (NOLOCK);  - scanning in index order
--    SELECT * FROM dbo.T1 WITH (TABLOCK); - scanning in index order

PRINT RAND(176); -- !!
SET NOCOUNT ON;
--USE master;
--GO
--IF DB_ID('DB1') IS NOT NULL DROP DATABASE DB1;
--GO
--CREATE DATABASE DB1;
--GO
--USE DB1;
--GO

use tempdb
IF OBJECT_ID('dbo.T1', 'U') IS NOT NULL DROP TABLE dbo.T1;
GO
CREATE TABLE dbo.T1
(
  k INT NOT NULL,
  s CHAR(2000) NOT NULL DEFAULT('s')
);

---- a)
CREATE CLUSTERED INDEX ci_k ON dbo.T1(k);
---- b)
--CREATE NONCLUSTERED INDEX ci_k ON dbo.T1(k);
GO
DECLARE @beg datetime, @end datetime, @dtm datetime;
set @beg = GETDATE();
DECLARE @i AS INT, @r AS INT;
SET @i = 1;
WHILE @i <= 300 -- 300 10000
BEGIN
  SET @r = CAST(RAND()*1000 AS INT);
  --IF @i < 20 PRINT(@r);
  INSERT INTO dbo.T1(k)
    --VALUES(@i);
    VALUES(@r);
  SET @i = @i + 1;
END

--SELECT * FROM dbo.T1
--SELECT * FROM dbo.T1 WITH (NOLOCK);
SELECT * FROM dbo.T1 WITH (TABLOCK);
set @end = GETDATE();
DECLARE @dt varchar(10)
set @dt = 'Cas: ' +  cast( DATEDIFF(MILLISECOND, @beg, @end) as varchar(10) )
RAISERROR (@dt, 11,-1)

----RAISERROR ('q', -13,1)
----print DATEDIFF(MILLISECOND, @beg, @end)
----print( @end)

------ DBCC IND and DBCC PAGE
------ DBCC PAGE (master, 1, 1, 0);

--
-- To guarantee consistency, in all cases besides when 
-- NOLOCK or TABLOCK are specified, SQL Server scans the data 
-- in (ORDERED Cs.) index order by following the linked list.
--
-- Allocation order scans (using IAM pages) are used to read data in two cases: 
-- when NOLOCK or TABLCOK are specified. 
--
-- These locking hints override the current transaction isolation level for the session.
-- When you specify NOLOCK you are asking SQL Server to return rows even if they are currently 
-- being modifyed by other transactions. 
-- The NOLOCK hint is employed by many users to avoid contention on tables where updates 
-- are performed concurrently with selects. - http://blogs.msdn.com/sqlcat/archive/2007/02/01/previously-committed-rows-might-be-missed-if-nolock-hint-is-used.aspx
-- The down side is that, if the other transaction 
-- rolls back, the data that you just read will become incorrect. 
-- You should carefully decide if you can afford a dirty read. 
-- For example, if you want to get a list of customers, you can use NOLOCK(in most cases). 
-- But while checking the available credit limit of the customer, you should not use NOLOCK.
--