Index scan

If i have only one index and that being a clustered index on say a datetime
column namely date1 and if my query is
select col1,col2 from table1 where date1 between '2/1/2008' and '2/3/2008'
If I look at the query plan and it says its doing an index scan, does that
mean its actually touching each and every page of that table or will it
somehow start at the page that has the first entry for '2/1/2008' and
continues through the linked list at the leaf level of the pages and stops
after it reaches '2/3/2008' ?
How is this different if instead of a clustered index, its a non clustered
index ?
ThanksJohn
It is doing Clustered Index Scan , you meant? If you have CI on the table
that means SQL Server logicaly orders all data by Clustered Index Key.
It depends on the query , an optimizer may or may not decide to do a scan
, for example the table is pretty small .
In this case it scans index pages toread the data which ordered (logicaly)
by date column
In you case I'd suggest t create an index on col1,col2 and dt columns called
COVERING index.
> How is this different if instead of a clustered index, its a non clustered
> index ?
The difference is that clusterd index contains at the bottom level the
actual data , while noclustetred contains pointers to the data pages.
"John Doe" <Johndoe@.jd.com> wrote in message
news:eXI36LscIHA.484@.TK2MSFTNGP06.phx.gbl...
> If i have only one index and that being a clustered index on say a
> datetime column namely date1 and if my query is
> select col1,col2 from table1 where date1 between '2/1/2008' and '2/3/2008'
> If I look at the query plan and it says its doing an index scan, does that
> mean its actually touching each and every page of that table or will it
> somehow start at the page that has the first entry for '2/1/2008' and
> continues through the linked list at the leaf level of the pages and stops
> after it reaches '2/3/2008' ?
> How is this different if instead of a clustered index, its a non clustered
> index ?
> Thanks
>|||Uri,
In my case, its doing a clustered index scan and wanted to know if as a
result, its touching all the pages that may have dates prior to '2/1' and
after '2/3' as my query is only seeking to obtain data between '2/1/2008 and
'2/3/2008' and as you mentioned that data in the CI is ordered. Let me know
how the storage engine fetches the pages.
Also if it was a non clustered index on date1 instead of a clustered index,
i take it that the leaf level of the non clustered index is also
ordered..right ?
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:OBuZpSscIHA.3932@.TK2MSFTNGP05.phx.gbl...
> John
> It is doing Clustered Index Scan , you meant? If you have CI on the table
> that means SQL Server logicaly orders all data by Clustered Index Key.
> It depends on the query , an optimizer may or may not decide to do a scan
> , for example the table is pretty small .
> In this case it scans index pages toread the data which ordered (logicaly)
> by date column
> In you case I'd suggest t create an index on col1,col2 and dt columns
> called COVERING index.
>
>> How is this different if instead of a clustered index, its a non
>> clustered index ?
> The difference is that clusterd index contains at the bottom level the
> actual data , while noclustetred contains pointers to the data pages.
>
> "John Doe" <Johndoe@.jd.com> wrote in message
> news:eXI36LscIHA.484@.TK2MSFTNGP06.phx.gbl...
>> If i have only one index and that being a clustered index on say a
>> datetime column namely date1 and if my query is
>> select col1,col2 from table1 where date1 between '2/1/2008' and
>> '2/3/2008'
>> If I look at the query plan and it says its doing an index scan, does
>> that mean its actually touching each and every page of that table or will
>> it somehow start at the page that has the first entry for '2/1/2008' and
>> continues through the linked list at the leaf level of the pages and
>> stops after it reaches '2/3/2008' ?
>> How is this different if instead of a clustered index, its a non
>> clustered index ?
>> Thanks
>>
>|||Let me get back to the original question. SQL Server may decide to go for
partial scan (reading a range of values) if data is ordered.
And yes, data is ordered logically by the key in both cases, difference
being that in the CI, ALL data is in the leaf level.
MC
"John Doe" <Johndoe@.jd.com> wrote in message
news:uT7oOcscIHA.748@.TK2MSFTNGP04.phx.gbl...
> Uri,
> In my case, its doing a clustered index scan and wanted to know if as a
> result, its touching all the pages that may have dates prior to '2/1' and
> after '2/3' as my query is only seeking to obtain data between '2/1/2008
> and '2/3/2008' and as you mentioned that data in the CI is ordered. Let me
> know how the storage engine fetches the pages.
> Also if it was a non clustered index on date1 instead of a clustered
> index, i take it that the leaf level of the non clustered index is also
> ordered..right ?
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:OBuZpSscIHA.3932@.TK2MSFTNGP05.phx.gbl...
>> John
>> It is doing Clustered Index Scan , you meant? If you have CI on the table
>> that means SQL Server logicaly orders all data by Clustered Index Key.
>> It depends on the query , an optimizer may or may not decide to do a
>> scan , for example the table is pretty small .
>> In this case it scans index pages toread the data which ordered
>> (logicaly) by date column
>> In you case I'd suggest t create an index on col1,col2 and dt columns
>> called COVERING index.
>>
>> How is this different if instead of a clustered index, its a non
>> clustered index ?
>> The difference is that clusterd index contains at the bottom level the
>> actual data , while noclustetred contains pointers to the data pages.
>>
>> "John Doe" <Johndoe@.jd.com> wrote in message
>> news:eXI36LscIHA.484@.TK2MSFTNGP06.phx.gbl...
>> If i have only one index and that being a clustered index on say a
>> datetime column namely date1 and if my query is
>> select col1,col2 from table1 where date1 between '2/1/2008' and
>> '2/3/2008'
>> If I look at the query plan and it says its doing an index scan, does
>> that mean its actually touching each and every page of that table or
>> will it somehow start at the page that has the first entry for
>> '2/1/2008' and continues through the linked list at the leaf level of
>> the pages and stops after it reaches '2/3/2008' ?
>> How is this different if instead of a clustered index, its a non
>> clustered index ?
>> Thanks
>>
>>
>|||John
> Also if it was a non clustered index on date1 instead of a clustered
> index, i take it that the leaf level of the non clustered index is also
> ordered..right ?
Think about CI as phone book which is 'ordered' by LastName ,so if you
want to search by Lastname is easy to traverse and get it
But what if you want to search by NCI -FirstName, then you will have to
page by page which requires an 'extra work'
I'd suggest to take some course or buying a book to underastand the
structure and behaviour.
"John Doe" <Johndoe@.jd.com> wrote in message
news:uT7oOcscIHA.748@.TK2MSFTNGP04.phx.gbl...
> Uri,
> In my case, its doing a clustered index scan and wanted to know if as a
> result, its touching all the pages that may have dates prior to '2/1' and
> after '2/3' as my query is only seeking to obtain data between '2/1/2008
> and '2/3/2008' and as you mentioned that data in the CI is ordered. Let me
> know how the storage engine fetches the pages.
> Also if it was a non clustered index on date1 instead of a clustered
> index, i take it that the leaf level of the non clustered index is also
> ordered..right ?
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:OBuZpSscIHA.3932@.TK2MSFTNGP05.phx.gbl...
>> John
>> It is doing Clustered Index Scan , you meant? If you have CI on the table
>> that means SQL Server logicaly orders all data by Clustered Index Key.
>> It depends on the query , an optimizer may or may not decide to do a
>> scan , for example the table is pretty small .
>> In this case it scans index pages toread the data which ordered
>> (logicaly) by date column
>> In you case I'd suggest t create an index on col1,col2 and dt columns
>> called COVERING index.
>>
>> How is this different if instead of a clustered index, its a non
>> clustered index ?
>> The difference is that clusterd index contains at the bottom level the
>> actual data , while noclustetred contains pointers to the data pages.
>>
>> "John Doe" <Johndoe@.jd.com> wrote in message
>> news:eXI36LscIHA.484@.TK2MSFTNGP06.phx.gbl...
>> If i have only one index and that being a clustered index on say a
>> datetime column namely date1 and if my query is
>> select col1,col2 from table1 where date1 between '2/1/2008' and
>> '2/3/2008'
>> If I look at the query plan and it says its doing an index scan, does
>> that mean its actually touching each and every page of that table or
>> will it somehow start at the page that has the first entry for
>> '2/1/2008' and continues through the linked list at the leaf level of
>> the pages and stops after it reaches '2/3/2008' ?
>> How is this different if instead of a clustered index, its a non
>> clustered index ?
>> Thanks
>>
>>
>|||On Feb 19, 12:45=A0pm, "John Doe" <John...@.jd.com> wrote:
> If i have only one index and that being a clustered index on say a datetim=e
> column namely date1 and if my query is
> select col1,col2 from table1 where date1 between '2/1/2008' and '2/3/2008'=
> If I look at the query plan and it says its doing an index scan, does that=
> mean its actually touching each and every page of that table or will it
> somehow start at the page that has the first entry for '2/1/2008' and
> continues through the linked list at the leaf level of the pages and stops=
> after it reaches '2/3/2008' ?
> How is this different if instead of a clustered index, its a non clustered=
> index ?
> Thanks
With clustered indexes records are stored on the leaf level and
therefore faster but non-clustered indexes leaf level pages have
locations to the page of searched record and therefore slower. HTH.|||If you have a clustered index, yes, it will go to the first entry in the
table with a datetime between '2/1/2008' and '2/3/2008', do a partial scan
of the clustered index and stop as soon as it finds an datetime >
'2/3/2008'. BTW, it is best in SQL Server to specify datetimes as
'yyyy-mm-ddThh:mm:ss.mmm'
or 'yyyy-mm-ddThh:mm:ss'
or 'yyyymmdd'
When you use a date format like '2/1/2008', that is Feb 1 in some locations
and Jan 2 in others. But '20080201' is Feb 1 everywhere.
For a nonclustered index, the answer is, "it depends". If the index is
nonclustered, then SQL Server knows the rows for any given value of dt might
be scattered throughout the physical table. So, for example, the first row
might be in page 1000, followed by a bunch of rows in other pages, followed
by another row in page 1000. But by this time page 1000 might not be in
memory anymore, so the page must be physically read again (this could
possibly happen many times). So SQL Server attempts to keep statistics on
how many rows are in each range and will attempt to estimate what percentage
of the table your query will return. If it is a small percentage, it will
use your index on dt, go to the first entry in the index with a date >='2/1/2008', start there and scan the index until it reaches a row with a dt
> '2/3'2008' and then stop. For each row it finds in the index on dt, it
will then use the clustered index to find the actual row and return your
values. But if it is a large percentage, then SQL Server will just scan the
entire clustered index (that is, it won't use the nonclustered index on dt
at all), because it knows that way it only has to read each physical page in
the clustered index once.
As Uri points out, your nonclustered index can be what is known as a
"covering index" for your query. This occurs when every column you need for
your query is in the index (either explicitly as part of the key or in the
INCLUDED columns or implicitly because the column is in the key of the
clustered index). When that happens, SQL Server knows it can satisify the
query requirements without ever reading from the table, so it will use the
index and only scan the part of the index it needs for the range of data you
want.
Tom
"John Doe" <Johndoe@.jd.com> wrote in message
news:uT7oOcscIHA.748@.TK2MSFTNGP04.phx.gbl...
> Uri,
> In my case, its doing a clustered index scan and wanted to know if as a
> result, its touching all the pages that may have dates prior to '2/1' and
> after '2/3' as my query is only seeking to obtain data between '2/1/2008
> and '2/3/2008' and as you mentioned that data in the CI is ordered. Let me
> know how the storage engine fetches the pages.
> Also if it was a non clustered index on date1 instead of a clustered
> index, i take it that the leaf level of the non clustered index is also
> ordered..right ?
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:OBuZpSscIHA.3932@.TK2MSFTNGP05.phx.gbl...
>> John
>> It is doing Clustered Index Scan , you meant? If you have CI on the table
>> that means SQL Server logicaly orders all data by Clustered Index Key.
>> It depends on the query , an optimizer may or may not decide to do a
>> scan , for example the table is pretty small .
>> In this case it scans index pages toread the data which ordered
>> (logicaly) by date column
>> In you case I'd suggest t create an index on col1,col2 and dt columns
>> called COVERING index.
>>
>> How is this different if instead of a clustered index, its a non
>> clustered index ?
>> The difference is that clusterd index contains at the bottom level the
>> actual data , while noclustetred contains pointers to the data pages.
>>
>> "John Doe" <Johndoe@.jd.com> wrote in message
>> news:eXI36LscIHA.484@.TK2MSFTNGP06.phx.gbl...
>> If i have only one index and that being a clustered index on say a
>> datetime column namely date1 and if my query is
>> select col1,col2 from table1 where date1 between '2/1/2008' and
>> '2/3/2008'
>> If I look at the query plan and it says its doing an index scan, does
>> that mean its actually touching each and every page of that table or
>> will it somehow start at the page that has the first entry for
>> '2/1/2008' and continues through the linked list at the leaf level of
>> the pages and stops after it reaches '2/3/2008' ?
>> How is this different if instead of a clustered index, its a non
>> clustered index ?
>> Thanks
>>
>>
>

