Sorry, newbie here...
BJHi,
"BJ" <lmelogra@.optonline.net> wrote in message
news:uHLB458uEHA.940@.TK2MSFTNGP14.phx.gbl...
> Sorry, newbie here...
> BJ
>|||Hi,
Before Reinstall:-
1. For safe take the backup of databases
2. Detach the databases (SP_detach_db)
3. Copy the MDF and LDF to a safe location
After install
1. Apply the same service pack
2. Restore the master database
3. Restore the other database one by one or use SP_ATTCH_DB to attach the
databases
Thanks
Hari
SQL Server MVP
"BJ" <lmelogra@.optonline.net> wrote in message
news:uHLB458uEHA.940@.TK2MSFTNGP14.phx.gbl...
> Sorry, newbie here...
> BJ
>|||ok, so if I installed MS CRM which creates a few databases with the newly
created MASTER database before I did any kind of restore it fails? I did
use this which I think will work?:
Did a SQL database backup to D:\<database_1>
then loaded new SQL server.
Ran the RESTORE database option in SQL pointing it to the existing databases
on my hard drive. They show up in SQL.
But, one database we did NOT run the backup on for Goldmine. Any way to
restore that now or are we SOL?
THANKS!
BJ
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:uchspB9uEHA.3200@.TK2MSFTNGP14.phx.gbl...
> Hi,
> Before Reinstall:-
> 1. For safe take the backup of databases
> 2. Detach the databases (SP_detach_db)
> 3. Copy the MDF and LDF to a safe location
> After install
> 1. Apply the same service pack
> 2. Restore the master database
> 3. Restore the other database one by one or use SP_ATTCH_DB to attach the
> databases
>
> --
> Thanks
> Hari
> SQL Server MVP
> "BJ" <lmelogra@.optonline.net> wrote in message
> news:uHLB458uEHA.940@.TK2MSFTNGP14.phx.gbl...
> > Sorry, newbie here...
> >
> > BJ
> >
> >
>|||Hi,
Check if the MDF and LDF file exists for that database,if exits then you try
using sp_attach_db (see books onlinr) to attach the database back to sql
server.
Thanks
Hari
SQL Server MVP
"BJ" <lmelogra@.optonline.net> wrote in message
news:u5kmQG9uEHA.4020@.TK2MSFTNGP10.phx.gbl...
> ok, so if I installed MS CRM which creates a few databases with the newly
> created MASTER database before I did any kind of restore it fails? I did
> use this which I think will work?:
> Did a SQL database backup to D:\<database_1>
> then loaded new SQL server.
> Ran the RESTORE database option in SQL pointing it to the existing
> databases
> on my hard drive. They show up in SQL.
> But, one database we did NOT run the backup on for Goldmine. Any way to
> restore that now or are we SOL?
> THANKS!
> BJ
>
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:uchspB9uEHA.3200@.TK2MSFTNGP14.phx.gbl...
>> Hi,
>> Before Reinstall:-
>> 1. For safe take the backup of databases
>> 2. Detach the databases (SP_detach_db)
>> 3. Copy the MDF and LDF to a safe location
>> After install
>> 1. Apply the same service pack
>> 2. Restore the master database
>> 3. Restore the other database one by one or use SP_ATTCH_DB to attach the
>> databases
>>
>> --
>> Thanks
>> Hari
>> SQL Server MVP
>> "BJ" <lmelogra@.optonline.net> wrote in message
>> news:uHLB458uEHA.940@.TK2MSFTNGP14.phx.gbl...
>> > Sorry, newbie here...
>> >
>> > BJ
>> >
>> >
>>
>|||Yes they are there!! we copied this folder to another drive! Thank you!
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:%23OvJo79uEHA.1308@.TK2MSFTNGP09.phx.gbl...
> Hi,
> Check if the MDF and LDF file exists for that database,if exits then you
try
> using sp_attach_db (see books onlinr) to attach the database back to sql
> server.
>
> --
> Thanks
> Hari
> SQL Server MVP
> "BJ" <lmelogra@.optonline.net> wrote in message
> news:u5kmQG9uEHA.4020@.TK2MSFTNGP10.phx.gbl...
> > ok, so if I installed MS CRM which creates a few databases with the
newly
> > created MASTER database before I did any kind of restore it fails? I
did
> > use this which I think will work?:
> >
> > Did a SQL database backup to D:\<database_1>
> >
> > then loaded new SQL server.
> >
> > Ran the RESTORE database option in SQL pointing it to the existing
> > databases
> > on my hard drive. They show up in SQL.
> >
> > But, one database we did NOT run the backup on for Goldmine. Any way to
> > restore that now or are we SOL?
> >
> > THANKS!
> >
> > BJ
> >
> >
> > "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> > news:uchspB9uEHA.3200@.TK2MSFTNGP14.phx.gbl...
> >> Hi,
> >>
> >> Before Reinstall:-
> >>
> >> 1. For safe take the backup of databases
> >> 2. Detach the databases (SP_detach_db)
> >> 3. Copy the MDF and LDF to a safe location
> >>
> >> After install
> >>
> >> 1. Apply the same service pack
> >> 2. Restore the master database
> >> 3. Restore the other database one by one or use SP_ATTCH_DB to attach
the
> >> databases
> >>
> >>
> >> --
> >> Thanks
> >> Hari
> >> SQL Server MVP
> >>
> >> "BJ" <lmelogra@.optonline.net> wrote in message
> >> news:uHLB458uEHA.940@.TK2MSFTNGP14.phx.gbl...
> >> > Sorry, newbie here...
> >> >
> >> > BJ
> >> >
> >> >
> >>
> >>
> >
> >
>
Showing posts with label online. Show all posts
Showing posts with label online. Show all posts
Friday, March 30, 2012
Wednesday, March 28, 2012
IF EXISTS
In Books Online it says "The select list of a subquery introduced by EXISTS
almost always consists of an asterisk (*). There is no reason to list column
names because you are simply testing for the existence of rows that meet the
conditions specified in the subquery."
If I've got two tables in my query should I still do SELECT *?
E.g...
IF EXISTS (SELECT inserted.CustomerSerialNo FROM inserted, deleted WHERE
inserted.CustomerSerialNo = deleted.CustomerSerialNo AND inserted.Location
<> deleted.Location)
IF EXISTS (SELECT * FROM inserted, deleted WHERE inserted.CustomerSerialNo =
deleted.CustomerSerialNo AND inserted.Location <> deleted.Location)
Does it make a difference?
Thanks,
Chris>If I've got two tables in my query should I still do SELECT *?
It doesnt matters.
Personally I prefer SELECT 1
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Chris" <cw@.community.nospam> wrote in message
news:OkyubuyRFHA.3156@.TK2MSFTNGP15.phx.gbl...
> In Books Online it says "The select list of a subquery introduced by
> EXISTS almost always consists of an asterisk (*). There is no reason to
> list column names because you are simply testing for the existence of rows
> that meet the conditions specified in the subquery."
> If I've got two tables in my query should I still do SELECT *?
> E.g...
> IF EXISTS (SELECT inserted.CustomerSerialNo FROM inserted, deleted WHERE
> inserted.CustomerSerialNo = deleted.CustomerSerialNo AND inserted.Location
> <> deleted.Location)
> IF EXISTS (SELECT * FROM inserted, deleted WHERE inserted.CustomerSerialNo
> = deleted.CustomerSerialNo AND inserted.Location <> deleted.Location)
> Does it make a difference?
> Thanks,
> Chris
>|||No difference. It's the presence or absence of a row that matters, not
what columns there are in that row.
I know some people say that the idea of specifying * is that it lets
the optimizer choose the best plan based on ANY column(s) in the
table/indexes. However, I don't recall ever seeing an example in SQL
Server where the SELECT list in an EXISTS statement makes any
performance difference. AFAIK the optimizer always ignores the columns
you specify. Whatever the case, in logical terms it makes no difference
to the result of the query.
David Portas
SQL Server MVP
--|||Thanks,
Chris
"Roji. P. Thomas" <thomasroji@.gmail.com> wrote in message
news:ONYyWxyRFHA.3716@.TK2MSFTNGP14.phx.gbl...
> It doesnt matters.
> Personally I prefer SELECT 1
>
> --
> Roji. P. Thomas
> Net Asset Management
> https://www.netassetmanagement.com
>
> "Chris" <cw@.community.nospam> wrote in message
> news:OkyubuyRFHA.3156@.TK2MSFTNGP15.phx.gbl...
>|||Chris,
I've heard a rumour that select * is a tiny bit faster to execute.
I don't like your old style join syntax though, this may be easier to read:
if exists
( select *
from inserted i
inner join deleted d
on d.CustomerSerialNo = i.CustomerSerialNo
and d.Location <> i.Location
)
Regards
AJ
"Chris" <cw@.community.nospam> wrote in message news:OkyubuyRFHA.3156@.TK2MSFTNGP15.phx.gbl..
.
> In Books Online it says "The select list of a subquery introduced by EXIST
S almost always consists of an asterisk (*).
> There is no reason to list column names because you are simply testing for
the existence of rows that meet the
> conditions specified in the subquery."
> If I've got two tables in my query should I still do SELECT *?
> E.g...
> IF EXISTS (SELECT inserted.CustomerSerialNo FROM inserted, deleted WHERE i
nserted.CustomerSerialNo =
> deleted.CustomerSerialNo AND inserted.Location <> deleted.Location)
> IF EXISTS (SELECT * FROM inserted, deleted WHERE inserted.CustomerSerialNo
= deleted.CustomerSerialNo AND
> inserted.Location <> deleted.Location)
> Does it make a difference?
> Thanks,
> Chris
>|||Agree. And, I'm pretty certain that we've been told from MS that this is ind
eed the fact (i.e., SQL
Server isn't stupid enough to actually materialize any column data is simila
r - column list in
EXISTS is irrelevant).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1114169328.986401.306840@.z14g2000cwz.googlegroups.com...
> No difference. It's the presence or absence of a row that matters, not
> what columns there are in that row.
> I know some people say that the idea of specifying * is that it lets
> the optimizer choose the best plan based on ANY column(s) in the
> table/indexes. However, I don't recall ever seeing an example in SQL
> Server where the SELECT list in an EXISTS statement makes any
> performance difference. AFAIK the optimizer always ignores the columns
> you specify. Whatever the case, in logical terms it makes no difference
> to the result of the query.
> --
> David Portas
> SQL Server MVP
> --
>|||Great, thanks for the information.
Chris
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23frGCO0RFHA.3704@.TK2MSFTNGP12.phx.gbl...
> Agree. And, I'm pretty certain that we've been told from MS that this is
> indeed the fact (i.e., SQL Server isn't stupid enough to actually
> materialize any column data is similar - column list in EXISTS is
> irrelevant).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:1114169328.986401.306840@.z14g2000cwz.googlegroups.com...
>|||As David mentioned, we have never seen an example posted that
demonstrated a difference in performance. So I would forget about the
rumour...
Gert-Jan
Andrew John wrote:
> Chris,
> I've heard a rumour that select * is a tiny bit faster to execute.
> I don't like your old style join syntax though, this may be easier to read
:
> if exists
> ( select *
> from inserted i
> inner join deleted d
> on d.CustomerSerialNo = i.CustomerSerialNo
> and d.Location <> i.Location
> )
> Regards
> AJ
> "Chris" <cw@.community.nospam> wrote in message news:OkyubuyRFHA.3156@.TK2MS
FTNGP15.phx.gbl...|||Gert-Jan,
I don't know if I'd call it a performance difference, but if there
are identifiers or literals in the select list of EXISTS, the query
processor must check that they are valid, a step not required
with *. So in at least one sense, SELECT * may be less work
than SELECT 1 or SELECT <column>.
The two queries below produce errors, and I doubt the code that
leads to these errors is executed at all with EXISTS (SELECT * ...).
But I'm sure this code *is* executed with SELECT 1 or SELECT i.
With everything else the query processor has to do, though,
I doubt this difference is measurable, and I believe the
query plan is always the same. Once a plan is cached, also,
there may not be a difference.
create table T (
i int
)
go
select i
from T
where exists (
select X from T
)
go
select i
from T
where exists (
select 9999999999999999999999999999999999999999
from T
)
go
Steve Kass
Drew University
I assume there
Gert-Jan Strik wrote:
>As David mentioned, we have never seen an example posted that
>demonstrated a difference in performance. So I would forget about the
>rumour...
>Gert-Jan
>
>Andrew John wrote:
>|||Right. No example that demonstrates a (measureable) performance
difference!
The code you posted just demonstrates that the query parser does not
allow illegal or inproperly formed queries.
Gert-Jan
Steve Kass wrote:
> Gert-Jan,
> I don't know if I'd call it a performance difference, but if there
> are identifiers or literals in the select list of EXISTS, the query
> processor must check that they are valid, a step not required
> with *. So in at least one sense, SELECT * may be less work
> than SELECT 1 or SELECT <column>.
> The two queries below produce errors, and I doubt the code that
> leads to these errors is executed at all with EXISTS (SELECT * ...).
> But I'm sure this code *is* executed with SELECT 1 or SELECT i.
> With everything else the query processor has to do, though,
> I doubt this difference is measurable, and I believe the
> query plan is always the same. Once a plan is cached, also,
> there may not be a difference.
> create table T (
> i int
> )
> go
> select i
> from T
> where exists (
> select X from T
> )
> go
> select i
> from T
> where exists (
> select 9999999999999999999999999999999999999999
> from T
> )
> go
> Steve Kass
> Drew University
> I assume there
> Gert-Jan Strik wrote:
>
almost always consists of an asterisk (*). There is no reason to list column
names because you are simply testing for the existence of rows that meet the
conditions specified in the subquery."
If I've got two tables in my query should I still do SELECT *?
E.g...
IF EXISTS (SELECT inserted.CustomerSerialNo FROM inserted, deleted WHERE
inserted.CustomerSerialNo = deleted.CustomerSerialNo AND inserted.Location
<> deleted.Location)
IF EXISTS (SELECT * FROM inserted, deleted WHERE inserted.CustomerSerialNo =
deleted.CustomerSerialNo AND inserted.Location <> deleted.Location)
Does it make a difference?
Thanks,
Chris>If I've got two tables in my query should I still do SELECT *?
It doesnt matters.
Personally I prefer SELECT 1
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Chris" <cw@.community.nospam> wrote in message
news:OkyubuyRFHA.3156@.TK2MSFTNGP15.phx.gbl...
> In Books Online it says "The select list of a subquery introduced by
> EXISTS almost always consists of an asterisk (*). There is no reason to
> list column names because you are simply testing for the existence of rows
> that meet the conditions specified in the subquery."
> If I've got two tables in my query should I still do SELECT *?
> E.g...
> IF EXISTS (SELECT inserted.CustomerSerialNo FROM inserted, deleted WHERE
> inserted.CustomerSerialNo = deleted.CustomerSerialNo AND inserted.Location
> <> deleted.Location)
> IF EXISTS (SELECT * FROM inserted, deleted WHERE inserted.CustomerSerialNo
> = deleted.CustomerSerialNo AND inserted.Location <> deleted.Location)
> Does it make a difference?
> Thanks,
> Chris
>|||No difference. It's the presence or absence of a row that matters, not
what columns there are in that row.
I know some people say that the idea of specifying * is that it lets
the optimizer choose the best plan based on ANY column(s) in the
table/indexes. However, I don't recall ever seeing an example in SQL
Server where the SELECT list in an EXISTS statement makes any
performance difference. AFAIK the optimizer always ignores the columns
you specify. Whatever the case, in logical terms it makes no difference
to the result of the query.
David Portas
SQL Server MVP
--|||Thanks,
Chris
"Roji. P. Thomas" <thomasroji@.gmail.com> wrote in message
news:ONYyWxyRFHA.3716@.TK2MSFTNGP14.phx.gbl...
> It doesnt matters.
> Personally I prefer SELECT 1
>
> --
> Roji. P. Thomas
> Net Asset Management
> https://www.netassetmanagement.com
>
> "Chris" <cw@.community.nospam> wrote in message
> news:OkyubuyRFHA.3156@.TK2MSFTNGP15.phx.gbl...
>|||Chris,
I've heard a rumour that select * is a tiny bit faster to execute.
I don't like your old style join syntax though, this may be easier to read:
if exists
( select *
from inserted i
inner join deleted d
on d.CustomerSerialNo = i.CustomerSerialNo
and d.Location <> i.Location
)
Regards
AJ
"Chris" <cw@.community.nospam> wrote in message news:OkyubuyRFHA.3156@.TK2MSFTNGP15.phx.gbl..
.
> In Books Online it says "The select list of a subquery introduced by EXIST
S almost always consists of an asterisk (*).
> There is no reason to list column names because you are simply testing for
the existence of rows that meet the
> conditions specified in the subquery."
> If I've got two tables in my query should I still do SELECT *?
> E.g...
> IF EXISTS (SELECT inserted.CustomerSerialNo FROM inserted, deleted WHERE i
nserted.CustomerSerialNo =
> deleted.CustomerSerialNo AND inserted.Location <> deleted.Location)
> IF EXISTS (SELECT * FROM inserted, deleted WHERE inserted.CustomerSerialNo
= deleted.CustomerSerialNo AND
> inserted.Location <> deleted.Location)
> Does it make a difference?
> Thanks,
> Chris
>|||Agree. And, I'm pretty certain that we've been told from MS that this is ind
eed the fact (i.e., SQL
Server isn't stupid enough to actually materialize any column data is simila
r - column list in
EXISTS is irrelevant).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1114169328.986401.306840@.z14g2000cwz.googlegroups.com...
> No difference. It's the presence or absence of a row that matters, not
> what columns there are in that row.
> I know some people say that the idea of specifying * is that it lets
> the optimizer choose the best plan based on ANY column(s) in the
> table/indexes. However, I don't recall ever seeing an example in SQL
> Server where the SELECT list in an EXISTS statement makes any
> performance difference. AFAIK the optimizer always ignores the columns
> you specify. Whatever the case, in logical terms it makes no difference
> to the result of the query.
> --
> David Portas
> SQL Server MVP
> --
>|||Great, thanks for the information.
Chris
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23frGCO0RFHA.3704@.TK2MSFTNGP12.phx.gbl...
> Agree. And, I'm pretty certain that we've been told from MS that this is
> indeed the fact (i.e., SQL Server isn't stupid enough to actually
> materialize any column data is similar - column list in EXISTS is
> irrelevant).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:1114169328.986401.306840@.z14g2000cwz.googlegroups.com...
>|||As David mentioned, we have never seen an example posted that
demonstrated a difference in performance. So I would forget about the
rumour...
Gert-Jan
Andrew John wrote:
> Chris,
> I've heard a rumour that select * is a tiny bit faster to execute.
> I don't like your old style join syntax though, this may be easier to read
:
> if exists
> ( select *
> from inserted i
> inner join deleted d
> on d.CustomerSerialNo = i.CustomerSerialNo
> and d.Location <> i.Location
> )
> Regards
> AJ
> "Chris" <cw@.community.nospam> wrote in message news:OkyubuyRFHA.3156@.TK2MS
FTNGP15.phx.gbl...|||Gert-Jan,
I don't know if I'd call it a performance difference, but if there
are identifiers or literals in the select list of EXISTS, the query
processor must check that they are valid, a step not required
with *. So in at least one sense, SELECT * may be less work
than SELECT 1 or SELECT <column>.
The two queries below produce errors, and I doubt the code that
leads to these errors is executed at all with EXISTS (SELECT * ...).
But I'm sure this code *is* executed with SELECT 1 or SELECT i.
With everything else the query processor has to do, though,
I doubt this difference is measurable, and I believe the
query plan is always the same. Once a plan is cached, also,
there may not be a difference.
create table T (
i int
)
go
select i
from T
where exists (
select X from T
)
go
select i
from T
where exists (
select 9999999999999999999999999999999999999999
from T
)
go
Steve Kass
Drew University
I assume there
Gert-Jan Strik wrote:
>As David mentioned, we have never seen an example posted that
>demonstrated a difference in performance. So I would forget about the
>rumour...
>Gert-Jan
>
>Andrew John wrote:
>|||Right. No example that demonstrates a (measureable) performance
difference!
The code you posted just demonstrates that the query parser does not
allow illegal or inproperly formed queries.
Gert-Jan
Steve Kass wrote:
> Gert-Jan,
> I don't know if I'd call it a performance difference, but if there
> are identifiers or literals in the select list of EXISTS, the query
> processor must check that they are valid, a step not required
> with *. So in at least one sense, SELECT * may be less work
> than SELECT 1 or SELECT <column>.
> The two queries below produce errors, and I doubt the code that
> leads to these errors is executed at all with EXISTS (SELECT * ...).
> But I'm sure this code *is* executed with SELECT 1 or SELECT i.
> With everything else the query processor has to do, though,
> I doubt this difference is measurable, and I believe the
> query plan is always the same. Once a plan is cached, also,
> there may not be a difference.
> create table T (
> i int
> )
> go
> select i
> from T
> where exists (
> select X from T
> )
> go
> select i
> from T
> where exists (
> select 9999999999999999999999999999999999999999
> from T
> )
> go
> Steve Kass
> Drew University
> I assume there
> Gert-Jan Strik wrote:
>
Wednesday, March 7, 2012
Identity Column Property SQL SERVER
hi
i export tables from Local to Online Server But some tables have a column with Identity=True
But after export tables that Property is not True How I can change it True With Query Analyzer????/
Would it be possible at all to do a 'create sql scripts' on the database, and use the scripts on the live server to recreate your tables? This should fix your problem.
Subscribe to:
Posts (Atom)