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
>
>

Info about Active/Active SQL 2000 Cluster

Hi all,
i need configure a active/active SQL2000 cluster but i don't have experience
in this scenario
The hardware and installation of MSCS di the same for the active/passive
configuration ?
In active/active configuration i must install more SQL virtual server (two)
on the same node ?
Thanks.http://msdn.microsoft.com/library/default.asp?
url=/library/en-us/adminsql/ad_clustering_81ma.asp
>--Original Message--
>Hi all,
>i need configure a active/active SQL2000 cluster but i
don't have experience
>in this scenario
>The hardware and installation of MSCS di the same for the
active/passive
>configuration ?
>In active/active configuration i must install more SQL
virtual server (two)
>on the same node ?
>Thanks.
>
>.
>|||Thanks,
but a last questions :
if i configure a active/active SQL Cluster may my web application may point
to virtual istance and the modified a date and a view the same data on the
both virtual SQL server ?
This mean active/active SQç cluster ?
bye
"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:11f5401c4423b$b8eadd40$a101280a@.phx.gbl...
> http://msdn.microsoft.com/library/default.asp?
> url=/library/en-us/adminsql/ad_clustering_81ma.asp
>
> >--Original Message--
> >Hi all,
> >
> >i need configure a active/active SQL2000 cluster but i
> don't have experience
> >in this scenario
> >
> >The hardware and installation of MSCS di the same for the
> active/passive
> >configuration ?
> >
> >In active/active configuration i must install more SQL
> virtual server (two)
> >on the same node ?
> >
> >Thanks.
> >
> >
> >.
> >|||Not unless you replicate the data between the 2 virtual servers...
An active/active cluster has a virtual server on EACH of the physical
servers... Each virtual server is alive and doing a piece of work ( like
one might be HR and the other the SHOPFloor server)... Each virtual server
can fail over to the other physical server... But they are otherwise NOT
related to one another... (unless YOU do something to move data between the
two)
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
<io.com> wrote in message news:%23ayW5OkQEHA.1620@.TK2MSFTNGP12.phx.gbl...
> Thanks,
> but a last questions :
> if i configure a active/active SQL Cluster may my web application may
point
> to virtual istance and the modified a date and a view the same data on the
> both virtual SQL server ?
> This mean active/active SQç cluster ?
> bye
>
> "Julie" <anonymous@.discussions.microsoft.com> wrote in message
> news:11f5401c4423b$b8eadd40$a101280a@.phx.gbl...
> > http://msdn.microsoft.com/library/default.asp?
> > url=/library/en-us/adminsql/ad_clustering_81ma.asp
> >
> >
> > >--Original Message--
> > >Hi all,
> > >
> > >i need configure a active/active SQL2000 cluster but i
> > don't have experience
> > >in this scenario
> > >
> > >The hardware and installation of MSCS di the same for the
> > active/passive
> > >configuration ?
> > >
> > >In active/active configuration i must install more SQL
> > virtual server (two)
> > >on the same node ?
> > >
> > >Thanks.
> > >
> > >
> > >.
> > >
>

Info about A/A SQL 2000 cluster

