More Materialized View questions

sql2k sp3
Im having a really hard time getting my head into
Materialized Views.
Accoring to BOL:
When a unique clustered index is created on a view, the
view is executed and the result set is stored in the
database in the same way a table with a clustered index is
stored.
Now this sounds good and makes sense. However, say I have
a Materialized View:
create view MatTest
as
select * from table1
Create unique clustered index... on MatTest(Column1)
So, if Im reading BOL correctly, the results are stored in
the DB already. But then what happens when I am selecting
from my view and put a WHERE clause on the SELECT?
select * from MatTest
where column1 = bla
and column2 = bla
Wouldn't the view then have to rebuild the results just
like a normal query would?
Also, (dont know if I would do this one, just curious)
what if I want to use Dynamic SQL to call my view from an
SP and be able to feed in Parameters? Is the clustered
index still used?
Thanks to all for your insights.
TIA, ChrisRChris,
Let's say we have the following non-materialized view:
CREATE VIEW NonMaterialized
AS
SELECT TblA.ColA, TblB.ColB
FROM TblA
JOIN TblB ON TblA.PK = TblB.PK
When you run a query against the view, e.g.:
SELECT *
FROM NonMaterialized
WHERE ColA = 5
What really happens is that your query is re-written first as:
SELECT *
FROM
(SELECT TblA.ColA, TblB.ColB
FROM TblA
JOIN TblB ON TblA.PK = TblB.PK) AS NonMaterialized
WHERE ColA = 5
And then most likely optimized into:
SELECT TblA.ColA, TblB.ColB
FROM TblA
JOIN TblB ON TblA.PK = TblB.PK
WHERE ColA = 5
So using this view, you still do all of the work, at query time, as you
would selecting from the base tables.
Now, assume that we've materialized the view. SQL Server now stores the
entire results of the view in the same way that it stores table data. So
when you exercise this query:
SELECT *
FROM Materialized
WHERE ColA = 5
There are no base tables to JOIN. This query can use indexes directly
against the view, as if it were a base table itself.
Does that make more sense?
"ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
news:8f7a01c49680$3eb535e0$a301280a@.phx.gbl...
> sql2k sp3
> Im having a really hard time getting my head into
> Materialized Views.
> Accoring to BOL:
> When a unique clustered index is created on a view, the
> view is executed and the result set is stored in the
> database in the same way a table with a clustered index is
> stored.
>
> Now this sounds good and makes sense. However, say I have
> a Materialized View:
> create view MatTest
> as
> select * from table1
> Create unique clustered index... on MatTest(Column1)
>
> So, if Im reading BOL correctly, the results are stored in
> the DB already. But then what happens when I am selecting
> from my view and put a WHERE clause on the SELECT?
> select * from MatTest
> where column1 = bla
> and column2 = bla
> Wouldn't the view then have to rebuild the results just
> like a normal query would?
> Also, (dont know if I would do this one, just curious)
> what if I want to use Dynamic SQL to call my view from an
> SP and be able to feed in Parameters? Is the clustered
> index still used?
>
> Thanks to all for your insights.
> TIA, ChrisR
>|||Perfect. Thats what I was missing. Also, BOL doesnt metion
needing Enterprise Ed to use these, but I seem to recall
reading that its a must. Do you know?
>--Original Message--
>Chris,
>Let's say we have the following non-materialized view:
>CREATE VIEW NonMaterialized
>AS
> SELECT TblA.ColA, TblB.ColB
> FROM TblA
> JOIN TblB ON TblA.PK = TblB.PK
>When you run a query against the view, e.g.:
>SELECT *
>FROM NonMaterialized
>WHERE ColA = 5
>What really happens is that your query is re-written
first as:
>SELECT *
>FROM
> (SELECT TblA.ColA, TblB.ColB
> FROM TblA
> JOIN TblB ON TblA.PK = TblB.PK) AS NonMaterialized
>WHERE ColA = 5
>And then most likely optimized into:
>SELECT TblA.ColA, TblB.ColB
> FROM TblA
> JOIN TblB ON TblA.PK = TblB.PK
> WHERE ColA = 5
>So using this view, you still do all of the work, at
query time, as you
>would selecting from the base tables.
>Now, assume that we've materialized the view. SQL Server
now stores the
>entire results of the view in the same way that it stores
table data. So
>when you exercise this query:
>SELECT *
>FROM Materialized
>WHERE ColA = 5
>There are no base tables to JOIN. This query can use
indexes directly
>against the view, as if it were a base table itself.
>Does that make more sense?
>
>"ChrisR" <anonymous@.discussions.microsoft.com> wrote in
message
>news:8f7a01c49680$3eb535e0$a301280a@.phx.gbl...
>> sql2k sp3
>> Im having a really hard time getting my head into
>> Materialized Views.
>> Accoring to BOL:
>> When a unique clustered index is created on a view, the
>> view is executed and the result set is stored in the
>> database in the same way a table with a clustered index
is
>> stored.
>>
>> Now this sounds good and makes sense. However, say I
have
>> a Materialized View:
>> create view MatTest
>> as
>> select * from table1
>> Create unique clustered index... on MatTest(Column1)
>>
>> So, if Im reading BOL correctly, the results are stored
in
>> the DB already. But then what happens when I am
selecting
>> from my view and put a WHERE clause on the SELECT?
>> select * from MatTest
>> where column1 = bla
>> and column2 = bla
>> Wouldn't the view then have to rebuild the results just
>> like a normal query would?
>> Also, (dont know if I would do this one, just curious)
>> what if I want to use Dynamic SQL to call my view from
an
>> SP and be able to feed in Parameters? Is the clustered
>> index still used?
>>
>> Thanks to all for your insights.
>> TIA, ChrisR
>
>.
>|||"ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
news:8f3001c49686$bc967ea0$a501280a@.phx.gbl...
> Perfect. Thats what I was missing. Also, BOL doesnt metion
> needing Enterprise Ed to use these, but I seem to recall
> reading that its a must. Do you know?
No, I have indexed views running on Standard Edition servers. I believe
that the restriction is that Standard Edition will not automatically
consider them when you query base tables. For instance, if you created this
view:
CREATE VIEW AView
AS
SELECT ColA, COUNT_BIG(*) AS RowCount
FROM dbo.YourTableA
GROUP BY ColA
... and then you materialized it, and then you did the following query:
SELECT ColA, COUNT(*) AS RowCount
FROM dbo.YourTableA
WHERE ColA = 'ABC'
GROUP BY ColA
Enterprise Edition will actually be able to automatically use AView to
answer the query. In Standard Edition, to use the view, you'd have to do:
SELECT ColA, RowCount
FROM AView|||Yes you need EE for the view to be used automatically. Otherwise you must
specify the NOEXPAND hint.
--
Andrew J. Kelly SQL MVP
"ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
news:8f3001c49686$bc967ea0$a501280a@.phx.gbl...
> Perfect. Thats what I was missing. Also, BOL doesnt metion
> needing Enterprise Ed to use these, but I seem to recall
> reading that its a must. Do you know?
>
> >--Original Message--
> >Chris,
> >
> >Let's say we have the following non-materialized view:
> >
> >CREATE VIEW NonMaterialized
> >AS
> > SELECT TblA.ColA, TblB.ColB
> > FROM TblA
> > JOIN TblB ON TblA.PK = TblB.PK
> >
> >When you run a query against the view, e.g.:
> >
> >SELECT *
> >FROM NonMaterialized
> >WHERE ColA = 5
> >
> >What really happens is that your query is re-written
> first as:
> >
> >SELECT *
> >FROM
> > (SELECT TblA.ColA, TblB.ColB
> > FROM TblA
> > JOIN TblB ON TblA.PK = TblB.PK) AS NonMaterialized
> >WHERE ColA = 5
> >
> >And then most likely optimized into:
> >
> >SELECT TblA.ColA, TblB.ColB
> > FROM TblA
> > JOIN TblB ON TblA.PK = TblB.PK
> > WHERE ColA = 5
> >
> >So using this view, you still do all of the work, at
> query time, as you
> >would selecting from the base tables.
> >
> >Now, assume that we've materialized the view. SQL Server
> now stores the
> >entire results of the view in the same way that it stores
> table data. So
> >when you exercise this query:
> >
> >SELECT *
> >FROM Materialized
> >WHERE ColA = 5
> >
> >There are no base tables to JOIN. This query can use
> indexes directly
> >against the view, as if it were a base table itself.
> >
> >Does that make more sense?
> >
> >
> >"ChrisR" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:8f7a01c49680$3eb535e0$a301280a@.phx.gbl...
> >> sql2k sp3
> >>
> >> Im having a really hard time getting my head into
> >> Materialized Views.
> >>
> >> Accoring to BOL:
> >>
> >> When a unique clustered index is created on a view, the
> >> view is executed and the result set is stored in the
> >> database in the same way a table with a clustered index
> is
> >> stored.
> >>
> >>
> >> Now this sounds good and makes sense. However, say I
> have
> >> a Materialized View:
> >>
> >> create view MatTest
> >> as
> >> select * from table1
> >>
> >> Create unique clustered index... on MatTest(Column1)
> >>
> >>
> >> So, if Im reading BOL correctly, the results are stored
> in
> >> the DB already. But then what happens when I am
> selecting
> >> from my view and put a WHERE clause on the SELECT?
> >>
> >> select * from MatTest
> >> where column1 = bla
> >> and column2 = bla
> >>
> >> Wouldn't the view then have to rebuild the results just
> >> like a normal query would?
> >>
> >> Also, (dont know if I would do this one, just curious)
> >> what if I want to use Dynamic SQL to call my view from
> an
> >> SP and be able to feed in Parameters? Is the clustered
> >> index still used?
> >>
> >>
> >> Thanks to all for your insights.
> >>
> >> TIA, ChrisR
> >>
> >
> >
> >.
> >

