INFORMATION_SCHEMA Views and Indexed Views

Hi,
I wanted to write some stored procedures to help me manage my indexed views
in SQL2000/2008. Since I wanted to follow the advice to use the
INFORMATION_SCHEMA views instead of system tables/catalog views. I have
this query that will run in both 2000 and 2008 and it correctly finds my
indexed views:
select *
from sysobjects
where type = 'V'
and id in (select id from sysindexes);
However, if I run this query in either 2000 or 2008 , it returns nothing:
select * from INFORMATION_SCHEMA.VIEWS
where TABLE_NAME in (
select TABLE_NAME from INFORMATION_SCHEMA.KEY_COLUMN_USAGE
)
The SQL 2008 BOL states "Returns one row for each column that is constrained
as a key in the current database.", and SQL 2000 BOL state "Contains one row
for each column, in the current database, that is constrained as a key."
Does anyone have any sage advice on this topic, or should I post it as a
Connect issue for SQL 2008?
--
Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.org"Daniel Jameson" <danjam47@.newsgroup.nospam> wrote in message
news:OpZ6jOpEIHA.936@.TK2MSFTNGP06.phx.gbl...
> Hi,
> I wanted to write some stored procedures to help me manage my indexed
> views in SQL2000/2008. Since I wanted to follow the advice to use the
> INFORMATION_SCHEMA views instead of system tables/catalog views. I have
> this query that will run in both 2000 and 2008 and it correctly finds my
> indexed views:
> select *
> from sysobjects
> where type = 'V'
> and id in (select id from sysindexes);
> However, if I run this query in either 2000 or 2008 , it returns nothing:
> select * from INFORMATION_SCHEMA.VIEWS
> where TABLE_NAME in (
> select TABLE_NAME from INFORMATION_SCHEMA.KEY_COLUMN_USAGE
> )
> The SQL 2008 BOL states "Returns one row for each column that is
> constrained as a key in the current database.", and SQL 2000 BOL state
> "Contains one row for each column, in the current database, that is
> constrained as a key."
> Does anyone have any sage advice on this topic, or should I post it as a
> Connect issue for SQL 2008?
> --
> Thank you,
> Daniel Jameson
> SQL Server DBA
> Children's Oncology Group
> www.childrensoncologygroup.org
>
>
The INFORMATION_SCHEMA describes only the logical features of the database:
tables, columns, constraints. Not indexes because they are a physical
implementation construct and aren't part of standard SQL like the
INFORMATION_SCHEMA.
For index information you need sys.indexes and sys.index_columns, or
dbo.sysindexes and dbo.sysindexkeys. That's unless the index is one that
supports a constraint, in which case the same information will be in
INFORMATION_SCHEMA.
--
David Portas|||Someone will point out what's going on and for every issue you encounter
they'll tell you how you screwed up (though usually politely - unless you
get celko). I'll probably get blasted for this post. However, personally, I
think INFORMATION_SCHEMA sucks and that the SQL Server catalog tables are
far, far superior and thought out much better (esp. 2005).
While I'll blame Microsoft for the poor docs on it, I don't blame them for
the bulk of my issues with it as they just implimented the ANSI standard. I
just don't like it and don't think it was well thought out.
Jay
"Daniel Jameson" <danjam47@.newsgroup.nospam> wrote in message
news:OpZ6jOpEIHA.936@.TK2MSFTNGP06.phx.gbl...
> Hi,
> I wanted to write some stored procedures to help me manage my indexed
> views in SQL2000/2008. Since I wanted to follow the advice to use the
> INFORMATION_SCHEMA views instead of system tables/catalog views. I have
> this query that will run in both 2000 and 2008 and it correctly finds my
> indexed views:
> select *
> from sysobjects
> where type = 'V'
> and id in (select id from sysindexes);
> However, if I run this query in either 2000 or 2008 , it returns nothing:
> select * from INFORMATION_SCHEMA.VIEWS
> where TABLE_NAME in (
> select TABLE_NAME from INFORMATION_SCHEMA.KEY_COLUMN_USAGE
> )
> The SQL 2008 BOL states "Returns one row for each column that is
> constrained as a key in the current database.", and SQL 2000 BOL state
> "Contains one row for each column, in the current database, that is
> constrained as a key."
> Does anyone have any sage advice on this topic, or should I post it as a
> Connect issue for SQL 2008?
> --
> Thank you,
> Daniel Jameson
> SQL Server DBA
> Children's Oncology Group
> www.childrensoncologygroup.org
>
>

