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 Changes in SP, Table and Views - Server Objects

Hello,
I would like to monitor the changes in current SQL Server Database
Objets like Tables, Stored Procedures, views and Triggers. I would like to
store the information in one table and would like to get the report.
How can i do this?Your best bet is via 3rd party tools. Take a look at the offerings of
lumigent. www.lumigent.com
HTH. Ryan
"Mehul Gurjar" <Mehul Gurjar@.discussions.microsoft.com> wrote in message
news:4C2A4D8D-BE04-4B66-9D95-6A631C11A6B9@.microsoft.com...
> Hello,
> I would like to monitor the changes in current SQL Server Database
> Objets like Tables, Stored Procedures, views and Triggers. I would like to
> store the information in one table and would like to get the report.
> How can i do this?
>|||Hello Ryan,
Thanks for the response. But can i do this using SQL server only.
The third party tool will either monitor the Changes using Processes and wil
l
be storing the Data in some database only. We have Jobs, Stored procedures
and we can run the processes from the SQL server only.
Do we have any other method except the third party tool ?
If we can do this, we can use it to all other client's environment
and can monitor the changes.
With regards,
"Ryan" wrote:

> Your best bet is via 3rd party tools. Take a look at the offerings of
> lumigent. www.lumigent.com
>
> --
> HTH. Ryan
> "Mehul Gurjar" <Mehul Gurjar@.discussions.microsoft.com> wrote in message
> news:4C2A4D8D-BE04-4B66-9D95-6A631C11A6B9@.microsoft.com...
>
>|||In SQL Server 2005 you can take a lookn at modify_date in sys.all_objects
catalog view
MS wrote that you can see the changes if you use an ALTER statement, however
modify_date will be changed if you did it direclty in the object by right
click--Modify ( I checked views) as well.
"Mehul Gurjar" <MehulGurjar@.discussions.microsoft.com> wrote in message
news:8F3AF150-AB1F-423C-B863-94D5A7010AE3@.microsoft.com...
> Hello Ryan,
> Thanks for the response. But can i do this using SQL server only.
> The third party tool will either monitor the Changes using Processes and
> will
> be storing the Data in some database only. We have Jobs, Stored procedures
> and we can run the processes from the SQL server only.
> Do we have any other method except the third party tool ?
> If we can do this, we can use it to all other client's environment
> and can monitor the changes.
> With regards,
> "Ryan" wrote:
>

Monitor data per session - available?

Hiya!

I've combed the SQL Server 2000 online help but I don't see anything
akin to Sybase ASE's 'montor' views - equivalent to Oracle's V$
views. The views are performance views which contain per session
information such as:

- SPID
- number of logical read/write and physical read I/Os accumulated
thus far
- CPU accumulated
- Memory

etc.

Does anyone know if I can snarf that data from SQL Server 2000?

Thx!
--
Pablo Sanchez, Blueoak Database Engineering
http://www.blueoakdb.comErland Sommarskog <sommar@.algonet.se> wrote in
news:Xns93E5EFFB099AEYazorman@.127.0.0.1:

> What's wrong with sysprocesses?

In the 'olden' days (Sybase 4.x/10.x) the values weren't valid. Are
they considered valid nowadays?

Thx!
--
Pablo Sanchez, Blueoak Database Engineering
http://www.blueoakdb.com|||Pablo Sanchez (honeypot@.blueoakdb.com) writes:
> Erland Sommarskog <sommar@.algonet.se> wrote in
> news:Xns93E5EFFB099AEYazorman@.127.0.0.1:
>> What's wrong with sysprocesses?
> In the 'olden' days (Sybase 4.x/10.x) the values weren't valid. Are
> they considered valid nowadays?

Sorry for the delay, but I tried to consult with my MVP colleagues and
the Microsoft people, if I would get any response.

I can't vouch for that all these values are extremely valid, but then
again, I don't think you will find anything better.

The ones on memory and CPU may be OK. Logical reads is a problem, because
with the new query strategies in SQL 7 and SQL 2000, the value reflected
by logical reads in SET STATISTICS IO may not always be accurate. And if
it's not accurate there, it is not likely to be accurate in sysprocesses
either. Still the value you see is probably good indication.

Of course, you can also use the performance monitor, but I don't think
you will find per-spid values there.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog <sommar@.algonet.se> wrote in
news:Xns93EAE79435FA0Yazorman@.127.0.0.1:

> Pablo Sanchez (honeypot@.blueoakdb.com) writes:
>> Erland Sommarskog <sommar@.algonet.se> wrote in
>> news:Xns93E5EFFB099AEYazorman@.127.0.0.1:
>>
>> In the 'olden' days (Sybase 4.x/10.x) the values weren't valid.
>> Are they considered valid nowadays?
> Sorry for the delay, but I tried to consult with my MVP colleagues
> and the Microsoft people, if I would get any response.

'preciate that _very_ much.

> [ sage info snipped ]

Thank you for the data Erland. FWIW, Sybase ASE has gone the way of
Oracle and now provides in-memory views of the metrics. Perhaps SQL
Server will soon follow.

Later!
--
Pablo Sanchez, Blueoak Database Engineering
http://www.blueoakdb.com