Index problem

I have a table, which contains address information,
have non-clustered index on phone no, search on phone
number was very quick, now users requested that search on
address be possible too, I created non-clustered index on
address which speeds up search by address but search by
phone number is extremely slow, query plan shows that
optimizer is using this new index instead of index on
phone number even during phone search, I updated stats
with full scan and still same result.
Dbcc show_statistics shows better selectivity for index on
address than phone number.
It is a very complicated dynamic query which I cannot
force optimizer to use specific index.
Any help will be appreciatedI would play with "set statistics IO on" to see your logical reads.
How are they asking for the address and phone number? (SQL syntax)
When they only ask for phone number how do they ask this? For example, if I
said
select phonenumber from table where street = 'main'
SQL would not use your nonclustered index on phone(unless you had a
clustered index on street)
You might consider covering non-clustered indexes.
HTH
--
Ray Higdon MCSE, MCDBA, CCNA
--
"Sam" <sam.moayedi@.moneymanagement.org> wrote in message
news:044b01c39350$a27e2a70$a401280a@.phx.gbl...
> I have a table, which contains address information,
> have non-clustered index on phone no, search on phone
> number was very quick, now users requested that search on
> address be possible too, I created non-clustered index on
> address which speeds up search by address but search by
> phone number is extremely slow, query plan shows that
> optimizer is using this new index instead of index on
> phone number even during phone search, I updated stats
> with full scan and still same result.
> Dbcc show_statistics shows better selectivity for index on
> address than phone number.
> It is a very complicated dynamic query which I cannot
> force optimizer to use specific index.
> Any help will be appreciated
>|||The sp goes something like this
select * from table where phoneno='xxxxxxxxx'
It is more complicated than|||Then your NC index on phone number definitely won't help, it would make no
sense to go out and find phone numbers only to redirect back to the table,
did you say you had a clustered index? Again, I would play with my indexes
using "set statistics IO on"
If you were saying
select phone_no from table where phone_no = 'blah'
Then your NCI would act as a covering index and SQL would naturally go to
the index and not touch the table.
HTH
--
Ray Higdon MCSE, MCDBA, CCNA
--
"Sam" <sam.moayedi@.moneymanagement.org> wrote in message
news:05f701c3935f$d57f1ec0$a401280a@.phx.gbl...
> The sp goes something like this
> select * from table where phoneno='xxxxxxxxx'
> It is more complicated than|||Sam
Have you checked for fragmentation on the phone number
index? Although it mostly tends to affect clustered
indexes, I have seen very similar problems with fragmented
non-clustered indexes.
Regards
Johnsql

index on computed column ignored