More Materialized View questions

sql2k sp3
Im having a really hard time getting my head into
Materialized Views.
Accoring to BOL:
When a unique clustered index is created on a view, the
view is executed and the result set is stored in the
database in the same way a table with a clustered index is
stored.
Now this sounds good and makes sense. However, say I have
a Materialized View:
create view MatTest
as
select * from table1
Create unique clustered index... on MatTest(Column1)
So, if Im reading BOL correctly, the results are stored in
the DB already. But then what happens when I am selecting
from my view and put a WHERE clause on the SELECT?
select * from MatTest
where column1 = bla
and column2 = bla
Wouldn't the view then have to rebuild the results just
like a normal query would?
Also, (dont know if I would do this one, just curious)
what if I want to use Dynamic SQL to call my view from an
SP and be able to feed in Parameters? Is the clustered
index still used?
Thanks to all for your insights.
TIA, ChrisR
Chris,
Let's say we have the following non-materialized view:
CREATE VIEW NonMaterialized
AS
SELECT TblA.ColA, TblB.ColB
FROM TblA
JOIN TblB ON TblA.PK = TblB.PK
When you run a query against the view, e.g.:
SELECT *
FROM NonMaterialized
WHERE ColA = 5
What really happens is that your query is re-written first as:
SELECT *
FROM
(SELECT TblA.ColA, TblB.ColB
FROM TblA
JOIN TblB ON TblA.PK = TblB.PK) AS NonMaterialized
WHERE ColA = 5
And then most likely optimized into:
SELECT TblA.ColA, TblB.ColB
FROM TblA
JOIN TblB ON TblA.PK = TblB.PK
WHERE ColA = 5
So using this view, you still do all of the work, at query time, as you
would selecting from the base tables.
Now, assume that we've materialized the view. SQL Server now stores the
entire results of the view in the same way that it stores table data. So
when you exercise this query:
SELECT *
FROM Materialized
WHERE ColA = 5
There are no base tables to JOIN. This query can use indexes directly
against the view, as if it were a base table itself.
Does that make more sense?
"ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
news:8f7a01c49680$3eb535e0$a301280a@.phx.gbl...
> sql2k sp3
> Im having a really hard time getting my head into
> Materialized Views.
> Accoring to BOL:
> When a unique clustered index is created on a view, the
> view is executed and the result set is stored in the
> database in the same way a table with a clustered index is
> stored.
>
> Now this sounds good and makes sense. However, say I have
> a Materialized View:
> create view MatTest
> as
> select * from table1
> Create unique clustered index... on MatTest(Column1)
>
> So, if Im reading BOL correctly, the results are stored in
> the DB already. But then what happens when I am selecting
> from my view and put a WHERE clause on the SELECT?
> select * from MatTest
> where column1 = bla
> and column2 = bla
> Wouldn't the view then have to rebuild the results just
> like a normal query would?
> Also, (dont know if I would do this one, just curious)
> what if I want to use Dynamic SQL to call my view from an
> SP and be able to feed in Parameters? Is the clustered
> index still used?
>
> Thanks to all for your insights.
> TIA, ChrisR
>
|||Perfect. Thats what I was missing. Also, BOL doesnt metion
needing Enterprise Ed to use these, but I seem to recall
reading that its a must. Do you know?