Indext extent fragmentation

Hi,
On the databases I manage I notice high (50%+) extent
fragmentation when I run DBCC SHOWCONTIG against the
tables' indexes. I've run dbcc indexdefrag, dbcc
dbreindex, and create index ...with drop existing on these
indexes and they still show the same fragmentation even
after an UPDATE STATISTICS.
Is there any way to eliminate this beyond completely
dropping the indexes and starting from scratch (not a
realistic option)? The procedure I run has virtually
eliminated the logical fragmentation- are there major
performance issues with leaving them as-is?
-DanHi,
If your select statement returns all the records of a table, such table
fragmentation can cause additional page reads which will reduce the
performance of your query and utilize more resource (Memory/CPU / Disk
reads- I/O. So INDEXDEFRAG will increase the performance on fragmented
table.
One more advantage is for fragmented tables, after INDEXDEFRAG / drop and
recreate index, all the fragmented pages will be cleared and you will have
enough
free space in your database.
Thanks
Hari
US Technology
Drop and re-create a clustered index. "Dan Wunder"
<anonymous@.discussions.microsoft.com> wrote in message
news:03c101c399a6$be7b5340$a401280a@.phx.gbl...
> Hi,
> On the databases I manage I notice high (50%+) extent
> fragmentation when I run DBCC SHOWCONTIG against the
> tables' indexes. I've run dbcc indexdefrag, dbcc
> dbreindex, and create index ...with drop existing on these
> indexes and they still show the same fragmentation even
> after an UPDATE STATISTICS.
> Is there any way to eliminate this beyond completely
> dropping the indexes and starting from scratch (not a
> realistic option)? The procedure I run has virtually
> eliminated the logical fragmentation- are there major
> performance issues with leaving them as-is?
> -Dan|||Please read the whitepaper at
http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/sql/maintain/optimize/ss2kidbp.asp
This explains everything you need to know.
Regards,
Paul.
--
Paul Randal
DBCC Technical Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:OAqkB7dmDHA.2592@.TK2MSFTNGP10.phx.gbl...
> Hi,
> If your select statement returns all the records of a table, such table
> fragmentation can cause additional page reads which will reduce the
> performance of your query and utilize more resource (Memory/CPU / Disk
> reads- I/O. So INDEXDEFRAG will increase the performance on fragmented
> table.
> One more advantage is for fragmented tables, after INDEXDEFRAG / drop and
> recreate index, all the fragmented pages will be cleared and you will
have
> enough
> free space in your database.
> Thanks
> Hari
> US Technology
>
> Drop and re-create a clustered index. "Dan Wunder"
> <anonymous@.discussions.microsoft.com> wrote in message
> news:03c101c399a6$be7b5340$a401280a@.phx.gbl...
> > Hi,
> >
> > On the databases I manage I notice high (50%+) extent
> > fragmentation when I run DBCC SHOWCONTIG against the
> > tables' indexes. I've run dbcc indexdefrag, dbcc
> > dbreindex, and create index ...with drop existing on these
> > indexes and they still show the same fragmentation even
> > after an UPDATE STATISTICS.
> > Is there any way to eliminate this beyond completely
> > dropping the indexes and starting from scratch (not a
> > realistic option)? The procedure I run has virtually
> > eliminated the logical fragmentation- are there major
> > performance issues with leaving them as-is?
> >
> > -Dan
>