_WA_ indexes in sysindexes

Hi
I was writing script to defrag my indexes on a regular basis.
I was checking the sysindexes table and found
_WA_ indexes and I do no see a corresponding object for the sysobjects
WHat are these indexes?
Mangesh
These are statistics rather than indexes. You can exclude statistics and
hyhothetical indexes (created by the Index Tuning Wizard) rows in sysindexes
using INDEXPROPERTY:
SELECT OBJECT_NAME(id), name
FROM sysindexes
WHERE
INDEXPROPERTY(id, name, 'IsStatistics') = 0 AND
INDEXPROPERTY(id, name, 'IsHypothetical') = 0
Hope this helps.
Dan Guzman
SQL Server MVP
"Mangesh Deshpande" <MangeshDeshpande@.discussions.microsoft.com> wrote in
message news:0C87800A-4467-4AD0-BCCD-DD822FFF0649@.microsoft.com...
> Hi
> I was writing script to defrag my indexes on a regular basis.
> I was checking the sysindexes table and found
> _WA_ indexes and I do no see a corresponding object for the sysobjects
> WHat are these indexes?
> Mangesh

_WA_ indexes in sysindexes

Hi
I was writing script to defrag my indexes on a regular basis.
I was checking the sysindexes table and found
_WA_ indexes and I do no see a corresponding object for the sysobjects
WHat are these indexes?
MangeshThese are statistics rather than indexes. You can exclude statistics and
hyhothetical indexes (created by the Index Tuning Wizard) rows in sysindexes
using INDEXPROPERTY:
SELECT OBJECT_NAME(id), name
FROM sysindexes
WHERE
INDEXPROPERTY(id, name, 'IsStatistics') = 0 AND
INDEXPROPERTY(id, name, 'IsHypothetical') = 0
Hope this helps.
Dan Guzman
SQL Server MVP
"Mangesh Deshpande" <MangeshDeshpande@.discussions.microsoft.com> wrote in
message news:0C87800A-4467-4AD0-BCCD-DD822FFF0649@.microsoft.com...
> Hi
> I was writing script to defrag my indexes on a regular basis.
> I was checking the sysindexes table and found
> _WA_ indexes and I do no see a corresponding object for the sysobjects
> WHat are these indexes?
> Mangesh

_WA_ indexes in sysindexes

Hi
I was writing script to defrag my indexes on a regular basis.
I was checking the sysindexes table and found
_WA_ indexes and I do no see a corresponding object for the sysobjects
WHat are these indexes?
MangeshThese are statistics rather than indexes. You can exclude statistics and
hyhothetical indexes (created by the Index Tuning Wizard) rows in sysindexes
using INDEXPROPERTY:
SELECT OBJECT_NAME(id), name
FROM sysindexes
WHERE
INDEXPROPERTY(id, name, 'IsStatistics') = 0 AND
INDEXPROPERTY(id, name, 'IsHypothetical') = 0
Hope this helps.
Dan Guzman
SQL Server MVP
"Mangesh Deshpande" <MangeshDeshpande@.discussions.microsoft.com> wrote in
message news:0C87800A-4467-4AD0-BCCD-DD822FFF0649@.microsoft.com...
> Hi
> I was writing script to defrag my indexes on a regular basis.
> I was checking the sysindexes table and found
> _WA_ indexes and I do no see a corresponding object for the sysobjects
> WHat are these indexes?
> Mangesh

_WA_ entries in sysindexes

