Creating a VIEW based on other VIEW.

Hi everyone,
Let's say I have a VIEW that is created by some complex joins - This
VIEW is used by many of the application's function. Let's call this
"VIEW_1".
"VIEW_1" select queries are already created using "with (nolock)".
Let's say a new system requirement comes in - I notice that it may be
better if i create another view "VIEW_2" based on "VIEW_1".
eg:
create view VIEW_2
as
(
select blah blah from VIEW_1 where blah='lala' ....etc etc etc...
)
For such cases, what are the performance issues I should watch out
for? WIll there be any performance issues? Should I be doing this in
the first place?
In SQL 2005 mgmt studio, how do I view the execution paths and
timings?
Please advise.
Thanks.
If your query completes in some reasonable time and does not take too much
resources that may be needed for other users then it is not a problem.
You can take a look at the performance and resources used by your query by
using all or some of these:
set statistics io on
set statistics time on
set statistics profile on
set statistics xml on
You can also display the graphic execution plan in SSMS by using the buttons
'Include Actual Execution Plan' or 'Display Estimated Execution Plan'. Text
and XML execution plans are also available.
You can also use some DMVs or SQL Server predefined reports (like
Performance - Top Queries by Total CPU Time ) to compare your query with some
other queries running on the instance.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"stevong" wrote:

> Hi everyone,
> Let's say I have a VIEW that is created by some complex joins - This
> VIEW is used by many of the application's function. Let's call this
> "VIEW_1".
> "VIEW_1" select queries are already created using "with (nolock)".
> Let's say a new system requirement comes in - I notice that it may be
> better if i create another view "VIEW_2" based on "VIEW_1".
> eg:
> create view VIEW_2
> as
> (
> select blah blah from VIEW_1 where blah='lala' ....etc etc etc...
> )
> For such cases, what are the performance issues I should watch out
> for? WIll there be any performance issues? Should I be doing this in
> the first place?
> In SQL 2005 mgmt studio, how do I view the execution paths and
> timings?
> Please advise.
> Thanks.
>

Creating a VIEW based on other VIEW.

Hi everyone,
Let's say I have a VIEW that is created by some complex joins - This
VIEW is used by many of the application's function. Let's call this
"VIEW_1".
"VIEW_1" select queries are already created using "with (nolock)".
Let's say a new system requirement comes in - I notice that it may be
better if i create another view "VIEW_2" based on "VIEW_1".
eg:
create view VIEW_2
as
(
select blah blah from VIEW_1 where blah='lala' ....etc etc etc...
)
For such cases, what are the performance issues I should watch out
for? WIll there be any performance issues? Should I be doing this in
the first place?
In SQL 2005 mgmt studio, how do I view the execution paths and
timings?
Please advise.
Thanks.If your query completes in some reasonable time and does not take too much
resources that may be needed for other users then it is not a problem.
You can take a look at the performance and resources used by your query by
using all or some of these:
set statistics io on
set statistics time on
set statistics profile on
set statistics xml on
You can also display the graphic execution plan in SSMS by using the buttons
'Include Actual Execution Plan' or 'Display Estimated Execution Plan'. Text
and XML execution plans are also available.
You can also use some DMVs or SQL Server predefined reports (like
Performance - Top Queries by Total CPU Time ) to compare your query with som
e
other queries running on the instance.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"stevong" wrote:

> Hi everyone,
> Let's say I have a VIEW that is created by some complex joins - This
> VIEW is used by many of the application's function. Let's call this
> "VIEW_1".
> "VIEW_1" select queries are already created using "with (nolock)".
> Let's say a new system requirement comes in - I notice that it may be
> better if i create another view "VIEW_2" based on "VIEW_1".
> eg:
> create view VIEW_2
> as
> (
> select blah blah from VIEW_1 where blah='lala' ....etc etc etc...
> )
> For such cases, what are the performance issues I should watch out
> for? WIll there be any performance issues? Should I be doing this in
> the first place?
> In SQL 2005 mgmt studio, how do I view the execution paths and
> timings?
> Please advise.
> Thanks.
>sql

Creating a VIEW based on other VIEW.