>--Original Message--
>Chris,
>Let's say we have the following non-materialized view:
>CREATE VIEW NonMaterialized
>AS
> SELECT TblA.ColA, TblB.ColB
> FROM TblA
> JOIN TblB ON TblA.PK = TblB.PK
>When you run a query against the view, e.g.:
>SELECT *
>FROM NonMaterialized
>WHERE ColA = 5
>What really happens is that your query is re-written
first as:
>SELECT *
>FROM
> (SELECT TblA.ColA, TblB.ColB
> FROM TblA
> JOIN TblB ON TblA.PK = TblB.PK) AS NonMaterialized
>WHERE ColA = 5
>And then most likely optimized into:
>SELECT TblA.ColA, TblB.ColB
> FROM TblA
> JOIN TblB ON TblA.PK = TblB.PK
> WHERE ColA = 5
>So using this view, you still do all of the work, at
query time, as you
>would selecting from the base tables.
>Now, assume that we've materialized the view. SQL Server
now stores the
>entire results of the view in the same way that it stores
table data. So
>when you exercise this query:
>SELECT *
>FROM Materialized
>WHERE ColA = 5
>There are no base tables to JOIN. This query can use
indexes directly
>against the view, as if it were a base table itself.
>Does that make more sense?
>
>"ChrisR" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:8f7a01c49680$3eb535e0$a301280a@.phx.gbl...
is[vbcol=seagreen]
have[vbcol=seagreen]
in[vbcol=seagreen]
selecting[vbcol=seagreen]
an
>
>.
>
|||"ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
news:8f3001c49686$bc967ea0$a501280a@.phx.gbl...
> Perfect. Thats what I was missing. Also, BOL doesnt metion
> needing Enterprise Ed to use these, but I seem to recall
> reading that its a must. Do you know?
No, I have indexed views running on Standard Edition servers. I believe
that the restriction is that Standard Edition will not automatically
consider them when you query base tables. For instance, if you created this
view:
CREATE VIEW AView
AS
SELECT ColA, COUNT_BIG(*) AS RowCount
FROM dbo.YourTableA
GROUP BY ColA
... and then you materialized it, and then you did the following query:
SELECT ColA, COUNT(*) AS RowCount
FROM dbo.YourTableA
WHERE ColA = 'ABC'
GROUP BY ColA
Enterprise Edition will actually be able to automatically use AView to
answer the query. In Standard Edition, to use the view, you'd have to do:
SELECT ColA, RowCount
FROM AView
|||Yes you need EE for the view to be used automatically. Otherwise you must
specify the NOEXPAND hint.
Andrew J. Kelly SQL MVP
"ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
news:8f3001c49686$bc967ea0$a501280a@.phx.gbl...[vbcol=seagreen]
> Perfect. Thats what I was missing. Also, BOL doesnt metion
> needing Enterprise Ed to use these, but I seem to recall
> reading that its a must. Do you know?
>
> first as:
> query time, as you
> now stores the
> table data. So
> indexes directly
> message
> is
> have
> in
> selecting
> an