Hi i need to configure 2 sql2000 server (S1 e S2) in a/a mode with two
database on the first node and other two database on the second node.
I have 3 Logical Unit SCSI on external diskarray
- q: quorum into "cluser group" with owner S1 Server
- r: dati1 into "Group 0" with owner S1 Server
- s: dati2 into "Group 1" with owner S1 Server
I now should install a SQL Virtual Server with SQL 2000 CD-ROM but how i
install virtual server for a/a mode ?
I read that i install first VS on the S1 Server and second VS on the S2
Server but how i can do it if all resource is locked is locked form S1
Server ?!?!?
How i set a datafile for VS on S1 to save to r: and datafile for VS on S2 to
save to s: if s: is locekd always from S1 server ?!?!?
Thanks in advance.
For every Active SQL you need another instance. In your case two instances
and two installs
Cheers,
Rod
<io.com> wrote in message news:%23qDH%23XlUEHA.2544@.TK2MSFTNGP10.phx.gbl...
> Hi i need to configure 2 sql2000 server (S1 e S2) in a/a mode with two
> database on the first node and other two database on the second node.
> I have 3 Logical Unit SCSI on external diskarray
> - q: quorum into "cluser group" with owner S1 Server
> - r: dati1 into "Group 0" with owner S1 Server
> - s: dati2 into "Group 1" with owner S1 Server
> I now should install a SQL Virtual Server with SQL 2000 CD-ROM but how i
> install virtual server for a/a mode ?
> I read that i install first VS on the S1 Server and second VS on the S2
> Server but how i can do it if all resource is locked is locked form S1
> Server ?!?!?
> How i set a datafile for VS on S1 to save to r: and datafile for VS on S2
to
> save to s: if s: is locekd always from S1 server ?!?!?
> Thanks in advance.
>
|||> For every Active SQL you need another instance. In your case two instances
> and two installs
Thanks but wjat do you think about this ?
I read that i install first VS on the S1 Server and second VS on the S2
Server but how i can do it if all resource is locked is locked form S1
Server ?!?!?
How i set a datafile for VS on S1 to save to r: and datafile for VS on S2
to save to s: if s: is locekd always from S1 server ?!?!?
Thanks in advance.
|||You run active/active with the Microsoft shared nothing model, each
instance/node will control its own group/disk drive. If you would like to
write to one database or the other, use T-SQL.
Cheers,
Rod
<io.com> wrote in message news:%23EjN8yqUEHA.3200@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
instances
> Thanks but wjat do you think about this ?
> I read that i install first VS on the S1 Server and second VS on the S2
> Server but how i can do it if all resource is locked is locked form S1
> Server ?!?!?
> How i set a datafile for VS on S1 to save to r: and datafile for VS on S2
> to save to s: if s: is locekd always from S1 server ?!?!?
> Thanks in advance.
>
|||Thanks Rodney,
but a last question :
How i do configure the cluster in "shared nothing model" !?!
From Cluster Administration / Cluster administration Wizard or during a SQL
installation ?
Thanks!
"Rodney R. Fournier [MVP]" <rod@.die.spam.die.nw-america.com> wrote in
message news:uHLonHtUEHA.3664@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> You run active/active with the Microsoft shared nothing model, each
> instance/node will control its own group/disk drive. If you would like to
> write to one database or the other, use T-SQL.
> Cheers,
> Rod
> <io.com> wrote in message news:%23EjN8yqUEHA.3200@.TK2MSFTNGP10.phx.gbl...
> instances
S2
>
|||The shared nothing model is just the way things are. When you setup
Clustering and then SQL, both us this model.
Cheers,
Rod
<io.com> wrote in message news:OvXo5ytUEHA.2408@.tk2msftngp13.phx.gbl...
> Thanks Rodney,
> but a last question :
> How i do configure the cluster in "shared nothing model" !?!
> From Cluster Administration / Cluster administration Wizard or during a
SQL[vbcol=seagreen]
> installation ?
> Thanks!
>
> "Rodney R. Fournier [MVP]" <rod@.die.spam.die.nw-america.com> wrote in
> message news:uHLonHtUEHA.3664@.TK2MSFTNGP12.phx.gbl...
to[vbcol=seagreen]
news:%23EjN8yqUEHA.3200@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
S2
> S2
>

Indexes and File groups

Something strange.

I have a database(SQL2000) with two file group(on seperate physical
drives).
One is meant for table data[PRIMARY] and one for indexes [INDEX].

So i create a table on the [PRIMARY] file group, and fill in
data.

Next I build a clustered index on the table, on the [INDEX] filegroup.

Once the index is built, the database now indicates that the filegroup
for the table [INDEX]! and not [PRIMARY] as i originally set it up for!

My question it then: Has the table been moved or is this somehow an
error in SQL server?
I would really appreciate any thought anyone might have on this?Jens

A clustered index is the table (Well not quiet, but close enough). It
is impossible to have a clustered index on a different filegroup from
the data. You can build non-clustered on a seperate filegroup.

I suggest you rebuild your clustered index on your primary filegroup.
Regards

John|||Aha! Solved some mysteries for me :-). Thank you very much.
I guess i didnt quite understand how clustered indexes worked.

Actually i have a bunch of tables with clustered indexes which
currently reside my file group for indexes. The good news is,if I
understand you correctly,
the if I simply rebuild the clustered index on my data file group
the table data will be moved back.|||Jens

Yes, rebuilding the clustered index will move the table. You can also
do it through enterprise manager, using design table, this rebuilds the
index for you.

Regards

John|||Im planning to recreate the clustered index like this:

CREATE
CLUSTERED INDEX [idx-clusteredindex]
ON
[dbo].[TABLE_NAME]([COLOUMN_NANE])
WITH
DROP_EXISTING,
FILLFACTOR = 90
ON
[PRIMARY]

As I understand this will alse cause all non-clustered index
on the table to be rebuilt/recalculated as well.
Is this infact the case of do I have to
do i have to do it explicitly afterwards like:

DBCC DBREINDEX ([dbo].[TABLE_NAME],[idx-nonclustered],90)

johnbandettini@.yahoo.co.uk wrote:
> Jens
> Yes, rebuilding the clustered index will move the table. You can also
> do it through enterprise manager, using design table, this rebuilds
the
> index for you.
>
> Regards
>
> John