Hello,
I have a non-unique, non-clustered index on a computed column:
SET ARITHABORT ON
SET CONCAT_NULL_YIELDS_NULL ON
SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET NUMERIC_ROUNDABORT OFF
CREATE NONCLUSTERED INDEX [IX_tbVehicleMain_ComputedYearTest] ON
[cweb].[tbVehicleMainTest]
(
[ComputedYear] ASC
)
INCLUDE ( [Make],
[Model]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF,
ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON
[PRIMARY]
The column computedYear is a "Presisted computed column" as:
(case when [DateFirstRegistered]<=[DateOfManufacture] then
datepart(year,[DateFirstRegistered]) when
[DateFirstRegistered]>[DateOfManufacture] then datepart(year,
[DateOfManufacture]) else datepart(year,[DateFirstRegistered]) end)
It basically year for some datetime columns and choose the year.
When I do query:
set QUOTED_IDENTIFIER on;
set ANSI_NULLS on;
set ANSI_PADDING on;
set ANSI_WARNINGS on;
set ARITHABORT on;
set CONCAT_NULL_YIELDS_NULL on;
set NUMERIC_ROUNDABORT off;
select [Make],[Model] from cweb.tbVehicleMainTest where
[ComputedYear]=1
Sql Server uses index scan instead of index seek.
Would someone mind to point out what is wrong with my method? (I am
using sql 2005)
If you use an index hint to force it to use that index does it perform
better? If I am understanding your code, I think you should also INCLUDE for
date columns.
Jason Massie
http://statisticsio.com
<DAXU@.hotmail.com> wrote in message
news:9c39ea60-7c62-42b5-8385-763b0e94f90b@.a28g2000hsc.googlegroups.com...
> Hello,
> I have a non-unique, non-clustered index on a computed column:
> SET ARITHABORT ON
> SET CONCAT_NULL_YIELDS_NULL ON
> SET QUOTED_IDENTIFIER ON
> SET ANSI_NULLS ON
> SET ANSI_PADDING ON
> SET ANSI_WARNINGS ON
> SET NUMERIC_ROUNDABORT OFF
> CREATE NONCLUSTERED INDEX [IX_tbVehicleMain_ComputedYearTest] ON
> [cweb].[tbVehicleMainTest]
> (
> [ComputedYear] ASC
> )
> INCLUDE ( [Make],
> [Model]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
> SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF,
> ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON
> [PRIMARY]
> The column computedYear is a "Presisted computed column" as:
> (case when [DateFirstRegistered]<=[DateOfManufacture] then
> datepart(year,[DateFirstRegistered]) when
> [DateFirstRegistered]>[DateOfManufacture] then datepart(year,
> [DateOfManufacture]) else datepart(year,[DateFirstRegistered]) end)
> It basically year for some datetime columns and choose the year.
> When I do query:
> set QUOTED_IDENTIFIER on;
> set ANSI_NULLS on;
> set ANSI_PADDING on;
> set ANSI_WARNINGS on;
> set ARITHABORT on;
> set CONCAT_NULL_YIELDS_NULL on;
> set NUMERIC_ROUNDABORT off;
> select [Make],[Model] from cweb.tbVehicleMainTest where
> [ComputedYear]=1
> Sql Server uses index scan instead of index seek.
> Would someone mind to point out what is wrong with my method? (I am
> using sql 2005)

index on computed column ignored

Hello,
I have a non-unique, non-clustered index on a computed column:
SET ARITHABORT ON
SET CONCAT_NULL_YIELDS_NULL ON
SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET NUMERIC_ROUNDABORT OFF
CREATE NONCLUSTERED INDEX [IX_tbVehicleMain_ComputedYearTest] ON
[cweb].[tbVehicleMainTest]
(
[ComputedYear] ASC
)
INCLUDE ( [Make],
[Model]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF,
ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON
[PRIMARY]
The column computedYear is a "Presisted computed column" as:
(case when [DateFirstRegistered]<=[DateOfManufacture] then
datepart(year,[DateFirstRegistered]) when
[DateFirstRegistered]>[DateOfManufacture] then datepart(year,
[DateOfManufacture]) else datepart(year,[DateFirstRegistered]) end)
It basically year for some datetime columns and choose the year.
When I do query:
set QUOTED_IDENTIFIER on;
set ANSI_NULLS on;
set ANSI_PADDING on;
set ANSI_WARNINGS on;
set ARITHABORT on;
set CONCAT_NULL_YIELDS_NULL on;
set NUMERIC_ROUNDABORT off;
select [Make],[Model] from cweb.tbVehicleMainTest where
[ComputedYear]=1
Sql Server uses index scan instead of index seek.
Would someone mind to point out what is wrong with my method? (I am
using sql 2005)If you use an index hint to force it to use that index does it perform
better? If I am understanding your code, I think you should also INCLUDE for
date columns.
Jason Massie
http://statisticsio.com
<DAXU@.hotmail.com> wrote in message
news:9c39ea60-7c62-42b5-8385-763b0e94f90b@.a28g2000hsc.googlegroups.com...
> Hello,
> I have a non-unique, non-clustered index on a computed column:
> SET ARITHABORT ON
> SET CONCAT_NULL_YIELDS_NULL ON
> SET QUOTED_IDENTIFIER ON
> SET ANSI_NULLS ON
> SET ANSI_PADDING ON
> SET ANSI_WARNINGS ON
> SET NUMERIC_ROUNDABORT OFF
> CREATE NONCLUSTERED INDEX [IX_tbVehicleMain_ComputedYearTest] ON
> [cweb].[tbVehicleMainTest]
> (
> [ComputedYear] ASC
> )
> INCLUDE ( [Make],
> [Model]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
> SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF,
> ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON
> [PRIMARY]
> The column computedYear is a "Presisted computed column" as:
> (case when [DateFirstRegistered]<=[DateOfManufacture] then
> datepart(year,[DateFirstRegistered]) when
> [DateFirstRegistered]>[DateOfManufacture] then datepart(year,
> [DateOfManufacture]) else datepart(year,[DateFirstRegistered]) end
)
> It basically year for some datetime columns and choose the year.
> When I do query:
> set QUOTED_IDENTIFIER on;
> set ANSI_NULLS on;
> set ANSI_PADDING on;
> set ANSI_WARNINGS on;
> set ARITHABORT on;
> set CONCAT_NULL_YIELDS_NULL on;
> set NUMERIC_ROUNDABORT off;
> select [Make],[Model] from cweb.tbVehicleMainTest where
> [ComputedYear]=1
> Sql Server uses index scan instead of index seek.
> Would someone mind to point out what is wrong with my method? (I am
> using sql 2005)

index on computed column ignored

Hello,
I have a non-unique, non-clustered index on a computed column:
SET ARITHABORT ON
SET CONCAT_NULL_YIELDS_NULL ON
SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET NUMERIC_ROUNDABORT OFF
CREATE NONCLUSTERED INDEX [IX_tbVehicleMain_ComputedYearTest] ON
[cweb].[tbVehicleMainTest]
(
[ComputedYear] ASC
)
INCLUDE ( [Make],
[Model]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF,
ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON
[PRIMARY]
The column computedYear is a "Presisted computed column" as:
(case when [DateFirstRegistered]<=[DateOfManufacture] then
datepart(year,[DateFirstRegistered]) when
[DateFirstRegistered]>[DateOfManufacture] then datepart(year,
[DateOfManufacture]) else datepart(year,[DateFirstRegistered]) end)
It basically year for some datetime columns and choose the year.
When I do query:
set QUOTED_IDENTIFIER on;
set ANSI_NULLS on;
set ANSI_PADDING on;
set ANSI_WARNINGS on;
set ARITHABORT on;
set CONCAT_NULL_YIELDS_NULL on;
set NUMERIC_ROUNDABORT off;
select [Make],[Model] from cweb.tbVehicleMainTest where
[ComputedYear]=1
Sql Server uses index scan instead of index seek.
Would someone mind to point out what is wrong with my method? (I am
using sql 2005)If you use an index hint to force it to use that index does it perform
better? If I am understanding your code, I think you should also INCLUDE for
date columns.
Jason Massie
http://statisticsio.com
<DAXU@.hotmail.com> wrote in message
news:9c39ea60-7c62-42b5-8385-763b0e94f90b@.a28g2000hsc.googlegroups.com...
> Hello,
> I have a non-unique, non-clustered index on a computed column:
> SET ARITHABORT ON
> SET CONCAT_NULL_YIELDS_NULL ON
> SET QUOTED_IDENTIFIER ON
> SET ANSI_NULLS ON
> SET ANSI_PADDING ON
> SET ANSI_WARNINGS ON
> SET NUMERIC_ROUNDABORT OFF
> CREATE NONCLUSTERED INDEX [IX_tbVehicleMain_ComputedYearTest] ON
> [cweb].[tbVehicleMainTest]
> (
> [ComputedYear] ASC
> )
> INCLUDE ( [Make],
> [Model]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
> SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF,
> ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON
> [PRIMARY]
> The column computedYear is a "Presisted computed column" as:
> (case when [DateFirstRegistered]<=[DateOfManufacture] then
> datepart(year,[DateFirstRegistered]) when
> [DateFirstRegistered]>[DateOfManufacture] then datepart(year,
> [DateOfManufacture]) else datepart(year,[DateFirstRegistered]) end)
> It basically year for some datetime columns and choose the year.
> When I do query:
> set QUOTED_IDENTIFIER on;
> set ANSI_NULLS on;
> set ANSI_PADDING on;
> set ANSI_WARNINGS on;
> set ARITHABORT on;
> set CONCAT_NULL_YIELDS_NULL on;
> set NUMERIC_ROUNDABORT off;
> select [Make],[Model] from cweb.tbVehicleMainTest where
> [ComputedYear]=1
> Sql Server uses index scan instead of index seek.
> Would someone mind to point out what is wrong with my method? (I am
> using sql 2005)

Index Help

Hi,
Can any please explain to me the diff.?
Use Northwind
I created a Non-Clustered index on "LastName' and "FirstName" (Combined)
Select * from customers where LastName = 'Hello' and FirstName = 'World'
Select * from customers where FirstName = 'World' and LastName = 'Hello'
Does that make the difference?
I really would like to know the Clustered Index part as well...!!!!!
I created a Clustered index on "LastName' and "FirstName" (Combined)
Select * from customers where LastName = 'Hello' and FirstName = 'World'
Select * from customers where FirstName = 'World' and LastName = 'Hello'
Does that make any difference as well if I filter that FirstName first?
Thanks for the answer on both clustered and Non clustered
EdIt doesn't make any difference. The query optimiser is smart enough to
rearrange the order of the parts of the WHERE clause in order to best
use the indexes it has available. See example below:
create table dbo.mikejunk
(
LastName varchar(50) not null,
FirstName varchar(50) not null
)
go
create nonclustered index IX_mikejunk on dbo.mikejunk (LastName,
FirstName)
go
insert into dbo.mikejunk (LastName, FirstName) values ('Ben-Gan',
'Itzik')
insert into dbo.mikejunk (LastName, FirstName) values ('Delaney',
'Kalen')
insert into dbo.mikejunk (LastName, FirstName) values ('Tripp',
'Kimberly')
insert into dbo.mikejunk (LastName, FirstName) values ('Waymire',
'Richard')
insert into dbo.mikejunk (LastName, FirstName) values ('Kelly',
'Andrew')
insert into dbo.mikejunk (LastName, FirstName) values ('Bertrand',
'Aaron')
insert into dbo.mikejunk (LastName, FirstName) values ('Sommarskog',
'Erland')
insert into dbo.mikejunk (LastName, FirstName) values ('Epprecht',
'Mike')
insert into dbo.mikejunk (LastName, FirstName) values ('Kass', 'Steve')
insert into dbo.mikejunk (LastName, FirstName) values ('Karaszi',
'Tibor')
go
set showplan_text on
go
select * from dbo.mikejunk where LastName = 'Delaney' and FirstName
= 'Kalen'
select * from dbo.mikejunk where FirstName = 'Kalen' and LastName =
'Delaney'
go
set showplan_text off
go
drop table dbo.mikejunk
go
If you have a look at the execution plans you'll see they are both the same:
StmtText
----
--
select * from dbo.mikejunk where LastName = 'Delaney' and FirstName
= 'Kalen'
StmtText
----
----
|--Table Scan(OBJECT:([tempdb].[dbo].[mikejunk]),
WHERE:([tempdb].[dbo].[mikejunk].[LastName]=[@.1] AND
[tempdb].[dbo].[mikejunk].[FirstName]=[@.2]))
StmtText
----
--
select * from dbo.mikejunk where FirstName = 'Kalen' and LastName =
'Delaney'
StmtText
----
----
|--Table Scan(OBJECT:([tempdb].[dbo].[mikejunk]),
WHERE:([tempdb].[dbo].[mikejunk].[FirstName]=[@.1] AND
[tempdb].[dbo].[mikejunk].[LastName]=[@.2]))
Same with the clustered index (try changing the "nonclustered" to
"clustered" in the test script above and look at the results).
However, if you have more columns than just LastName & FirstName in your
table then it probably becomes a moot point, since at the very least the
query engine will need to do a lookup into the clustered index to get
the other columns for the SELECT list or it may just decide that it's
more efficient to do a table/clustered index scan rather than look at
the nonclustered index at all.
*mike hodgson*
blog: http://sqlnerd.blogspot.com
Ed wrote:

>Hi,
> Can any please explain to me the diff.?
>Use Northwind
>I created a Non-Clustered index on "LastName' and "FirstName" (Combined)
>Select * from customers where LastName = 'Hello' and FirstName = 'World'
>Select * from customers where FirstName = 'World' and LastName = 'Hello'
>Does that make the difference?
>I really would like to know the Clustered Index part as well...!!!!!
>I created a Clustered index on "LastName' and "FirstName" (Combined)
>Select * from customers where LastName = 'Hello' and FirstName = 'World'
>Select * from customers where FirstName = 'World' and LastName = 'Hello'
>Does that make any difference as well if I filter that FirstName first?
>Thanks for the answer on both clustered and Non clustered
>Ed
>
>|||Oops, sorry - I just noticed that the showplan results I posted were
from a run I did with no indexes. <blush> Here are the showplan
results I meant to post (using the nonclustered index):
StmtText
----
--
select * from dbo.mikejunk where LastName = 'Delaney' and FirstName
= 'Kalen'
StmtText
----
----
--
|--Index S(OBJECT:([tempdb].[dbo].[mikejunk].[IX_mikejunk]),
SEEK:([tempdb].[dbo].[mikejunk].[LastName]=[@.1] AND
[tempdb].[dbo].[mikejunk].[FirstName]=[@.2]) ORDERED FORWARD)
StmtText
----
--
select * from dbo.mikejunk where FirstName = 'Kalen' and LastName =
'Delaney'
StmtText
----
----
--
|--Index S(OBJECT:([tempdb].[dbo].[mikejunk].[IX_mikejunk]),
SEEK:([tempdb].[dbo].[mikejunk].[LastName]=[@.2] AND
[tempdb].[dbo].[mikejunk].[FirstName]=[@.1]) ORDERED FORWARD)
*mike hodgson*
blog: http://sqlnerd.blogspot.com
Mike Hodgson wrote:
> It doesn't make any difference. The query optimiser is smart enough
> to rearrange the order of the parts of the WHERE clause in order to
> best use the indexes it has available. See example below:
> create table dbo.mikejunk
> (
> LastName varchar(50) not null,
> FirstName varchar(50) not null
> )
> go
> create nonclustered index IX_mikejunk on dbo.mikejunk (LastName,
> FirstName)
> go
> insert into dbo.mikejunk (LastName, FirstName) values ('Ben-Gan',
> 'Itzik')
> insert into dbo.mikejunk (LastName, FirstName) values ('Delaney',
> 'Kalen')
> insert into dbo.mikejunk (LastName, FirstName) values ('Tripp',
> 'Kimberly')
> insert into dbo.mikejunk (LastName, FirstName) values ('Waymire',
> 'Richard')
> insert into dbo.mikejunk (LastName, FirstName) values ('Kelly',
> 'Andrew')
> insert into dbo.mikejunk (LastName, FirstName) values ('Bertrand',
> 'Aaron')
> insert into dbo.mikejunk (LastName, FirstName) values
> ('Sommarskog', 'Erland')
> insert into dbo.mikejunk (LastName, FirstName) values ('Epprecht',
> 'Mike')
> insert into dbo.mikejunk (LastName, FirstName) values ('Kass',
> 'Steve')
> insert into dbo.mikejunk (LastName, FirstName) values ('Karaszi',
> 'Tibor')
> go
> set showplan_text on
> go
> select * from dbo.mikejunk where LastName = 'Delaney' and
> FirstName = 'Kalen'
> select * from dbo.mikejunk where FirstName = 'Kalen' and LastName
> = 'Delaney'
> go
> set showplan_text off
> go
> drop table dbo.mikejunk
> go
>
> If you have a look at the execution plans you'll see they are both the
> same:
> StmtText
> ---
--
> select * from dbo.mikejunk where LastName = 'Delaney' and
> FirstName = 'Kalen'
> StmtText
> ---
----
--
> |--Table Scan(OBJECT:([tempdb].[dbo].[mikejunk]),
> WHERE:([tempdb].[dbo].[mikejunk].[LastName]=[@.1] AND
> [tempdb].[dbo].[mikejunk].[FirstName]=[@.2]))
>
> StmtText
> ---
--
> select * from dbo.mikejunk where FirstName = 'Kalen' and LastName
> = 'Delaney'
> StmtText
> ---
----
--
> |--Table Scan(OBJECT:([tempdb].[dbo].[mikejunk]),
> WHERE:([tempdb].[dbo].[mikejunk].[FirstName]=[@.1] AND
> [tempdb].[dbo].[mikejunk].[LastName]=[@.2]))
> Same with the clustered index (try changing the "nonclustered" to
> "clustered" in the test script above and look at the results).
> However, if you have more columns than just LastName & FirstName in
> your table then it probably becomes a moot point, since at the very
> least the query engine will need to do a lookup into the clustered
> index to get the other columns for the SELECT list or it may just
> decide that it's more efficient to do a table/clustered index scan
> rather than look at the nonclustered index at all.
> --
> *mike hodgson*
> blog: http://sqlnerd.blogspot.com
>
> Ed wrote:
>

index defrag rebuild

Hi,
I have a table that has one clustered index and one non-clustered index
defined. What difference would it be if you do the following actions:
DBCC INDEXDEFRAG
DBCC DBREINDEX
REBUILD INDEX
Also, if the table had 100 rows in the beginning when the indexes were
created. After creating the indexes 100 more rows were inserted into the
table. When would these newly inserted rows gets included in the index?
Thanks in advance.
Yes, after you create the index, this index will be updated when records are
inserted or deleted.
If your table only has a few hundred records you do not need to worry about
reindexing. Even if you have big tables you need to check the level of index
fragmentation first. Start by looking at DBCC SHOWCONTIG or
sys.dm_db_index_physical_stats on BOL.
Hope this helps,
Ben Nevarez
"sharman" wrote:

> Hi,
> I have a table that has one clustered index and one non-clustered index
> defined. What difference would it be if you do the following actions:
> DBCC INDEXDEFRAG
> DBCC DBREINDEX
> REBUILD INDEX
> Also, if the table had 100 rows in the beginning when the indexes were
> created. After creating the indexes 100 more rows were inserted into the
> table. When would these newly inserted rows gets included in the index?
> Thanks in advance.
|||> Also, if the table had 100 rows in the beginning when the indexes were
> created. After creating the indexes 100 more rows were inserted into the
> table. When would these newly inserted rows gets included in the index?
When they are inserted. That’s when the database will touch the indexes. You
have the clustered index in there. That clustered index is your table however
wired it sounds, it is your physical order and the physical pages can be
organized only one way. BOL for sql 2005 has pretty good description what a
clustered index is. Have questions, ask…

> DBCC INDEXDEFRAG
That is a low priority operation. Pretty much it will reorganize the index
for you, will shuffle pages in place. It will take in consideration the fill
factor of the index and compact the pages, if there are any empty ones as a
result of the compaction, they will be freed (removed that is). This is an
on-line operation and it does not lock other processes and is not locked by
them, does not create long living locks, will skip pages locked by other
processes. What does it do?
DBCC INDEXDEFRAG de-fragments the leaf level of an index so that the
physical order of the pages matches the left-to-right logical order of the
leaf nodes, therefore improving index-scanning performance. When DBCC
INDEXDEFRAG is run, index de-fragmentation occurs serially. This means that
the operation on a single index is performed using a single thread. No
parallelism occurs. Also, operations on multiple indexes from the same DBCC
INDEXDEFRAG statement are performed on one index at a time.
Unlike DBCC DBREINDEX is the real rebuild. It is not an on-line operation
and holds long term locks. DBCC DBREINDEX in other words will and should
conflict with any dml on the same table, DBCC INDEXDEFRAG will not
lock/conflick as it was mentioned earlier.
The chose if -time and the fragmentation level you have. If your
maintenance windows allows and your fragmentation can not be fixed by
INDEXDEFRAG, then rebuild that thing.
Does not matter which sql command is used to de-fragment the index, it is
always fully logged, does not matter what mode is your database in. Therefore
do not run INDEXDEFRAG when your database is active period, all you are going
to get is about 1.4 size of your data in tlogs and you did not de-fragment
your index anyways, because the applications were 'dancing' on it locking
pages, INDEXDEFRAG has skipped those, but it was really busy trying to do
the job you have asked for. BOL says that defragmentation of a very
fragmented index can generate more log than a fully logged index creation.
I've seen that one not once, not a joke . The defragmentation, is performed
as a series of short transactions, but still tlog gets quite big (esp when
you have fast SAN under the tlogs).
DBREINDEX is not good to run when the database is active, pick your quiet
time if you can. Otherwise you will see spike in locks and dead locks. What
will it do to your system depends how fault tolerant your applications are
and if they can handle dead lock errors (many developers just do not code
such a stuff)
When to choose what:
If the index is interleaved with other indexes on disk, running DBCC
INDEXDEFRAG against that index does not make all leaf pages in the index
contiguous. Therefore if clustering is what you are after, rebuild.
Maintenance plans:
Native Rebuild Index Task uses the ALTER INDEX statement when connected to
SQL Server 2005, and the DBCC DBREINDEX statement when connected to SQL
Server 2000
If you are using 3-rd party backup/maintenance read carefully their doc and
just run good old trace to see what exactly are they calling. The trace will
show you what is it, it may be not what the doc has…

> DBCC DBREINDEX
> REBUILD INDEX
On index rebuild BOL has pretty good description.
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/a28c684a-c4e9-4b24-a7ae-e248808b31e9.htm
Making a decision if you have fragmentation.
DBCC SHOWCONTIG WITH TABLERESULTS
Avg. Page Density (full):
average level how filled the pages are. The value should be compared to
the fill factor setting of the particular index, then you look and decide
whether or not the index is internally fragmented.
Scan Density:
the ratio between the Best Count (what was necessary to read when
scanning all the pages of the index) and Actual Count of extents (what did
it actually read). You want this to be as close to 100 as possible. 75% and
below definitely indicates external fragmentation. It is a fine line to
deside what is fragmented what is not. you may have a system that just
creates such a number because it is it's nature and this is how your data are
insrterd,deleted,updated and you have to live with it.
Logical Scan Fragmentation:
shows the ratio of pages out of logical order. You want this close to 0.
10% + indicates external fragmentation.
I've seen references that "Extent Scan Fragmentation" is not really
important. This is because the algorithm that calculates it does not work for
indexes that span over multiple files. According to some Microsoft sources,
in the next version of SQL Server, DBCC SHOWCONTIG will not even include this
metric.
I did not mess with 2008 to test that one to be honest.
Thanks, Liliya
"sharman" wrote:

> Hi,
> I have a table that has one clustered index and one non-clustered index
> defined. What difference would it be if you do the following actions:
> DBCC INDEXDEFRAG
> DBCC DBREINDEX
> REBUILD INDEX
> Also, if the table had 100 rows in the beginning when the indexes were
> created. After creating the indexes 100 more rows were inserted into the
> table. When would these newly inserted rows gets included in the index?
> Thanks in advance.
|||> Also, if the table had 100 rows in the beginning when the indexes were
> created. After creating the indexes 100 more rows were inserted into the
> table. When would these newly inserted rows gets included in the index?
When they are inserted. That’s when the database will touch the indexes. You
have the clustered index in there. That clustered index is your table however
wired it sounds, it is your physical order and the physical pages can be
organized only one way. BOL for sql 2005 has pretty good description what a
clustered index is. Have questions, ask…

> DBCC INDEXDEFRAG
That is a low priority operation. Pretty much it will reorganize the index
for you, will shuffle pages in place. It will take in consideration the fill
factor of the index and compact the pages, if there are any empty ones as a
result of the compaction, they will be freed (removed that is). This is an
on-line operation and it does not lock other processes and is not locked by
them, does not create long living locks, will skip pages locked by other
processes. What does it do?
DBCC INDEXDEFRAG de-fragments the leaf level of an index so that the
physical order of the pages matches the left-to-right logical order of the
leaf nodes, therefore improving index-scanning performance. When DBCC
INDEXDEFRAG is run, index de-fragmentation occurs serially. This means that
the operation on a single index is performed using a single thread. No
parallelism occurs. Also, operations on multiple indexes from the same DBCC
INDEXDEFRAG statement are performed on one index at a time.
Unlike DBCC DBREINDEX is the real rebuild. It is not an on-line operation
and holds long term locks. DBCC DBREINDEX in other words will and should
conflict with any dml on the same table, DBCC INDEXDEFRAG will not
lock/conflick as it was mentioned earlier.
The chose if -time and the fragmentation level you have. If your
maintenance windows allows and your fragmentation can not be fixed by
INDEXDEFRAG, then rebuild that thing.
Does not matter which sql command is used to de-fragment the index, it is
always fully logged, does not matter what mode is your database in. Therefore
do not run INDEXDEFRAG when your database is active period, all you are going
to get is about 1.4 size of your data in tlogs and you did not de-fragment
your index anyways, because the applications were 'dancing' on it locking
pages, INDEXDEFRAG has skipped those, but it was really busy trying to do
the job you have asked for. BOL says that defragmentation of a very
fragmented index can generate more log than a fully logged index creation.
I've seen that one not once, not a joke . The defragmentation, is performed
as a series of short transactions, but still tlog gets quite big (esp when
you have fast SAN under the tlogs).
DBREINDEX is not good to run when the database is active, pick your quiet
time if you can. Otherwise you will see spike in locks and dead locks. What
will it do to your system depends how fault tolerant your applications are
and if they can handle dead lock errors (many developers just do not code
such a stuff)
When to choose what:
If the index is interleaved with other indexes on disk, running DBCC
INDEXDEFRAG against that index does not make all leaf pages in the index
contiguous. Therefore if clustering is what you are after, rebuild.
Maintenance plans:
Native Rebuild Index Task uses the ALTER INDEX statement when connected to
SQL Server 2005, and the DBCC DBREINDEX statement when connected to SQL
Server 2000
If you are using 3-rd party backup/maintenance read carefully their doc and
just run good old trace to see what exactly are they calling. The trace will
show you what is it, it may be not what the doc has…

> DBCC DBREINDEX
> REBUILD INDEX
On index rebuild BOL has pretty good description.
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/a28c684a-c4e9-4b24-a7ae-e248808b31e9.htm
Making a decision if you have fragmentation.
DBCC SHOWCONTIG WITH TABLERESULTS
Avg. Page Density (full):
average level how filled the pages are. The value should be compared to
the fill factor setting of the particular index, then you look and decide
whether or not the index is internally fragmented.
Scan Density:
the ratio between the Best Count (what was necessary to read when
scanning all the pages of the index) and Actual Count of extents (what did
it actually read). You want this to be as close to 100 as possible. 75% and
below definitely indicates external fragmentation. It is a fine line to
deside what is fragmented what is not. you may have a system that just
creates such a number because it is it's nature and this is how your data are
insrterd,deleted,updated and you have to live with it.
Logical Scan Fragmentation:
shows the ratio of pages out of logical order. You want this close to 0.
10% + indicates external fragmentation.
I've seen references that "Extent Scan Fragmentation" is not really
important. This is because the algorithm that calculates it does not work for
indexes that span over multiple files. According to some Microsoft sources,
in the next version of SQL Server, DBCC SHOWCONTIG will not even include this
metric.
I did not mess with 2008 to test that one to be honest.
Thanks, Liliya
"sharman" wrote:

> Hi,
> I have a table that has one clustered index and one non-clustered index
> defined. What difference would it be if you do the following actions:
> DBCC INDEXDEFRAG
> DBCC DBREINDEX
> REBUILD INDEX
> Also, if the table had 100 rows in the beginning when the indexes were
> created. After creating the indexes 100 more rows were inserted into the
> table. When would these newly inserted rows gets included in the index?
> Thanks in advance.

index defrag rebuild

Hi,
I have a table that has one clustered index and one non-clustered index
defined. What difference would it be if you do the following actions:
DBCC INDEXDEFRAG
DBCC DBREINDEX
REBUILD INDEX
Also, if the table had 100 rows in the beginning when the indexes were
created. After creating the indexes 100 more rows were inserted into the
table. When would these newly inserted rows gets included in the index?
Thanks in advance.Yes, after you create the index, this index will be updated when records are
inserted or deleted.
If your table only has a few hundred records you do not need to worry about
reindexing. Even if you have big tables you need to check the level of index
fragmentation first. Start by looking at DBCC SHOWCONTIG or
sys.dm_db_index_physical_stats on BOL.
Hope this helps,
Ben Nevarez
"sharman" wrote:
> Hi,
> I have a table that has one clustered index and one non-clustered index
> defined. What difference would it be if you do the following actions:
> DBCC INDEXDEFRAG
> DBCC DBREINDEX
> REBUILD INDEX
> Also, if the table had 100 rows in the beginning when the indexes were
> created. After creating the indexes 100 more rows were inserted into the
> table. When would these newly inserted rows gets included in the index?
> Thanks in advance.|||> Also, if the table had 100 rows in the beginning when the indexes were
> created. After creating the indexes 100 more rows were inserted into the
> table. When would these newly inserted rows gets included in the index?
When they are inserted. Thatâ's when the database will touch the indexes. You
have the clustered index in there. That clustered index is your table however
wired it sounds, it is your physical order and the physical pages can be
organized only one way. BOL for sql 2005 has pretty good description what a
clustered index is. Have questions, askâ?¦
> DBCC INDEXDEFRAG
That is a low priority operation. Pretty much it will reorganize the index
for you, will shuffle pages in place. It will take in consideration the fill
factor of the index and compact the pages, if there are any empty ones as a
result of the compaction, they will be freed (removed that is). This is an
on-line operation and it does not lock other processes and is not locked by
them, does not create long living locks, will skip pages locked by other
processes. What does it do?
DBCC INDEXDEFRAG de-fragments the leaf level of an index so that the
physical order of the pages matches the left-to-right logical order of the
leaf nodes, therefore improving index-scanning performance. When DBCC
INDEXDEFRAG is run, index de-fragmentation occurs serially. This means that
the operation on a single index is performed using a single thread. No
parallelism occurs. Also, operations on multiple indexes from the same DBCC
INDEXDEFRAG statement are performed on one index at a time.
Unlike DBCC DBREINDEX is the real rebuild. It is not an on-line operation
and holds long term locks. DBCC DBREINDEX in other words will and should
conflict with any dml on the same table, DBCC INDEXDEFRAG will not
lock/conflick as it was mentioned earlier.
The chose if -time and the fragmentation level you have. If your
maintenance windows allows and your fragmentation can not be fixed by
INDEXDEFRAG, then rebuild that thing.
Does not matter which sql command is used to de-fragment the index, it is
always fully logged, does not matter what mode is your database in. Therefore
do not run INDEXDEFRAG when your database is active period, all you are going
to get is about 1.4 size of your data in tlogs and you did not de-fragment
your index anyways, because the applications were 'dancing' on it locking
pages, INDEXDEFRAG has skipped those, but it was really busy trying to do
the job you have asked for. BOL says that defragmentation of a very
fragmented index can generate more log than a fully logged index creation.
I've seen that one not once, not a joke :). The defragmentation, is performed
as a series of short transactions, but still tlog gets quite big (esp when
you have fast SAN under the tlogs).
DBREINDEX is not good to run when the database is active, pick your quiet
time if you can. Otherwise you will see spike in locks and dead locks. What
will it do to your system depends how fault tolerant your applications are
and if they can handle dead lock errors (many developers just do not code
such a stuff)
When to choose what:
If the index is interleaved with other indexes on disk, running DBCC
INDEXDEFRAG against that index does not make all leaf pages in the index
contiguous. Therefore if clustering is what you are after, rebuild.
Maintenance plans:
Native Rebuild Index Task uses the ALTER INDEX statement when connected to
SQL Server 2005, and the DBCC DBREINDEX statement when connected to SQL
Server 2000
If you are using 3-rd party backup/maintenance read carefully their doc and
just run good old trace to see what exactly are they calling. The trace will
show you what is it, it may be not what the doc hasâ?¦
> DBCC DBREINDEX
> REBUILD INDEX
On index rebuild BOL has pretty good description.
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/a28c684a-c4e9-4b24-a7ae-e248808b31e9.htm
Making a decision if you have fragmentation.
DBCC SHOWCONTIG WITH TABLERESULTS
Avg. Page Density (full):
average level how filled the pages are. The value should be compared to
the fill factor setting of the particular index, then you look and decide
whether or not the index is internally fragmented.
Scan Density:
the ratio between the Best Count (what was necessary to read when
scanning all the pages of the index) and Actual Count of extents (what did
it actually read). You want this to be as close to 100 as possible. 75% and
below definitely indicates external fragmentation. It is a fine line to
deside what is fragmented what is not. you may have a system that just
creates such a number because it is it's nature and this is how your data are
insrterd,deleted,updated and you have to live with it.
Logical Scan Fragmentation:
shows the ratio of pages out of logical order. You want this close to 0.
10% + indicates external fragmentation.
I've seen references that "Extent Scan Fragmentation" is not really
important. This is because the algorithm that calculates it does not work for
indexes that span over multiple files. According to some Microsoft sources,
in the next version of SQL Server, DBCC SHOWCONTIG will not even include this
metric.
I did not mess with 2008 to test that one to be honest.
--
Thanks, Liliya
"sharman" wrote:
> Hi,
> I have a table that has one clustered index and one non-clustered index
> defined. What difference would it be if you do the following actions:
> DBCC INDEXDEFRAG
> DBCC DBREINDEX
> REBUILD INDEX
> Also, if the table had 100 rows in the beginning when the indexes were
> created. After creating the indexes 100 more rows were inserted into the
> table. When would these newly inserted rows gets included in the index?
> Thanks in advance.|||> Also, if the table had 100 rows in the beginning when the indexes were
> created. After creating the indexes 100 more rows were inserted into the
> table. When would these newly inserted rows gets included in the index?
When they are inserted. Thatâ's when the database will touch the indexes. You
have the clustered index in there. That clustered index is your table however
wired it sounds, it is your physical order and the physical pages can be
organized only one way. BOL for sql 2005 has pretty good description what a
clustered index is. Have questions, askâ?¦
> DBCC INDEXDEFRAG
That is a low priority operation. Pretty much it will reorganize the index
for you, will shuffle pages in place. It will take in consideration the fill
factor of the index and compact the pages, if there are any empty ones as a
result of the compaction, they will be freed (removed that is). This is an
on-line operation and it does not lock other processes and is not locked by
them, does not create long living locks, will skip pages locked by other
processes. What does it do?
DBCC INDEXDEFRAG de-fragments the leaf level of an index so that the
physical order of the pages matches the left-to-right logical order of the
leaf nodes, therefore improving index-scanning performance. When DBCC
INDEXDEFRAG is run, index de-fragmentation occurs serially. This means that
the operation on a single index is performed using a single thread. No
parallelism occurs. Also, operations on multiple indexes from the same DBCC
INDEXDEFRAG statement are performed on one index at a time.
Unlike DBCC DBREINDEX is the real rebuild. It is not an on-line operation
and holds long term locks. DBCC DBREINDEX in other words will and should
conflict with any dml on the same table, DBCC INDEXDEFRAG will not
lock/conflick as it was mentioned earlier.
The chose if -time and the fragmentation level you have. If your
maintenance windows allows and your fragmentation can not be fixed by
INDEXDEFRAG, then rebuild that thing.
Does not matter which sql command is used to de-fragment the index, it is
always fully logged, does not matter what mode is your database in. Therefore
do not run INDEXDEFRAG when your database is active period, all you are going
to get is about 1.4 size of your data in tlogs and you did not de-fragment
your index anyways, because the applications were 'dancing' on it locking
pages, INDEXDEFRAG has skipped those, but it was really busy trying to do
the job you have asked for. BOL says that defragmentation of a very
fragmented index can generate more log than a fully logged index creation.
I've seen that one not once, not a joke :). The defragmentation, is performed
as a series of short transactions, but still tlog gets quite big (esp when
you have fast SAN under the tlogs).
DBREINDEX is not good to run when the database is active, pick your quiet
time if you can. Otherwise you will see spike in locks and dead locks. What
will it do to your system depends how fault tolerant your applications are
and if they can handle dead lock errors (many developers just do not code
such a stuff)
When to choose what:
If the index is interleaved with other indexes on disk, running DBCC
INDEXDEFRAG against that index does not make all leaf pages in the index
contiguous. Therefore if clustering is what you are after, rebuild.
Maintenance plans:
Native Rebuild Index Task uses the ALTER INDEX statement when connected to
SQL Server 2005, and the DBCC DBREINDEX statement when connected to SQL
Server 2000
If you are using 3-rd party backup/maintenance read carefully their doc and
just run good old trace to see what exactly are they calling. The trace will
show you what is it, it may be not what the doc hasâ?¦
> DBCC DBREINDEX
> REBUILD INDEX
On index rebuild BOL has pretty good description.
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/a28c684a-c4e9-4b24-a7ae-e248808b31e9.htm
Making a decision if you have fragmentation.
DBCC SHOWCONTIG WITH TABLERESULTS
Avg. Page Density (full):
average level how filled the pages are. The value should be compared to
the fill factor setting of the particular index, then you look and decide
whether or not the index is internally fragmented.
Scan Density:
the ratio between the Best Count (what was necessary to read when
scanning all the pages of the index) and Actual Count of extents (what did
it actually read). You want this to be as close to 100 as possible. 75% and
below definitely indicates external fragmentation. It is a fine line to
deside what is fragmented what is not. you may have a system that just
creates such a number because it is it's nature and this is how your data are
insrterd,deleted,updated and you have to live with it.
Logical Scan Fragmentation:
shows the ratio of pages out of logical order. You want this close to 0.
10% + indicates external fragmentation.
I've seen references that "Extent Scan Fragmentation" is not really
important. This is because the algorithm that calculates it does not work for
indexes that span over multiple files. According to some Microsoft sources,
in the next version of SQL Server, DBCC SHOWCONTIG will not even include this
metric.
I did not mess with 2008 to test that one to be honest.
--
Thanks, Liliya
"sharman" wrote:
> Hi,
> I have a table that has one clustered index and one non-clustered index
> defined. What difference would it be if you do the following actions:
> DBCC INDEXDEFRAG
> DBCC DBREINDEX
> REBUILD INDEX
> Also, if the table had 100 rows in the beginning when the indexes were
> created. After creating the indexes 100 more rows were inserted into the
> table. When would these newly inserted rows gets included in the index?
> Thanks in advance.

Index count

How can I find the number of indexes created on a user database (Only
clustered and Non-clustered indexes in user tables - not system).
Thanks."DXC" <DXC@.discussions.microsoft.com> wrote in message
news:5F002D25-DB29-49A3-9F35-36E782D1882B@.microsoft.com...
> How can I find the number of indexes created on a user database (Only
> clustered and Non-clustered indexes in user tables - not system).
>
Select count(*) from sysindexes
where id in (select id from sysobjects where type = 'U')
and indid <> 255
255 is Text column.|||Set objSqlServer to your sql connection
Sub ListIndexes(strDBName)
WSCript.Echo "Database:" & Trim(strDBName)
Set oDatabase = objSqlServer.Databases(Trim(strDBName))
For Each Table In oDatabase.Tables
If NOT Table.SystemObject Then
WSCript.Echo Table.Name & " (" & Table.Indexes.Count & " indexes)"
<--here is the actual count property
For Each Index in Table.Indexes
If NOT Index.StatisticsIndex Then
WSCript.Echo vbTab & Index.Name & " (Stat: " & Index.StatisticsIndex &
")"
For Each Column in Index.ListIndexedColumns( )
WSCript.Echo vbTab & vbTab & "[" & Column.Name & "]"
Next
WSCript.Echo vbSpace
End If
Next
End IF
Next
End Sub
Or... more to the point
Set objSqlServer = YourSQLServerConnection
Set oDatabase = objSqlServer.Databases(TheDatabase)
For Each Table In oDatabase.Tables
If NOT Table.SystemObject Then
WSCript.StdOout.WriteLine Table.Indexes.Count
End If
Next
"DXC" <DXC@.discussions.microsoft.com> wrote in message
news:5F002D25-DB29-49A3-9F35-36E782D1882B@.microsoft.com...
> How can I find the number of indexes created on a user database (Only
> clustered and Non-clustered indexes in user tables - not system).
> Thanks.|||Not quite, in SQL 2000 statistics also take up an indid value in
sysindexes, so you would need to eliminate them also.
I think you would need to use INDEXPROPERTY to weed them out:
WHERE INDEXPROPERTY ( ID, name, 'IsStatistics') = 0 and INDEXPROPERTY (ID,
name, 'IsHypothetical') = 0
HTH
Kalen Delaney
www.solidqualitylearning.com
"rkusenet" <rkusenet@.yahoo.com> wrote in message
news:4321d67f$0$91790$892e7fe2@.authen.white.readfreenews.net...
> "DXC" <DXC@.discussions.microsoft.com> wrote in message
> news:5F002D25-DB29-49A3-9F35-36E782D1882B@.microsoft.com...
>> How can I find the number of indexes created on a user database (Only
>> clustered and Non-clustered indexes in user tables - not system).
> Select count(*) from sysindexes
> where id in (select id from sysobjects where type = 'U')
> and indid <> 255
> 255 is Text column.
>|||So, is following the correct syntax '
SELECT COUNT(*) FROM sysindexes
WHERE INDEXPROPERTY ( ID, name, 'IsStatistics') = 0 and INDEXPROPERTY (ID,
name, 'IsHypothetical') = 0
Thanks.
"Kalen Delaney" wrote:
> Not quite, in SQL 2000 statistics also take up an indid value in
> sysindexes, so you would need to eliminate them also.
> I think you would need to use INDEXPROPERTY to weed them out:
> WHERE INDEXPROPERTY ( ID, name, 'IsStatistics') = 0 and INDEXPROPERTY (ID,
> name, 'IsHypothetical') = 0
> HTH
> Kalen Delaney
> www.solidqualitylearning.com
>
> "rkusenet" <rkusenet@.yahoo.com> wrote in message
> news:4321d67f$0$91790$892e7fe2@.authen.white.readfreenews.net...
> >
> > "DXC" <DXC@.discussions.microsoft.com> wrote in message
> > news:5F002D25-DB29-49A3-9F35-36E782D1882B@.microsoft.com...
> >> How can I find the number of indexes created on a user database (Only
> >> clustered and Non-clustered indexes in user tables - not system).
> >>
> >
> > Select count(*) from sysindexes
> > where id in (select id from sysobjects where type = 'U')
> > and indid <> 255
> >
> > 255 is Text column.
> >
> >
>
>|||what does 'IsHypothetical' mean?|||Hypothetical indexes hold column level statistics. They are created by SQL
Server and used internally. They cannot be used directly as a data access
path.
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
<ford_desperado@.yahoo.com> wrote in message
news:1126296886.681664.90670@.g47g2000cwa.googlegroups.com...
> what does 'IsHypothetical' mean?
>|||Did you try it? If you have a syntax error you will be told so.
You will also need the other conditions in the WHERE clause, not just these
two.
HTH
Kalen Delaney
"DXC" <DXC@.discussions.microsoft.com> wrote in message
news:700341BA-4C8D-4BE5-9970-DFC1151E6DBB@.microsoft.com...
> So, is following the correct syntax '
> SELECT COUNT(*) FROM sysindexes
> WHERE INDEXPROPERTY ( ID, name, 'IsStatistics') = 0 and INDEXPROPERTY
> (ID,
> name, 'IsHypothetical') = 0
> Thanks.
> "Kalen Delaney" wrote:
>> Not quite, in SQL 2000 statistics also take up an indid value in
>> sysindexes, so you would need to eliminate them also.
>> I think you would need to use INDEXPROPERTY to weed them out:
>> WHERE INDEXPROPERTY ( ID, name, 'IsStatistics') = 0 and INDEXPROPERTY
>> (ID,
>> name, 'IsHypothetical') = 0
>> HTH
>> Kalen Delaney
>> www.solidqualitylearning.com
>>
>> "rkusenet" <rkusenet@.yahoo.com> wrote in message
>> news:4321d67f$0$91790$892e7fe2@.authen.white.readfreenews.net...
>> >
>> > "DXC" <DXC@.discussions.microsoft.com> wrote in message
>> > news:5F002D25-DB29-49A3-9F35-36E782D1882B@.microsoft.com...
>> >> How can I find the number of indexes created on a user database (Only
>> >> clustered and Non-clustered indexes in user tables - not system).
>> >>
>> >
>> > Select count(*) from sysindexes
>> > where id in (select id from sysobjects where type = 'U')
>> > and indid <> 255
>> >
>> > 255 is Text column.
>> >
>> >
>>
>>
>|||An index created by the Index Tuning Wizard; these are usually removed when
the IDW is done, but sometimes they manage to stick around.
HTH
Kalen Delaney
<ford_desperado@.yahoo.com> wrote in message
news:1126296886.681664.90670@.g47g2000cwa.googlegroups.com...
> what does 'IsHypothetical' mean?
>

index count

Is there a way to find out the total number of indexes
(clustered and non-clustered) on a given database, either
through the use of QA or EM? I'm currently running
sp_helpindex against all tables in my database and then
manually counting each index.
Thanks.SELECT COUNT(*) FROM sysindexes
WHERE indid BETWEEN 1 AND 254
Indexes with indid 0 are heaps (tables without a clustered index), indid 1
are clustered indexes, indid 2-254 are non clustered indexes and indid 255
are used for text/ntext/images columns
--
Jacco Schalkwijk
SQL Server MVP
"Rob" <anonymous@.discussions.microsoft.com> wrote in message
news:07f501c4ad40$fe7d1920$a501280a@.phx.gbl...
> Is there a way to find out the total number of indexes
> (clustered and non-clustered) on a given database, either
> through the use of QA or EM? I'm currently running
> sp_helpindex against all tables in my database and then
> manually counting each index.
> Thanks.|||Also not that this will include statistics and hypothetical indexes. These can be filtered out using
INDEXPEROPERTY.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote in message
news:%23heAEIUrEHA.1272@.TK2MSFTNGP09.phx.gbl...
> SELECT COUNT(*) FROM sysindexes
> WHERE indid BETWEEN 1 AND 254
> Indexes with indid 0 are heaps (tables without a clustered index), indid 1 are clustered indexes,
> indid 2-254 are non clustered indexes and indid 255 are used for text/ntext/images columns
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Rob" <anonymous@.discussions.microsoft.com> wrote in message
> news:07f501c4ad40$fe7d1920$a501280a@.phx.gbl...
>> Is there a way to find out the total number of indexes
>> (clustered and non-clustered) on a given database, either
>> through the use of QA or EM? I'm currently running
>> sp_helpindex against all tables in my database and then
>> manually counting each index.
>> Thanks.
>|||select count(*) from (SELECT sysobjects.name, sysobjects.id,
sysindexkeys.indid
FROM sysindexes INNER JOIN
sysobjects ON sysindexes.id = sysobjects.id INNER JOIN
sysindexkeys ON sysindexes.id = sysindexkeys.id
GROUP BY sysobjects.name, sysobjects.id, sysindexkeys.indid) drv
ll give the current databases index count
"Rob" <anonymous@.discussions.microsoft.com> wrote in message
news:07f501c4ad40$fe7d1920$a501280a@.phx.gbl...
> Is there a way to find out the total number of indexes
> (clustered and non-clustered) on a given database, either
> through the use of QA or EM? I'm currently running
> sp_helpindex against all tables in my database and then
> manually counting each index.
> Thanks.|||Oops, forgot about those.
SELECT COUNT(*) FROM sysindexes
WHERE indid BETWEEN 1 AND 254
AND INDEXPROPERTY (ID, name, 'IsHypothetical') = 0
AND INDEXPROPERTY (ID, name, 'IsStatistics') = 0
--
Jacco Schalkwijk
SQL Server MVP
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OcFKtLUrEHA.3748@.TK2MSFTNGP09.phx.gbl...
> Also not that this will include statistics and hypothetical indexes. These
> can be filtered out using INDEXPEROPERTY.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid>
> wrote in message news:%23heAEIUrEHA.1272@.TK2MSFTNGP09.phx.gbl...
>> SELECT COUNT(*) FROM sysindexes
>> WHERE indid BETWEEN 1 AND 254
>> Indexes with indid 0 are heaps (tables without a clustered index), indid
>> 1 are clustered indexes, indid 2-254 are non clustered indexes and indid
>> 255 are used for text/ntext/images columns
>> --
>> Jacco Schalkwijk
>> SQL Server MVP
>>
>> "Rob" <anonymous@.discussions.microsoft.com> wrote in message
>> news:07f501c4ad40$fe7d1920$a501280a@.phx.gbl...
>> Is there a way to find out the total number of indexes
>> (clustered and non-clustered) on a given database, either
>> through the use of QA or EM? I'm currently running
>> sp_helpindex against all tables in my database and then
>> manually counting each index.
>> Thanks.
>>
>|||Thanks guys, that was really helpful. Any ideas on how to
apply the same filter in SQL6.5, since the INDEXPROPERTY
function isn't available in 6.5?
Thanks again.
>--Original Message--
>Oops, forgot about those.
>SELECT COUNT(*) FROM sysindexes
>WHERE indid BETWEEN 1 AND 254
>AND INDEXPROPERTY (ID, name, 'IsHypothetical') = 0
>AND INDEXPROPERTY (ID, name, 'IsStatistics') = 0
>--
>Jacco Schalkwijk
>SQL Server MVP
>
>"Tibor Karaszi"
<tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
>message news:OcFKtLUrEHA.3748@.TK2MSFTNGP09.phx.gbl...
>> Also not that this will include statistics and
hypothetical indexes. These
>> can be filtered out using INDEXPEROPERTY.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Jacco Schalkwijk"
<jacco.please.reply@.to.newsgroups.mvps.org.invalid>
>> wrote in message news:%
23heAEIUrEHA.1272@.TK2MSFTNGP09.phx.gbl...
>> SELECT COUNT(*) FROM sysindexes
>> WHERE indid BETWEEN 1 AND 254
>> Indexes with indid 0 are heaps (tables without a
clustered index), indid
>> 1 are clustered indexes, indid 2-254 are non clustered
indexes and indid
>> 255 are used for text/ntext/images columns
>> --
>> Jacco Schalkwijk
>> SQL Server MVP
>>
>> "Rob" <anonymous@.discussions.microsoft.com> wrote in
message
>> news:07f501c4ad40$fe7d1920$a501280a@.phx.gbl...
>> Is there a way to find out the total number of indexes
>> (clustered and non-clustered) on a given database,
either
>> through the use of QA or EM? I'm currently running
>> sp_helpindex against all tables in my database and
then
>> manually counting each index.
>> Thanks.
>>
>>
>
>.
>|||I forget how a text page is represented in 6.5 sysindexes. But... you won't
have hypothetical indexes and I don't think stats show up as a row in
sysindexes. It's been sooo long, I just don't remember.
But I think you'll be safe simply looking at user tables with an indid
between 1-255 (or 254 if a row is there for text/image)
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Rob" <anonymous@.discussions.microsoft.com> wrote in message
news:171201c4ad4c$1569c6f0$a301280a@.phx.gbl...
> Thanks guys, that was really helpful. Any ideas on how to
> apply the same filter in SQL6.5, since the INDEXPROPERTY
> function isn't available in 6.5?
> Thanks again.
> >--Original Message--
> >Oops, forgot about those.
> >
> >SELECT COUNT(*) FROM sysindexes
> >WHERE indid BETWEEN 1 AND 254
> >AND INDEXPROPERTY (ID, name, 'IsHypothetical') = 0
> >AND INDEXPROPERTY (ID, name, 'IsStatistics') = 0
> >
> >--
> >Jacco Schalkwijk
> >SQL Server MVP
> >
> >
> >"Tibor Karaszi"
> <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in
> >message news:OcFKtLUrEHA.3748@.TK2MSFTNGP09.phx.gbl...
> >> Also not that this will include statistics and
> hypothetical indexes. These
> >> can be filtered out using INDEXPEROPERTY.
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >>
> >>
> >> "Jacco Schalkwijk"
> <jacco.please.reply@.to.newsgroups.mvps.org.invalid>
> >> wrote in message news:%
> 23heAEIUrEHA.1272@.TK2MSFTNGP09.phx.gbl...
> >> SELECT COUNT(*) FROM sysindexes
> >> WHERE indid BETWEEN 1 AND 254
> >>
> >> Indexes with indid 0 are heaps (tables without a
> clustered index), indid
> >> 1 are clustered indexes, indid 2-254 are non clustered
> indexes and indid
> >> 255 are used for text/ntext/images columns
> >>
> >> --
> >> Jacco Schalkwijk
> >> SQL Server MVP
> >>
> >>
> >> "Rob" <anonymous@.discussions.microsoft.com> wrote in
> message
> >> news:07f501c4ad40$fe7d1920$a501280a@.phx.gbl...
> >> Is there a way to find out the total number of indexes
> >> (clustered and non-clustered) on a given database,
> either
> >> through the use of QA or EM? I'm currently running
> >> sp_helpindex against all tables in my database and
> then
> >> manually counting each index.
> >>
> >> Thanks.
> >>
> >>
> >>
> >>
> >
> >
> >.
> >|||SQL 6.5 didn't have column stats (only those associated with indexes) and it
didn't have hypothetical indexes, so you don't need to check for either of
these conditions.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Rob" <anonymous@.discussions.microsoft.com> wrote in message
news:171201c4ad4c$1569c6f0$a301280a@.phx.gbl...
> Thanks guys, that was really helpful. Any ideas on how to
> apply the same filter in SQL6.5, since the INDEXPROPERTY
> function isn't available in 6.5?
> Thanks again.
>>--Original Message--
>>Oops, forgot about those.
>>SELECT COUNT(*) FROM sysindexes
>>WHERE indid BETWEEN 1 AND 254
>>AND INDEXPROPERTY (ID, name, 'IsHypothetical') = 0
>>AND INDEXPROPERTY (ID, name, 'IsStatistics') = 0
>>--
>>Jacco Schalkwijk
>>SQL Server MVP
>>
>>"Tibor Karaszi"
> <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in
>>message news:OcFKtLUrEHA.3748@.TK2MSFTNGP09.phx.gbl...
>> Also not that this will include statistics and
> hypothetical indexes. These
>> can be filtered out using INDEXPEROPERTY.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Jacco Schalkwijk"
> <jacco.please.reply@.to.newsgroups.mvps.org.invalid>
>> wrote in message news:%
> 23heAEIUrEHA.1272@.TK2MSFTNGP09.phx.gbl...
>> SELECT COUNT(*) FROM sysindexes
>> WHERE indid BETWEEN 1 AND 254
>> Indexes with indid 0 are heaps (tables without a
> clustered index), indid
>> 1 are clustered indexes, indid 2-254 are non clustered
> indexes and indid
>> 255 are used for text/ntext/images columns
>> --
>> Jacco Schalkwijk
>> SQL Server MVP
>>
>> "Rob" <anonymous@.discussions.microsoft.com> wrote in
> message
>> news:07f501c4ad40$fe7d1920$a501280a@.phx.gbl...
>> Is there a way to find out the total number of indexes
>> (clustered and non-clustered) on a given database,
> either
>> through the use of QA or EM? I'm currently running
>> sp_helpindex against all tables in my database and
> then
>> manually counting each index.
>> Thanks.
>>
>>
>>
>>.

index count

Is there a way to find out the total number of indexes
(clustered and non-clustered) on a given database, either
through the use of QA or EM? I'm currently running
sp_helpindex against all tables in my database and then
manually counting each index.
Thanks.
SELECT COUNT(*) FROM sysindexes
WHERE indid BETWEEN 1 AND 254
Indexes with indid 0 are heaps (tables without a clustered index), indid 1
are clustered indexes, indid 2-254 are non clustered indexes and indid 255
are used for text/ntext/images columns
Jacco Schalkwijk
SQL Server MVP
"Rob" <anonymous@.discussions.microsoft.com> wrote in message
news:07f501c4ad40$fe7d1920$a501280a@.phx.gbl...
> Is there a way to find out the total number of indexes
> (clustered and non-clustered) on a given database, either
> through the use of QA or EM? I'm currently running
> sp_helpindex against all tables in my database and then
> manually counting each index.
> Thanks.
|||Also not that this will include statistics and hypothetical indexes. These can be filtered out using
INDEXPEROPERTY.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid > wrote in message
news:%23heAEIUrEHA.1272@.TK2MSFTNGP09.phx.gbl...
> SELECT COUNT(*) FROM sysindexes
> WHERE indid BETWEEN 1 AND 254
> Indexes with indid 0 are heaps (tables without a clustered index), indid 1 are clustered indexes,
> indid 2-254 are non clustered indexes and indid 255 are used for text/ntext/images columns
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Rob" <anonymous@.discussions.microsoft.com> wrote in message
> news:07f501c4ad40$fe7d1920$a501280a@.phx.gbl...
>
|||select count(*) from (SELECT sysobjects.name, sysobjects.id,
sysindexkeys.indid
FROM sysindexes INNER JOIN
sysobjects ON sysindexes.id = sysobjects.id INNER JOIN
sysindexkeys ON sysindexes.id = sysindexkeys.id
GROUP BY sysobjects.name, sysobjects.id, sysindexkeys.indid) drv
ll give the current databases index count
"Rob" <anonymous@.discussions.microsoft.com> wrote in message
news:07f501c4ad40$fe7d1920$a501280a@.phx.gbl...
> Is there a way to find out the total number of indexes
> (clustered and non-clustered) on a given database, either
> through the use of QA or EM? I'm currently running
> sp_helpindex against all tables in my database and then
> manually counting each index.
> Thanks.
|||Oops, forgot about those.
SELECT COUNT(*) FROM sysindexes
WHERE indid BETWEEN 1 AND 254
AND INDEXPROPERTY (ID, name, 'IsHypothetical') = 0
AND INDEXPROPERTY (ID, name, 'IsStatistics') = 0
Jacco Schalkwijk
SQL Server MVP
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OcFKtLUrEHA.3748@.TK2MSFTNGP09.phx.gbl...
> Also not that this will include statistics and hypothetical indexes. These
> can be filtered out using INDEXPEROPERTY.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid >
> wrote in message news:%23heAEIUrEHA.1272@.TK2MSFTNGP09.phx.gbl...
>
|||Thanks guys, that was really helpful. Any ideas on how to
apply the same filter in SQL6.5, since the INDEXPROPERTY
function isn't available in 6.5?
Thanks again.

>--Original Message--
>Oops, forgot about those.
>SELECT COUNT(*) FROM sysindexes
>WHERE indid BETWEEN 1 AND 254
>AND INDEXPROPERTY (ID, name, 'IsHypothetical') = 0
>AND INDEXPROPERTY (ID, name, 'IsStatistics') = 0
>--
>Jacco Schalkwijk
>SQL Server MVP
>
>"Tibor Karaszi"
<tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in[vbcol=seagreen]
>message news:OcFKtLUrEHA.3748@.TK2MSFTNGP09.phx.gbl...
hypothetical indexes. These[vbcol=seagreen]
<jacco.please.reply@.to.newsgroups.mvps.org.invalid >[vbcol=seagreen]
23heAEIUrEHA.1272@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
clustered index), indid[vbcol=seagreen]
indexes and indid[vbcol=seagreen]
message[vbcol=seagreen]
either[vbcol=seagreen]
then
>
>.
>
|||I forget how a text page is represented in 6.5 sysindexes. But... you won't
have hypothetical indexes and I don't think stats show up as a row in
sysindexes. It's been sooo long, I just don't remember.
But I think you'll be safe simply looking at user tables with an indid
between 1-255 (or 254 if a row is there for text/image)
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Rob" <anonymous@.discussions.microsoft.com> wrote in message
news:171201c4ad4c$1569c6f0$a301280a@.phx.gbl...[vbcol=seagreen]
> Thanks guys, that was really helpful. Any ideas on how to
> apply the same filter in SQL6.5, since the INDEXPROPERTY
> function isn't available in 6.5?
> Thanks again.
> <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in
> hypothetical indexes. These
> <jacco.please.reply@.to.newsgroups.mvps.org.invalid >
> 23heAEIUrEHA.1272@.TK2MSFTNGP09.phx.gbl...
> clustered index), indid
> indexes and indid
> message
> either
> then
|||SQL 6.5 didn't have column stats (only those associated with indexes) and it
didn't have hypothetical indexes, so you don't need to check for either of
these conditions.
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Rob" <anonymous@.discussions.microsoft.com> wrote in message
news:171201c4ad4c$1569c6f0$a301280a@.phx.gbl...[vbcol=seagreen]
> Thanks guys, that was really helpful. Any ideas on how to
> apply the same filter in SQL6.5, since the INDEXPROPERTY
> function isn't available in 6.5?
> Thanks again.
> <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in
> hypothetical indexes. These
> <jacco.please.reply@.to.newsgroups.mvps.org.invalid >
> 23heAEIUrEHA.1272@.TK2MSFTNGP09.phx.gbl...
> clustered index), indid
> indexes and indid
> message
> either
> then

Index count

How can I find the number of indexes created on a user database (Only
clustered and Non-clustered indexes in user tables - not system).
Thanks.
"DXC" <DXC@.discussions.microsoft.com> wrote in message
news:5F002D25-DB29-49A3-9F35-36E782D1882B@.microsoft.com...
> How can I find the number of indexes created on a user database (Only
> clustered and Non-clustered indexes in user tables - not system).
>
Select count(*) from sysindexes
where id in (select id from sysobjects where type = 'U')
and indid <> 255
255 is Text column.
|||Not quite, in SQL 2000 statistics also take up an indid value in
sysindexes, so you would need to eliminate them also.
I think you would need to use INDEXPROPERTY to weed them out:
WHERE INDEXPROPERTY ( ID, name, 'IsStatistics') = 0 and INDEXPROPERTY (ID,
name, 'IsHypothetical') = 0
HTH
Kalen Delaney
www.solidqualitylearning.com
"rkusenet" <rkusenet@.yahoo.com> wrote in message
news:4321d67f$0$91790$892e7fe2@.authen.white.readfr eenews.net...
> "DXC" <DXC@.discussions.microsoft.com> wrote in message
> news:5F002D25-DB29-49A3-9F35-36E782D1882B@.microsoft.com...
> Select count(*) from sysindexes
> where id in (select id from sysobjects where type = 'U')
> and indid <> 255
> 255 is Text column.
>
|||So, is following the correct syntax ?
SELECT COUNT(*) FROM sysindexes
WHERE INDEXPROPERTY ( ID, name, 'IsStatistics') = 0 and INDEXPROPERTY (ID,
name, 'IsHypothetical') = 0
Thanks.
"Kalen Delaney" wrote:

> Not quite, in SQL 2000 statistics also take up an indid value in
> sysindexes, so you would need to eliminate them also.
> I think you would need to use INDEXPROPERTY to weed them out:
> WHERE INDEXPROPERTY ( ID, name, 'IsStatistics') = 0 and INDEXPROPERTY (ID,
> name, 'IsHypothetical') = 0
> HTH
> Kalen Delaney
> www.solidqualitylearning.com
>
> "rkusenet" <rkusenet@.yahoo.com> wrote in message
> news:4321d67f$0$91790$892e7fe2@.authen.white.readfr eenews.net...
>
>
|||what does 'IsHypothetical' mean?
|||Hypothetical indexes hold column level statistics. They are created by SQL
Server and used internally. They cannot be used directly as a data access
path.
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
<ford_desperado@.yahoo.com> wrote in message
news:1126296886.681664.90670@.g47g2000cwa.googlegro ups.com...
> what does 'IsHypothetical' mean?
>
|||Did you try it? If you have a syntax error you will be told so.
You will also need the other conditions in the WHERE clause, not just these
two.
HTH
Kalen Delaney
"DXC" <DXC@.discussions.microsoft.com> wrote in message
news:700341BA-4C8D-4BE5-9970-DFC1151E6DBB@.microsoft.com...
> So, is following the correct syntax ?
> SELECT COUNT(*) FROM sysindexes
> WHERE INDEXPROPERTY ( ID, name, 'IsStatistics') = 0 and INDEXPROPERTY
> (ID,
> name, 'IsHypothetical') = 0
> Thanks.
> "Kalen Delaney" wrote:
>
|||An index created by the Index Tuning Wizard; these are usually removed when
the IDW is done, but sometimes they manage to stick around.
HTH
Kalen Delaney
<ford_desperado@.yahoo.com> wrote in message
news:1126296886.681664.90670@.g47g2000cwa.googlegro ups.com...
> what does 'IsHypothetical' mean?
>

Index count

How can I find the number of indexes created on a user database (Only
clustered and Non-clustered indexes in user tables - not system).
Thanks."DXC" <DXC@.discussions.microsoft.com> wrote in message
news:5F002D25-DB29-49A3-9F35-36E782D1882B@.microsoft.com...
> How can I find the number of indexes created on a user database (Only
> clustered and Non-clustered indexes in user tables - not system).
>
Select count(*) from sysindexes
where id in (select id from sysobjects where type = 'U')
and indid <> 255
255 is Text column.|||Not quite, in SQL 2000 statistics also take up an indid value in
sysindexes, so you would need to eliminate them also.
I think you would need to use INDEXPROPERTY to weed them out:
WHERE INDEXPROPERTY ( ID, name, 'IsStatistics') = 0 and INDEXPROPERTY (ID,
name, 'IsHypothetical') = 0
HTH
Kalen Delaney
www.solidqualitylearning.com
"rkusenet" <rkusenet@.yahoo.com> wrote in message
news:4321d67f$0$91790$892e7fe2@.authen.white.readfreenews.net...
> "DXC" <DXC@.discussions.microsoft.com> wrote in message
> news:5F002D25-DB29-49A3-9F35-36E782D1882B@.microsoft.com...
> Select count(*) from sysindexes
> where id in (select id from sysobjects where type = 'U')
> and indid <> 255
> 255 is Text column.
>|||So, is following the correct syntax '
SELECT COUNT(*) FROM sysindexes
WHERE INDEXPROPERTY ( ID, name, 'IsStatistics') = 0 and INDEXPROPERTY (ID,
name, 'IsHypothetical') = 0
Thanks.
"Kalen Delaney" wrote:

> Not quite, in SQL 2000 statistics also take up an indid value in
> sysindexes, so you would need to eliminate them also.
> I think you would need to use INDEXPROPERTY to weed them out:
> WHERE INDEXPROPERTY ( ID, name, 'IsStatistics') = 0 and INDEXPROPERTY (ID
,
> name, 'IsHypothetical') = 0
> HTH
> Kalen Delaney
> www.solidqualitylearning.com
>
> "rkusenet" <rkusenet@.yahoo.com> wrote in message
> news:4321d67f$0$91790$892e7fe2@.authen.white.readfreenews.net...
>
>|||what does 'IsHypothetical' mean?|||Hypothetical indexes hold column level statistics. They are created by SQL
Server and used internally. They cannot be used directly as a data access
path.
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
<ford_desperado@.yahoo.com> wrote in message
news:1126296886.681664.90670@.g47g2000cwa.googlegroups.com...
> what does 'IsHypothetical' mean?
>|||Did you try it? If you have a syntax error you will be told so.
You will also need the other conditions in the WHERE clause, not just these
two.
HTH
Kalen Delaney
"DXC" <DXC@.discussions.microsoft.com> wrote in message
news:700341BA-4C8D-4BE5-9970-DFC1151E6DBB@.microsoft.com...
> So, is following the correct syntax '
> SELECT COUNT(*) FROM sysindexes
> WHERE INDEXPROPERTY ( ID, name, 'IsStatistics') = 0 and INDEXPROPERTY
> (ID,
> name, 'IsHypothetical') = 0
> Thanks.
> "Kalen Delaney" wrote:
>
>|||An index created by the Index Tuning Wizard; these are usually removed when
the IDW is done, but sometimes they manage to stick around.
HTH
Kalen Delaney
<ford_desperado@.yahoo.com> wrote in message
news:1126296886.681664.90670@.g47g2000cwa.googlegroups.com...
> what does 'IsHypothetical' mean?
>

Index causes INSERTs to fail

Has anyone ever had an non-clustered index on a table cause INSERT statements to fail. I ran into a peculiar issue where the composite index defined suddenly caused INSERTs to fail on a table where it had existed for more than 2.5 years.
Once the index was removed, the INSERTs began working again.
Let me know if anyone else has run into this.
What do you mean by "caused"? Are you receiving any specific errors when the
insert fails?
Deadlocks can occur when indexes are being updated during insert operations.
Are you getting deadlocks perhaps?
Regards,
Greg Linwood
SQL Server MVP
"rowentx" <rowentx@.discussions.microsoft.com> wrote in message
news:BBAD0449-A137-41D3-AA43-A26256BF4A81@.microsoft.com...
> Has anyone ever had an non-clustered index on a table cause INSERT
statements to fail. I ran into a peculiar issue where the composite index
defined suddenly caused INSERTs to fail on a table where it had existed for
more than 2.5 years.
> Once the index was removed, the INSERTs began working again.
> Let me know if anyone else has run into this.
|||On Wed, 4 Aug 2004 15:07:01 -0700, rowentx wrote:

>Has anyone ever had an non-clustered index on a table cause INSERT statements to fail. I ran into a peculiar issue where the composite index defined suddenly caused INSERTs to fail on a table where it had existed for more than 2.5 years.
>Once the index was removed, the INSERTs began working again.
>Let me know if anyone else has run into this.
Ho rowentx,
What exactly do you mean by "cause INSERT statements to fail"? Did you get
any error messages? Did SQL Server silently discard the data? Did your
server start to emit grey smoke? Please be more specific.
Also, I'd like to know if the non-clustered index you mention is defined
as nonunique or unique.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Was the index defined as unique, and were you getting duplicate key errors?
What does it mean that the inserts 'failed'. Did you get an error message?
Was the data just not inserted?
What version are you running?
How are you performing the inserts?
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"rowentx" <rowentx@.discussions.microsoft.com> wrote in message
news:BBAD0449-A137-41D3-AA43-A26256BF4A81@.microsoft.com...
> Has anyone ever had an non-clustered index on a table cause INSERT
statements to fail. I ran into a peculiar issue where the composite index
defined suddenly caused INSERTs to fail on a table where it had existed for
more than 2.5 years.
> Once the index was removed, the INSERTs began working again.
> Let me know if anyone else has run into this.
|||After reviewing my post, and some of the replies, I realized my information was not detailed enough and was a little misleading.
We are currently running the Enterprise Edition of SQL Server 2000. In my previous post I said that the INSERT statement fails, well that is completely true. The statement actually never fails, but it never completes either. The INSERT statement that f
ailed was being executed via a stored procedure. Data in the table could be viewed with simple selects, but nothing could be inserted at least within a resonable time frame.
The the table has approximately 7 million rows of data and the index that appears to have been the issue was non-unique. The index was comprised of four fields, two ints, and two varchar 255s. We also never experienced any deadlocks and the insert state
ments never seemed to complete.
However, once I removed the Index, the insert statement completed in milliseconds.
Hopefully this helps clear up my previous post.
"rowentx" wrote:

> Has anyone ever had an non-clustered index on a table cause INSERT statements to fail. I ran into a peculiar issue where the composite index defined suddenly caused INSERTs to fail on a table where it had existed for more than 2.5 years.
> Once the index was removed, the INSERTs began working again.
> Let me know if anyone else has run into this.
|||Is the insert a single record insert or an insert / select type operation?
There are many possible causes, so I'd suggest narrowing things further by:
(a) Provide the SQL DDL (create table , index etc) for the table & indexes
(b) Provide the sp code
(b) See if the process that "never fails" is blocked by another process
(c) Inspect at least some basic performance counters - is the disk being
accessed heavily, is the CPU maxed & memory usage.
(d) Profile the stored proc's i/o usage (reads)
(e) Check execution plans
(f) Inspect locks taken (sp_lock)
Some of this information would help narrow things down & avoid speculation..
Regards,
Greg Linwood
SQL Server MVP
"rowentx" <rowentx@.discussions.microsoft.com> wrote in message
news:966D1F5C-465D-465F-8A2F-77B8D319B65B@.microsoft.com...
> After reviewing my post, and some of the replies, I realized my
information was not detailed enough and was a little misleading.
> We are currently running the Enterprise Edition of SQL Server 2000. In my
previous post I said that the INSERT statement fails, well that is
completely true. The statement actually never fails, but it never completes
either. The INSERT statement that failed was being executed via a stored
procedure. Data in the table could be viewed with simple selects, but
nothing could be inserted at least within a resonable time frame.
> The the table has approximately 7 million rows of data and the index that
appears to have been the issue was non-unique. The index was comprised of
four fields, two ints, and two varchar 255s. We also never experienced any
deadlocks and the insert statements never seemed to complete.
> However, once I removed the Index, the insert statement completed in
milliseconds.[vbcol=seagreen]
> Hopefully this helps clear up my previous post.
> "rowentx" wrote:
statements to fail. I ran into a peculiar issue where the composite index
defined suddenly caused INSERTs to fail on a table where it had existed for
more than 2.5 years.[vbcol=seagreen]
|||If that's the case try changing the FILL FACTOR for
indexes:
sp_configure 'allow updates',1
go
sp_configure 'fill factor', 60
go
sp_configure 'allow updates',0
go
The best way to see if you nead to change that value is
to check in the perfmon if the counter Page Splits is to
high

>--Original Message--
>What do you mean by "caused"? Are you receiving any
specific errors when the
>insert fails?
>Deadlocks can occur when indexes are being updated
during insert operations.
>Are you getting deadlocks perhaps?
>Regards,
>Greg Linwood
>SQL Server MVP
>"rowentx" <rowentx@.discussions.microsoft.com> wrote in
message
>news:BBAD0449-A137-41D3-AA43-
A26256BF4A81@.microsoft.com...[vbcol=seagreen]
cause INSERT
>statements to fail. I ran into a peculiar issue where
the composite index
>defined suddenly caused INSERTs to fail on a table where
it had existed for[vbcol=seagreen]
>more than 2.5 years.
again.
>
>.
>
|||And don't forget about any triggers that may be executed as a part of the
insert.
"Greg Linwood" <g_linwoodQhotmail.com> wrote in message
news:%23ZVaEFpeEHA.372@.TK2MSFTNGP12.phx.gbl...
> Is the insert a single record insert or an insert / select type operation?
> There are many possible causes, so I'd suggest narrowing things further
by:
> (a) Provide the SQL DDL (create table , index etc) for the table & indexes
> (b) Provide the sp code
> (b) See if the process that "never fails" is blocked by another process
> (c) Inspect at least some basic performance counters - is the disk being
> accessed heavily, is the CPU maxed & memory usage.
> (d) Profile the stored proc's i/o usage (reads)
> (e) Check execution plans
> (f) Inspect locks taken (sp_lock)
> Some of this information would help narrow things down & avoid
speculation..[vbcol=seagreen]
> Regards,
> Greg Linwood
> SQL Server MVP
> "rowentx" <rowentx@.discussions.microsoft.com> wrote in message
> news:966D1F5C-465D-465F-8A2F-77B8D319B65B@.microsoft.com...
> information was not detailed enough and was a little misleading.
my
> previous post I said that the INSERT statement fails, well that is
> completely true. The statement actually never fails, but it never
completes[vbcol=seagreen]
> either. The INSERT statement that failed was being executed via a stored
> procedure. Data in the table could be viewed with simple selects, but
> nothing could be inserted at least within a resonable time frame.
that
> appears to have been the issue was non-unique. The index was comprised of
> four fields, two ints, and two varchar 255s. We also never experienced
any
> deadlocks and the insert statements never seemed to complete.
> milliseconds.
> statements to fail. I ran into a peculiar issue where the composite index
> defined suddenly caused INSERTs to fail on a table where it had existed
for
> more than 2.5 years.
>

Index causes INSERTs to fail

Has anyone ever had an non-clustered index on a table cause INSERT statement
s to fail. I ran into a peculiar issue where the composite index defined su
ddenly caused INSERTs to fail on a table where it had existed for more than
2.5 years.
Once the index was removed, the INSERTs began working again.
Let me know if anyone else has run into this.What do you mean by "caused"? Are you receiving any specific errors when the
insert fails?
Deadlocks can occur when indexes are being updated during insert operations.
Are you getting deadlocks perhaps?
Regards,
Greg Linwood
SQL Server MVP
"rowentx" <rowentx@.discussions.microsoft.com> wrote in message
news:BBAD0449-A137-41D3-AA43-A26256BF4A81@.microsoft.com...
> Has anyone ever had an non-clustered index on a table cause INSERT
statements to fail. I ran into a peculiar issue where the composite index
defined suddenly caused INSERTs to fail on a table where it had existed for
more than 2.5 years.
> Once the index was removed, the INSERTs began working again.
> Let me know if anyone else has run into this.|||On Wed, 4 Aug 2004 15:07:01 -0700, rowentx wrote:

>Has anyone ever had an non-clustered index on a table cause INSERT statemen
ts to fail. I ran into a peculiar issue where the composite index defined s
uddenly caused INSERTs to fail on a table where it had existed for more than
2.5 years.
>Once the index was removed, the INSERTs began working again.
>Let me know if anyone else has run into this.
Ho rowentx,
What exactly do you mean by "cause INSERT statements to fail"? Did you get
any error messages? Did SQL Server silently discard the data? Did your
server start to emit grey smoke? Please be more specific.
Also, I'd like to know if the non-clustered index you mention is defined
as nonunique or unique.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Was the index defined as unique, and were you getting duplicate key errors?
What does it mean that the inserts 'failed'. Did you get an error message?
Was the data just not inserted?
What version are you running?
How are you performing the inserts?
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"rowentx" <rowentx@.discussions.microsoft.com> wrote in message
news:BBAD0449-A137-41D3-AA43-A26256BF4A81@.microsoft.com...
> Has anyone ever had an non-clustered index on a table cause INSERT
statements to fail. I ran into a peculiar issue where the composite index
defined suddenly caused INSERTs to fail on a table where it had existed for
more than 2.5 years.
> Once the index was removed, the INSERTs began working again.
> Let me know if anyone else has run into this.|||After reviewing my post, and some of the replies, I realized my information
was not detailed enough and was a little misleading.
We are currently running the Enterprise Edition of SQL Server 2000. In my p
revious post I said that the INSERT statement fails, well that is completely
true. The statement actually never fails, but it never completes either.
The INSERT statement that f
ailed was being executed via a stored procedure. Data in the table could be
viewed with simple selects, but nothing could be inserted at least within a
resonable time frame.
The the table has approximately 7 million rows of data and the index that ap
pears to have been the issue was non-unique. The index was comprised of fou
r fields, two ints, and two varchar 255s. We also never experienced any dea
dlocks and the insert state
ments never seemed to complete.
However, once I removed the Index, the insert statement completed in millise
conds.
Hopefully this helps clear up my previous post.
"rowentx" wrote:

> Has anyone ever had an non-clustered index on a table cause INSERT stateme
nts to fail. I ran into a peculiar issue where the composite index defined
suddenly caused INSERTs to fail on a table where it had existed for more tha
n 2.5 years.
> Once the index was removed, the INSERTs began working again.
> Let me know if anyone else has run into this.|||Is the insert a single record insert or an insert / select type operation?
There are many possible causes, so I'd suggest narrowing things further by:
(a) Provide the SQL DDL (create table , index etc) for the table & indexes
(b) Provide the sp code
(b) See if the process that "never fails" is blocked by another process
(c) Inspect at least some basic performance counters - is the disk being
accessed heavily, is the CPU maxed & memory usage.
(d) Profile the stored proc's i/o usage (reads)
(e) Check execution plans
(f) Inspect locks taken (sp_lock)
Some of this information would help narrow things down & avoid speculation..
Regards,
Greg Linwood
SQL Server MVP
"rowentx" <rowentx@.discussions.microsoft.com> wrote in message
news:966D1F5C-465D-465F-8A2F-77B8D319B65B@.microsoft.com...
> After reviewing my post, and some of the replies, I realized my
information was not detailed enough and was a little misleading.
> We are currently running the Enterprise Edition of SQL Server 2000. In my
previous post I said that the INSERT statement fails, well that is
completely true. The statement actually never fails, but it never completes
either. The INSERT statement that failed was being executed via a stored
procedure. Data in the table could be viewed with simple selects, but
nothing could be inserted at least within a resonable time frame.
> The the table has approximately 7 million rows of data and the index that
appears to have been the issue was non-unique. The index was comprised of
four fields, two ints, and two varchar 255s. We also never experienced any
deadlocks and the insert statements never seemed to complete.
> However, once I removed the Index, the insert statement completed in
milliseconds.[vbcol=seagreen]
> Hopefully this helps clear up my previous post.
> "rowentx" wrote:
>
statements to fail. I ran into a peculiar issue where the composite index
defined suddenly caused INSERTs to fail on a table where it had existed for
more than 2.5 years.[vbcol=seagreen]|||If that's the case try changing the FILL FACTOR for
indexes:
sp_configure 'allow updates',1
go
sp_configure 'fill factor', 60
go
sp_configure 'allow updates',0
go
The best way to see if you nead to change that value is
to check in the perfmon if the counter Page Splits is to
high

>--Original Message--
>What do you mean by "caused"? Are you receiving any
specific errors when the
>insert fails?
>Deadlocks can occur when indexes are being updated
during insert operations.
>Are you getting deadlocks perhaps?
>Regards,
>Greg Linwood
>SQL Server MVP
>"rowentx" <rowentx@.discussions.microsoft.com> wrote in
message
>news:BBAD0449-A137-41D3-AA43-
A26256BF4A81@.microsoft.com...
cause INSERT[vbcol=seagreen]
>statements to fail. I ran into a peculiar issue where
the composite index
>defined suddenly caused INSERTs to fail on a table where
it had existed for
>more than 2.5 years.
again.[vbcol=seagreen]
>
>.
>|||And don't forget about any triggers that may be executed as a part of the
insert.
"Greg Linwood" <g_linwoodQhotmail.com> wrote in message
news:%23ZVaEFpeEHA.372@.TK2MSFTNGP12.phx.gbl...
> Is the insert a single record insert or an insert / select type operation?
> There are many possible causes, so I'd suggest narrowing things further
by:
> (a) Provide the SQL DDL (create table , index etc) for the table & indexes
> (b) Provide the sp code
> (b) See if the process that "never fails" is blocked by another process
> (c) Inspect at least some basic performance counters - is the disk being
> accessed heavily, is the CPU maxed & memory usage.
> (d) Profile the stored proc's i/o usage (reads)
> (e) Check execution plans
> (f) Inspect locks taken (sp_lock)
> Some of this information would help narrow things down & avoid
speculation..
> Regards,
> Greg Linwood
> SQL Server MVP
> "rowentx" <rowentx@.discussions.microsoft.com> wrote in message
> news:966D1F5C-465D-465F-8A2F-77B8D319B65B@.microsoft.com...
> information was not detailed enough and was a little misleading.
my[vbcol=seagreen]
> previous post I said that the INSERT statement fails, well that is
> completely true. The statement actually never fails, but it never
completes
> either. The INSERT statement that failed was being executed via a stored
> procedure. Data in the table could be viewed with simple selects, but
> nothing could be inserted at least within a resonable time frame.
that[vbcol=seagreen]
> appears to have been the issue was non-unique. The index was comprised of
> four fields, two ints, and two varchar 255s. We also never experienced
any
> deadlocks and the insert statements never seemed to complete.
> milliseconds.
> statements to fail. I ran into a peculiar issue where the composite index
> defined suddenly caused INSERTs to fail on a table where it had existed
for
> more than 2.5 years.
>