Monitoring failovers on clustered servers

I've been asked to write a script to monitor whether a clustered server is up and alive and if so which node it's actually running on. Apparently there's been some problems of failover to the passive server without anyone knowing that it happened and they want to know. Any suggestions?The script below will result in capturing the currently active node:

begin tran
create table #tmp (hostname varchar(128) null)
commit tran
insert #tmp exec master.dbo.xp_cmdshell 'hostname' , no_infomsgs
select * from #tmp where hostname is not null
go
drop table #tmp
go|||We're in an active/passive environment, so when I run this script I just get the virtual servername back.

Originally posted by rdjabarov
The script below will result in capturing the currently active node:

begin tran
create table #tmp (hostname varchar(128) null)
commit tran
insert #tmp exec master.dbo.xp_cmdshell 'hostname' , no_infomsgs
select * from #tmp where hostname is not null
go
drop table #tmp
go|||And what do you get when you run HOSTNAME from command line? I am getting the active node name. In fact, I went to our lab environment and ran it, then I forced a failover and got the other node's name - which proves that it workes as expected in identifying the active node.|||From the command line I do get the active server name, but when I run that script I get the virtual server name.

Originally posted by rdjabarov
And what do you get when you run HOSTNAME from command line? I am getting the active node name. In fact, I went to our lab environment and ran it, then I forced a failover and got the other node's name - which proves that it workes as expected in identifying the active node.|||SQL Service account should belong to local administrators group on the box.|||Yes, it does.

