How to force some commands to run by using certain index

Hi,
Can i force some of the update command by using the
index that i want. Normally when we update something, we
will let sql to select the index, how can i choose the
index that i want in the transact-sql statement?
Can anyone teach me and give me an example?
Thanks a lot!
regards,
florence
> Can i force some of the update command by using the
> index that i want. Normally when we update something, we
> will let sql to select the index, how can i choose the
> index that i want in the transact-sql statement?
Yes, you can use optimizer hints. But a common advice is to do everything
else before using hnts. Check how to tune queries, including optimzer hints,
at
http://www.microsoft.com/technet/pro...e14.mspx#EDAA.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com

How to force some commands to run by using certain index

Hi,
Can i force some of the update command by using the
index that i want. Normally when we update something, we
will let sql to select the index, how can i choose the
index that i want in the transact-sql statement?
Can anyone teach me and give me an example?
Thanks a lot!
regards,
florence> Can i force some of the update command by using the
> index that i want. Normally when we update something, we
> will let sql to select the index, how can i choose the
> index that i want in the transact-sql statement?
Yes, you can use optimizer hints. But a common advice is to do everything
else before using hnts. Check how to tune queries, including optimzer hints,
at
]
Dejan Sarka, SQL Server MVP
Associate Mentor
[url]www.SolidQualityLearning.com" target="_blank">http://www.microsoft.com/technet/pr...ityLearning.com

How to force some commands to run by using certain index

Hi,
Can i force some of the update command by using the
index that i want. Normally when we update something, we
will let sql to select the index, how can i choose the
index that i want in the transact-sql statement?
Can anyone teach me and give me an example?
Thanks a lot!
regards,
florence> Can i force some of the update command by using the
> index that i want. Normally when we update something, we
> will let sql to select the index, how can i choose the
> index that i want in the transact-sql statement?
Yes, you can use optimizer hints. But a common advice is to do everything
else before using hnts. Check how to tune queries, including optimzer hints,
at
http://www.microsoft.com/technet/prodtechnol/sql/70/books/inside14.mspx#EDAA.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com

how to force a table scan in query