Index on result of function

I have a table with about 28 million records in it. Each row has an ID (PK), logged (datetime), IP varchar(15)

The data grows at about 14 million records per year. I'm going to be running queries on the table that extract the MONTH or YEAR from the logged column. In Foxpro tables I would have created indexes on YEAR(logged) and MONTH(logged) so my queries would run faster. Is this possible/necessary in SQL Server?

Yes. You can achive this using the indexed views.

Create different views for each year & index it.

|||Bes, this table sounds like a good candidate for the new table partitioning method of SQL 2005. You could partition by the year and month... There would be separate indexes on each partition slice and SQL Server would direct a query to just the partition needed and the query would run much faster... but... you need Enterprise Edition for paritioning. If you have Enterprise, then it's something to check out... Bruce|||

Bruce,

It's good to know there is another way to do it. The little I've read about Indexed Views indicates they'll increase my maintenance and I should only use them in special cases.

We're not running Enterprise (too much $ for dual CPUs), but if depending on how we use this data maybe we'll be able to justify it.

Thanks!

Brian

|||

I think creating a couple of computed column(s) and creating an index on those field(s) will give you the best combination of query performance and maintenance. Lots of modifications to data in the base table in an indexed view could cause a server to grind to a halt. The index maintenance on the computed columns should be minimal.

