Difference between the querries.

I have two tables
1. AccountMaster(AccountID int, Name varchar(20))
2. Transactions(TransID, AccountID int, Amount float)

I have two queries for achieving the same result set. They are

Query1:
Select A.AccountID,A.Name,T.TransID,T.Amount
from AccountMaster A, Transactions T
where T. AccountID=A. AccountID

Query1:
Select A.AccountID,A.Name,T.TransID,T.Amount
from AccountMaster A
inner join Transactions T
on T. AccountID=A. AccountID

Here both will make use of inner join then,
Is there any difference in executing the queries ?
If yes, why SQL Server provides both the option ?

Quote:

Originally Posted by getmeidea

I have two tables
1. AccountMaster(AccountID int, Name varchar(20))
2. Transactions(TransID, AccountID int, Amount float)

I have two queries for achieving the same result set. They are

Query1:
Select A.AccountID,A.Name,T.TransID,T.Amount
from AccountMaster A, Transactions T
where T. AccountID=A. AccountID

Query1:
Select A.AccountID,A.Name,T.TransID,T.Amount
from AccountMaster A
inner join Transactions T
on T. AccountID=A. AccountID

Here both will make use of inner join then,
Is there any difference in executing the queries ?
If yes, why SQL Server provides both the option ?

I am not sure but one uses a cross join, the best thing for you is to look up joins, also it is mainly to do with speed issues. In some cases it may actually be nessecary to use a inner join that a cross join can't do or doesn't allow.|||

Quote:

Originally Posted by getmeidea

I have two tables
1. AccountMaster(AccountID int, Name varchar(20))
2. Transactions(TransID, AccountID int, Amount float)

I have two queries for achieving the same result set. They are

Query1:
Select A.AccountID,A.Name,T.TransID,T.Amount
from AccountMaster A, Transactions T
where T. AccountID=A. AccountID

Query1:
Select A.AccountID,A.Name,T.TransID,T.Amount
from AccountMaster A
inner join Transactions T
on T. AccountID=A. AccountID

Here both will make use of inner join then,
Is there any difference in executing the queries ?
If yes, why SQL Server provides both the option ?

Select A.AccountID,A.Name,T.TransID,T.Amount
from AccountMaster A, Transactions T
where T. AccountID=A. AccountID

this kind of qry may easy for join one or two tables with 1 or 2 fields, but if u go beyond that
you have to write many condition with combination in where clause
for join itself. some more conditions for selecting records after joining...

difference between table relations and foreign keys?

I've bought a book about ASP.NET with SQL Server 2000.
In one chapter, they talk about setting relations between tables, and in
another, about setting foreign keys.
But I can't understand the difference, as the purpose seems to be the same:
navigating from a parent table to a child one, and vice-versa.
Am I missing something?
Thanks!
HenriUnless you can show more detailed context, I think they are talking about
the exact same thing. A relationship is a bit more abstract; a foreign key
represents the implementation-specific way that a child row is connected to
a parent row.
http://www.aspfaq.com/
(Reverse address to reply.)
"Henri" <hmfireball@.hotmail.com> wrote in message
news:%23nSPik0TEHA.3828@.TK2MSFTNGP09.phx.gbl...
> I've bought a book about ASP.NET with SQL Server 2000.
> In one chapter, they talk about setting relations between tables, and in
> another, about setting foreign keys.
> But I can't understand the difference, as the purpose seems to be the
> same:
> navigating from a parent table to a child one, and vice-versa.
> Am I missing something?
> Thanks!
> Henri
>
>|||Actually, the first chapter is about the DataRelation class, and
DataTable.ChildRelations(String) and DataRow.GetChildRows(DataRelation).
The second chapter is about ForeignKeyConstraint Class
The only difference I see is that a foreign key allows to define cascade
update and delete rules.
Actually, ASP.NET permits to define many things like indexes, primary keys,
relations, etc. but I thought that this had to be done on SQL Server, not
from ASP.NET.
That's why I'm a bit lost :-)
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> a crit dans le messag
e de
news:ORWb%23o0TEHA.2416@.TK2MSFTNGP12.phx.gbl...
> Unless you can show more detailed context, I think they are talking about
> the exact same thing. A relationship is a bit more abstract; a foreign
key
> represents the implementation-specific way that a child row is connected
to
> a parent row.
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Henri" <hmfireball@.hotmail.com> wrote in message
> news:%23nSPik0TEHA.3828@.TK2MSFTNGP09.phx.gbl...
>
>|||What you are reading about is Data Sets in ADO.Net. The
relations and constraints you define in a data set are
different and separate from relations and constraints in the
database. In ADO.Net, you can create something similar to a
database - tables (data tables), relationships (using the
DataRelation class), constraints (such as with the
ForeignKeyConstraint class). It's not the same thing as the
database you may be accessing for the data in the data sets.
For ADO.Net, you can find more information on how relations
and constraints work together, differently in .Net Framework
documentation. Check the remarks section of the definition
of the DataRelation class.
-Sue
On Fri, 11 Jun 2004 03:40:00 +0200, "Henri"
<hmfireball@.hotmail.com> wrote:

>Actually, the first chapter is about the DataRelation class, and
>DataTable.ChildRelations(String) and DataRow.GetChildRows(DataRelation).
>The second chapter is about ForeignKeyConstraint Class
>The only difference I see is that a foreign key allows to define cascade
>update and delete rules.
>Actually, ASP.NET permits to define many things like indexes, primary keys,
>relations, etc. but I thought that this had to be done on SQL Server, not
>from ASP.NET.
>That's why I'm a bit lost :-)
>
>"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> a crit dans le messa
ge de
>news:ORWb%23o0TEHA.2416@.TK2MSFTNGP12.phx.gbl...
>key
>to
>|||Hi,
Within ADO.Net, a Foreign Key Constaint is a subtype of the Data Relation. T
he FKC in ADO.Net behaves in a similar manner to a FKC within SQL Server, bu
t it is a constraint on the Data Set, not the database, which can be an impo
rtant consideration.
A Data Relation has additonal functionality that is not provided by the FKC,
( I think one is providing the Child Rows in the Data Set to a particular M
aster row.) I suggest you have a look at the class definitions and examine t
he different methods and pr
operties each suppiles

difference between table relations and foreign keys?

I've bought a book about ASP.NET with SQL Server 2000.
In one chapter, they talk about setting relations between tables, and in
another, about setting foreign keys.
But I can't understand the difference, as the purpose seems to be the same:
navigating from a parent table to a child one, and vice-versa.
Am I missing something?
Thanks!
HenriUnless you can show more detailed context, I think they are talking about
the exact same thing. A relationship is a bit more abstract; a foreign key
represents the implementation-specific way that a child row is connected to
a parent row.
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Henri" <hmfireball@.hotmail.com> wrote in message
news:%23nSPik0TEHA.3828@.TK2MSFTNGP09.phx.gbl...
> I've bought a book about ASP.NET with SQL Server 2000.
> In one chapter, they talk about setting relations between tables, and in
> another, about setting foreign keys.
> But I can't understand the difference, as the purpose seems to be the
> same:
> navigating from a parent table to a child one, and vice-versa.
> Am I missing something?
> Thanks!
> Henri
>
>|||Actually, the first chapter is about the DataRelation class, and
DataTable.ChildRelations(String) and DataRow.GetChildRows(DataRelation).
The second chapter is about ForeignKeyConstraint Class
The only difference I see is that a foreign key allows to define cascade
update and delete rules.
Actually, ASP.NET permits to define many things like indexes, primary keys,
relations, etc. but I thought that this had to be done on SQL Server, not
from ASP.NET.
That's why I'm a bit lost :-)
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> a écrit dans le message de
news:ORWb%23o0TEHA.2416@.TK2MSFTNGP12.phx.gbl...
> Unless you can show more detailed context, I think they are talking about
> the exact same thing. A relationship is a bit more abstract; a foreign
key
> represents the implementation-specific way that a child row is connected
to
> a parent row.
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Henri" <hmfireball@.hotmail.com> wrote in message
> news:%23nSPik0TEHA.3828@.TK2MSFTNGP09.phx.gbl...
> > I've bought a book about ASP.NET with SQL Server 2000.
> > In one chapter, they talk about setting relations between tables, and in
> > another, about setting foreign keys.
> > But I can't understand the difference, as the purpose seems to be the
> > same:
> > navigating from a parent table to a child one, and vice-versa.
> > Am I missing something?
> > Thanks!
> > Henri
> >
> >
> >
>
>|||What you are reading about is Data Sets in ADO.Net. The
relations and constraints you define in a data set are
different and separate from relations and constraints in the
database. In ADO.Net, you can create something similar to a
database - tables (data tables), relationships (using the
DataRelation class), constraints (such as with the
ForeignKeyConstraint class). It's not the same thing as the
database you may be accessing for the data in the data sets.
For ADO.Net, you can find more information on how relations
and constraints work together, differently in .Net Framework
documentation. Check the remarks section of the definition
of the DataRelation class.
-Sue
On Fri, 11 Jun 2004 03:40:00 +0200, "Henri"
<hmfireball@.hotmail.com> wrote:
>Actually, the first chapter is about the DataRelation class, and
>DataTable.ChildRelations(String) and DataRow.GetChildRows(DataRelation).
>The second chapter is about ForeignKeyConstraint Class
>The only difference I see is that a foreign key allows to define cascade
>update and delete rules.
>Actually, ASP.NET permits to define many things like indexes, primary keys,
>relations, etc. but I thought that this had to be done on SQL Server, not
>from ASP.NET.
>That's why I'm a bit lost :-)
>
>"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> a écrit dans le message de
>news:ORWb%23o0TEHA.2416@.TK2MSFTNGP12.phx.gbl...
>> Unless you can show more detailed context, I think they are talking about
>> the exact same thing. A relationship is a bit more abstract; a foreign
>key
>> represents the implementation-specific way that a child row is connected
>to
>> a parent row.
>> --
>> http://www.aspfaq.com/
>> (Reverse address to reply.)
>>
>>
>> "Henri" <hmfireball@.hotmail.com> wrote in message
>> news:%23nSPik0TEHA.3828@.TK2MSFTNGP09.phx.gbl...
>> > I've bought a book about ASP.NET with SQL Server 2000.
>> > In one chapter, they talk about setting relations between tables, and in
>> > another, about setting foreign keys.
>> > But I can't understand the difference, as the purpose seems to be the
>> > same:
>> > navigating from a parent table to a child one, and vice-versa.
>> > Am I missing something?
>> > Thanks!
>> > Henri
>> >
>> >
>> >
>>
>|||Hi
Within ADO.Net, a Foreign Key Constaint is a subtype of the Data Relation. The FKC in ADO.Net behaves in a similar manner to a FKC within SQL Server, but it is a constraint on the Data Set, not the database, which can be an important consideration
A Data Relation has additonal functionality that is not provided by the FKC, ( I think one is providing the Child Rows in the Data Set to a particular Master row.) I suggest you have a look at the class definitions and examine the different methods and properties each suppiles|||Thanks for your help to both of you :-)
Just one last question:
If I define constraints and relations(diagrams) within SQL Server, can
ADO.NET retrieve them, so that I can use them without having to redefine
them when declaring the DataSet?
I'm currently checking the.NET doc and might find the answer by myself, but
if you know it, thank you for telling me :-)
Henri
"Henri" <hmfireball@.hotmail.com> a écrit dans le message de
news:%23nSPik0TEHA.3828@.TK2MSFTNGP09.phx.gbl...
> I've bought a book about ASP.NET with SQL Server 2000.
> In one chapter, they talk about setting relations between tables, and in
> another, about setting foreign keys.
> But I can't understand the difference, as the purpose seems to be the
same:
> navigating from a parent table to a child one, and vice-versa.
> Am I missing something?
> Thanks!
> Henri
>
>|||AFAIK, not by itself. I'd be surprised if there weren't utilities available out there to generate this stuff
for you, though.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Henri" <hmfireball@.hotmail.com> wrote in message news:uvGyEt5TEHA.3420@.TK2MSFTNGP09.phx.gbl...
> Thanks for your help to both of you :-)
> Just one last question:
> If I define constraints and relations(diagrams) within SQL Server, can
> ADO.NET retrieve them, so that I can use them without having to redefine
> them when declaring the DataSet?
> I'm currently checking the.NET doc and might find the answer by myself, but
> if you know it, thank you for telling me :-)
> Henri
> "Henri" <hmfireball@.hotmail.com> a écrit dans le message de
> news:%23nSPik0TEHA.3828@.TK2MSFTNGP09.phx.gbl...
> > I've bought a book about ASP.NET with SQL Server 2000.
> > In one chapter, they talk about setting relations between tables, and in
> > another, about setting foreign keys.
> > But I can't understand the difference, as the purpose seems to be the
> same:
> > navigating from a parent table to a child one, and vice-versa.
> > Am I missing something?
> > Thanks!
> > Henri
> >
> >
> >
> >
>
>|||Thanks :)
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> a écrit
dans le message de news:eljMa15TEHA.1012@.TK2MSFTNGP09.phx.gbl...
> AFAIK, not by itself. I'd be surprised if there weren't utilities
available out there to generate this stuff
> for you, though.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Henri" <hmfireball@.hotmail.com> wrote in message
news:uvGyEt5TEHA.3420@.TK2MSFTNGP09.phx.gbl...
> > Thanks for your help to both of you :-)
> > Just one last question:
> > If I define constraints and relations(diagrams) within SQL Server, can
> > ADO.NET retrieve them, so that I can use them without having to redefine
> > them when declaring the DataSet?
> > I'm currently checking the.NET doc and might find the answer by myself,
but
> > if you know it, thank you for telling me :-)
> > Henri
> >
> > "Henri" <hmfireball@.hotmail.com> a écrit dans le message de
> > news:%23nSPik0TEHA.3828@.TK2MSFTNGP09.phx.gbl...
> > > I've bought a book about ASP.NET with SQL Server 2000.
> > > In one chapter, they talk about setting relations between tables, and
in
> > > another, about setting foreign keys.
> > > But I can't understand the difference, as the purpose seems to be the
> > same:
> > > navigating from a parent table to a child one, and vice-versa.
> > > Am I missing something?
> > > Thanks!
> > > Henri
> > >
> > >
> > >
> > >
> >
> >
> >
> >
>
>

difference between table relations and foreign keys?

I've bought a book about ASP.NET with SQL Server 2000.
In one chapter, they talk about setting relations between tables, and in
another, about setting foreign keys.
But I can't understand the difference, as the purpose seems to be the same:
navigating from a parent table to a child one, and vice-versa.
Am I missing something?
Thanks!
Henri
Unless you can show more detailed context, I think they are talking about
the exact same thing. A relationship is a bit more abstract; a foreign key
represents the implementation-specific way that a child row is connected to
a parent row.
http://www.aspfaq.com/
(Reverse address to reply.)
"Henri" <hmfireball@.hotmail.com> wrote in message
news:%23nSPik0TEHA.3828@.TK2MSFTNGP09.phx.gbl...
> I've bought a book about ASP.NET with SQL Server 2000.
> In one chapter, they talk about setting relations between tables, and in
> another, about setting foreign keys.
> But I can't understand the difference, as the purpose seems to be the
> same:
> navigating from a parent table to a child one, and vice-versa.
> Am I missing something?
> Thanks!
> Henri
>
>
|||Actually, the first chapter is about the DataRelation class, and
DataTable.ChildRelations(String) and DataRow.GetChildRows(DataRelation).
The second chapter is about ForeignKeyConstraint Class
The only difference I see is that a foreign key allows to define cascade
update and delete rules.
Actually, ASP.NET permits to define many things like indexes, primary keys,
relations, etc. but I thought that this had to be done on SQL Server, not
from ASP.NET.
That's why I'm a bit lost :-)
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> a crit dans le message de
news:ORWb%23o0TEHA.2416@.TK2MSFTNGP12.phx.gbl...
> Unless you can show more detailed context, I think they are talking about
> the exact same thing. A relationship is a bit more abstract; a foreign
key
> represents the implementation-specific way that a child row is connected
to
> a parent row.
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Henri" <hmfireball@.hotmail.com> wrote in message
> news:%23nSPik0TEHA.3828@.TK2MSFTNGP09.phx.gbl...
>
>
|||What you are reading about is Data Sets in ADO.Net. The
relations and constraints you define in a data set are
different and separate from relations and constraints in the
database. In ADO.Net, you can create something similar to a
database - tables (data tables), relationships (using the
DataRelation class), constraints (such as with the
ForeignKeyConstraint class). It's not the same thing as the
database you may be accessing for the data in the data sets.
For ADO.Net, you can find more information on how relations
and constraints work together, differently in .Net Framework
documentation. Check the remarks section of the definition
of the DataRelation class.
-Sue
On Fri, 11 Jun 2004 03:40:00 +0200, "Henri"
<hmfireball@.hotmail.com> wrote:

>Actually, the first chapter is about the DataRelation class, and
>DataTable.ChildRelations(String) and DataRow.GetChildRows(DataRelation).
>The second chapter is about ForeignKeyConstraint Class
>The only difference I see is that a foreign key allows to define cascade
>update and delete rules.
>Actually, ASP.NET permits to define many things like indexes, primary keys,
>relations, etc. but I thought that this had to be done on SQL Server, not
>from ASP.NET.
>That's why I'm a bit lost :-)
>
>"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> a crit dans le message de
>news:ORWb%23o0TEHA.2416@.TK2MSFTNGP12.phx.gbl...
>key
>to
>
|||Hi,
Within ADO.Net, a Foreign Key Constaint is a subtype of the Data Relation. The FKC in ADO.Net behaves in a similar manner to a FKC within SQL Server, but it is a constraint on the Data Set, not the database, which can be an important consideration.
A Data Relation has additonal functionality that is not provided by the FKC, ( I think one is providing the Child Rows in the Data Set to a particular Master row.) I suggest you have a look at the class definitions and examine the different methods and pr
operties each suppiles

difference between filesize in Backupfile and backupset

Can anyone tell me the difference between the filesize values being logged
in the Backupfile and Backupset tables in the msdb database.
I am a little confused as the data for the backup on a same day registers
different file sizes. Also can anyone give me a little more idea on what
each of these tables are being used for.
Thanks
Hi
In BackupSet, there is a column 'backupsize'. This value isthe size, in
bytes, of the backup dumped to tape/disk.
In BackupFile, there is a column 'filesize'. This is the actual size of the
device files that are currently configured for the DB, that have been backed
up in the BackupSet..
Definitions from systbl.chm:
backupfile
Contains one row for each data or log file that is backed up. This table is
stored in the msdb database.
backupset
Contains a row for each backup set. This table is stored in the msdb database.
Regards
Mike
"Rahul Chatterjee" wrote:

> Can anyone tell me the difference between the filesize values being logged
> in the Backupfile and Backupset tables in the msdb database.
> I am a little confused as the data for the backup on a same day registers
> different file sizes. Also can anyone give me a little more idea on what
> each of these tables are being used for.
> Thanks
>
>

difference between filesize in Backupfile and backupset

Can anyone tell me the difference between the filesize values being logged
in the Backupfile and Backupset tables in the msdb database.
I am a little confused as the data for the backup on a same day registers
different file sizes. Also can anyone give me a little more idea on what
each of these tables are being used for.
ThanksHi
In BackupSet, there is a column 'backupsize'. This value isthe size, in
bytes, of the backup dumped to tape/disk.
In BackupFile, there is a column 'filesize'. This is the actual size of the
device files that are currently configured for the DB, that have been backed
up in the BackupSet..
Definitions from systbl.chm:
backupfile
Contains one row for each data or log file that is backed up. This table is
stored in the msdb database.
backupset
Contains a row for each backup set. This table is stored in the msdb database.
Regards
Mike
"Rahul Chatterjee" wrote:
> Can anyone tell me the difference between the filesize values being logged
> in the Backupfile and Backupset tables in the msdb database.
> I am a little confused as the data for the backup on a same day registers
> different file sizes. Also can anyone give me a little more idea on what
> each of these tables are being used for.
> Thanks
>
>

difference between filesize in Backupfile and backupset

Can anyone tell me the difference between the filesize values being logged
in the Backupfile and Backupset tables in the msdb database.
I am a little confused as the data for the backup on a same day registers
different file sizes. Also can anyone give me a little more idea on what
each of these tables are being used for.
ThanksHi
In BackupSet, there is a column 'backupsize'. This value isthe size, in
bytes, of the backup dumped to tape/disk.
In BackupFile, there is a column 'filesize'. This is the actual size of the
device files that are currently configured for the DB, that have been backed
up in the BackupSet..
Definitions from systbl.chm:
backupfile
Contains one row for each data or log file that is backed up. This table is
stored in the msdb database.
backupset
Contains a row for each backup set. This table is stored in the msdb databas
e.
Regards
Mike
"Rahul Chatterjee" wrote:

> Can anyone tell me the difference between the filesize values being logged
> in the Backupfile and Backupset tables in the msdb database.
> I am a little confused as the data for the backup on a same day registers
> different file sizes. Also can anyone give me a little more idea on what
> each of these tables are being used for.
> Thanks
>
>

Difference between 2 tables

Hi,

We have 2 tables as follows,

TableA
----
doc_id ver_id ref_date

TableB
----
doc_id ver_id ref_min_date

We need to find out the records that exists in TableA but not in TableB
and also to get the minimum of TableA.ref_date from the result.

For example,

TableA has the following rows
doc_id ver_id ref_date
100 1 10-AUG-2006
100 2 12-AUG-2006
100 2 13-AUG-2006
100 2 14-AUG-2006
100 2 15-AUG-2006
200 1 17-AUG-2006
300 1 18-AUG-2006

TableB has the following rows
doc_id ver_id ref_date
100 1 10-AUG-2006
200 1 10-AUG-2006

Result should be as follows
doc_id ver_id ref_date
100 2 12-AUG-2006
300 1 18-AUG-2006

Please help us in writing the query

Thanks
AshokHi Ashok,

You may like to try this.

Select A.doc_id,A.ver_id ,Min(A.ref_date) From TableA A
Where Not Exists ( Select 1 From TableB B where A.doc_id=B.doc_id
And A.ver_id=B.ver_id
And A.ref_date=B.ref_date
) Group By A.doc_id,A.ver_id ;

I hope this helps.

With Warm regards
Jatinder Singh
http://jatindersingh.blogspot.com
http://sqloracle.tripod.comsql

Diff. transactional publications ; synchronizing at diif. times

Is it possible to have more than one transactional replication publications
which will be containing differenr list of tables and will be synchronizing
at different times.
If yes pls. tell me how ?
Thanks in advance.
I'm not too sure I follow the question, the process to create a second
publication is much the same as the first as far as the selection of tables
is concerned. Each one will have different jobs which can be scheduled. Make
sure the second publication is configured to have an independant
distribution agent before creating the subscription so the synchronization
times can be different.
HTH
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

DIFF Objects

Does anybody know of a tool or script that will tell the difference between
two tables including objects such as indexes?Give Red Gate SQLCompare
(http://www.red-gate.com/products/SQL_Compare/index.htm) a try
Denis the SQL Menace
http://sqlservercode.blogspot.com/
morphius wrote:
> Does anybody know of a tool or script that will tell the difference between
> two tables including objects such as indexes?

Diff for tables *structures* rather than data

If I have two sql server databases that started out with identical
table/key/index structures, but were not properly kept in sync, is
there any way I can generate a table change script to essentially
'diff' the two databases and come up with table change scripts to
bring one in line with the other?

An answer to this age-old question of mine would make me very
happy...!

BrianERwin will do this easily for you (reverse engineer one database and then do
a compare with the other). PowerDesigner and ER/Studio will probably also
do it.

Downside? They're not free...

To find out what's different, you could do something like this:

select so.name, sc.name, sc.type from sysobject so inner join syscolumns sc
on so.id = sc.id
order by so.name, sc.name

in both databases, paste the output into .TXT files and then do a compare on
the .TXT files (WINDIFF utility) to get started. You'll have to generate
the change scripts by hand, of course.

You could completely script the databases and then WINDIFF the scripts.
However, the scripting order might be different between the two databases
and this may muddy the waters (you could rearrange the scripts by hand to
resolve some ordering problems).

If you have a little money, see if you can find a database consultant with
access to ERwin or one of the other tools to come in for a couple of hours
and use his tools to generate the scripts for you. It might save a lot of
time. You could ask him to print diagrams, too, which might be helpful down
the road.

If you have a fair amount of cash, consider buying one of these tools
yourself - they're very, very handy. ER/Studio used to offer a freely
downloadable demo; don't know about ERwin or PowerDesigner. It seems to me
that ERwin is something like $4000. I think ER/Studion was less, don't
recall about PowerDesigner.

DesktopDBA, if it's still around, may also offer some capability this way.

I suppose you could check C|Net, SQLServerCentral or some of the other
SQL-oriented group sites for freely downloadable utilities, too.

"Brian McGee" <brian.mcgee@.Sentrio.com> wrote in message
news:831a513c.0309110332.2184b751@.posting.google.c om...
> If I have two sql server databases that started out with identical
> table/key/index structures, but were not properly kept in sync, is
> there any way I can generate a table change script to essentially
> 'diff' the two databases and come up with table change scripts to
> bring one in line with the other?
> An answer to this age-old question of mine would make me very
> happy...!
> Brian|||In article <831a513c.0309110332.2184b751@.posting.google.com>,
brian.mcgee@.Sentrio.com says...
> If I have two sql server databases that started out with identical
> table/key/index structures, but were not properly kept in sync, is
> there any way I can generate a table change script to essentially
> 'diff' the two databases and come up with table change scripts to
> bring one in line with the other?

I like Red-gate Softwares "SQL Tools" product for that.
(http://www.red-gate.com) You can get a single-user license for the SQL
Compare portion of the product for about $200. That would bring the
table definitions in line. If you also want scripts to modify the
contents of the tables, that's another $200. Of course, at that point
you're better off with the bundle, which is $350 and includes DTS
Compare which diffs server settings, DTS packages, jobs and logins.

-- Rick

P.S. No affiliation at all with Red-Gate software but their product
saved my cojones once, so I'm just passing on my experience.

Did SP1 fix Query Optimizer issues w/ Partitioned tables?

Hi there,

We've implemented a partitioned table to a large table within our EDW. However, we'd discovered that the Query Optimizer was not able to understand parameterized queries. It basically was scanning all the partitions even though a parameter was passed defining the partition to use. Consequently, the queries need to hard code parameter values!

Does anyone know if SP1 addressed this problem?

Thanks

-Walter

? Are you certain that it's scanning all of the partitions? You might want to look at the Actual Row Count in the execution plan to find out what's really happening. Partition elimination may not occur when the query is compiled -- instead, it may use a runtime value. -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <Walti@.discussions.microsoft.com> wrote in message news:fd66588f-8124-45c7-b977-6785bf117a17@.discussions.microsoft.com... Hi there, We've implemented a partitioned table to a large table within our EDW. However, we'd discovered that the Query Optimizer was not able to understand parameterized queries. It basically was scanning all the partitions even though a parameter was passed defining the partition to use. Consequently, the queries need to hard code parameter values! Does anyone know if SP1 addressed this problem? Thanks -Walter|||

Yes, I am sure that it is scanning all the partition. We had a MS champion at the site to overview the full EDW design, and he confirmed that it was a known problem and that it would have been fixed on a future SP release.

-Walter Biffi

|||? Can you describe the scenario in a bit more detail? Or better yet, post a script to reproduce the issue? -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <Walti@.discussions.microsoft.com> wrote in message news:a11d1a77-7f52-43cb-bf37-5798188d9a0c@.discussions.microsoft.com... Yes, I am sure that it is scanning all the partition. We had a MS champion at the site to overview the full EDW design, and he confirmed that it was a known problem and that it would have been fixed on a future SP release. -Walter Biffi

Diagrams SQL Server 2005

Hello,
I am working with the 2005er version.
I am missing the diagrams where I can show the tables and there relation
ships (th old daVinci tools).
Can somebody tell me where they are gone?
Thanks
Michael
Michael Zdarsky
www.zac-it.com
ZAC-IT GmbH
09190-9271010
You should be discussing this in the beta groups for SQL Server 2005. But
since SQL 2005 B2 isn't covered under beta I can say that they diagrams are
not in the version of the beta you are working with. New features, such as
this one, may or may not be added to the final release.
Brian
"Michael Zdarsky" <zdarsky@.zac-it.com.(nospamplease)> wrote in message
news:02A8C7FB-11DE-46DF-B592-49A3F1FB6FCE@.microsoft.com...
> Hello,
> I am working with the 2005er version.
> I am missing the diagrams where I can show the tables and there relation
> ships (th old daVinci tools).
> Can somebody tell me where they are gone?
> Thanks
> Michael
> --
> Michael Zdarsky
> www.zac-it.com
> ZAC-IT GmbH
> 09190-9271010
|||http://www.aspfaq.com/sql2005/show.asp?id=6
Please use the SQL Server 2005 newsgroups to discuss SQL Server 2005.
That's what the groups were created for.
http://www.aspfaq.com/sql2005/show.asp?id=1
http://www.aspfaq.com/
(Reverse address to reply.)
"Michael Zdarsky" <zdarsky@.zac-it.com.(nospamplease)> wrote in message
news:02A8C7FB-11DE-46DF-B592-49A3F1FB6FCE@.microsoft.com...
> Hello,
> I am working with the 2005er version.
> I am missing the diagrams where I can show the tables and there relation
> ships (th old daVinci tools).
> Can somebody tell me where they are gone?
> Thanks
> Michael
> --
> Michael Zdarsky
> www.zac-it.com
> ZAC-IT GmbH
> 09190-9271010

Diagrams SQL Server 2005

Hello,
I am working with the 2005er version.
I am missing the diagrams where I can show the tables and there relation
ships (th old daVinci tools).
Can somebody tell me where they are gone?
Thanks
Michael
--
Michael Zdarsky
www.zac-it.com
ZAC-IT GmbH
09190-9271010You should be discussing this in the beta groups for SQL Server 2005. But
since SQL 2005 B2 isn't covered under beta I can say that they diagrams are
not in the version of the beta you are working with. New features, such as
this one, may or may not be added to the final release.
--
Brian
"Michael Zdarsky" <zdarsky@.zac-it.com.(nospamplease)> wrote in message
news:02A8C7FB-11DE-46DF-B592-49A3F1FB6FCE@.microsoft.com...
> Hello,
> I am working with the 2005er version.
> I am missing the diagrams where I can show the tables and there relation
> ships (th old daVinci tools).
> Can somebody tell me where they are gone?
> Thanks
> Michael
> --
> Michael Zdarsky
> www.zac-it.com
> ZAC-IT GmbH
> 09190-9271010|||http://www.aspfaq.com/sql2005/show.asp?id=6
Please use the SQL Server 2005 newsgroups to discuss SQL Server 2005.
That's what the groups were created for.
http://www.aspfaq.com/sql2005/show.asp?id=1
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Michael Zdarsky" <zdarsky@.zac-it.com.(nospamplease)> wrote in message
news:02A8C7FB-11DE-46DF-B592-49A3F1FB6FCE@.microsoft.com...
> Hello,
> I am working with the 2005er version.
> I am missing the diagrams where I can show the tables and there relation
> ships (th old daVinci tools).
> Can somebody tell me where they are gone?
> Thanks
> Michael
> --
> Michael Zdarsky
> www.zac-it.com
> ZAC-IT GmbH
> 09190-9271010

Diagrams not showing relationships

I am trying to create a picture of my DB and a few tables
and how they relate. I have picked them, and gone through
the Diagram wizard, but am not able to see the
relationship labels. I am clicking 'Show relationship
labels', but nothing is displaying. Any help would be
appreciated. Thanks!!Hi,
Does the underlying tables have primary key-foreign key relationships?
--
Dinesh
SQL Server MVP
--
--
SQL Server FAQ at
http://www.tkdinesh.com
"BMS" <anonymous@.discussions.microsoft.com> wrote in message
news:494f01c42bd7$dba05f60$a101280a@.phx.gbl...
> I am trying to create a picture of my DB and a few tables
> and how they relate. I have picked them, and gone through
> the Diagram wizard, but am not able to see the
> relationship labels. I am clicking 'Show relationship
> labels', but nothing is displaying. Any help would be
> appreciated. Thanks!!|||They should, I know of 2 tables that relate by
order_number, but they don't show the relationship.
>--Original Message--
>Hi,
>Does the underlying tables have primary key-foreign key
relationships?
>--
>Dinesh
>SQL Server MVP
>--
>--
>SQL Server FAQ at
>http://www.tkdinesh.com
>"BMS" <anonymous@.discussions.microsoft.com> wrote in
message
>news:494f01c42bd7$dba05f60$a101280a@.phx.gbl...
>> I am trying to create a picture of my DB and a few
tables
>> and how they relate. I have picked them, and gone
through
>> the Diagram wizard, but am not able to see the
>> relationship labels. I am clicking 'Show relationship
>> labels', but nothing is displaying. Any help would be
>> appreciated. Thanks!!
>
>.
>

Diagrams

What are these diagrams in each database used for?
If I make a link betwwen two tables in a diagram ,,does it affect the
relationships?
Why dosn't it detect the relations between the tables?
Thanks
They are a simple ERD tool for visualising the schema of your database.
Changes to relationships in a diagram will affect your schema (it will
prompt to warn you).If you don't have foreign key relationships setup then
it won't be able to automatically determine the table relations.
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Reza Alirezaei" <anonymous@.discussions.microsoft.com> wrote in message
news:e$xI1SEPEHA.1388@.TK2MSFTNGP09.phx.gbl...
> What are these diagrams in each database used for?
> If I make a link betwwen two tables in a diagram ,,does it affect the
> relationships?
> Why dosn't it detect the relations between the tables?
> Thanks
>
|||How can I setup a foreign key while I'm designing a tabel?
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:O3a59pEPEHA.252@.TK2MSFTNGP10.phx.gbl...
> They are a simple ERD tool for visualising the schema of your database.
> Changes to relationships in a diagram will affect your schema (it will
> prompt to warn you).If you don't have foreign key relationships setup then
> it won't be able to automatically determine the table relations.
> --
> HTH
> Jasper Smith (SQL Server MVP)
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
>
> "Reza Alirezaei" <anonymous@.discussions.microsoft.com> wrote in message
> news:e$xI1SEPEHA.1388@.TK2MSFTNGP09.phx.gbl...
>
|||Reza,
CREATE TABLE master_table
(
master_id INT NOT NULL PRIMARY KEY,
master_name VARCHAR(200)
)
--One way of creating a Foreign Key
CREATE TABLE child_table
(
child_id INT NOT NULL PRIMARY KEY,
master_id INT NOT NULL REFERENCES master_table(master_id),
child_name VARCHAR(200)
)
--Another way, using Alter table
ALTER TABLE [dbo].[child_tyable] ADD
FOREIGN KEY
(
[master_id]
) REFERENCES [dbo].[master_table] (
[master_id]
)
GO
You can find basic tsql syntax in BooksOnLine.
Dinesh
SQL Server MVP
--
SQL Server FAQ at
http://www.tkdinesh.com
"Reza Alirezaei" <anonymous@.discussions.microsoft.com> wrote in message
news:#jDyX8EPEHA.3016@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> How can I setup a foreign key while I'm designing a tabel?
> "Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
> news:O3a59pEPEHA.252@.TK2MSFTNGP10.phx.gbl...
then
>
|||You can also drag and drop in columns in the diagram itself to visually
create your relationships however I would recomend the approcah from Dinesh
i.e. explicitly create them as part of your schema as its too easy to get
things the wrong way round using the diagram tool.
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Dinesh T.K" <tkdinesh@.nospam.mail.tkdinesh.com> wrote in message
news:uaAAHKFPEHA.3052@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Reza,
> CREATE TABLE master_table
> (
> master_id INT NOT NULL PRIMARY KEY,
> master_name VARCHAR(200)
> )
> --One way of creating a Foreign Key
> CREATE TABLE child_table
> (
> child_id INT NOT NULL PRIMARY KEY,
> master_id INT NOT NULL REFERENCES master_table(master_id),
> child_name VARCHAR(200)
> )
> --Another way, using Alter table
> ALTER TABLE [dbo].[child_tyable] ADD
> FOREIGN KEY
> (
> [master_id]
> ) REFERENCES [dbo].[master_table] (
> [master_id]
> )
> GO
> You can find basic tsql syntax in BooksOnLine.
>
> --
> Dinesh
> SQL Server MVP
> --
> --
> SQL Server FAQ at
> http://www.tkdinesh.com
> "Reza Alirezaei" <anonymous@.discussions.microsoft.com> wrote in message
> news:#jDyX8EPEHA.3016@.tk2msftngp13.phx.gbl...
database.[vbcol=seagreen]
> then
message[vbcol=seagreen]
the
>

Diagrams

What are these diagrams in each database used for?
If I make a link betwwen two tables in a diagram ,,does it affect the
relationships'
Why dosn't it detect the relations between the tables'
ThanksThey are a simple ERD tool for visualising the schema of your database.
Changes to relationships in a diagram will affect your schema (it will
prompt to warn you).If you don't have foreign key relationships setup then
it won't be able to automatically determine the table relations.
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Reza Alirezaei" <anonymous@.discussions.microsoft.com> wrote in message
news:e$xI1SEPEHA.1388@.TK2MSFTNGP09.phx.gbl...
> What are these diagrams in each database used for?
> If I make a link betwwen two tables in a diagram ,,does it affect the
> relationships'
> Why dosn't it detect the relations between the tables'
> Thanks
>|||How can I setup a foreign key while I'm designing a tabel?
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:O3a59pEPEHA.252@.TK2MSFTNGP10.phx.gbl...
> They are a simple ERD tool for visualising the schema of your database.
> Changes to relationships in a diagram will affect your schema (it will
> prompt to warn you).If you don't have foreign key relationships setup then
> it won't be able to automatically determine the table relations.
> --
> HTH
> Jasper Smith (SQL Server MVP)
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
>
> "Reza Alirezaei" <anonymous@.discussions.microsoft.com> wrote in message
> news:e$xI1SEPEHA.1388@.TK2MSFTNGP09.phx.gbl...
> > What are these diagrams in each database used for?
> > If I make a link betwwen two tables in a diagram ,,does it affect the
> > relationships'
> > Why dosn't it detect the relations between the tables'
> >
> > Thanks
> >
> >
>|||Reza,
CREATE TABLE master_table
(
master_id INT NOT NULL PRIMARY KEY,
master_name VARCHAR(200)
)
--One way of creating a Foreign Key
CREATE TABLE child_table
(
child_id INT NOT NULL PRIMARY KEY,
master_id INT NOT NULL REFERENCES master_table(master_id),
child_name VARCHAR(200)
)
--Another way, using Alter table
ALTER TABLE [dbo].[child_tyable] ADD
FOREIGN KEY
(
[master_id]
) REFERENCES [dbo].[master_table] (
[master_id]
)
GO
You can find basic tsql syntax in BooksOnLine.
Dinesh
SQL Server MVP
--
--
SQL Server FAQ at
http://www.tkdinesh.com
"Reza Alirezaei" <anonymous@.discussions.microsoft.com> wrote in message
news:#jDyX8EPEHA.3016@.tk2msftngp13.phx.gbl...
> How can I setup a foreign key while I'm designing a tabel?
> "Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
> news:O3a59pEPEHA.252@.TK2MSFTNGP10.phx.gbl...
> > They are a simple ERD tool for visualising the schema of your database.
> > Changes to relationships in a diagram will affect your schema (it will
> > prompt to warn you).If you don't have foreign key relationships setup
then
> > it won't be able to automatically determine the table relations.
> >
> > --
> > HTH
> >
> > Jasper Smith (SQL Server MVP)
> >
> > I support PASS - the definitive, global
> > community for SQL Server professionals -
> > http://www.sqlpass.org
> >
> >
> > "Reza Alirezaei" <anonymous@.discussions.microsoft.com> wrote in message
> > news:e$xI1SEPEHA.1388@.TK2MSFTNGP09.phx.gbl...
> > > What are these diagrams in each database used for?
> > > If I make a link betwwen two tables in a diagram ,,does it affect the
> > > relationships'
> > > Why dosn't it detect the relations between the tables'
> > >
> > > Thanks
> > >
> > >
> >
> >
>|||You can also drag and drop in columns in the diagram itself to visually
create your relationships however I would recomend the approcah from Dinesh
i.e. explicitly create them as part of your schema as its too easy to get
things the wrong way round using the diagram tool.
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Dinesh T.K" <tkdinesh@.nospam.mail.tkdinesh.com> wrote in message
news:uaAAHKFPEHA.3052@.TK2MSFTNGP09.phx.gbl...
> Reza,
> CREATE TABLE master_table
> (
> master_id INT NOT NULL PRIMARY KEY,
> master_name VARCHAR(200)
> )
> --One way of creating a Foreign Key
> CREATE TABLE child_table
> (
> child_id INT NOT NULL PRIMARY KEY,
> master_id INT NOT NULL REFERENCES master_table(master_id),
> child_name VARCHAR(200)
> )
> --Another way, using Alter table
> ALTER TABLE [dbo].[child_tyable] ADD
> FOREIGN KEY
> (
> [master_id]
> ) REFERENCES [dbo].[master_table] (
> [master_id]
> )
> GO
> You can find basic tsql syntax in BooksOnLine.
>
> --
> Dinesh
> SQL Server MVP
> --
> --
> SQL Server FAQ at
> http://www.tkdinesh.com
> "Reza Alirezaei" <anonymous@.discussions.microsoft.com> wrote in message
> news:#jDyX8EPEHA.3016@.tk2msftngp13.phx.gbl...
> > How can I setup a foreign key while I'm designing a tabel?
> >
> > "Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
> > news:O3a59pEPEHA.252@.TK2MSFTNGP10.phx.gbl...
> > > They are a simple ERD tool for visualising the schema of your
database.
> > > Changes to relationships in a diagram will affect your schema (it will
> > > prompt to warn you).If you don't have foreign key relationships setup
> then
> > > it won't be able to automatically determine the table relations.
> > >
> > > --
> > > HTH
> > >
> > > Jasper Smith (SQL Server MVP)
> > >
> > > I support PASS - the definitive, global
> > > community for SQL Server professionals -
> > > http://www.sqlpass.org
> > >
> > >
> > > "Reza Alirezaei" <anonymous@.discussions.microsoft.com> wrote in
message
> > > news:e$xI1SEPEHA.1388@.TK2MSFTNGP09.phx.gbl...
> > > > What are these diagrams in each database used for?
> > > > If I make a link betwwen two tables in a diagram ,,does it affect
the
> > > > relationships'
> > > > Why dosn't it detect the relations between the tables'
> > > >
> > > > Thanks
> > > >
> > > >
> > >
> > >
> >
> >
>

Diagrams

What are these diagrams in each database used for?
If I make a link betwwen two tables in a diagram ,,does it affect the
relationships'
Why dosn't it detect the relations between the tables'
ThanksThey are a simple ERD tool for visualising the schema of your database.
Changes to relationships in a diagram will affect your schema (it will
prompt to warn you).If you don't have foreign key relationships setup then
it won't be able to automatically determine the table relations.
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Reza Alirezaei" <anonymous@.discussions.microsoft.com> wrote in message
news:e$xI1SEPEHA.1388@.TK2MSFTNGP09.phx.gbl...
> What are these diagrams in each database used for?
> If I make a link betwwen two tables in a diagram ,,does it affect the
> relationships'
> Why dosn't it detect the relations between the tables'
> Thanks
>|||How can I setup a foreign key while I'm designing a tabel?
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:O3a59pEPEHA.252@.TK2MSFTNGP10.phx.gbl...
> They are a simple ERD tool for visualising the schema of your database.
> Changes to relationships in a diagram will affect your schema (it will
> prompt to warn you).If you don't have foreign key relationships setup then
> it won't be able to automatically determine the table relations.
> --
> HTH
> Jasper Smith (SQL Server MVP)
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
>
> "Reza Alirezaei" <anonymous@.discussions.microsoft.com> wrote in message
> news:e$xI1SEPEHA.1388@.TK2MSFTNGP09.phx.gbl...
>|||Reza,
CREATE TABLE master_table
(
master_id INT NOT NULL PRIMARY KEY,
master_name VARCHAR(200)
)
--One way of creating a Foreign Key
CREATE TABLE child_table
(
child_id INT NOT NULL PRIMARY KEY,
master_id INT NOT NULL REFERENCES master_table(master_id),
child_name VARCHAR(200)
)
--Another way, using Alter table
ALTER TABLE [dbo].[child_tyable] ADD
FOREIGN KEY
(
[master_id]
) REFERENCES [dbo].[master_table] (
[master_id]
)
GO
You can find basic tsql syntax in BooksOnLine.
Dinesh
SQL Server MVP
--
--
SQL Server FAQ at
http://www.tkdinesh.com
"Reza Alirezaei" <anonymous@.discussions.microsoft.com> wrote in message
news:#jDyX8EPEHA.3016@.tk2msftngp13.phx.gbl...
> How can I setup a foreign key while I'm designing a tabel?
> "Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
> news:O3a59pEPEHA.252@.TK2MSFTNGP10.phx.gbl...
then[vbcol=seagreen]
>|||You can also drag and drop in columns in the diagram itself to visually
create your relationships however I would recomend the approcah from Dinesh
i.e. explicitly create them as part of your schema as its too easy to get
things the wrong way round using the diagram tool.
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Dinesh T.K" <tkdinesh@.nospam.mail.tkdinesh.com> wrote in message
news:uaAAHKFPEHA.3052@.TK2MSFTNGP09.phx.gbl...
> Reza,
> CREATE TABLE master_table
> (
> master_id INT NOT NULL PRIMARY KEY,
> master_name VARCHAR(200)
> )
> --One way of creating a Foreign Key
> CREATE TABLE child_table
> (
> child_id INT NOT NULL PRIMARY KEY,
> master_id INT NOT NULL REFERENCES master_table(master_id),
> child_name VARCHAR(200)
> )
> --Another way, using Alter table
> ALTER TABLE [dbo].[child_tyable] ADD
> FOREIGN KEY
> (
> [master_id]
> ) REFERENCES [dbo].[master_table] (
> [master_id]
> )
> GO
> You can find basic tsql syntax in BooksOnLine.
>
> --
> Dinesh
> SQL Server MVP
> --
> --
> SQL Server FAQ at
> http://www.tkdinesh.com
> "Reza Alirezaei" <anonymous@.discussions.microsoft.com> wrote in message
> news:#jDyX8EPEHA.3016@.tk2msftngp13.phx.gbl...
database.[vbcol=seagreen]
> then
message[vbcol=seagreen]
the[vbcol=seagreen]
>

diagraming databases

Are diagrams passive? What really holds the relationships between the
tables? I have to look at a legacy database in SQL2000 and the diagram does
not seem to match the behavor of the tables.Diagrams do work if there is a relation established on the tables.
Just check if Primary Key - Foreign Key combination is correctly defined
regards
Chandra
"Rich" wrote:
> Are diagrams passive? What really holds the relationships between the
> tables? I have to look at a legacy database in SQL2000 and the diagram does
> not seem to match the behavor of the tables.|||> Are diagrams passive?
Sort of. Possibly that EM tries to "synchronize" a diagram when you open it and you have performed
modifications in the database structure.
> What really holds the relationships between the
> tables?
Defined FOREIGN KEY constraints used in commands such as ALTER TABLE tblref ADD CONSTRAINT col
REFERENCES tblreferenced(col).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:6D4302AE-1F21-41A0-9125-AF8D6085F276@.microsoft.com...
> Are diagrams passive? What really holds the relationships between the
> tables? I have to look at a legacy database in SQL2000 and the diagram does
> not seem to match the behavor of the tables.

Diagram Tool Causes Strange Write Table Behavior

Using SQL Server 2000...
I'm creating a series of database diagrams for a database that has
~ 200 tables. The tables are segmented by module, so instead of
having one massive diagram I am creating a diagram per module.
All I'm doing is creating a new diagram, selecting all the tables
that belong to a module, and letting the software create the
diagram. I do make some format/layout changes to make the
diagram easier to read, but at no time am I making any structural
changes to the table.
For three of the four modules this approach has worked perfectly.
However, there is one set of tables that the tool thinks has been
altered, and it insists on writing the tables back to the database
before it will save it. The first time this happened I just said "No"
to any writing/saving and started over from scratch (I assumed I
had accidentally made a change to a table). The next time I added
all the module's tables, I simply moved one table two or three
inches to the left, and then tried to save the daigram (having 100%
confidence there were no accidental structural changes). Once
again, it wanted to write a few of the tables back to the database
before saving.
I have no idea why it thinks any structural changes occured to the
referenced tables. I certainly didn't make any, and on the rare
occasion I have accidentally made a change, I was able to
successfully start over by not writing/saving the change and
closing/opening the diagram.
Can anyone let me know what possible changes could have been
applied to a table(s) outside the diagram tool, that could affect the
diagram tool and make it think it needs to write the tables to the
database before saving?
ThanksHi Garth,
I don't know the answer to your question because I don't like, or use the
SQL Server diagramming tool - for the exact reason you're experiencing ...
changes to the diagram make changes to the database.
While I used to use ER/win (which is a fantastic tool), I've shifted to
Visio. Now, without the developer edition (I think that's the one) you can't
do a forward engineer, but you can reverse engineer any DB you can connect
to (ODBC, OLE DB, etc), it provides at least 80% of what matters in ER/win
(or Rational Rose) and you have the advantage that anyone with Visio can
read the file.
I admit it's going to be hard to learn it (as there is virtually no one on
the boards who know how), but I find it far, far superior to what is
available in SQL Server and more convenient than anything else.
Thanks,
Jay
"Garth Wells" <nobody@.nowhere.com> wrote in message
news:eQq7Mx1FIHA.4712@.TK2MSFTNGP04.phx.gbl...
> Using SQL Server 2000...
> I'm creating a series of database diagrams for a database that has
> ~ 200 tables. The tables are segmented by module, so instead of
> having one massive diagram I am creating a diagram per module.
> All I'm doing is creating a new diagram, selecting all the tables
> that belong to a module, and letting the software create the
> diagram. I do make some format/layout changes to make the
> diagram easier to read, but at no time am I making any structural
> changes to the table.
> For three of the four modules this approach has worked perfectly.
> However, there is one set of tables that the tool thinks has been
> altered, and it insists on writing the tables back to the database
> before it will save it. The first time this happened I just said "No"
> to any writing/saving and started over from scratch (I assumed I
> had accidentally made a change to a table). The next time I added
> all the module's tables, I simply moved one table two or three
> inches to the left, and then tried to save the daigram (having 100%
> confidence there were no accidental structural changes). Once
> again, it wanted to write a few of the tables back to the database
> before saving.
> I have no idea why it thinks any structural changes occured to the
> referenced tables. I certainly didn't make any, and on the rare
> occasion I have accidentally made a change, I was able to
> successfully start over by not writing/saving the change and
> closing/opening the diagram.
> Can anyone let me know what possible changes could have been
> applied to a table(s) outside the diagram tool, that could affect the
> diagram tool and make it think it needs to write the tables to the
> database before saving?
> Thanks
>
>