I have a query thats using a particular index and I would like to force the
query to do a table scan instead. How can i do so ?
Heres a sample query
select * from tableA where col1= 5Geeze why would you want to?!
I think your only way would be to drop the index. I don't think you can
force the optimiser not to use an index...
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:OcGkZRwnDHA.2064@.TK2MSFTNGP11.phx.gbl...
> I have a query thats using a particular index and I would like to force
the
> query to do a table scan instead. How can i do so ?
> Heres a sample query
> select * from tableA where col1= 5
>|||Remove the index.
>--Original Message--
>I have a query thats using a particular index and I would
like to force the
>query to do a table scan instead. How can i do so ?
>Heres a sample query
>select * from tableA where col1= 5
>
>.
>|||Table hint WITH INDEX(0)
BOL: If a clustered index exists, INDEX(0) forces a clustered index scan
and INDEX(1) forces a clustered index scan or seek. If no clustered index
exists, INDEX(0) forces a table scan and INDEX(1) is interpreted as an
error.
Russell Fields
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:OcGkZRwnDHA.2064@.TK2MSFTNGP11.phx.gbl...
> I have a query thats using a particular index and I would like to force
the
> query to do a table scan instead. How can i do so ?
> Heres a sample query
> select * from tableA where col1= 5
>|||There are definitely reasons why you wouldn't want to use an index, but most
of them can be solved by making sure your statistics are updated. If the
optimizer is incorrectly choosing to use a nonclustered index, you can far
far more reads than a simple table scan would take. You might also just want
to run the query without the index for testing and comparison purposes, to
find out how much the index is really saving you, to see if it's worth the
cost of its maintenance.
As Russell pointed out, you can use WITH INDEX(0) to force no index to be
used on a particular table.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"London Developer" <dev@.nowhere.com> wrote in message
news:#otRHcwnDHA.2772@.TK2MSFTNGP12.phx.gbl...
> Geeze why would you want to?!
> I think your only way would be to drop the index. I don't think you can
> force the optimiser not to use an index...
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:OcGkZRwnDHA.2064@.TK2MSFTNGP11.phx.gbl...
> > I have a query thats using a particular index and I would like to force
> the
> > query to do a table scan instead. How can i do so ?
> >
> > Heres a sample query
> >
> > select * from tableA where col1= 5
> >
> >
>|||btw , Kalen, in your latest article in SQL Mag about the optimiser, listing
2 did take an index scan when you mentioned that it would take a table scan
bcos of less reads. My optimiser is not smart enough I guess as yours :-)
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:usV2PtwnDHA.1004@.TK2MSFTNGP09.phx.gbl...
> There are definitely reasons why you wouldn't want to use an index, but
most
> of them can be solved by making sure your statistics are updated. If the
> optimizer is incorrectly choosing to use a nonclustered index, you can far
> far more reads than a simple table scan would take. You might also just
want
> to run the query without the index for testing and comparison purposes, to
> find out how much the index is really saving you, to see if it's worth the
> cost of its maintenance.
> As Russell pointed out, you can use WITH INDEX(0) to force no index to be
> used on a particular table.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "London Developer" <dev@.nowhere.com> wrote in message
> news:#otRHcwnDHA.2772@.TK2MSFTNGP12.phx.gbl...
> > Geeze why would you want to?!
> > I think your only way would be to drop the index. I don't think you can
> > force the optimiser not to use an index...
> >
> > "Hassan" <fatima_ja@.hotmail.com> wrote in message
> > news:OcGkZRwnDHA.2064@.TK2MSFTNGP11.phx.gbl...
> > > I have a query thats using a particular index and I would like to
force
> > the
> > > query to do a table scan instead. How can i do so ?
> > >
> > > Heres a sample query
> > >
> > > select * from tableA where col1= 5
> > >
> > >
> >
> >
>|||Hi Hassan
Can you be more specific? I usually write my articles about 3 months in
advance (I just submitted February's article yesterday), so which is the
latest? October or November?
I'll take a look at it.
Thanks!
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:e2MYfNynDHA.3700@.TK2MSFTNGP11.phx.gbl...
> btw , Kalen, in your latest article in SQL Mag about the optimiser,
listing
> 2 did take an index scan when you mentioned that it would take a table
scan
> bcos of less reads. My optimiser is not smart enough I guess as yours :-)
>
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:usV2PtwnDHA.1004@.TK2MSFTNGP09.phx.gbl...
> > There are definitely reasons why you wouldn't want to use an index, but
> most
> > of them can be solved by making sure your statistics are updated. If the
> > optimizer is incorrectly choosing to use a nonclustered index, you can
far
> > far more reads than a simple table scan would take. You might also just
> want
> > to run the query without the index for testing and comparison purposes,
to
> > find out how much the index is really saving you, to see if it's worth
the
> > cost of its maintenance.
> >
> > As Russell pointed out, you can use WITH INDEX(0) to force no index to
be
> > used on a particular table.
> >
> > --
> > HTH
> > --
> > Kalen Delaney
> > SQL Server MVP
> > www.SolidQualityLearning.com
> >
> >
> > "London Developer" <dev@.nowhere.com> wrote in message
> > news:#otRHcwnDHA.2772@.TK2MSFTNGP12.phx.gbl...
> > > Geeze why would you want to?!
> > > I think your only way would be to drop the index. I don't think you
can
> > > force the optimiser not to use an index...
> > >
> > > "Hassan" <fatima_ja@.hotmail.com> wrote in message
> > > news:OcGkZRwnDHA.2064@.TK2MSFTNGP11.phx.gbl...
> > > > I have a query thats using a particular index and I would like to
> force
> > > the
> > > > query to do a table scan instead. How can i do so ?
> > > >
> > > > Heres a sample query
> > > >
> > > > select * from tableA where col1= 5
> > > >
> > > >
> > >
> > >
> >
> >
>|||I think it was November..the latest issue on the newstand... that had Yukon
on the cover plus the listing that had examples of orderdetails table
http://www.sqlmag.com/Articles/Index.cfm?ArticleID=39906&
These lines in particular
"If you run the command SET STATISTICS IO ON, then execute Listing 2's
queries, you'll see that the queries each take 10 logical reads-one for each
page in the table. The first query returns 58 rows. If the optimizer had
decided to use the nonclustered index on Quantity, SQL Server would have had
to perform 58 bookmark lookup operations, a much higher cost than the 10
logical reads of the table scan. The second query returns 33 rows, so it,
too, would have cost more than 10 logical reads if the optimizer had decided
to access the nonclustered index and perform bookmark lookups."
But when executed my queries had around 60 logical reads since it was using
the index with a bookmark lookup
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:O19nzqynDHA.2536@.tk2msftngp13.phx.gbl...
> Hi Hassan
> Can you be more specific? I usually write my articles about 3 months in
> advance (I just submitted February's article yesterday), so which is the
> latest? October or November?
> I'll take a look at it.
> Thanks!
>
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:e2MYfNynDHA.3700@.TK2MSFTNGP11.phx.gbl...
> > btw , Kalen, in your latest article in SQL Mag about the optimiser,
> listing
> > 2 did take an index scan when you mentioned that it would take a table
> scan
> > bcos of less reads. My optimiser is not smart enough I guess as yours
:-)
> >
> >
> > "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> > news:usV2PtwnDHA.1004@.TK2MSFTNGP09.phx.gbl...
> > > There are definitely reasons why you wouldn't want to use an index,
but
> > most
> > > of them can be solved by making sure your statistics are updated. If
the
> > > optimizer is incorrectly choosing to use a nonclustered index, you can
> far
> > > far more reads than a simple table scan would take. You might also
just
> > want
> > > to run the query without the index for testing and comparison
purposes,
> to
> > > find out how much the index is really saving you, to see if it's worth
> the
> > > cost of its maintenance.
> > >
> > > As Russell pointed out, you can use WITH INDEX(0) to force no index to
> be
> > > used on a particular table.
> > >
> > > --
> > > HTH
> > > --
> > > Kalen Delaney
> > > SQL Server MVP
> > > www.SolidQualityLearning.com
> > >
> > >
> > > "London Developer" <dev@.nowhere.com> wrote in message
> > > news:#otRHcwnDHA.2772@.TK2MSFTNGP12.phx.gbl...
> > > > Geeze why would you want to?!
> > > > I think your only way would be to drop the index. I don't think you
> can
> > > > force the optimiser not to use an index...
> > > >
> > > > "Hassan" <fatima_ja@.hotmail.com> wrote in message
> > > > news:OcGkZRwnDHA.2064@.TK2MSFTNGP11.phx.gbl...
> > > > > I have a query thats using a particular index and I would like to
> > force
> > > > the
> > > > > query to do a table scan instead. How can i do so ?
> > > > >
> > > > > Heres a sample query
> > > > >
> > > > > select * from tableA where col1= 5
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||Thanks, I just got that in the mail today!
I'll take a look and see if I can figure out why you might have gotten
different behavior than I did.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:#XajESznDHA.3700@.TK2MSFTNGP11.phx.gbl...
> I think it was November..the latest issue on the newstand... that had
Yukon
> on the cover plus the listing that had examples of orderdetails table
> http://www.sqlmag.com/Articles/Index.cfm?ArticleID=39906&
> These lines in particular
> "If you run the command SET STATISTICS IO ON, then execute Listing 2's
> queries, you'll see that the queries each take 10 logical reads-one for
each
> page in the table. The first query returns 58 rows. If the optimizer had
> decided to use the nonclustered index on Quantity, SQL Server would have
had
> to perform 58 bookmark lookup operations, a much higher cost than the 10
> logical reads of the table scan. The second query returns 33 rows, so it,
> too, would have cost more than 10 logical reads if the optimizer had
decided
> to access the nonclustered index and perform bookmark lookups."
> But when executed my queries had around 60 logical reads since it was
using
> the index with a bookmark lookup
>
>
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:O19nzqynDHA.2536@.tk2msftngp13.phx.gbl...
> > Hi Hassan
> >
> > Can you be more specific? I usually write my articles about 3 months in
> > advance (I just submitted February's article yesterday), so which is the
> > latest? October or November?
> > I'll take a look at it.
> >
> > Thanks!
> >
> >
> > --
> > HTH
> > --
> > Kalen Delaney
> > SQL Server MVP
> > www.SolidQualityLearning.com
> >
> >
> > "Hassan" <fatima_ja@.hotmail.com> wrote in message
> > news:e2MYfNynDHA.3700@.TK2MSFTNGP11.phx.gbl...
> > > btw , Kalen, in your latest article in SQL Mag about the optimiser,
> > listing
> > > 2 did take an index scan when you mentioned that it would take a table
> > scan
> > > bcos of less reads. My optimiser is not smart enough I guess as yours
> :-)
> > >
> > >
> > > "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> > > news:usV2PtwnDHA.1004@.TK2MSFTNGP09.phx.gbl...
> > > > There are definitely reasons why you wouldn't want to use an index,
> but
> > > most
> > > > of them can be solved by making sure your statistics are updated. If
> the
> > > > optimizer is incorrectly choosing to use a nonclustered index, you
can
> > far
> > > > far more reads than a simple table scan would take. You might also
> just
> > > want
> > > > to run the query without the index for testing and comparison
> purposes,
> > to
> > > > find out how much the index is really saving you, to see if it's
worth
> > the
> > > > cost of its maintenance.
> > > >
> > > > As Russell pointed out, you can use WITH INDEX(0) to force no index
to
> > be
> > > > used on a particular table.
> > > >
> > > > --
> > > > HTH
> > > > --
> > > > Kalen Delaney
> > > > SQL Server MVP
> > > > www.SolidQualityLearning.com
> > > >
> > > >
> > > > "London Developer" <dev@.nowhere.com> wrote in message
> > > > news:#otRHcwnDHA.2772@.TK2MSFTNGP12.phx.gbl...
> > > > > Geeze why would you want to?!
> > > > > I think your only way would be to drop the index. I don't think
you
> > can
> > > > > force the optimiser not to use an index...
> > > > >
> > > > > "Hassan" <fatima_ja@.hotmail.com> wrote in message
> > > > > news:OcGkZRwnDHA.2064@.TK2MSFTNGP11.phx.gbl...
> > > > > > I have a query thats using a particular index and I would like
to
> > > force
> > > > > the
> > > > > > query to do a table scan instead. How can i do so ?
> > > > > >
> > > > > > Heres a sample query
> > > > > >
> > > > > > select * from tableA where col1= 5
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>