alter table MyTable add MyDateYear AS YEAR(MyDate)

alter table MyTable add MyDateMonth AS Month(MyDate)

CREATE INDEX IX_MyTable_Year_Month ON MyTable(MyDateYear, MyDateMonth)

index on datetime

If I have an index on a datetime, will a where clause with year(<datatime
column>) use the index? Or should I create a seperate index for it?
Thank you
No it won't use the index. But you can change your query so that it will
use the index. Instead of
Select ...
Where Year(MyDateTime) = 2008
do
Select ...
Where MyDateTime >= '20080101' And MyDateTime < '20090101'
and that will use the index.
Tom
"SandpointGuy" <SandpointGuy@.discussions.microsoft.com> wrote in message
news:186D952F-DB39-45F1-8ED3-70A7BA2162C7@.microsoft.com...
> If I have an index on a datetime, will a where clause with year(<datatime
> column>) use the index? Or should I create a seperate index for it?
> Thank you
|||Actually, I should clarify. If you do the query as
Select ...
Where MyDateTime >= '20080101' And MyDateTime < '20090101'
then SQL will use the index whenever SQL believes using the index will be
faster. Depending on the amount and distribution of your data and exactly
what your query is, SQL may decide that it is faster to scan the table or
use another index. In those cases, of course, SQL will not use the index.
And, if your query is "covered" by this index, either query will use the
index. But the form using the Year() function will scan the entire index,
and the other will only scan the part of the index that contain the 2008
entries.
That's the long and messy answer to your question. The short answer is that
the form of the query using the Year() function is never better at using the
index and is often worse.
Tom
"Tom Cooper" <tomcooper@.comcast.no.spam.please.net> wrote in message
news:%23DIHnGShIHA.5368@.TK2MSFTNGP04.phx.gbl...
> No it won't use the index. But you can change your query so that it will
> use the index. Instead of
> Select ...
> Where Year(MyDateTime) = 2008
> do
> Select ...
> Where MyDateTime >= '20080101' And MyDateTime < '20090101'
> and that will use the index.
> Tom
> "SandpointGuy" <SandpointGuy@.discussions.microsoft.com> wrote in message
> news:186D952F-DB39-45F1-8ED3-70A7BA2162C7@.microsoft.com...
>