Originally posted by rdjabarov
SQL Service account should belong to local administrators group on the box.|||When you were testing the script, did you run it from QA or scheduled it as a job? As I said, I ran this script in both prod and lab environments, and even did a failover on the lab cluster, and hostname reported the correct active node. Anyone else sees something I missed?|||I've done it both ways - both through QA then through a ksh script and I'm still getting the virtual server name back. How odd!

Originally posted by rdjabarov
When you were testing the script, did you run it from QA or scheduled it as a job? As I said, I ran this script in both prod and lab environments, and even did a failover on the lab cluster, and hostname reported the correct active node. Anyone else sees something I missed?|||Yup, it sure is.

Monitoring Clustered SQL Server

I am attempting to montior failovers, using Sitescope, so does anyone know
event what events are created when a node fails over?
Dave B
The SDK for Windows Clustering should give you a few.
http://msdn.microsoft.com/library/de...start_page.asp I
am not a developer, so I can't help you anymore with the SDK.
You can also create a failure and use the ones you want from that wonderful
experience.
Cheers,
Rod
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering Website
http://msmvps.com/clustering - Blog
http://www.itsummitseries.com/experts/fournier.htm - Cluster Summit
"Dave B" <DaveB@.discussions.microsoft.com> wrote in message
news:5C9DDBD9-4FF8-461D-BAEE-8C480EA9A713@.microsoft.com...
>I am attempting to montior failovers, using Sitescope, so does anyone know
> event what events are created when a node fails over?
> --
> Dave B
|||Dave B wrote:
> I am attempting to montior failovers, using Sitescope, so does anyone know
> event what events are created when a node fails over?
We use nagios to monitor if the sql service is running on the prefered
node. If not it must have failed over. sitescope can monitor services too.
Hans