Hi,
When I look at sysindexes, there are lot of _WA_% entries in there for my
database. These are constraints I believe, created by checking 'Auto create
statistics' option at the DB level. SOmewhere I read that if you see lot of
these constraints, it is better to create 'em as permanent indexes for better
performance.
1) Is that true ?
2) How do I extract the ddl for these constraints ?
TIA
KNM> When I look at sysindexes, there are lot of _WA_% entries in there for my
> database. These are constraints I believe
No, constraints is something entirely different.
> ...created by checking 'Auto create
> statistics' option at the DB level.
Correct. These entries are statistics, the same thing as when you execute the CREATE STATISTICS
command.
> SOmewhere I read that if you see lot of
> these constraints, it is better to create 'em as permanent indexes for better
> performance.
That is not necessarily the case. First, these are not indexes, it is statistics. Big difference.
Also, just because the optimizer feels like having statistics over a column doesn't mean that an
index would be beneficial (and an index comes with overhead). You should analyze what indexes your
queries would benefit from, and it is possible that some of these auto created statistics should be
indexes, but only you can know whether that is the case or not.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"MO" <MO@.discussions.microsoft.com> wrote in message
news:18235A22-6059-4F5E-B2A8-71121719E519@.microsoft.com...
> Hi,
> When I look at sysindexes, there are lot of _WA_% entries in there for my
> database. These are constraints I believe, created by checking 'Auto create
> statistics' option at the DB level. SOmewhere I read that if you see lot of
> these constraints, it is better to create 'em as permanent indexes for better
> performance.
> 1) Is that true ?
> 2) How do I extract the ddl for these constraints ?
> TIA
> KNM|||Thx for your info.
Dumb Q. How do I see what columns the stats is for ?
I know the stats name is say _WA_Sys_ord_billto_66F53242
But on what column the stats is built on ? What command will tell me that ?
TIA
Knm
"Tibor Karaszi" wrote:
> > When I look at sysindexes, there are lot of _WA_% entries in there for my
> > database. These are constraints I believe
> No, constraints is something entirely different.
>
> > ...created by checking 'Auto create
> > statistics' option at the DB level.
> Correct. These entries are statistics, the same thing as when you execute the CREATE STATISTICS
> command.
>
> > SOmewhere I read that if you see lot of
> > these constraints, it is better to create 'em as permanent indexes for better
> > performance.
> That is not necessarily the case. First, these are not indexes, it is statistics. Big difference.
> Also, just because the optimizer feels like having statistics over a column doesn't mean that an
> index would be beneficial (and an index comes with overhead). You should analyze what indexes your
> queries would benefit from, and it is possible that some of these auto created statistics should be
> indexes, but only you can know whether that is the case or not.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "MO" <MO@.discussions.microsoft.com> wrote in message
> news:18235A22-6059-4F5E-B2A8-71121719E519@.microsoft.com...
> > Hi,
> > When I look at sysindexes, there are lot of _WA_% entries in there for my
> > database. These are constraints I believe, created by checking 'Auto create
> > statistics' option at the DB level. SOmewhere I read that if you see lot of
> > these constraints, it is better to create 'em as permanent indexes for better
> > performance.
> >
> > 1) Is that true ?
> >
> > 2) How do I extract the ddl for these constraints ?
> >
> > TIA
> > KNM
>|||> Dumb Q. How do I see what columns the stats is for ?
One method is to query the system table with a query like the following:
Try
SELECT
OBJECT_NAME(i.id) ,AS TableName
i.name AS StatisticsName,
c.name AS ColumnName
FROM sysindexes i
JOIN sysindexkeys k ON
k.id = i.id and k.indid = i.indid
JOIN syscolumns c on
c.id = k.id and c.colid = k.colid
WHERE INDEXPROPERTY(i.id, i.name, 'IsStatistics') = 1
ORDER BY
OBJECT_NAME(i.id),
i.name,
c.name
--
Hope this helps.
Dan Guzman
SQL Server MVP
"MO" <MO@.discussions.microsoft.com> wrote in message
news:80809AF6-100F-46E9-8CF3-A7F9B8D2E2AF@.microsoft.com...
> Thx for your info.
> Dumb Q. How do I see what columns the stats is for ?
> I know the stats name is say _WA_Sys_ord_billto_66F53242
> But on what column the stats is built on ? What command will tell me that
> ?
> TIA
> Knm
> "Tibor Karaszi" wrote:
>> > When I look at sysindexes, there are lot of _WA_% entries in there
>> > for my
>> > database. These are constraints I believe
>> No, constraints is something entirely different.
>>
>> > ...created by checking 'Auto create
>> > statistics' option at the DB level.
>> Correct. These entries are statistics, the same thing as when you execute
>> the CREATE STATISTICS
>> command.
>>
>> > SOmewhere I read that if you see lot of
>> > these constraints, it is better to create 'em as permanent indexes for
>> > better
>> > performance.
>> That is not necessarily the case. First, these are not indexes, it is
>> statistics. Big difference.
>> Also, just because the optimizer feels like having statistics over a
>> column doesn't mean that an
>> index would be beneficial (and an index comes with overhead). You should
>> analyze what indexes your
>> queries would benefit from, and it is possible that some of these auto
>> created statistics should be
>> indexes, but only you can know whether that is the case or not.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "MO" <MO@.discussions.microsoft.com> wrote in message
>> news:18235A22-6059-4F5E-B2A8-71121719E519@.microsoft.com...
>> > Hi,
>> > When I look at sysindexes, there are lot of _WA_% entries in there
>> > for my
>> > database. These are constraints I believe, created by checking 'Auto
>> > create
>> > statistics' option at the DB level. SOmewhere I read that if you see
>> > lot of
>> > these constraints, it is better to create 'em as permanent indexes for
>> > better
>> > performance.
>> >
>> > 1) Is that true ?
>> >
>> > 2) How do I extract the ddl for these constraints ?
>> >
>> > TIA
>> > KNM
>>|||If you know the table, you can use sp_helpstats
You can pull the table name from sysindexes with this:
select object_name(id)
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"MO" <MO@.discussions.microsoft.com> wrote in message
news:80809AF6-100F-46E9-8CF3-A7F9B8D2E2AF@.microsoft.com...
> Thx for your info.
> Dumb Q. How do I see what columns the stats is for ?
> I know the stats name is say _WA_Sys_ord_billto_66F53242
> But on what column the stats is built on ? What command will tell me that
> ?
> TIA
> Knm
> "Tibor Karaszi" wrote:
>> > When I look at sysindexes, there are lot of _WA_% entries in there
>> > for my
>> > database. These are constraints I believe
>> No, constraints is something entirely different.
>>
>> > ...created by checking 'Auto create
>> > statistics' option at the DB level.
>> Correct. These entries are statistics, the same thing as when you execute
>> the CREATE STATISTICS
>> command.
>>
>> > SOmewhere I read that if you see lot of
>> > these constraints, it is better to create 'em as permanent indexes for
>> > better
>> > performance.
>> That is not necessarily the case. First, these are not indexes, it is
>> statistics. Big difference.
>> Also, just because the optimizer feels like having statistics over a
>> column doesn't mean that an
>> index would be beneficial (and an index comes with overhead). You should
>> analyze what indexes your
>> queries would benefit from, and it is possible that some of these auto
>> created statistics should be
>> indexes, but only you can know whether that is the case or not.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "MO" <MO@.discussions.microsoft.com> wrote in message
>> news:18235A22-6059-4F5E-B2A8-71121719E519@.microsoft.com...
>> > Hi,
>> > When I look at sysindexes, there are lot of _WA_% entries in there
>> > for my
>> > database. These are constraints I believe, created by checking 'Auto
>> > create
>> > statistics' option at the DB level. SOmewhere I read that if you see
>> > lot of
>> > these constraints, it is better to create 'em as permanent indexes for
>> > better
>> > performance.
>> >
>> > 1) Is that true ?
>> >
>> > 2) How do I extract the ddl for these constraints ?
>> >
>> > TIA
>> > KNM
>>|||Thanks for the info. I looked at a table, it has 118 entries in sysindexes
for that table. 6 indexes and 112 contraints. I dropped those constraints to
see if they are being used, but they all got created in couple of min which
tells me that they are being used. So, my question is should I convert those
constraints to an index ?
What are the cons of keeping these as a constraint and not converting to an
index in the long run ?
TIA
MO
"Kalen Delaney" wrote:
> If you know the table, you can use sp_helpstats
> You can pull the table name from sysindexes with this:
> select object_name(id)
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "MO" <MO@.discussions.microsoft.com> wrote in message
> news:80809AF6-100F-46E9-8CF3-A7F9B8D2E2AF@.microsoft.com...
> > Thx for your info.
> > Dumb Q. How do I see what columns the stats is for ?
> > I know the stats name is say _WA_Sys_ord_billto_66F53242
> > But on what column the stats is built on ? What command will tell me that
> > ?
> > TIA
> > Knm
> >
> > "Tibor Karaszi" wrote:
> >
> >> > When I look at sysindexes, there are lot of _WA_% entries in there
> >> > for my
> >> > database. These are constraints I believe
> >>
> >> No, constraints is something entirely different.
> >>
> >>
> >> > ...created by checking 'Auto create
> >> > statistics' option at the DB level.
> >>
> >> Correct. These entries are statistics, the same thing as when you execute
> >> the CREATE STATISTICS
> >> command.
> >>
> >>
> >> > SOmewhere I read that if you see lot of
> >> > these constraints, it is better to create 'em as permanent indexes for
> >> > better
> >> > performance.
> >>
> >> That is not necessarily the case. First, these are not indexes, it is
> >> statistics. Big difference.
> >> Also, just because the optimizer feels like having statistics over a
> >> column doesn't mean that an
> >> index would be beneficial (and an index comes with overhead). You should
> >> analyze what indexes your
> >> queries would benefit from, and it is possible that some of these auto
> >> created statistics should be
> >> indexes, but only you can know whether that is the case or not.
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >>
> >>
> >> "MO" <MO@.discussions.microsoft.com> wrote in message
> >> news:18235A22-6059-4F5E-B2A8-71121719E519@.microsoft.com...
> >> > Hi,
> >> > When I look at sysindexes, there are lot of _WA_% entries in there
> >> > for my
> >> > database. These are constraints I believe, created by checking 'Auto
> >> > create
> >> > statistics' option at the DB level. SOmewhere I read that if you see
> >> > lot of
> >> > these constraints, it is better to create 'em as permanent indexes for
> >> > better
> >> > performance.
> >> >
> >> > 1) Is that true ?
> >> >
> >> > 2) How do I extract the ddl for these constraints ?
> >> >
> >> > TIA
> >> > KNM
> >>
> >>
>
>|||They are not constraints, bit difference. They are entries for SQL Server to hold statistics over
the data in a column of the table. Whether you should create such indexes, only you can tell. It is
likely that your queries can benefit from indexes on some over the columns that these statistics has
been created. OTOH, you most probably don't want to have 118 indexes on a table (imagine the
slowness of your modifications and storage requirements). Analyze your queries, their execution
plans and create indexes that makes sense.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"MO" <MO@.discussions.microsoft.com> wrote in message
news:AE7CA10C-4F2C-49D0-B64D-CA40728053CC@.microsoft.com...
> Thanks for the info. I looked at a table, it has 118 entries in sysindexes
> for that table. 6 indexes and 112 contraints. I dropped those constraints to
> see if they are being used, but they all got created in couple of min which
> tells me that they are being used. So, my question is should I convert those
> constraints to an index ?
> What are the cons of keeping these as a constraint and not converting to an
> index in the long run ?
> TIA
> MO
> "Kalen Delaney" wrote:
>> If you know the table, you can use sp_helpstats
>> You can pull the table name from sysindexes with this:
>> select object_name(id)
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.solidqualitylearning.com
>>
>> "MO" <MO@.discussions.microsoft.com> wrote in message
>> news:80809AF6-100F-46E9-8CF3-A7F9B8D2E2AF@.microsoft.com...
>> > Thx for your info.
>> > Dumb Q. How do I see what columns the stats is for ?
>> > I know the stats name is say _WA_Sys_ord_billto_66F53242
>> > But on what column the stats is built on ? What command will tell me that
>> > ?
>> > TIA
>> > Knm
>> >
>> > "Tibor Karaszi" wrote:
>> >
>> >> > When I look at sysindexes, there are lot of _WA_% entries in there
>> >> > for my
>> >> > database. These are constraints I believe
>> >>
>> >> No, constraints is something entirely different.
>> >>
>> >>
>> >> > ...created by checking 'Auto create
>> >> > statistics' option at the DB level.
>> >>
>> >> Correct. These entries are statistics, the same thing as when you execute
>> >> the CREATE STATISTICS
>> >> command.
>> >>
>> >>
>> >> > SOmewhere I read that if you see lot of
>> >> > these constraints, it is better to create 'em as permanent indexes for
>> >> > better
>> >> > performance.
>> >>
>> >> That is not necessarily the case. First, these are not indexes, it is
>> >> statistics. Big difference.
>> >> Also, just because the optimizer feels like having statistics over a
>> >> column doesn't mean that an
>> >> index would be beneficial (and an index comes with overhead). You should
>> >> analyze what indexes your
>> >> queries would benefit from, and it is possible that some of these auto
>> >> created statistics should be
>> >> indexes, but only you can know whether that is the case or not.
>> >>
>> >> --
>> >> Tibor Karaszi, SQL Server MVP
>> >> http://www.karaszi.com/sqlserver/default.asp
>> >> http://www.solidqualitylearning.com/
>> >>
>> >>
>> >> "MO" <MO@.discussions.microsoft.com> wrote in message
>> >> news:18235A22-6059-4F5E-B2A8-71121719E519@.microsoft.com...
>> >> > Hi,
>> >> > When I look at sysindexes, there are lot of _WA_% entries in there
>> >> > for my
>> >> > database. These are constraints I believe, created by checking 'Auto
>> >> > create
>> >> > statistics' option at the DB level. SOmewhere I read that if you see
>> >> > lot of
>> >> > these constraints, it is better to create 'em as permanent indexes for
>> >> > better
>> >> > performance.
>> >> >
>> >> > 1) Is that true ?
>> >> >
>> >> > 2) How do I extract the ddl for these constraints ?
>> >> >
>> >> > TIA
>> >> > KNM
>> >>
>> >>
>>sql