index on datetime

If I have an index on a datetime, will a where clause with year(<datatime
column>) use the index? Or should I create a seperate index for it?
Thank youNo it won't use the index. But you can change your query so that it will
use the index. Instead of
Select ...
Where Year(MyDateTime) = 2008
do
Select ...
Where MyDateTime >= '20080101' And MyDateTime < '20090101'
and that will use the index.
Tom
"SandpointGuy" <SandpointGuy@.discussions.microsoft.com> wrote in message
news:186D952F-DB39-45F1-8ED3-70A7BA2162C7@.microsoft.com...
> If I have an index on a datetime, will a where clause with year(<datatime
> column>) use the index? Or should I create a seperate index for it?
> Thank you|||Actually, I should clarify. If you do the query as
Select ...
Where MyDateTime >= '20080101' And MyDateTime < '20090101'
then SQL will use the index whenever SQL believes using the index will be
faster. Depending on the amount and distribution of your data and exactly
what your query is, SQL may decide that it is faster to scan the table or
use another index. In those cases, of course, SQL will not use the index.
And, if your query is "covered" by this index, either query will use the
index. But the form using the Year() function will scan the entire index,
and the other will only scan the part of the index that contain the 2008
entries.
That's the long and messy answer to your question. The short answer is that
the form of the query using the Year() function is never better at using the
index and is often worse.
Tom
"Tom Cooper" <tomcooper@.comcast.no.spam.please.net> wrote in message
news:%23DIHnGShIHA.5368@.TK2MSFTNGP04.phx.gbl...
> No it won't use the index. But you can change your query so that it will
> use the index. Instead of
> Select ...
> Where Year(MyDateTime) = 2008
> do
> Select ...
> Where MyDateTime >= '20080101' And MyDateTime < '20090101'
> and that will use the index.
> Tom
> "SandpointGuy" <SandpointGuy@.discussions.microsoft.com> wrote in message
> news:186D952F-DB39-45F1-8ED3-70A7BA2162C7@.microsoft.com...
>> If I have an index on a datetime, will a where clause with
>> year(<datatime
>> column>) use the index? Or should I create a seperate index for it?
>> Thank you
>

