-----start-----
Create Table DummyTable3
(
EmpId Int,
EmpName Varchar(8000)
)
-----end-----
2. Insert Dummy Data
-----start----
Insert Into DummyTable3 Values (4, Replicate ('d',2000))
GO
Insert Into DummyTable3 Values (6, Replicate ('f',2000))
GO
Insert Into DummyTable3 Values (1, Replicate ('a',2000))
GO
Insert Into DummyTable3 Values (3, Replicate ('c',2000))
GO
-----end-----
3. Check for the index
-----start-----
select name, indid, rows, * from sysindexes
where name = 'DummyTable3'
-----end-----
If indid=0 --> there no index
4. Check PageID without Index
-----start-----
DBCC TRACEON (3604)
GO
Declare @DBID Int, @TableID Int
Select @DBID = db_id(), @TableID = object_id('DummyTable3')
DBCC ind(@DBID, @TableID, -1)
GO
-----end-----
5. Check Row Allocator in Page
-----start-----
DBCC TRACEON (3604)
GO
Declare @DBID Int, @TableID Int
Select @DBID = db_id(), @TableID = object_id('DummyTable3')
DBCC page(@DBID, 1, 160, 3)
GO
-----end-----
The row location display in one of two ways:
* If the table does not have a clustered index, the row locator will be combination of fileno, pageno and the no of rows in a page.
* If the table does have clustered index, the row location will be clustered index key value.
Source:
http://www.sql-server-performance.com/2004/index-data-structures/2/