How to findout if the column is Unique or Key?

---

I use sp_columns to retrieve colum information of a table.
But, it doesn't return the columns' index information such as Key or Unique..

How can I get these info along with those from sp_columns?

You want to take a look at sp_helpindex, sp_pkeys, sp_foreignkey stored procedures. Also, you want to take a look at info views (i.e. information_schema.*).

how to find size of the index

sp_spaceused <tablename>
select dpages,reserved,used from sysindexes where id=357576312
These values seem to be different. Can somebody explan?
sp_spaceused <tablename>
namerowsreserveddataindex_sizeunused
<tablename>1998 136 KB72 KB8 KB56 KB
select id from sysobjects where name = <table_name>
357576312
select dpages,reserved,used from sysindexes where id=357576312
dpagesreservedused
91710
select (9*8)+(17*8)+(10*8) 288
select (136+72+8+56) 272
"mani" wrote:

> sp_spaceused <tablename>
> select dpages,reserved,used from sysindexes where id=357576312
>
> These values seem to be different. Can somebody explan?
|||You need a little math help:
Reserved = Data + Index + Unused = Used + Unused
17 Reserved Pages * 8 KB/page = 136 KB =
9 Data Pages * 8 KB/page = 72 KB +
1 Index Page * 8 KB/page = 10 Used Pages - 9 Data Pages = 8 KB
(9 Data Pages + 1 Index Page) * 8 KB/page = 10 Used Pages * 8 KB/page = 80 KB
Now, (17 Reserved Pages - 10 Used Pages) * 8 KB/page =
7 Unused Pages * 8 KB/page = 56 KB.
You just have to figure out which pieces are which but it is all consistent.
Your problem was that you added Reserved to what was used: reserved is
already the grand total you were aiming for.
Hope this helps. What also can help is to read what the authors wrote: take
a look at the sp_spaceused stored procedure,
USE master
GO
EXEC dbo.sp_helptext 'dbo.sp_spaceused'
GO
You can find a lot of interesting information by examining the code for the
system procedures.
Sincerely,
Anthony Thomas
"mani" wrote:
[vbcol=seagreen]
> sp_spaceused <tablename>
> namerowsreserveddataindex_sizeunused
> <tablename>1998 136 KB72 KB8 KB56 KB
>
> select id from sysobjects where name = <table_name>
> 357576312
> select dpages,reserved,used from sysindexes where id=357576312
> dpagesreservedused
> 91710
>
> select (9*8)+(17*8)+(10*8) 288
> select (136+72+8+56) 272
> "mani" wrote:

how to find size of the index

sp_spaceused <tablename>
select dpages,reserved,used from sysindexes where id=357576312
These values seem to be different. Can somebody explan?sp_spaceused <tablename>
name rows reserved data index_size unus
ed
<tablename> 1998 136 KB 72 KB 8 KB 56 KB
select id from sysobjects where name = <table_name>
357576312
select dpages,reserved,used from sysindexes where id=357576312
dpages reserved used
9 17 10
select (9*8)+(17*8)+(10*8) 288
select (136+72+8+56) 272
"mani" wrote:

> sp_spaceused <tablename>
> select dpages,reserved,used from sysindexes where id=357576312
>
> These values seem to be different. Can somebody explan?|||You need a little math help:
Reserved = Data + Index + Unused = Used + Unused
17 Reserved Pages * 8 KB/page = 136 KB =
9 Data Pages * 8 KB/page = 72 KB +
1 Index Page * 8 KB/page = 10 Used Pages - 9 Data Pages = 8 KB
(9 Data Pages + 1 Index Page) * 8 KB/page = 10 Used Pages * 8 KB/page = 80 K
B
Now, (17 Reserved Pages - 10 Used Pages) * 8 KB/page =
7 Unused Pages * 8 KB/page = 56 KB.
You just have to figure out which pieces are which but it is all consistent.
Your problem was that you added Reserved to what was used: reserved is
already the grand total you were aiming for.
Hope this helps. What also can help is to read what the authors wrote: take
a look at the sp_spaceused stored procedure,
USE master
GO
EXEC dbo.sp_helptext 'dbo.sp_spaceused'
GO
You can find a lot of interesting information by examining the code for the
system procedures.
Sincerely,
Anthony Thomas
"mani" wrote:
[vbcol=seagreen]
> sp_spaceused <tablename>
> name rows reserved data index_size unus
ed
> <tablename> 1998 136 KB 72 KB 8 KB 56 KB
>
> select id from sysobjects where name = <table_name>
> 357576312
> select dpages,reserved,used from sysindexes where id=357576312
> dpages reserved used
> 9 17 10
>
> select (9*8)+(17*8)+(10*8) 288
> select (136+72+8+56) 272
> "mani" wrote:
>

