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

More Efficient way to Concat Rows

Hi I have this problem with a query to process data concatenating
Units/Modules.
I have one View called Certificates thats lists Course IDs, Candidate
Names and Units. As each Certificate shows multiple Units achieved by
the Candidate I need to transform this data and combine the Units into
a single value - so I have 1 row per certificate rathan than 1 row per
unit.
I have been using this Query and several UDF's (Unit names, Unit
Grades, Unit Award dates etc) but as the database incresed in size the
query is timing out - Is there a more efficient way to do this ? Also
the function seems to create duplicates so i am using DISTINCT and
thisn seems to slow it down too. Help!
Thanks
hals_left
SELECT DISTINCT CourseID, CandidateID,dbo.GetUnits(CandidateID,
CourseID) AS Units
FROM Certificates
CREATE FUNCTION dbo.GetUnits
(@.CandidateID AS smallint, @.CourseID AS smallint )
RETURNS varchar(1000)
AS
BEGIN
DECLARE @.str AS varchar(1000)
SET @.str = ''
SELECT @.str = @.str + UnitTitle + char(13) + char(10)
FROM Certificates
WHERE CandidateID = @.CandidateID AND CourseID= @.CourseID
RETURN @.str
ENDIf this is just formatting for display then it isn't obvious why you
would need to do it in the database. The simplest and most likely
fastest method is in the client or presentation tier. See:
http://msdn.microsoft.com/library/d...
etstringmethod(recordset)ado.asp
David Portas
SQL Server MVP
--|||I agree but the client application (Word 2K Mailmerge) isnt capable of
this.|||Hello, hals_left
To make sure that the function is not called too many times, use a
query like this:
SELECT CourseID, CandidateID,
dbo.GetUnits(CandidateID, CourseID) AS Units
FROM (
SELECT DISTINCT CourseID, CandidateID,
FROM Certificates
) x
Razvan

Monitoring SQL Compact Edition Performance

Does SQL Compact Edition expose performance counters to tools like Perfmon as SQL Server does? For instance, can you view lock wait times, cache hit ratio, etc.?

Currently, SQL Compact does not expose any performance counters. It is a in-process database engine, not a service/server process like SQL Server. You can analyze query execution with the query analyzer in SQL Server Management Studio (Express SP2).

monitoring changes on a view

I have a number of tables, each with many fields. I put together
information by a view onto the relevant tables and fields. Now I'd like
to monitor if data in my view has been updated or inserted. I don't care
about the fields not included in the view. If something changes, the ID
of the corresponding dataset should be inserted into a log table.
I have no idea how to handle this with a trigger. If I put a trigger on
the basetables, it will fire on every change. On the other hand i can't
put a trigger on the view. Any ideas?
Thanks in advance
Michael
Example:
Table1
a1
a2
a3
a4
Table2
b1
b2
b3
b4
Table3
c1
c2
c3
c4
c5
View combines
a1,a2,b1,c4,c5Michael Schroeder" wrote:

> I have a number of tables, each with many fields. I put together
> On the other hand i can't
> put a trigger on the view. Any ideas?
>
Do you mean that certain business rules prevent you from putting a trigger
on the view? You can put an INSTEAD OF trigger on the view, and put logic
into the trigger to update the underlying base tables, as well as put an
entry into an audit log.|||I don't know what type of events you are wanting to audit, but you can
create an insert, update, or delete triggers on the base tables. Whether or
not this presents a problem depends on how complex the logic of the trigger
is. Below is an example of a simple and low cost implementation of an
auditing trigger that I have used in the past. In this case, the table
EmployeeAudit has the same column layout of Employee but with the addition
of a column named AuditDate and AuditType (delete or insert). An update is a
delete immediately followed by an insert.
CREATE trigger EmployeeAudit on Employee
AFTER INSERT, UPDATE, DELETE
AS
insert into EmployeeAudit select getdate(), 'D', * from deleted
insert into EmployeeAudit select getdate(), 'I', * from inserted
GO
"Michael Schroeder" <schroeder@.idicos.[germany]> wrote in message
news:%23zqtnQ1JGHA.1544@.TK2MSFTNGP11.phx.gbl...
>I have a number of tables, each with many fields. I put together
>information by a view onto the relevant tables and fields. Now I'd like to
>monitor if data in my view has been updated or inserted. I don't care about
>the fields not included in the view. If something changes, the ID of the
>corresponding dataset should be inserted into a log table.
> I have no idea how to handle this with a trigger. If I put a trigger on
> the basetables, it will fire on every change. On the other hand i can't
> put a trigger on the view. Any ideas?
> Thanks in advance
> Michael
> --
> Example:
> Table1
> a1
> a2
> a3
> a4
> Table2
> b1
> b2
> b3
> b4
> Table3
> c1
> c2
> c3
> c4
> c5
> View combines
> a1,a2,b1,c4,c5|||Mark Williams schrieb:
> Michael Schroeder" wrote:
>
> Do you mean that certain business rules prevent you from putting a trigger
> on the view? You can put an INSTEAD OF trigger on the view, and put logic
> into the trigger to update the underlying base tables, as well as put an
> entry into an audit log.
No. I can put an INSTEAD OF trigger on that view. But it does not fire
when something in the underlying basetables changes or something is
beeing inserted/deleted.
Maybe I have to mention that the basetables a beeing updated by
replication and the view and its trigger are just for monitoring and
reporting.
Putting triggers on each basetable is not a good idea, because there are
many of them and just a fraction of their data is interesting for
processing.
Thanks for your reply
Michael