Re: Indexes
From: Tibor Karaszi (tibor_please.no.email_karaszi_at_hotmail.nomail.com)
Date: 02/25/05
- Next message: Erland Sommarskog: "Re: Seek method, table-direct, and sql server2005"
- Previous message: spp: "Re: Transaction log too big"
- In reply to: Uri Dimant: "Re: Indexes"
- Messages sorted by: [ date ] [ thread ]
Date: Fri, 25 Feb 2005 09:27:01 +0100
I got a significantly lower cost with the non-clustered index. Here's a reproduction sript. It uses
only 10000 rows to make it faster to run, along with the STATISTICS IO result I got:
SET NOCOUNT ON
USE tempdb
GO
CREATE TABLE t(c1 int identity, c2 char(5000) default 'a')
--Insert values
DECLARE @i int
SET @i = 1
WHILE @i <= 10000
BEGIN
INSERT t DEFAULT VALUES
SET @i = @i + 1
END
GO
CREATE CLUSTERED INDEX t_cl ON t(c1)
SET STATISTICS IO ON
GO
SELECT SUM(c1) FROM t
--Table 't'. Scan count 1, logical reads 10002, physical reads 1, read-ahead reads 10016.
GO
CREATE NONCLUSTERED INDEX t_nc ON t(c1)
GO
SELECT SUM(c1) FROM t
--Table 't'. Scan count 1, logical reads 14, physical reads 0, read-ahead reads 0.
-- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ http://www.sqlug.se/ "Uri Dimant" <urid@iscar.co.il> wrote in message news:%23ItcPwnGFHA.3964@TK2MSFTNGP14.phx.gbl... > Hi ,Tibor > In theoretics , looks fine. > I did the test based on example that you gave me, > In both queries ( after creation NCI) the optimizer was used clustered > indexe scan. Its ok, because we have a clustered index but I was expecting > to see that the optimizer was looked at NCI. The execution time is the same > as well. > > > > > "Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.com> wrote in > message news:eNJ5zHnGFHA.2736@TK2MSFTNGP09.phx.gbl... >> > Can you elaborate a little bit under what circumstances it can be > benefical? >> >> I'll try:-). Example: >> >> CREATE TABLE t(c1 int, c2 char(5000)) >> >> Clustered index on c1. With the row size I specified above, only one row > fit per data page (makes it >> easy to do the maths). Imagine 100000 rows. >> >> SELECT SUM(c1) FROM t >> >> Above need to look at every row. Only one row fit per page, so SQL Server > need to look at 100000 >> pages to do the SUM. >> >> Now create a non-clustered index on c1. And do the same SELECT. A > non-clustered index has one row in >> the leaf page per row in the table. For the sake of discussion, say that > 1000 rows fit per index >> page. You have 100000 rows in the table and 1000 rows fit per index page. > I.e., the leaf level of >> the index has about 100 pages. SQL Server can do the SUM by scanning the > leaf level of the NC index >> and only have to read 100 pages. We cut down from reading 100000 pages to > 100 pages. >> >> -- >> Tibor Karaszi, SQL Server MVP >> http://www.karaszi.com/sqlserver/default.asp >> http://www.solidqualitylearning.com/ >> >> >> "Uri Dimant" <urid@iscar.co.il> wrote in message > news:u9l07emGFHA.2976@TK2MSFTNGP15.phx.gbl... >> > Hi, Tibor >> >> The clustered index does indeed come with an index tree. NC index on > the >> > same column *can* be >> >> beneficial, >> > >> > Can you elaborate a little bit under what circumstances it can be > benefical? >> > I have just finished test with a larger table that one column has both >> > indexes and even if I got fewer rows it was using clustrerd index. >> > >> > >> > >> > >> > "Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.com> wrote > in >> > message news:%231D6PFmGFHA.2900@TK2MSFTNGP10.phx.gbl... >> >> The clustered index does indeed come with an index tree. NC index on > the >> > same column *can* be >> >> beneficial, however, in case you can cover queries using that nc index. >> > This is because the nc index >> >> only contains the columns over which you create the index, so more rows >> > will fit on the leaf level >> >> of that index. Imagine a scan now, where SQL Server can read fewer > pages >> > compared to of SLQ server >> >> needed to read the data pages. >> >> >> >> -- >> >> Tibor Karaszi, SQL Server MVP >> >> http://www.karaszi.com/sqlserver/default.asp >> >> http://www.solidqualitylearning.com/ >> >> >> >> >> >> "B.J." <BJ@discussions.microsoft.com> wrote in message >> >> news:FAED71F9-78BA-4E8D-B5A6-2BDB076B8AF6@microsoft.com... >> >> > Hi, >> >> > >> >> > Do I need to create non-clustered index on same columns as clustered >> > index >> >> > has, if I have already created clustered index ? What I am really > asking >> > is >> >> > whether clustered index only order table physically or it also > creates >> > index >> >> > ..., and if it orders table phusically only may I gain something when > I >> > make >> >> > also non-clustered index ... >> >> > >> >> > Thank you >> >> >> >> >> > >> > >> >> > >
- Next message: Erland Sommarskog: "Re: Seek method, table-direct, and sql server2005"
- Previous message: spp: "Re: Transaction log too big"
- In reply to: Uri Dimant: "Re: Indexes"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