how to find size of the index

sp_spaceused <tablename>
select dpages,reserved,used from sysindexes where id=357576312
These values seem to be different. Can somebody explan?sp_spaceused <tablename>
name rows reserved data index_size unused
<tablename> 1998 136 KB 72 KB 8 KB 56 KB
select id from sysobjects where name = <table_name>
357576312
select dpages,reserved,used from sysindexes where id=357576312
dpages reserved used
9 17 10
select (9*8)+(17*8)+(10*8) 288
select (136+72+8+56) 272
"mani" wrote:
> sp_spaceused <tablename>
> select dpages,reserved,used from sysindexes where id=357576312
>
> These values seem to be different. Can somebody explan?|||You need a little math help:
Reserved = Data + Index + Unused = Used + Unused
17 Reserved Pages * 8 KB/page = 136 KB =9 Data Pages * 8 KB/page = 72 KB +
1 Index Page * 8 KB/page = 10 Used Pages - 9 Data Pages = 8 KB
(9 Data Pages + 1 Index Page) * 8 KB/page = 10 Used Pages * 8 KB/page = 80 KB
Now, (17 Reserved Pages - 10 Used Pages) * 8 KB/page =7 Unused Pages * 8 KB/page = 56 KB.
You just have to figure out which pieces are which but it is all consistent.
Your problem was that you added Reserved to what was used: reserved is
already the grand total you were aiming for.
Hope this helps. What also can help is to read what the authors wrote: take
a look at the sp_spaceused stored procedure,
USE master
GO
EXEC dbo.sp_helptext 'dbo.sp_spaceused'
GO
You can find a lot of interesting information by examining the code for the
system procedures.
Sincerely,
Anthony Thomas
"mani" wrote:
> sp_spaceused <tablename>
> name rows reserved data index_size unused
> <tablename> 1998 136 KB 72 KB 8 KB 56 KB
>
> select id from sysobjects where name = <table_name>
> 357576312
> select dpages,reserved,used from sysindexes where id=357576312
> dpages reserved used
> 9 17 10
>
> select (9*8)+(17*8)+(10*8) 288
> select (136+72+8+56) 272
> "mani" wrote:
> > sp_spaceused <tablename>
> >
> > select dpages,reserved,used from sysindexes where id=357576312
> >
> >
> > These values seem to be different. Can somebody explan?

