Database Reference
In-Depth Information
As you can see, the index is able to pack substantially more rows of data into each page
of memory within the database buffer pool. This means substantially less I/O is
physically required to bring those rows of data into the database buffer pool. Less I/O
means faster performance of your virtualized database.
An Index Provides All the Data Needed to Resolve a Query
To demonstrate the point of an index answering the results of a query without having to
go back to the source table, let's create a really simple table. In this example, we create
a table called MYTABLE that has four columns, named A, B, C, and D. In this example,
the columns are type char, in keeping with the previous example. In the real world, you
would more likely use nvarchar or varchar to conserve space.
Create Table MYTABLE
(A Char(100) not null,
B Char(100) not Null,
C Char(100) not null
D Char(100) not null)
Go
We then populate the table MYTABLE with data:
INSERT dbo.PEOPLE (FNAME, LNAME)
VALUES
('John', 'Smith', '70 Kilby Street', 'Anywhere', 'BLUE'),
('Fred', 'Harglebargle', '1600 Pennsylvania Avenue','RED'),
('Mary', 'Johnson'), '10 Downing Street', 'Orange'),
('Andy', 'Andrews'), '1 End of World', 'Pink'),
('John', 'Johannsen', '2 Moonscape Lane', 'Black'),
('Ranjan', 'Gupta', '100 Board Walk', 'Yellow'),
('Susan', 'Brandonson', '10 Yawkey Way', 'Red'),
('Mark', 'Chan', '9999999 Ocean Drive','Blue')
GO 50000
After loading the table with data, we then create an index on the table. The index we
create will be a compound/composite index on the first two columns of the table:
Create Index IX Myindex mytable on dbo.mytable (A,B)
We then issue a basic select statement against the table we created. The select statement
we issue will only retrieve data from columns A and B:
Select A,B from dbo.MYTABLE where A='Mary'
In this example, the SQL Server database will be able to resolve this query without ever
looking within the source table itself. Think of the index as a mini copy of the table, only