Index enquiry

hi all,
I have a table with 700K records with the primary key as cluster index.
TableA {
chrRef char(10), -- key
chrStatus char(2),
dtTrade datetime,
dtSettle datetime,
....
}
When query the table by filter records on non-primay key, the performance is
acceptable (less than 10K records)
e.g. select * from TableA where dtTrade = '20060101'
When records has been grown to 700K, the query is quite slow. I have added
an index (IX_dtTrade) on the column "dtTrade" in order to reduce the query
time. However, i found that SQL server did not use the index (IX_dtTrade) to
speed up the query. SQL server still using the cluster index to retrieve
records. From the help, i found that there was a method to force SQL server
to use the index. As a result, the query time reduce a lot.
e.g. select * from TableA with index (IX_dtTrade) where dtTrade = '20060101'
For this case,
1) Is there any setup so that the SQL server will use the index (IX_dtTrade)
automatically without explicit the cluase (with index ())?
2) When create index, what is the difference between single index and
compound index in SQL server? It seems that when an index is created on the
column "dtTrade" (IX_dtTrade) and "dtTrade, dtSettle" (IX_dtTrade_dtSettle),
the query time is same.
3) If there are many queries filter on columns "dtTrade", "dtSettle" and
"chrStatus", create an index on each column or a compound index on the three
columns?
3) Is it the only way to speed up the query time by creating index on target
column? (provided that no change on number of records)
Any suggestions? Thank in advance!!
Regards,
MartinCheck the execution plan to see how many rows are estimated to be returned. If SQL Server estimates
a large number of rows, it will consider a scan more efficient than using a non-clustered index.
This is because using a non-clustered index, SQL Server will navigate the index, and *for each row*
access the data page. Imagine if you return 10 000 rows, then you have 10 000 data page accesses,
even if the whole table perhaps fits on 5 000 pages. This is easier to explain with a white-board.
Assuming the estimate is off, we need to figure out why. It could be several reasons, for instance:
Bad statistics.
The query you showed us is not what you are running.
You use a stored procedure and the data you search for is a parameter.
The data you search for is a variable.
The condition for the data isn't expressed as in your example.
As for your questions:
> 1) Is there any setup so that the SQL server will use the index (IX_dtTrade) automatically without
> explicit the cluase (with index ())?
There's no "magic button" for this. See my above elaboration.
> 2) When create index, what is the difference between single index and compound index in SQL
> server? It seems that when an index is created on the column "dtTrade" (IX_dtTrade) and "dtTrade,
> dtSettle" (IX_dtTrade_dtSettle), the query time is same.
An index on several columns, say (a, b) , can be good for conditions like:
A = 2 AND B = 7
But not for:
B = 45
So you need to know your queries in order to create a good indexing strategy. If you are uncertain,
start by one index per column.
> 3) If there are many queries filter on columns "dtTrade", "dtSettle" and "chrStatus", create an
> index on each column or a compound index on the three columns?
See above.
> 3) Is it the only way to speed up the query time by creating index on target column? (provided
> that no change on number of records)
If you don't give SQL Server any way to limit which rows it need to look for in order to determine
which satisfies your condition, well, then SQL Server need to look at each row. An index does just
that.
Also, don't do SELECT *. Only return the columns you need. The main importance for this isn't
perhaps to reduce network bandwidth. It is that you probably lose the ability to cover your queries
with a non-clustered index. Such an index has all the columns that the query need in it and SQL
Server doesn't have to access the data page for each row, the answer is in the index page.
This is a big topic, so I suggest you start studying and reading a bit. Books Online has some good
sections which is a good start. Kalen Delaney's "Inside SQL Server" book is very good at describing
these constructs.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Atenza" <Atenza@.mail.hongkong.com> wrote in message news:%23q8OTNn9GHA.788@.TK2MSFTNGP05.phx.gbl...
> hi all,
> I have a table with 700K records with the primary key as cluster index.
> TableA {
> chrRef char(10), -- key
> chrStatus char(2),
> dtTrade datetime,
> dtSettle datetime,
> ....
> }
> When query the table by filter records on non-primay key, the performance is acceptable (less than
> 10K records)
> e.g. select * from TableA where dtTrade = '20060101'
> When records has been grown to 700K, the query is quite slow. I have added an index (IX_dtTrade)
> on the column "dtTrade" in order to reduce the query time. However, i found that SQL server did
> not use the index (IX_dtTrade) to speed up the query. SQL server still using the cluster index to
> retrieve records. From the help, i found that there was a method to force SQL server to use the
> index. As a result, the query time reduce a lot.
> e.g. select * from TableA with index (IX_dtTrade) where dtTrade = '20060101'
> For this case,
> 1) Is there any setup so that the SQL server will use the index (IX_dtTrade) automatically without
> explicit the cluase (with index ())?
> 2) When create index, what is the difference between single index and compound index in SQL
> server? It seems that when an index is created on the column "dtTrade" (IX_dtTrade) and "dtTrade,
> dtSettle" (IX_dtTrade_dtSettle), the query time is same.
> 3) If there are many queries filter on columns "dtTrade", "dtSettle" and "chrStatus", create an
> index on each column or a compound index on the three columns?
> 3) Is it the only way to speed up the query time by creating index on target column? (provided
> that no change on number of records)
> Any suggestions? Thank in advance!!
> Regards,
> Martin
>|||thank you for your suggestion!!! really useful!
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23UbAQXn9GHA.3384@.TK2MSFTNGP05.phx.gbl...
> Check the execution plan to see how many rows are estimated to be
> returned. If SQL Server estimates a large number of rows, it will consider
> a scan more efficient than using a non-clustered index. This is because
> using a non-clustered index, SQL Server will navigate the index, and *for
> each row* access the data page. Imagine if you return 10 000 rows, then
> you have 10 000 data page accesses, even if the whole table perhaps fits
> on 5 000 pages. This is easier to explain with a white-board.
> Assuming the estimate is off, we need to figure out why. It could be
> several reasons, for instance:
> Bad statistics.
> The query you showed us is not what you are running.
> You use a stored procedure and the data you search for is a parameter.
> The data you search for is a variable.
> The condition for the data isn't expressed as in your example.
> As for your questions:
>> 1) Is there any setup so that the SQL server will use the index
>> (IX_dtTrade) automatically without explicit the cluase (with index ())?
> There's no "magic button" for this. See my above elaboration.
>
>> 2) When create index, what is the difference between single index and
>> compound index in SQL server? It seems that when an index is created on
>> the column "dtTrade" (IX_dtTrade) and "dtTrade, dtSettle"
>> (IX_dtTrade_dtSettle), the query time is same.
> An index on several columns, say (a, b) , can be good for conditions like:
> A = 2 AND B = 7
> But not for:
> B = 45
> So you need to know your queries in order to create a good indexing
> strategy. If you are uncertain, start by one index per column.
>
>> 3) If there are many queries filter on columns "dtTrade", "dtSettle" and
>> "chrStatus", create an index on each column or a compound index on the
>> three columns?
> See above.
>
>> 3) Is it the only way to speed up the query time by creating index on
>> target column? (provided that no change on number of records)
> If you don't give SQL Server any way to limit which rows it need to look
> for in order to determine which satisfies your condition, well, then SQL
> Server need to look at each row. An index does just that.
> Also, don't do SELECT *. Only return the columns you need. The main
> importance for this isn't perhaps to reduce network bandwidth. It is that
> you probably lose the ability to cover your queries with a non-clustered
> index. Such an index has all the columns that the query need in it and SQL
> Server doesn't have to access the data page for each row, the answer is in
> the index page.
> This is a big topic, so I suggest you start studying and reading a bit.
> Books Online has some good sections which is a good start. Kalen Delaney's
> "Inside SQL Server" book is very good at describing these constructs.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Atenza" <Atenza@.mail.hongkong.com> wrote in message
> news:%23q8OTNn9GHA.788@.TK2MSFTNGP05.phx.gbl...
>> hi all,
>> I have a table with 700K records with the primary key as cluster index.
>> TableA {
>> chrRef char(10), -- key
>> chrStatus char(2),
>> dtTrade datetime,
>> dtSettle datetime,
>> ....
>> }
>> When query the table by filter records on non-primay key, the performance
>> is acceptable (less than 10K records)
>> e.g. select * from TableA where dtTrade = '20060101'
>> When records has been grown to 700K, the query is quite slow. I have
>> added an index (IX_dtTrade) on the column "dtTrade" in order to reduce
>> the query time. However, i found that SQL server did not use the index
>> (IX_dtTrade) to speed up the query. SQL server still using the cluster
>> index to retrieve records. From the help, i found that there was a method
>> to force SQL server to use the index. As a result, the query time reduce
>> a lot.
>> e.g. select * from TableA with index (IX_dtTrade) where dtTrade =>> '20060101'
>> For this case,
>> 1) Is there any setup so that the SQL server will use the index
>> (IX_dtTrade) automatically without explicit the cluase (with index ())?
>> 2) When create index, what is the difference between single index and
>> compound index in SQL server? It seems that when an index is created on
>> the column "dtTrade" (IX_dtTrade) and "dtTrade, dtSettle"
>> (IX_dtTrade_dtSettle), the query time is same.
>> 3) If there are many queries filter on columns "dtTrade", "dtSettle" and
>> "chrStatus", create an index on each column or a compound index on the
>> three columns?
>> 3) Is it the only way to speed up the query time by creating index on
>> target column? (provided that no change on number of records)
>> Any suggestions? Thank in advance!!
>> Regards,
>> Martin
>