How to find out tables which cant be replicated

Is there any query to find out all the tables without a Primary key or without a Unique index ?select * from INFORMATION_SCHEMA.TABLES where TABLE_NAME not in(
select TABLE_NAME from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where CONSTRAINT_TYPE in('PRIMARY KEY', 'UNIQUE')
)|||I am assuming for Transactional Replication, minimum requirement is a unique index !

Thanks for above query|||BOL:

Microsoft SQL Server 2000 automatically creates unique indexes to enforce the uniqueness requirements of PRIMARY KEY and UNIQUE constraints.

My query returns list of tables without PRIMARY KEY or UNIQUE constraints. Try it on your database.

Former Kentuckian.

How to Find Index Size / Usage (mb)

I'm trying to establish the mb usage of a series of nonclustered indexes, I'm used to using the manage indexes GUI in 6.5, and showcontig doesn't quite give me what I want, any suggestions?Have you tried sp_spaceused <table_name> yet?|||That's a great help thanks, is it possible to drill down into the 3 indexes to see how the usage is split?

how to find index # and name in the same place

Hi,
I am wondering if anyone has a query handy for looking up the indexes
for a given table, that would show both the index name *and* the index
number in the output
Thanks much
tootsuite,
On SQL 2K:
select
o.name TableName
, i.indid IndexNumber
, i.name IndexName
from sysobjects o
inner join sysindexes i on o.id = i.id
order by o.name, i.indid
-- Bill
<tootsuite@.gmail.com> wrote in message
news:1168623613.741766.142850@.m58g2000cwm.googlegr oups.com...
> Hi,
> I am wondering if anyone has a query handy for looking up the indexes
> for a given table, that would show both the index name *and* the index
> number in the output
> Thanks much
>
|||Thanks much, this works on 2005 also
AlterEgo wrote:[vbcol=seagreen]
> tootsuite,
> On SQL 2K:
> select
> o.name TableName
> , i.indid IndexNumber
> , i.name IndexName
> from sysobjects o
> inner join sysindexes i on o.id = i.id
> order by o.name, i.indid
> -- Bill
> <tootsuite@.gmail.com> wrote in message
> news:1168623613.741766.142850@.m58g2000cwm.googlegr oups.com...