Hi everyone,
Let's say I have a VIEW that is created by some complex joins - This
VIEW is used by many of the application's function. Let's call this
"VIEW_1".
"VIEW_1" select queries are already created using "with (nolock)".
Let's say a new system requirement comes in - I notice that it may be
better if i create another view "VIEW_2" based on "VIEW_1".
eg:
create view VIEW_2
as
(
select blah blah from VIEW_1 where blah='lala' ....etc etc etc...
)
For such cases, what are the performance issues I should watch out
for? WIll there be any performance issues? Should I be doing this in
the first place?
In SQL 2005 mgmt studio, how do I view the execution paths and
timings?
Please advise.
Thanks.If your query completes in some reasonable time and does not take too much
resources that may be needed for other users then it is not a problem.
You can take a look at the performance and resources used by your query by
using all or some of these:
set statistics io on
set statistics time on
set statistics profile on
set statistics xml on
You can also display the graphic execution plan in SSMS by using the buttons
'Include Actual Execution Plan' or 'Display Estimated Execution Plan'. Text
and XML execution plans are also available.
You can also use some DMVs or SQL Server predefined reports (like
Performance - Top Queries by Total CPU Time ) to compare your query with some
other queries running on the instance.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"stevong" wrote:
> Hi everyone,
> Let's say I have a VIEW that is created by some complex joins - This
> VIEW is used by many of the application's function. Let's call this
> "VIEW_1".
> "VIEW_1" select queries are already created using "with (nolock)".
> Let's say a new system requirement comes in - I notice that it may be
> better if i create another view "VIEW_2" based on "VIEW_1".
> eg:
> create view VIEW_2
> as
> (
> select blah blah from VIEW_1 where blah='lala' ....etc etc etc...
> )
> For such cases, what are the performance issues I should watch out
> for? WIll there be any performance issues? Should I be doing this in
> the first place?
> In SQL 2005 mgmt studio, how do I view the execution paths and
> timings?
> Please advise.
> Thanks.
>

Creating a partitioned view based on results from select?

I have a situation which seems like it should have a fairly simple
answer... but I'm not sure how to do it.
I want to create a view that joins several remotely distributed tables.
Something like:
CREATE VIEW AllData
AS
SELECT * FROM Server1.SomeDB.dbo.DataTable
UNION ALL
SELECT * FROM Server2.SomeDB.dbo.DataTable
...
SELECT * FROM ServerN.SomeDB.dbo.DataTable
The problem is that we anticipate some downtime or discontinuity with
our server links. Will the view fail if just ONE server can't be
reached? I think it will... please correct me if I'm wrong.
So, to solve this we have another table which stores the server name
and a status field. The question is how do I take this list of
available servers and turn it into a valid view? Is this going to
require some form of dynamic SQL? I'm picturing creating a trigger so
that any time the status DB changes the view gets recreated.status table could be out of sync and you will still get error. Long story
short, there isn't anything builtin right now to allow you precheck for
linked server status other than sending a query to it.
-oj
<bryanp10@.hotmail.com> wrote in message
news:1113511868.193053.138020@.l41g2000cwc.googlegroups.com...
>I have a situation which seems like it should have a fairly simple
> answer... but I'm not sure how to do it.
> I want to create a view that joins several remotely distributed tables.
> Something like:
> CREATE VIEW AllData
> AS
> SELECT * FROM Server1.SomeDB.dbo.DataTable
> UNION ALL
> SELECT * FROM Server2.SomeDB.dbo.DataTable
> ...
> SELECT * FROM ServerN.SomeDB.dbo.DataTable
>
> The problem is that we anticipate some downtime or discontinuity with
> our server links. Will the view fail if just ONE server can't be
> reached? I think it will... please correct me if I'm wrong.
> So, to solve this we have another table which stores the server name
> and a status field. The question is how do I take this list of
> available servers and turn it into a valid view? Is this going to
> require some form of dynamic SQL? I'm picturing creating a trigger so
> that any time the status DB changes the view gets recreated.
>|||OK... not quite what I was asking, but thanks. :)
Basically, I'm useless in T-SQL and what I really need to know is how
to transform a table of server names into:
select * from server1
UNION ALL
select * from server2
...
etc.
Really this is just a basic T-SQL question. In ANY other programming
language I've ever seen, this would be simplicity itself. But for some
reason T-SQL just baffles me.
The other question was whether this absolutely requires dynamic SQL or
not. At this point I'm thinking I might just build the create view
procedure in my app where I have a real language to do it.