Index Creation Question

I have a table with structure
payment_id varchar(10)
date_received datetime
order_id_response_stat varchar(10)
order_response_error_msg varchar(10)
The payment_id was the clustered index and primary key.
The problem now is this field should allow duplicates so I need to change
the indexing on this table. These table is used mostly for INSERTS and will
have about 1 million rows in future. The only occasional select will be based
on payment_id and date_received fields.
I dont know which options will be best for me under this situation as I want
to get max perf gains be using an index.
Can anyone pls help me"Anup" <Anup@.discussions.microsoft.com> wrote in message
news:8991B556-271E-4A51-BB70-2125E0F11F6B@.microsoft.com...
> The payment_id was the clustered index and primary key.
> The problem now is this field should allow duplicates so I need to change
OK, so what's your new primary key? Think about data integrity first --
then consider performance.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--|||Thats my question.
I can either create a composite index on payment and date and as a primary
key.
Can create identidy colmun as primary key
But I want to make sure that creating these wont impact the performance.
Pls somebody help what to do and the best scenario
"Adam Machanic" wrote:
> "Anup" <Anup@.discussions.microsoft.com> wrote in message
> news:8991B556-271E-4A51-BB70-2125E0F11F6B@.microsoft.com...
> >
> > The payment_id was the clustered index and primary key.
> >
> > The problem now is this field should allow duplicates so I need to change
> OK, so what's your new primary key? Think about data integrity first --
> then consider performance.
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
>|||From an insert point of view, it might make more sense to put the date first
in the composite key, assuming that you insert the payments as they're made.
But that might have a negative impact on selects, if you retrieve the data
based on the payment id. You should probably experiment on your end to find
the best combination.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Anup" <Anup@.discussions.microsoft.com> wrote in message
news:6A5A06FA-3087-4E8E-B926-4C30C98710A0@.microsoft.com...
> Thats my question.
> I can either create a composite index on payment and date and as a primary
> key.
> Can create identidy colmun as primary key
> But I want to make sure that creating these wont impact the performance.
> Pls somebody help what to do and the best scenario
> "Adam Machanic" wrote:
>> "Anup" <Anup@.discussions.microsoft.com> wrote in message
>> news:8991B556-271E-4A51-BB70-2125E0F11F6B@.microsoft.com...
>> >
>> > The payment_id was the clustered index and primary key.
>> >
>> > The problem now is this field should allow duplicates so I need to
>> > change
>> OK, so what's your new primary key? Think about data integrity
>> first --
>> then consider performance.
>>
>> --
>> Adam Machanic
>> Pro SQL Server 2005, available now
>> http://www.apress.com/book/bookDisplay.html?bID=457
>> --
>>
>>

Index creation datetime

Hi,
How I find out the creation datetime of index (clustered
and nonclustered)?
Regards.
--
Farhan SoomroNo, SQL Server does not keep any track of index creation date., However, if
you have primary key constraint (which will have an index
clustered/non-clustered) then you can refer to crdate column of sysobjects
table to check the creation date of this constraint which will be same as
creation date of index on primary key.
--
-Vishal
"Farhan Soomro" <fsoomro@.chartlinks.com> wrote in message
news:0bcc01c35b62$5157d860$a501280a@.phx.gbl...
> Hi,
> How I find out the creation datetime of index (clustered
> and nonclustered)?
> Regards.
> --
> Farhan Soomro