how to find index # and name in the same place

Hi,
I am wondering if anyone has a query handy for looking up the indexes
for a given table, that would show both the index name *and* the index
number in the output
Thanks muchtootsuite,
On SQL 2K:
select
o.name TableName
, i.indid IndexNumber
, i.name IndexName
from sysobjects o
inner join sysindexes i on o.id = i.id
order by o.name, i.indid
-- Bill
<tootsuite@.gmail.com> wrote in message
news:1168623613.741766.142850@.m58g2000cwm.googlegroups.com...
> Hi,
> I am wondering if anyone has a query handy for looking up the indexes
> for a given table, that would show both the index name *and* the index
> number in the output
> Thanks much
>|||Thanks much, this works on 2005 also
AlterEgo wrote:[vbcol=seagreen]
> tootsuite,
> On SQL 2K:
> select
> o.name TableName
> , i.indid IndexNumber
> , i.name IndexName
> from sysobjects o
> inner join sysindexes i on o.id = i.id
> order by o.name, i.indid
> -- Bill
> <tootsuite@.gmail.com> wrote in message
> news:1168623613.741766.142850@.m58g2000cwm.googlegroups.com...

how to find index # and name in the same place

Hi,
I am wondering if anyone has a query handy for looking up the indexes
for a given table, that would show both the index name *and* the index
number in the output
Thanks muchtootsuite,
On SQL 2K:
select
o.name TableName
, i.indid IndexNumber
, i.name IndexName
from sysobjects o
inner join sysindexes i on o.id = i.id
order by o.name, i.indid
-- Bill
<tootsuite@.gmail.com> wrote in message
news:1168623613.741766.142850@.m58g2000cwm.googlegroups.com...
> Hi,
> I am wondering if anyone has a query handy for looking up the indexes
> for a given table, that would show both the index name *and* the index
> number in the output
> Thanks much
>|||Thanks much, this works on 2005 also
AlterEgo wrote:
> tootsuite,
> On SQL 2K:
> select
> o.name TableName
> , i.indid IndexNumber
> , i.name IndexName
> from sysobjects o
> inner join sysindexes i on o.id = i.id
> order by o.name, i.indid
> -- Bill
> <tootsuite@.gmail.com> wrote in message
> news:1168623613.741766.142850@.m58g2000cwm.googlegroups.com...
> > Hi,
> >
> > I am wondering if anyone has a query handy for looking up the indexes
> > for a given table, that would show both the index name *and* the index
> > number in the output
> >
> > Thanks much
> >

how to find if index key is ASC or DESC from system tables?

There is a index: CustomerInfo_1
with keys: customerId, EnteryDate DESC
I could not find where the order of index key (i.e. whether the key is ascending or descending) is stored?
I tried system tables such as sysindexes and sysindexkeys tables. But could not find it.
Any help in this regard will be truly appreciated.

Thank you.
Regards,
Anuj GoyalWhen defining indexes, you can specify whether the data for each column is stored in ascending or descending order. If neither direction is specified, ascending is the default.

You can use INDEXKEY_PROPERTY to know the order, refer to books online for more information.

How to Find Full Text Index Catalog Creation - Time duration.

I Have used full text indexin my project.
I want to find "Full Text Index Catalog Creation" Time duration.
Is there any method to find.
I am using SQL Server 2005

Regards

Vasanth Thangasamy

The start and stop times are logged in the event log. I believe the stop time includes the duration but not sure without checking.