Indexes and data - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Indexes and data

Author

Topic

RobVG
Starting Member

42 Posts

Posted - 2004-07-13 : 12:38:58

quote:
BOL Indexes/composite: Creating indexes that cover a query can improve performance because all the data for the query is contained within the index itself; only the index pages, not the data pages, of the table must be referenced to retrieve the data, thereby reducing overall I/O

I don't get it. I thought indexes only contained pointers to data on data pages?

Kristen
Test

22859 Posts

Posted - 2004-07-13 : 13:41:54

All the data that SQL needs to satisfy the SELECT is available in the INDEX, so it doesn;t bother to load the record itself.


CREATE TABLE MyTable
(
	ID int,
	Foo varchar(10),
	bar varchar(10)
)
GO
INSERT MyTable 
SELECT 1, 'aaa', 'AAA' UNION ALL
SELECT 2, 'bbb', 'BBB' UNION ALL
SELECT 3, 'ccc', 'CCC'

CREATE INDEX MyIndex ON MyTable
(
	ID,
	Foo
)

SET SHOWPLAN_TEXT ON
GO
SELECT * FROM MyTable
GO
SELECT ID, Foo FROM MyTable
GO
SET SHOWPLAN_TEXT OFF
GO
DROP TABLE MyTable
GO

Have a look at the difference in the PLANs for the two above, the second doesn't go near the actual records

Kristen

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2004-07-13 : 13:47:17

quote:
Originally posted by RobVG
I don't get it. I thought indexes only contained pointers to data on data pages?

An index contains data and a pointer. If your select statement only selects the columns contained in an index (a covered index for example), the optimizer will choose to access only the index.

If your select statement selects more columns than an index contains, the pointer is used to go back to the table to read the data needed.

-ec

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-07-13 : 13:51:09

Kristen - You forgot to Btach off each set showplan with a go statement directly above and below.
:)

Duane.

RobVG
Starting Member

42 Posts

Posted - 2004-07-13 : 14:12:24

So, you guys are saying that the Myindex contains not only pointers to the records on Mytable, but also the actual values in the ID and Foo columns?

(I don't have access to SQL right now.)

If that's the case, I'm off to slap my instructor...

X002548
Not Just a Number

15586 Posts

Posted - 2004-07-13 : 14:15:49

Let the slapping begin...just make sure you have him/her say it again....could you have misunderstood?

Brett

8-)

RobVG
Starting Member

42 Posts

Posted - 2004-07-13 : 14:24:29

Misunderstanding is always a distinct possibility. But In this case, I remember him saying "indexes don't contain data only pointers..."

X002548
Not Just a Number

15586 Posts

Posted - 2004-07-13 : 14:45:57

Then let the slapping begin...

Did he discuss index intersection?

Does he know what that is?

Brett

8-)

Kristen
Test

22859 Posts

Posted - 2004-07-14 : 02:31:33

ditch> You forgot to Btach off each set showplan with a go statement directly above and below.

Blast! My excuse is that I highlighted and executed them individually ...

(That's one post each!)

Kristen

Kristen
Test

22859 Posts

Posted - 2004-07-14 : 02:33:00

"indexes don't contain data only pointers..."

So hows SQL going to look anything up with jsut pointers?

That would be about as useful as a telephone directory that only has the numbers!

Kristen

RobVG
Starting Member

42 Posts

Posted - 2004-07-14 : 16:24:59

It actually made sense to me. I thought he meant that indexes were applied to tables to sort the records by the key order of the index- which was determined by a pre-sort of the values in the indexed column. Sort of like a virtual clustered index.

I still find BOL somewhat ambiguous. Had to read this a few time before it was clear that "particular value" mean the index value corresponding to the table value- not the Key.

quote:

Indexes/creating/Visual Database Tools

The index provides pointers to the data values stored in specified columns of the table, and then orders those pointers according to the sort order you specify. The database uses the index much as you use an index in a book: it searches the index to find a particular value and then follows the pointer to the row containing that value

Don't you think they should have mentioned column values here as well as key values?

quote:

Indexes/overview/SQL Server Architecture:

The lowest rows of a nonclustered index contain the nonclustered index key values and each key value entry has pointers to the data rows containing the key value. The data rows are not stored in order based on the nonclustered key.

And here, check out the diagram at Indexes/overview/Creating and Maintaining Databases. It shows only the Primary key [emp_ID] in the index. They could have shown the index on another column fer cryin' out loud- All you see are PK's, no values .

So a covering index doesn't access data pages and values in an indexed column, used in a where clause for instance, do access the entire record, in the table, on the data page.

(hope that's right)

Kristen
Test

22859 Posts

Posted - 2004-07-14 : 20:40:27

> used in a where clause for instance

No I don't think that's it (its late here and I may have misunderstood what you meant), but:

Indexes will always be used to deal with the WHERE clause (and JOINs) where they can.

However, a "covered" index means that it also covers the SELECT.

So, in my example above
SELECT ID, Foo FROM MyTable
can ALL be done from the Index without going near the data pages.

Kristen

RobVG
Starting Member

42 Posts

Posted - 2004-07-14 : 22:52:12

I worded that poorly. I think I'm getting it.

Thanks for your help Kirsten. No more about indexes from me.

(Tell Brett if his homebrew is flat- he's doing something wrong.)

Kristen
Test

22859 Posts

Posted - 2004-07-15 : 09:43:00

s/wrong/right/

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources