Comparing table structures.

Hi all,

Could anyone let me know if there are any Scripts available for comparing the schema differences between two SQL server databases?

Thanks

DBAnalyst

I once made a script for that, feel free to contact me if I should send it over.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||Please send it Jens, Thanks a lot.sqlsql

Comparing datetime fields results in slow query performance

Hi,
I am currently deduping a data warehouse containing around 3 million
records. Most of my dedupe scripts run in 3 or 4 minutes but the
scripts which compare datetime fields take up to 3 hours. I have a
non-clustered index on the date column. Can anybody offer any advice on
how I might improve query times please?
Thanks,
Charlie.which version of SQL Server? (2000 or 2005)
what the index plan says?
what type of comparison do you do?
what the index tunning wizard says against your query?
comparing date/time is slower then comparing integer.
<chairleg@.gmail.com> wrote in message
news:1137577249.061546.244550@.g43g2000cwa.googlegroups.com...
> Hi,
> I am currently deduping a data warehouse containing around 3 million
> records. Most of my dedupe scripts run in 3 or 4 minutes but the
> scripts which compare datetime fields take up to 3 hours. I have a
> non-clustered index on the date column. Can anybody offer any advice on
> how I might improve query times please?
> Thanks,
> Charlie.
>|||In addition to the previous post, my guess is you use the columns inside an
expression, so they are not Searchable ARGuments (SARGs) anymore. Try to
rewrite the queries to have the indexed datetime columns without an
expression in the Where clause. For example:
create table a
(a datetime)
create index aa on a(a)
insert into a values ('2006-01-16')
insert into a values ('2006-01-17')
go
select a from a
where datediff(dd,a,getdate()) < 2 -- this query should do an index scan
select a from a
where dateadd(dd,-1,convert(char(10),getdate(),112)) = a -- this query
should do an index seek
However, take care you get correct results - don't forget that you always
have time part in the datetime data. You should check if the Between
operator would be useful for you.
Dejan Sarka, SQL Server MVP
Mentor, www.SolidQualityLearning.com
Anything written in this message represents solely the point of view of the
sender.
This message does not imply endorsement from Solid Quality Learning, and it
does not represent the point of view of Solid Quality Learning or any other
person, company or institution mentioned in this message
<chairleg@.gmail.com> wrote in message
news:1137577249.061546.244550@.g43g2000cwa.googlegroups.com...
> Hi,
> I am currently deduping a data warehouse containing around 3 million
> records. Most of my dedupe scripts run in 3 or 4 minutes but the
> scripts which compare datetime fields take up to 3 hours. I have a
> non-clustered index on the date column. Can anybody offer any advice on
> how I might improve query times please?
> Thanks,
> Charlie.
>

Comparing datetime fields results in slow query performance

Hi,
I am currently deduping a data warehouse containing around 3 million
records. Most of my dedupe scripts run in 3 or 4 minutes but the
scripts which compare datetime fields take up to 3 hours. I have a
non-clustered index on the date column. Can anybody offer any advice on
how I might improve query times please?
Thanks,
Charlie.
which version of SQL Server? (2000 or 2005)
what the index plan says?
what type of comparison do you do?
what the index tunning wizard says against your query?
comparing date/time is slower then comparing integer.
<chairleg@.gmail.com> wrote in message
news:1137577249.061546.244550@.g43g2000cwa.googlegr oups.com...
> Hi,
> I am currently deduping a data warehouse containing around 3 million
> records. Most of my dedupe scripts run in 3 or 4 minutes but the
> scripts which compare datetime fields take up to 3 hours. I have a
> non-clustered index on the date column. Can anybody offer any advice on
> how I might improve query times please?
> Thanks,
> Charlie.
>
|||In addition to the previous post, my guess is you use the columns inside an
expression, so they are not Searchable ARGuments (SARGs) anymore. Try to
rewrite the queries to have the indexed datetime columns without an
expression in the Where clause. For example:
create table a
(a datetime)
create index aa on a(a)
insert into a values ('2006-01-16')
insert into a values ('2006-01-17')
go
select a from a
where datediff(dd,a,getdate()) < 2 -- this query should do an index scan
select a from a
where dateadd(dd,-1,convert(char(10),getdate(),112)) = a -- this query
should do an index seek
However, take care you get correct results - don't forget that you always
have time part in the datetime data. You should check if the Between
operator would be useful for you.
Dejan Sarka, SQL Server MVP
Mentor, www.SolidQualityLearning.com
Anything written in this message represents solely the point of view of the
sender.
This message does not imply endorsement from Solid Quality Learning, and it
does not represent the point of view of Solid Quality Learning or any other
person, company or institution mentioned in this message
<chairleg@.gmail.com> wrote in message
news:1137577249.061546.244550@.g43g2000cwa.googlegr oups.com...
> Hi,
> I am currently deduping a data warehouse containing around 3 million
> records. Most of my dedupe scripts run in 3 or 4 minutes but the
> scripts which compare datetime fields take up to 3 hours. I have a
> non-clustered index on the date column. Can anybody offer any advice on
> how I might improve query times please?
> Thanks,
> Charlie.
>
sqlsql

command scripts

I have created some stored procedures (SP) containing DBCC commands for
system maintenance and status checks. Does anybody know where to get sample
windows command scripts that will wrap all of my SPs, create logs, and also
where the windows scheduler could run at specific times. The idea is to
automate the maintenance instead of running individual SPs or DBCCs in Query
Analyzer.
Thanks...Schedule them using SQLServerAgent - Look up the service features in Books
online.
"mmc" <mmc@.discussions.microsoft.com> wrote in message
news:3AC72F6E-B0A3-4238-9021-DA964E4A8C06@.microsoft.com...
>I have created some stored procedures (SP) containing DBCC commands for
> system maintenance and status checks. Does anybody know where to get
> sample
> windows command scripts that will wrap all of my SPs, create logs, and
> also
> where the windows scheduler could run at specific times. The idea is to
> automate the maintenance instead of running individual SPs or DBCCs in
> Query
> Analyzer.
> Thanks...|||Create a job and use the SQL Server Agent for scheduling it.
Maybe you can check this link
http://www.sql-server-performance.c...erver_agent.asp
Hope this helps.|||Thanks..
"Omnibuzz" wrote:

> Create a job and use the SQL Server Agent for scheduling it.
> Maybe you can check this link
> http://www.sql-server-performance.c...erver_agent.asp
>
> Hope this helps.

Comitting statemnts

Hi!
I have a lot of sql scripts in *.sql files.
I'm looking for a solution how to execute them in "one statement" (for
example in Query Analizer) .
Thanks.Query analyzer does not have the ability to read .sql files and execute the SQL code inside those
files. OSQL.EXE does, check out the :r option. But I don't think that you can have GO in those .sql
files.
Another option is to use xp_cmdshell and from there execute OSQL.EXE with appropriate command line
parameters (including name of the .sql file).
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Dmitry Karneyev" <karneyev@.msn.com> wrote in message news:ONXUBrUnDHA.2652@.TK2MSFTNGP09.phx.gbl...
> Hi!
> I have a lot of sql scripts in *.sql files.
> I'm looking for a solution how to execute them in "one statement" (for
> example in Query Analizer) .
> Thanks.
>|||Thanks for advice!
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
ÓÏÏÂÝÉÌ/ÓÏÏÂÝÉÌÁ × ÎÏ×ÏÓÔÑÈ ÓÌÅÄÕÀÝÅÅ:
news:%23XrYmsUnDHA.2536@.tk2msftngp13.phx.gbl...
> Query analyzer does not have the ability to read .sql files and execute
the SQL code inside those
> files. OSQL.EXE does, check out the :r option. But I don't think that you
can have GO in those .sql
> files.
> Another option is to use xp_cmdshell and from there execute OSQL.EXE with
appropriate command line
> parameters (including name of the .sql file).
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "Dmitry Karneyev" <karneyev@.msn.com> wrote in message
news:ONXUBrUnDHA.2652@.TK2MSFTNGP09.phx.gbl...
> > Hi!
> >
> > I have a lot of sql scripts in *.sql files.
> > I'm looking for a solution how to execute them in "one statement" (for
> > example in Query Analizer) .
> >
> > Thanks.
> >
> >
>|||Hi Tibor ,
We can have "go" in .SQL files and execute this in OSQL / ISQL.
Hi Dmitry Karneyev ,
From Query analyzer u cant do that, instead create a .BAT file
inside the .BAT file , u can call the .SQL files using OSQL / ISQL
eg:
OSQL -Usa -Ppass -Sservername -ic:\a1.sql >out.txt
OSQL -Usa -Ppass -Sservername -ic:\a2.sql >>out.txt
OSQL -Usa -Ppass -Sservername -ic:\a3.sql >>out.txt
Thanks
Hari
MCDBA
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:#XrYmsUnDHA.2536@.tk2msftngp13.phx.gbl...
> Query analyzer does not have the ability to read .sql files and execute
the SQL code inside those
> files. OSQL.EXE does, check out the :r option. But I don't think that you
can have GO in those .sql
> files.
> Another option is to use xp_cmdshell and from there execute OSQL.EXE with
appropriate command line
> parameters (including name of the .sql file).
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "Dmitry Karneyev" <karneyev@.msn.com> wrote in message
news:ONXUBrUnDHA.2652@.TK2MSFTNGP09.phx.gbl...
> > Hi!
> >
> > I have a lot of sql scripts in *.sql files.
> > I'm looking for a solution how to execute them in "one statement" (for
> > example in Query Analizer) .
> >
> > Thanks.
> >
> >
>|||What Tibor refers to is using the :r options in a input file for osql. :r
allows you pass a list of files to run to osql, for example:
file all.sql
:ra1.sql
:ra2.sql
:ra3.sql
If you now pass the file all.sql as the input file to osql
(OSQL -Usa -Ppass -Sservername -ic:\all.sql), the files a1.sql, a2.sql and
a3.sql will be executed. The drawback of this is that you can't use GO
within the files a1.sql, a2.sql and a3.sql, which is a bummer.
If you use the input .sql files directly with osql you can indeed include GO
in the sql file.
:r is an undocumented option, so we have to take it as it comes I guess.
Jacco Schalkwijk
SQL Server MVP
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:ug%23PoAVnDHA.2304@.TK2MSFTNGP11.phx.gbl...
> Hi Tibor ,
> We can have "go" in .SQL files and execute this in OSQL / ISQL.
>
> Hi Dmitry Karneyev ,
> From Query analyzer u cant do that, instead create a .BAT file
> inside the .BAT file , u can call the .SQL files using OSQL / ISQL
> eg:
> OSQL -Usa -Ppass -Sservername -ic:\a1.sql >out.txt
> OSQL -Usa -Ppass -Sservername -ic:\a2.sql >>out.txt
> OSQL -Usa -Ppass -Sservername -ic:\a3.sql >>out.txt
> Thanks
> Hari
> MCDBA
>
>
>
> "Tibor Karaszi"
<tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
> wrote in message news:#XrYmsUnDHA.2536@.tk2msftngp13.phx.gbl...
> > Query analyzer does not have the ability to read .sql files and execute
> the SQL code inside those
> > files. OSQL.EXE does, check out the :r option. But I don't think that
you
> can have GO in those .sql
> > files.
> >
> > Another option is to use xp_cmdshell and from there execute OSQL.EXE
with
> appropriate command line
> > parameters (including name of the .sql file).
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > Archive at:
>
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
> >
> >
> > "Dmitry Karneyev" <karneyev@.msn.com> wrote in message
> news:ONXUBrUnDHA.2652@.TK2MSFTNGP09.phx.gbl...
> > > Hi!
> > >
> > > I have a lot of sql scripts in *.sql files.
> > > I'm looking for a solution how to execute them in "one statement" (for
> > > example in Query Analizer) .
> > >
> > > Thanks.
> > >
> > >
> >
> >
>|||> We can have "go" in .SQL files and execute this in OSQL / ISQL.
I never said we can't. I was referring to the :r option of OSQL, See Jacco's explanation (thanks
Jacco!).
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Hari" <hari_prasad_k@.hotmail.com> wrote in message news:ug%23PoAVnDHA.2304@.TK2MSFTNGP11.phx.gbl...
> Hi Tibor ,
> We can have "go" in .SQL files and execute this in OSQL / ISQL.
>|||My pleasure :)
--
Jacco Schalkwijk
SQL Server MVP
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:eW7gJhVnDHA.2456@.TK2MSFTNGP09.phx.gbl...
> > We can have "go" in .SQL files and execute this in OSQL / ISQL.
> I never said we can't. I was referring to the :r option of OSQL, See
Jacco's explanation (thanks
> Jacco!).
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:ug%23PoAVnDHA.2304@.TK2MSFTNGP11.phx.gbl...
> > Hi Tibor ,
> >
> > We can have "go" in .SQL files and execute this in OSQL / ISQL.
> >
> >
>|||Thanks guys for explanation!
Since I have "go" in all of my files I decided to do this:
create a "bat" file with following strings
osql -E -S "servername" -d "databasename" -i "sqlfilename"
it works perfect!
"Dmitry Karneyev" <karneyev@.msn.com> ÓÏÏÂÝÉÌ/ÓÏÏÂÝÉÌÁ × ÎÏ×ÏÓÔÑÈ ÓÌÅÄÕÀÝÅÅ:
news:ONXUBrUnDHA.2652@.TK2MSFTNGP09.phx.gbl...
> Hi!
> I have a lot of sql scripts in *.sql files.
> I'm looking for a solution how to execute them in "one statement" (for
> example in Query Analizer) .
> Thanks.
>