How to fetch index data in SQL Server?

Question

I have created a clustered index on a table. Now I want to know the script to see what data it holds. I am querying the below statement:

select * from Hrindex

but it throws an error

Msg 208, Level 16, State 1, Line 105
Invalid object name 'hrindex'.

You need to select from the table, not the index – Dec 21 '16 at 07:25 — , Dec 21 '16 at 07:25

score 3 · Accepted Answer · edited May 23 '17 at 10:30

3

Since you are using clustered index so it will sort the existing data rows depending on the column you have selected while creating the clustered Index.It doesn't create any different object or structure to store the indexed data.Yes in a case of a Nonclustered index, indexes do get stored at some different location on physical memory.

Link: What does this sentence mean: Clustered indexes are stored physically on the table?

select * from sys.indexes
where object_id = (select object_id from sys.objects where name = 'MYTABLE')

EXEC sys.sp_helpindex @objname = 'objectname'

edited May 23 '17 at 10:30

Community

1
1

answered Dec 21 '16 at 05:58

vinay koul

348
1
9

how can I Query index data? – Jason Clark Dec 21 '16 at 06:14
1

@JasonClark what exactly Index data means?Do you mean to get the data rows after applying indexes.Also i have edited above solution with one link that might help you. – vinay koul Dec 21 '16 at 06:24
@JasonClark: the "data" of the clustered index is the **TABLE DATA** ... – marc_s Dec 21 '16 at 07:21
@vinay Actually I wanted to know how can we retrieve records from an index using select query? not the physical storage of indexes. – Jason Clark Dec 22 '16 at 04:13

How to fetch index data in SQL Server?

1 Answers1