_WA_ entries in sysindexes

Hi,
When I look at sysindexes, there are lot of _WA_% entries in there for my
database. These are constraints I believe, created by checking 'Auto create
statistics' option at the DB level. SOmewhere I read that if you see lot of
these constraints, it is better to create 'em as permanent indexes for bette
r
performance.
1) Is that true ?
2) How do I extract the ddl for these constraints ?
TIA
KNM> When I look at sysindexes, there are lot of _WA_% entries in there for my">
> database. These are constraints I believe
No, constraints is something entirely different.

> ...created by checking 'Auto create
> statistics' option at the DB level.
Correct. These entries are statistics, the same thing as when you execute th
e CREATE STATISTICS
command.

> SOmewhere I read that if you see lot of
> these constraints, it is better to create 'em as permanent indexes for bet
ter
> performance.
That is not necessarily the case. First, these are not indexes, it is statis
tics. Big difference.
Also, just because the optimizer feels like having statistics over a column
doesn't mean that an
index would be beneficial (and an index comes with overhead). You should ana
lyze what indexes your
queries would benefit from, and it is possible that some of these auto creat
ed statistics should be
indexes, but only you can know whether that is the case or not.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"MO" <MO@.discussions.microsoft.com> wrote in message
news:18235A22-6059-4F5E-B2A8-71121719E519@.microsoft.com...
> Hi,
> When I look at sysindexes, there are lot of _WA_% entries in there for m
y
> database. These are constraints I believe, created by checking 'Auto creat
e
> statistics' option at the DB level. SOmewhere I read that if you see lot o
f
> these constraints, it is better to create 'em as permanent indexes for bet
ter
> performance.
> 1) Is that true ?
> 2) How do I extract the ddl for these constraints ?
> TIA
> KNM|||Thx for your info.
Dumb Q. How do I see what columns the stats is for ?
I know the stats name is say _WA_Sys_ord_billto_66F53242
But on what column the stats is built on ? What command will tell me that ?
TIA
Knm
"Tibor Karaszi" wrote:

> No, constraints is something entirely different.
>
> Correct. These entries are statistics, the same thing as when you execute
the CREATE STATISTICS
> command.
>
> That is not necessarily the case. First, these are not indexes, it is stat
istics. Big difference.
> Also, just because the optimizer feels like having statistics over a colum
n doesn't mean that an
> index would be beneficial (and an index comes with overhead). You should a
nalyze what indexes your
> queries would benefit from, and it is possible that some of these auto cre
ated statistics should be
> indexes, but only you can know whether that is the case or not.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "MO" <MO@.discussions.microsoft.com> wrote in message
> news:18235A22-6059-4F5E-B2A8-71121719E519@.microsoft.com...
>|||> Dumb Q. How do I see what columns the stats is for ?
One method is to query the system table with a query like the following:
Try
SELECT
OBJECT_NAME(i.id) ,AS TableName
i.name AS StatisticsName,
c.name AS ColumnName
FROM sysindexes i
JOIN sysindexkeys k ON
k.id = i.id and k.indid = i.indid
JOIN syscolumns c on
c.id = k.id and c.colid = k.colid
WHERE INDEXPROPERTY(i.id, i.name, 'IsStatistics') = 1
ORDER BY
OBJECT_NAME(i.id),
i.name,
c.name
Hope this helps.
Dan Guzman
SQL Server MVP
"MO" <MO@.discussions.microsoft.com> wrote in message
news:80809AF6-100F-46E9-8CF3-A7F9B8D2E2AF@.microsoft.com...[vbcol=seagreen]
> Thx for your info.
> Dumb Q. How do I see what columns the stats is for ?
> I know the stats name is say _WA_Sys_ord_billto_66F53242
> But on what column the stats is built on ? What command will tell me that
> ?
> TIA
> Knm
> "Tibor Karaszi" wrote:
>|||If you know the table, you can use sp_helpstats
You can pull the table name from sysindexes with this:
select object_name(id)
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"MO" <MO@.discussions.microsoft.com> wrote in message
news:80809AF6-100F-46E9-8CF3-A7F9B8D2E2AF@.microsoft.com...[vbcol=seagreen]
> Thx for your info.
> Dumb Q. How do I see what columns the stats is for ?
> I know the stats name is say _WA_Sys_ord_billto_66F53242
> But on what column the stats is built on ? What command will tell me that
> ?
> TIA
> Knm
> "Tibor Karaszi" wrote:
>|||Thanks for the info. I looked at a table, it has 118 entries in sysindexes
for that table. 6 indexes and 112 contraints. I dropped those constraints to
see if they are being used, but they all got created in couple of min which
tells me that they are being used. So, my question is should I convert those
constraints to an index ?
What are the cons of keeping these as a constraint and not converting to an
index in the long run ?
TIA
MO
"Kalen Delaney" wrote:

> If you know the table, you can use sp_helpstats
> You can pull the table name from sysindexes with this:
> select object_name(id)
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "MO" <MO@.discussions.microsoft.com> wrote in message
> news:80809AF6-100F-46E9-8CF3-A7F9B8D2E2AF@.microsoft.com...
>
>|||They are not constraints, bit difference. They are entries for SQL Server to
hold statistics over
the data in a column of the table. Whether you should create such indexes, o
nly you can tell. It is
likely that your queries can benefit from indexes on some over the columns t
hat these statistics has
been created. OTOH, you most probably don't want to have 118 indexes on a ta
ble (imagine the
slowness of your modifications and storage requirements). Analyze your queri
es, their execution
plans and create indexes that makes sense.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"MO" <MO@.discussions.microsoft.com> wrote in message
news:AE7CA10C-4F2C-49D0-B64D-CA40728053CC@.microsoft.com...[vbcol=seagreen]
> Thanks for the info. I looked at a table, it has 118 entries in sysindexes
> for that table. 6 indexes and 112 contraints. I dropped those constraints
to
> see if they are being used, but they all got created in couple of min whic
h
> tells me that they are being used. So, my question is should I convert tho
se
> constraints to an index ?
> What are the cons of keeping these as a constraint and not converting to a
n
> index in the long run ?
> TIA
> MO
> "Kalen Delaney" wrote:
>

