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 test copy of a database?

I was wondering if there is an easy way to "copy" an existing database,
complete with data, into a new one with a different name.
We have a complex SQL system that imports data from an external SQL source
(PervasiveSQL). We are in the process of upgrading that software to a much
newer version that has undergone major modifications, although I don't
_believe_ they impact the import process. I have already made a copy of the
PervasiveSQL database in the new format. What I would like to do now is copy
our SQLServer database, then run our importer against the two test databases.
Any advice?
Maury
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:D90FD511-6857-4573-94B6-60BEBE7ABB23@.microsoft.com...
>I was wondering if there is an easy way to "copy" an existing database,
> complete with data, into a new one with a different name.
>
The easiest way usually is one of the following:
Backup the source database and then restore it with a different name. This
can be done in a production environment with no downtime.
Or, stop SQL Server, make a copy of the files and then restart SQL Server.
Use sp_attach_db to attach the copied files with a different database name.

> We have a complex SQL system that imports data from an external SQL source
> (PervasiveSQL). We are in the process of upgrading that software to a much
> newer version that has undergone major modifications, although I don't
> _believe_ they impact the import process. I have already made a copy of
> the
> PervasiveSQL database in the new format. What I would like to do now is
> copy
> our SQLServer database, then run our importer against the two test
> databases.
> Any advice?
> Maury
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com
|||Hello,
You will have to BACKUP the database and Restore the database using a new
name.
1. Backup the database usuing BACKUP DATABASE command
2. Restore the database with new name specifying MOVE optiion.
take a look into BACKUP DATABASE and RESTORE DATABASE command with MOVE
option in Books online.
Thanks
Hari
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:D90FD511-6857-4573-94B6-60BEBE7ABB23@.microsoft.com...
>I was wondering if there is an easy way to "copy" an existing database,
> complete with data, into a new one with a different name.
> We have a complex SQL system that imports data from an external SQL source
> (PervasiveSQL). We are in the process of upgrading that software to a much
> newer version that has undergone major modifications, although I don't
> _believe_ they impact the import process. I have already made a copy of
> the
> PervasiveSQL database in the new format. What I would like to do now is
> copy
> our SQLServer database, then run our importer against the two test
> databases.
> Any advice?
> Maury
|||Maury Markowitz,
Take a full back of the db and restore it using a new database name and the
"with move" option. See "restore database" in BOL for more info.
AMB
"Maury Markowitz" wrote:

> I was wondering if there is an easy way to "copy" an existing database,
> complete with data, into a new one with a different name.
> We have a complex SQL system that imports data from an external SQL source
> (PervasiveSQL). We are in the process of upgrading that software to a much
> newer version that has undergone major modifications, although I don't
> _believe_ they impact the import process. I have already made a copy of the
> PervasiveSQL database in the new format. What I would like to do now is copy
> our SQLServer database, then run our importer against the two test databases.
> Any advice?
> Maury
|||Backup and restore is the easiest method - see "Copying a database using
BACKUP and RESTORE" in BOL.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
|||"Alejandro Mesa" wrote:

> Take a full back of the db and restore it using a new database name and the
> "with move" option. See "restore database" in BOL for more info.
Thanks! I'll start working on this now.
Maury
|||> Or, stop SQL Server, make a copy of the files and then restart SQL Server.
Personally I think it's safer to detach using sp_detach_db. Stopping SQL
Server will not necessarily correctly initiate the files for attaching to
another server (at least I've seen plenty of reports of such).
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
|||"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OCCGX8BXHHA.2212@.TK2MSFTNGP02.phx.gbl...
> Personally I think it's safer to detach using sp_detach_db. Stopping SQL
> Server will not necessarily correctly initiate the files for attaching to
> another server (at least I've seen plenty of reports of such).
You know, I've heard that it's a "bad idea".
But have actually never heard of it not working, as long as the db was
correctly shut down.
But yeah, if the attach doesn't work, I'd go back to the original server and
try that.

> --
> Aaron Bertrand
> SQL Server MVP
> http://www.sqlblog.com/
> http://www.aspfaq.com/5006
>
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com
|||> But yeah, if the attach doesn't work, I'd go back to the original server
> and try that.
Two more reasons to use an explicit detach:
(a) you only have to take one database offline, instead of ALL databases.
(b) if you in a clustered environment, shutting down SQL Server on the
active node will only initiate a failover, and won't free up the files for
copy because now they are active on the other node.
sql

Creating a test copy of a database?

I was wondering if there is an easy way to "copy" an existing database,
complete with data, into a new one with a different name.
We have a complex SQL system that imports data from an external SQL source
(PervasiveSQL). We are in the process of upgrading that software to a much
newer version that has undergone major modifications, although I don't
_believe_ they impact the import process. I have already made a copy of the
PervasiveSQL database in the new format. What I would like to do now is copy
our SQLServer database, then run our importer against the two test databases
.
Any advice?
Maury"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:D90FD511-6857-4573-94B6-60BEBE7ABB23@.microsoft.com...
>I was wondering if there is an easy way to "copy" an existing database,
> complete with data, into a new one with a different name.
>
The easiest way usually is one of the following:
Backup the source database and then restore it with a different name. This
can be done in a production environment with no downtime.
Or, stop SQL Server, make a copy of the files and then restart SQL Server.
Use sp_attach_db to attach the copied files with a different database name.

> We have a complex SQL system that imports data from an external SQL source
> (PervasiveSQL). We are in the process of upgrading that software to a much
> newer version that has undergone major modifications, although I don't
> _believe_ they impact the import process. I have already made a copy of
> the
> PervasiveSQL database in the new format. What I would like to do now is
> copy
> our SQLServer database, then run our importer against the two test
> databases.
> Any advice?
> Maury
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com|||Hello,
You will have to BACKUP the database and Restore the database using a new
name.
1. Backup the database usuing BACKUP DATABASE command
2. Restore the database with new name specifying MOVE optiion.
take a look into BACKUP DATABASE and RESTORE DATABASE command with MOVE
option in Books online.
Thanks
Hari
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:D90FD511-6857-4573-94B6-60BEBE7ABB23@.microsoft.com...
>I was wondering if there is an easy way to "copy" an existing database,
> complete with data, into a new one with a different name.
> We have a complex SQL system that imports data from an external SQL source
> (PervasiveSQL). We are in the process of upgrading that software to a much
> newer version that has undergone major modifications, although I don't
> _believe_ they impact the import process. I have already made a copy of
> the
> PervasiveSQL database in the new format. What I would like to do now is
> copy
> our SQLServer database, then run our importer against the two test
> databases.
> Any advice?
> Maury|||Maury Markowitz,
Take a full back of the db and restore it using a new database name and the
"with move" option. See "restore database" in BOL for more info.
AMB
"Maury Markowitz" wrote:

> I was wondering if there is an easy way to "copy" an existing database,
> complete with data, into a new one with a different name.
> We have a complex SQL system that imports data from an external SQL source
> (PervasiveSQL). We are in the process of upgrading that software to a much
> newer version that has undergone major modifications, although I don't
> _believe_ they impact the import process. I have already made a copy of th
e
> PervasiveSQL database in the new format. What I would like to do now is co
py
> our SQLServer database, then run our importer against the two test databas
es.
> Any advice?
> Maury|||Backup and restore is the easiest method - see "Copying a database using
BACKUP and RESTORE" in BOL.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||"Alejandro Mesa" wrote:

> Take a full back of the db and restore it using a new database name and th
e
> "with move" option. See "restore database" in BOL for more info.
Thanks! I'll start working on this now.
Maury|||> Or, stop SQL Server, make a copy of the files and then restart SQL Server.
Personally I think it's safer to detach using sp_detach_db. Stopping SQL
Server will not necessarily correctly initiate the files for attaching to
another server (at least I've seen plenty of reports of such).
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006|||"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in mess
age
news:OCCGX8BXHHA.2212@.TK2MSFTNGP02.phx.gbl...
> Personally I think it's safer to detach using sp_detach_db. Stopping SQL
> Server will not necessarily correctly initiate the files for attaching to
> another server (at least I've seen plenty of reports of such).
You know, I've heard that it's a "bad idea".
But have actually never heard of it not working, as long as the db was
correctly shut down.
But yeah, if the attach doesn't work, I'd go back to the original server and
try that.

> --
> Aaron Bertrand
> SQL Server MVP
> http://www.sqlblog.com/
> http://www.aspfaq.com/5006
>
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com|||> But yeah, if the attach doesn't work, I'd go back to the original server
> and try that.
Two more reasons to use an explicit detach:
(a) you only have to take one database offline, instead of ALL databases.
(b) if you in a clustered environment, shutting down SQL Server on the
active node will only initiate a failover, and won't free up the files for
copy because now they are active on the other node.

Creating a test copy of a database?

I was wondering if there is an easy way to "copy" an existing database,
complete with data, into a new one with a different name.
We have a complex SQL system that imports data from an external SQL source
(PervasiveSQL). We are in the process of upgrading that software to a much
newer version that has undergone major modifications, although I don't
_believe_ they impact the import process. I have already made a copy of the
PervasiveSQL database in the new format. What I would like to do now is copy
our SQLServer database, then run our importer against the two test databases.
Any advice?
Maury"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:D90FD511-6857-4573-94B6-60BEBE7ABB23@.microsoft.com...
>I was wondering if there is an easy way to "copy" an existing database,
> complete with data, into a new one with a different name.
>
The easiest way usually is one of the following:
Backup the source database and then restore it with a different name. This
can be done in a production environment with no downtime.
Or, stop SQL Server, make a copy of the files and then restart SQL Server.
Use sp_attach_db to attach the copied files with a different database name.
> We have a complex SQL system that imports data from an external SQL source
> (PervasiveSQL). We are in the process of upgrading that software to a much
> newer version that has undergone major modifications, although I don't
> _believe_ they impact the import process. I have already made a copy of
> the
> PervasiveSQL database in the new format. What I would like to do now is
> copy
> our SQLServer database, then run our importer against the two test
> databases.
> Any advice?
> Maury
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com|||Hello,
You will have to BACKUP the database and Restore the database using a new
name.
1. Backup the database usuing BACKUP DATABASE command
2. Restore the database with new name specifying MOVE optiion.
take a look into BACKUP DATABASE and RESTORE DATABASE command with MOVE
option in Books online.
Thanks
Hari
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:D90FD511-6857-4573-94B6-60BEBE7ABB23@.microsoft.com...
>I was wondering if there is an easy way to "copy" an existing database,
> complete with data, into a new one with a different name.
> We have a complex SQL system that imports data from an external SQL source
> (PervasiveSQL). We are in the process of upgrading that software to a much
> newer version that has undergone major modifications, although I don't
> _believe_ they impact the import process. I have already made a copy of
> the
> PervasiveSQL database in the new format. What I would like to do now is
> copy
> our SQLServer database, then run our importer against the two test
> databases.
> Any advice?
> Maury|||Maury Markowitz,
Take a full back of the db and restore it using a new database name and the
"with move" option. See "restore database" in BOL for more info.
AMB
"Maury Markowitz" wrote:
> I was wondering if there is an easy way to "copy" an existing database,
> complete with data, into a new one with a different name.
> We have a complex SQL system that imports data from an external SQL source
> (PervasiveSQL). We are in the process of upgrading that software to a much
> newer version that has undergone major modifications, although I don't
> _believe_ they impact the import process. I have already made a copy of the
> PervasiveSQL database in the new format. What I would like to do now is copy
> our SQLServer database, then run our importer against the two test databases.
> Any advice?
> Maury|||Backup and restore is the easiest method - see "Copying a database using
BACKUP and RESTORE" in BOL.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||"Alejandro Mesa" wrote:
> Take a full back of the db and restore it using a new database name and the
> "with move" option. See "restore database" in BOL for more info.
Thanks! I'll start working on this now.
Maury|||> Or, stop SQL Server, make a copy of the files and then restart SQL Server.
Personally I think it's safer to detach using sp_detach_db. Stopping SQL
Server will not necessarily correctly initiate the files for attaching to
another server (at least I've seen plenty of reports of such).
--
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006|||"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OCCGX8BXHHA.2212@.TK2MSFTNGP02.phx.gbl...
>> Or, stop SQL Server, make a copy of the files and then restart SQL
>> Server.
> Personally I think it's safer to detach using sp_detach_db. Stopping SQL
> Server will not necessarily correctly initiate the files for attaching to
> another server (at least I've seen plenty of reports of such).
You know, I've heard that it's a "bad idea".
But have actually never heard of it not working, as long as the db was
correctly shut down.
But yeah, if the attach doesn't work, I'd go back to the original server and
try that.
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.sqlblog.com/
> http://www.aspfaq.com/5006
>
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com|||> But yeah, if the attach doesn't work, I'd go back to the original server
> and try that.
Two more reasons to use an explicit detach:
(a) you only have to take one database offline, instead of ALL databases.
(b) if you in a clustered environment, shutting down SQL Server on the
active node will only initiate a failover, and won't free up the files for
copy because now they are active on the other node.

Creating a comma delimited list

Hi,
I have a complex query where each row in the final dataset is a product.
However each product has a number of authors associated with it. What I
would like to do is have a query/subroutine join the authors to the product,
as a string:

ProductID Title Authors
1 The Sacred and the Profane John Rieggle, George Alexi
2 Dancingin the Dark DanBrown, Peter Kay, Paul
Dwebinski
Products Table
==============
ProductID
Title
Authors Table
=============
AuthorID
Name
Product Authors Table
=====================
AuthorID
ProductID
Is this at all possible?
Thanks
jr.
It is possible with the use of a UDF. However, I highly suggestthat you do this work on the front end. Preparing the data foroutput is not something the database engine should be doing, and theUDF method is fairly intensive as it results in additional queries.
Here's a link showing the UDF method:Joining Strings into Delimiter Separated Lists.

.
|||Terri,
Thanks for your reply. I understand that this is a UI rendering issue, and I have been toying with how best to proceed. Currently my paging routine returns one resultset, were each row represents a product. To return the authors, I would need to return a second resultset, and join them at the middle tier:
Pseudo steps:
1. Do complex paging query
2. Based on items in paging query, join them to the authors table and return that resultset too.
This would solve the problem, but sends more data across the wire which I was thinknig could be avoided, even though I know that the DB shouldnt be doing ui rendering steps. Other than "best practice" I see no other reason to put this code in the ui, since it is just a dumb string without much meaning when rendered in a listing. As you mention though, this maybe putting too much stress on the DB when using a UDF...I am relatively new to this so forgive my naivete...
Thanks Terri,
jr.|||

This might not be very efficient if you have a large sum of data, but here is how to do it.

create table #product (
productID int,
productTitle varchar(50)
)
go

create table #author (
authorID int,
name varchar(100)
)
go

create table #productAuthor (
productID int,
authorID int
)
go

insert into #product values (1,'product1')
insert into #product values (2,'product2')

insert into #author values (1,'author1')
insert into #author values (2,'author2')
insert into #author values (3,'author3')
insert into #author values (4,'author4')

insert into #productAuthor values (1,1)
insert into #productAuthor values (1,2)
insert into #productAuthor values (2,3)
insert into #productAuthor values (2,4)

create table #nameList (
productid int,
names varchar(1000)
)
go

select distinct p.productid into #tmp
from #product p
inner join #productAuthor pa on p.productid = pa.productid

declare @.productid int,
@.names varchar(1000)

while exists (select null from #tmp)
begin
set @.names = ''
select top 1 @.productid = productid from #tmp

select @.names = CASE WHEN @.names = '' THEN name ELSE @.names + ',' + name END
from #product p
inner join #productAuthor pa on p.productid = pa.productid
inner join #author a on pa.authorid = a.authorid
where p.productid = @.productid

insert into #nameList values (@.productid, @.names)

delete from #tmp where productid = @.productid
end

select p.productID, productTitle, nl.names
from #product p
inner join #nameList nl ON p.productid = nl.productID

Let me know if you have any questions.

Nick