@@ROWCOUNT AND TRANSACTION

I was testing some checking techniques to add to my SPs while retreiving data or inserting a series of reocords to a series of tables.

Check this testing small piece of code:

begin transaction t1select * from roles where rolename = 'student'if @.@.rowcount = 0goto cleanupselect * from usersif @.@.rowcount = 0goto cleanupcommit transaction t1goto endupcleanup:rollback transaction t1SET ROWCOUNT 0endup:print @.@.rowcount

On one of the testing cases: I made the first query return 1 record and the second returned 5 records, so we ended up at the label "endup", I printed out the RowCount it was 0, does the commit transaction reset that variable?

In addition, are there smart and effective techniques to check against validity of the query statements inside the SP, for example, I can use @.@.ERROR and @.@.ROWCOUNT in a transaction based SP to know if I proceed with sub queries, are there more stuff like that?

Regards

Bilal,

Actually I don't know if commit transaction resets the@.@.rowcount field, but from my experience the only way to use the @.@.rowcount andthe @.@.error fields, is to declare local variables and immediately after DML statementscopy the @.@.rowcount and @.@.error fields to the local variable, for example:

Declare @.xRowCount int

Declare @.xError int

Select ……

Select @.xRowCount = @.@.rowcount, @.xError = @.@.Error

Following this method you are sure that the values don't get"messed up". I always insert this "copystatement" after select, insert, update and delete statements.

BTW:

Why do you have the SET Rowcount 0 after the rollback statement(The set rowcount 0 has nothing to do with the value in the @.@.rowcount)?And why do you use transactions in this example (I mean as long as you justhave select statements and none update, insert or delete statements?).

|||

Hello:

Thanks for the reply.

I usually use transactions when I have update, insert or delete and off ocurse multiple statements need to be executed. I used it here to show something, wierd for me at least. The first statement returned @.@.ROWCOUNT = 1, the second 5, and the final result (ROWSCOUNT value) was 0 and that made me think of the effect of the transaction on RowCount.

Thanks for the hints.

Regards

|||Hi.

You are right. The commit transaction reset the rowcount to 0 (at least according to the SqlServer 2005 documentation).

The documentation stats that:
Statementssuch as USE, SET <option>, DEALLOCATE CURSOR, CLOSE CURSOR, BEGIN TRANSACTION or COMMIT TRANSACTION reset the ROWCOUNT value to 0.

Mark the words "Such as", meaning you can't be sure about the other statements. In other words, never trust the @.@.rowcount except right after a select, insert, update or delete statement.

I agree that is't a bit wired.