I have a question about foreign keys and indexes that I am unsure
about. If a table has a foreign key does this column have a non
clustered index assigned to it as default (that is hidden)? or does it
make sense to add a non clustered index to the foreign key column in
the foreign key table?
I am thinking this as I am unsure how SQL server handles foreign keys.
EXAMPLE BELOW...
DOES TABLE2.Table1ID have a non clustered index that SQL server uses?
CREATE TABLE dbo.Table1
(
Table1Id int NOT NULL,
Foo varchar(10) NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE dbo.Table1 ADD CONSTRAINT
PK_Table1_1 PRIMARY KEY CLUSTERED
(
Table1Id
) ON [PRIMARY]
GO
CREATE TABLE dbo.Table2
(
Table2ID int NOT NULL,
Table1ID int NULL,
barr varchar(10) NULL
) ON [PRIMARY]
GO
ALTER TABLE dbo.Table2 ADD CONSTRAINT
PK_Table2 PRIMARY KEY CLUSTERED
(
Table2ID
) ON [PRIMARY]
GO
ALTER TABLE dbo.Table2 WITH NOCHECK ADD CONSTRAINT
FK_Table2_Table1 FOREIGN KEY
(
Table1ID
) REFERENCES dbo.Table1
(
Table1Id
) NOT FOR REPLICATION
GO
ALTER TABLE dbo.Table2
NOCHECK CONSTRAINT FK_Table2_Table1
GOSQL Server does not index foreign keys by default. Usually it does make
sense to create an index on a foreign key.
David Portas
SQL Server MVP
--sql
Showing posts with label keys. Show all posts
Showing posts with label keys. Show all posts
Thursday, March 29, 2012
Foreign key
How do I find out Foreign keys associated with a table?
How do I create Foreign Key when creating a table
using Enterprise manager.
Thank you,
Lin> How do I find out Foreign keys associated with a table?
http://www.aspfaq.com/2520
> How do I create Foreign Key when creating a table
> using Enterprise manager.
I recommend creating tables in Query Analyzer, then you can explicitly
declare foreign keys instead of using a GUI. See CREATE TABLE in Books
Online.
--
http://www.aspfaq.com/
(Reverse address to reply.)|||sp_help <table name> will show you the foreign keys associated with a give
table.
To add foreign key relationships in EM, click on the "Manage
Relationship..." toolbar item when creating a "New Table".
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Lin" <anonymous@.discussions.microsoft.com> wrote in message
news:1bf2f01c4522f$18cc4fc0$a601280a@.phx.gbl...
> How do I find out Foreign keys associated with a table?
> How do I create Foreign Key when creating a table
> using Enterprise manager.
> Thank you,
> Lin
>|||Greg,
Thank you very much, this really helps!
-Lin
>--Original Message--
>sp_help <table name> will show you the foreign keys
associated with a give
>table.
>To add foreign key relationships in EM, click on
the "Manage
>Relationship..." toolbar item when creating a "New
Table".
>
>--
>----
--
>----
--
>--
>Need SQL Server Examples check out my website at
>http://www.geocities.com/sqlserverexamples
>"Lin" <anonymous@.discussions.microsoft.com> wrote in
message
>news:1bf2f01c4522f$18cc4fc0$a601280a@.phx.gbl...
>> How do I find out Foreign keys associated with a table?
>> How do I create Foreign Key when creating a table
>> using Enterprise manager.
>> Thank you,
>> Lin
>
>.
>|||Aaron,
Thank you very much!
-Lin
>--Original Message--
>> How do I find out Foreign keys associated with a table?
>http://www.aspfaq.com/2520
>> How do I create Foreign Key when creating a table
>> using Enterprise manager.
>I recommend creating tables in Query Analyzer, then you
can explicitly
>declare foreign keys instead of using a GUI. See CREATE
TABLE in Books
>Online.
>--
>http://www.aspfaq.com/
>(Reverse address to reply.)
>
>.
>sql
How do I create Foreign Key when creating a table
using Enterprise manager.
Thank you,
Lin> How do I find out Foreign keys associated with a table?
http://www.aspfaq.com/2520
> How do I create Foreign Key when creating a table
> using Enterprise manager.
I recommend creating tables in Query Analyzer, then you can explicitly
declare foreign keys instead of using a GUI. See CREATE TABLE in Books
Online.
--
http://www.aspfaq.com/
(Reverse address to reply.)|||sp_help <table name> will show you the foreign keys associated with a give
table.
To add foreign key relationships in EM, click on the "Manage
Relationship..." toolbar item when creating a "New Table".
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Lin" <anonymous@.discussions.microsoft.com> wrote in message
news:1bf2f01c4522f$18cc4fc0$a601280a@.phx.gbl...
> How do I find out Foreign keys associated with a table?
> How do I create Foreign Key when creating a table
> using Enterprise manager.
> Thank you,
> Lin
>|||Greg,
Thank you very much, this really helps!
-Lin
>--Original Message--
>sp_help <table name> will show you the foreign keys
associated with a give
>table.
>To add foreign key relationships in EM, click on
the "Manage
>Relationship..." toolbar item when creating a "New
Table".
>
>--
>----
--
>----
--
>--
>Need SQL Server Examples check out my website at
>http://www.geocities.com/sqlserverexamples
>"Lin" <anonymous@.discussions.microsoft.com> wrote in
message
>news:1bf2f01c4522f$18cc4fc0$a601280a@.phx.gbl...
>> How do I find out Foreign keys associated with a table?
>> How do I create Foreign Key when creating a table
>> using Enterprise manager.
>> Thank you,
>> Lin
>
>.
>|||Aaron,
Thank you very much!
-Lin
>--Original Message--
>> How do I find out Foreign keys associated with a table?
>http://www.aspfaq.com/2520
>> How do I create Foreign Key when creating a table
>> using Enterprise manager.
>I recommend creating tables in Query Analyzer, then you
can explicitly
>declare foreign keys instead of using a GUI. See CREATE
TABLE in Books
>Online.
>--
>http://www.aspfaq.com/
>(Reverse address to reply.)
>
>.
>sql
Foreign key
How do I find out Foreign keys associated with a table?
How do I create Foreign Key when creating a table
using Enterprise manager.
Thank you,
Lin
> How do I find out Foreign keys associated with a table?
http://www.aspfaq.com/2520
> How do I create Foreign Key when creating a table
> using Enterprise manager.
I recommend creating tables in Query Analyzer, then you can explicitly
declare foreign keys instead of using a GUI. See CREATE TABLE in Books
Online.
http://www.aspfaq.com/
(Reverse address to reply.)
|||sp_help <table name> will show you the foreign keys associated with a give
table.
To add foreign key relationships in EM, click on the "Manage
Relationship..." toolbar item when creating a "New Table".
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Lin" <anonymous@.discussions.microsoft.com> wrote in message
news:1bf2f01c4522f$18cc4fc0$a601280a@.phx.gbl...
> How do I find out Foreign keys associated with a table?
> How do I create Foreign Key when creating a table
> using Enterprise manager.
> Thank you,
> Lin
>
|||Greg,
Thank you very much, this really helps!
-Lin
>--Original Message--
>sp_help <table name> will show you the foreign keys
associated with a give
>table.
>To add foreign key relationships in EM, click on
the "Manage
>Relationship..." toolbar item when creating a "New
Table".
>
>--
>----
--
>----
--
>--
>Need SQL Server Examples check out my website at
>http://www.geocities.com/sqlserverexamples
>"Lin" <anonymous@.discussions.microsoft.com> wrote in
message
>news:1bf2f01c4522f$18cc4fc0$a601280a@.phx.gbl...
>
>.
>
|||Aaron,
Thank you very much!
-Lin
>--Original Message--
>http://www.aspfaq.com/2520
>
>I recommend creating tables in Query Analyzer, then you
can explicitly
>declare foreign keys instead of using a GUI. See CREATE
TABLE in Books
>Online.
>--
>http://www.aspfaq.com/
>(Reverse address to reply.)
>
>.
>
How do I create Foreign Key when creating a table
using Enterprise manager.
Thank you,
Lin
> How do I find out Foreign keys associated with a table?
http://www.aspfaq.com/2520
> How do I create Foreign Key when creating a table
> using Enterprise manager.
I recommend creating tables in Query Analyzer, then you can explicitly
declare foreign keys instead of using a GUI. See CREATE TABLE in Books
Online.
http://www.aspfaq.com/
(Reverse address to reply.)
|||sp_help <table name> will show you the foreign keys associated with a give
table.
To add foreign key relationships in EM, click on the "Manage
Relationship..." toolbar item when creating a "New Table".
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Lin" <anonymous@.discussions.microsoft.com> wrote in message
news:1bf2f01c4522f$18cc4fc0$a601280a@.phx.gbl...
> How do I find out Foreign keys associated with a table?
> How do I create Foreign Key when creating a table
> using Enterprise manager.
> Thank you,
> Lin
>
|||Greg,
Thank you very much, this really helps!
-Lin
>--Original Message--
>sp_help <table name> will show you the foreign keys
associated with a give
>table.
>To add foreign key relationships in EM, click on
the "Manage
>Relationship..." toolbar item when creating a "New
Table".
>
>--
>----
--
>----
--
>--
>Need SQL Server Examples check out my website at
>http://www.geocities.com/sqlserverexamples
>"Lin" <anonymous@.discussions.microsoft.com> wrote in
message
>news:1bf2f01c4522f$18cc4fc0$a601280a@.phx.gbl...
>
>.
>
|||Aaron,
Thank you very much!
-Lin
>--Original Message--
>http://www.aspfaq.com/2520
>
>I recommend creating tables in Query Analyzer, then you
can explicitly
>declare foreign keys instead of using a GUI. See CREATE
TABLE in Books
>Online.
>--
>http://www.aspfaq.com/
>(Reverse address to reply.)
>
>.
>
Foreign key
How do I find out Foreign keys associated with a table?
How do I create Foreign Key when creating a table
using Enterprise manager.
Thank you,
Lin> How do I find out Foreign keys associated with a table?
http://www.aspfaq.com/2520
> How do I create Foreign Key when creating a table
> using Enterprise manager.
I recommend creating tables in Query Analyzer, then you can explicitly
declare foreign keys instead of using a GUI. See CREATE TABLE in Books
Online.
http://www.aspfaq.com/
(Reverse address to reply.)|||sp_help <table name> will show you the foreign keys associated with a give
table.
To add foreign key relationships in EM, click on the "Manage
Relationship..." toolbar item when creating a "New Table".
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Lin" <anonymous@.discussions.microsoft.com> wrote in message
news:1bf2f01c4522f$18cc4fc0$a601280a@.phx
.gbl...
> How do I find out Foreign keys associated with a table?
> How do I create Foreign Key when creating a table
> using Enterprise manager.
> Thank you,
> Lin
>|||Greg,
Thank you very much, this really helps!
-Lin
>--Original Message--
>sp_help <table name> will show you the foreign keys
associated with a give
>table.
>To add foreign key relationships in EM, click on
the "Manage
>Relationship..." toolbar item when creating a "New
Table".
>
>--
>----
--
>----
--
>--
>Need SQL Server Examples check out my website at
>http://www.geocities.com/sqlserverexamples
>"Lin" <anonymous@.discussions.microsoft.com> wrote in
message
> news:1bf2f01c4522f$18cc4fc0$a601280a@.phx
.gbl...
>
>.
>|||Aaron,
Thank you very much!
-Lin
>--Original Message--
>http://www.aspfaq.com/2520
>
>I recommend creating tables in Query Analyzer, then you
can explicitly
>declare foreign keys instead of using a GUI. See CREATE
TABLE in Books
>Online.
>--
>http://www.aspfaq.com/
>(Reverse address to reply.)
>
>.
>
How do I create Foreign Key when creating a table
using Enterprise manager.
Thank you,
Lin> How do I find out Foreign keys associated with a table?
http://www.aspfaq.com/2520
> How do I create Foreign Key when creating a table
> using Enterprise manager.
I recommend creating tables in Query Analyzer, then you can explicitly
declare foreign keys instead of using a GUI. See CREATE TABLE in Books
Online.
http://www.aspfaq.com/
(Reverse address to reply.)|||sp_help <table name> will show you the foreign keys associated with a give
table.
To add foreign key relationships in EM, click on the "Manage
Relationship..." toolbar item when creating a "New Table".
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Lin" <anonymous@.discussions.microsoft.com> wrote in message
news:1bf2f01c4522f$18cc4fc0$a601280a@.phx
.gbl...
> How do I find out Foreign keys associated with a table?
> How do I create Foreign Key when creating a table
> using Enterprise manager.
> Thank you,
> Lin
>|||Greg,
Thank you very much, this really helps!
-Lin
>--Original Message--
>sp_help <table name> will show you the foreign keys
associated with a give
>table.
>To add foreign key relationships in EM, click on
the "Manage
>Relationship..." toolbar item when creating a "New
Table".
>
>--
>----
--
>----
--
>--
>Need SQL Server Examples check out my website at
>http://www.geocities.com/sqlserverexamples
>"Lin" <anonymous@.discussions.microsoft.com> wrote in
message
> news:1bf2f01c4522f$18cc4fc0$a601280a@.phx
.gbl...
>
>.
>|||Aaron,
Thank you very much!
-Lin
>--Original Message--
>http://www.aspfaq.com/2520
>
>I recommend creating tables in Query Analyzer, then you
can explicitly
>declare foreign keys instead of using a GUI. See CREATE
TABLE in Books
>Online.
>--
>http://www.aspfaq.com/
>(Reverse address to reply.)
>
>.
>
Foreign in Snapshot Replication
When I initialized and started the snapshot agent, how can I move the table
schema along with all the foreign keys? The only way I can think of is add
the script for before and after the initialization.
Does that mean the Replication not support repicate the foreign keys?
If I have the foreign keys in each replicated table, is the transacational
replication smart enough to insert data into into the parent table first?
Thanks
Ed
Hi Ed,
You can replicate fks with the initial snapshot for transactional
replication from SQL2000 onwards by enabling the 0x200 (DriForiegnKeys)
article schema option. There are, however a few things you need to watch out
for:
1) Support on SQL2000 is admittedly a bit glitchy, this is especially the
case if you use concurrent snapshot, have circular references (including
self-referencing fk), or you have fks referencing from outside the
publication.
2) Even though transactional replication is suposed to replay what happened
at the publisher in the same order, an update on the primary key is
typically translated into deletes followed by inserts. This can lead to
temporary violation of fks referencing the pk and the only way to workaround
this is to make sure that your fks at the subcriber has the "NOT FOR
REPLICATION" (NFR) property on them. SQL2005 makes this easier by providing
a schema option to mark all replicated fks NFR when they are replicated to
the subscriber.
-Raymond
"Ed" <Ed@.discussions.microsoft.com> wrote in message
news:83EE4199-8A99-4673-B999-CBF3C9151B78@.microsoft.com...
> When I initialized and started the snapshot agent, how can I move the
> table
> schema along with all the foreign keys? The only way I can think of is
> add
> the script for before and after the initialization.
> Does that mean the Replication not support repicate the foreign keys?
> If I have the foreign keys in each replicated table, is the transacational
> replication smart enough to insert data into into the parent table first?
> Thanks
> Ed
|||In SQL 05, there is an option if I want to replicate the foreign keys. Do I
still have to include all table Dependencies? Replication is smart enough to
know which table/foreign key needs to be created first?
The reason why i ask the question is I have a Prod and a Dev Server.
Developers always ask me to transfer all tables only to the Dev Server. I am
thinking using snapshot / transactional replication to update tables/data for
them.
That's why I would like to make sure moving the tables along with the
foreign keys is good and the replication knows the sequence of creating the
keys.
Thanks
"Raymond Mak [MSFT]" wrote:
> Hi Ed,
> You can replicate fks with the initial snapshot for transactional
> replication from SQL2000 onwards by enabling the 0x200 (DriForiegnKeys)
> article schema option. There are, however a few things you need to watch out
> for:
> 1) Support on SQL2000 is admittedly a bit glitchy, this is especially the
> case if you use concurrent snapshot, have circular references (including
> self-referencing fk), or you have fks referencing from outside the
> publication.
> 2) Even though transactional replication is suposed to replay what happened
> at the publisher in the same order, an update on the primary key is
> typically translated into deletes followed by inserts. This can lead to
> temporary violation of fks referencing the pk and the only way to workaround
> this is to make sure that your fks at the subcriber has the "NOT FOR
> REPLICATION" (NFR) property on them. SQL2005 makes this easier by providing
> a schema option to mark all replicated fks NFR when they are replicated to
> the subscriber.
> -Raymond
> "Ed" <Ed@.discussions.microsoft.com> wrote in message
> news:83EE4199-8A99-4673-B999-CBF3C9151B78@.microsoft.com...
>
>
|||You don't need to include all objects referenced by fks in your publication,
and the fks should be created in the right order (they only have to be
created after the unique\primary keys that they reference). The snapshot
agent history will tell you which fks cannot be replicated because they
reference something not in the publication.
-Raymond
"Ed" <Ed@.discussions.microsoft.com> wrote in message
news:3651674B-3142-462D-BD65-FE3D911591F7@.microsoft.com...[vbcol=seagreen]
> In SQL 05, there is an option if I want to replicate the foreign keys. Do
> I
> still have to include all table Dependencies? Replication is smart enough
> to
> know which table/foreign key needs to be created first?
> The reason why i ask the question is I have a Prod and a Dev Server.
> Developers always ask me to transfer all tables only to the Dev Server. I
> am
> thinking using snapshot / transactional replication to update tables/data
> for
> them.
> That's why I would like to make sure moving the tables along with the
> foreign keys is good and the replication knows the sequence of creating
> the
> keys.
> Thanks
> "Raymond Mak [MSFT]" wrote:
|||Thanks.
While I am playing with the table "Customers" and "Orders" in Northwind
database in SQL 2000, when I checked on "Include declared referential
integrity", the foreign keys still not get copied. Am I missing something?
Ed
"Raymond Mak [MSFT]" wrote:
> You don't need to include all objects referenced by fks in your publication,
> and the fks should be created in the right order (they only have to be
> created after the unique\primary keys that they reference). The snapshot
> agent history will tell you which fks cannot be replicated because they
> reference something not in the publication.
> -Raymond
> "Ed" <Ed@.discussions.microsoft.com> wrote in message
> news:3651674B-3142-462D-BD65-FE3D911591F7@.microsoft.com...
>
>
|||The Orders table in the Northwind database has FKs referencing the Employees
table and the Shippers table as well. In SQL2000, the snapshot agent will
simply not script any foreign key constraints from the referencing table if
*any* of the referenced tables is not part of the publication. The SQL2005
snapshot agent implements a much more granular approach of analyzing each
individual foreign key so the foreign key from Orders to Customers will be
replicated with the SQL2005 snapshot agent despite the fact that you didn't
include Employees and Shippers in your publication (you will even see in the
snapshot history messages why the FKs referencing Employees and Shippers are
not scripted). Ed, I got exactly the same question (down to the same tables)
from one of our support engineers just two days ago, did you contact
Microsoft Support for this?
-Raymond
"Ed" <Ed@.discussions.microsoft.com> wrote in message
news:190B9FF0-EC95-45A1-9B7A-6D6C3713D32F@.microsoft.com...[vbcol=seagreen]
> Thanks.
> While I am playing with the table "Customers" and "Orders" in Northwind
> database in SQL 2000, when I checked on "Include declared referential
> integrity", the foreign keys still not get copied. Am I missing
> something?
> Ed
> "Raymond Mak [MSFT]" wrote:
|||It was not me but I will play with SQL 05 to see the result.
Thanks again.
"Raymond Mak [MSFT]" wrote:
> The Orders table in the Northwind database has FKs referencing the Employees
> table and the Shippers table as well. In SQL2000, the snapshot agent will
> simply not script any foreign key constraints from the referencing table if
> *any* of the referenced tables is not part of the publication. The SQL2005
> snapshot agent implements a much more granular approach of analyzing each
> individual foreign key so the foreign key from Orders to Customers will be
> replicated with the SQL2005 snapshot agent despite the fact that you didn't
> include Employees and Shippers in your publication (you will even see in the
> snapshot history messages why the FKs referencing Employees and Shippers are
> not scripted). Ed, I got exactly the same question (down to the same tables)
> from one of our support engineers just two days ago, did you contact
> Microsoft Support for this?
> -Raymond
> "Ed" <Ed@.discussions.microsoft.com> wrote in message
> news:190B9FF0-EC95-45A1-9B7A-6D6C3713D32F@.microsoft.com...
>
>
schema along with all the foreign keys? The only way I can think of is add
the script for before and after the initialization.
Does that mean the Replication not support repicate the foreign keys?
If I have the foreign keys in each replicated table, is the transacational
replication smart enough to insert data into into the parent table first?
Thanks
Ed
Hi Ed,
You can replicate fks with the initial snapshot for transactional
replication from SQL2000 onwards by enabling the 0x200 (DriForiegnKeys)
article schema option. There are, however a few things you need to watch out
for:
1) Support on SQL2000 is admittedly a bit glitchy, this is especially the
case if you use concurrent snapshot, have circular references (including
self-referencing fk), or you have fks referencing from outside the
publication.
2) Even though transactional replication is suposed to replay what happened
at the publisher in the same order, an update on the primary key is
typically translated into deletes followed by inserts. This can lead to
temporary violation of fks referencing the pk and the only way to workaround
this is to make sure that your fks at the subcriber has the "NOT FOR
REPLICATION" (NFR) property on them. SQL2005 makes this easier by providing
a schema option to mark all replicated fks NFR when they are replicated to
the subscriber.
-Raymond
"Ed" <Ed@.discussions.microsoft.com> wrote in message
news:83EE4199-8A99-4673-B999-CBF3C9151B78@.microsoft.com...
> When I initialized and started the snapshot agent, how can I move the
> table
> schema along with all the foreign keys? The only way I can think of is
> add
> the script for before and after the initialization.
> Does that mean the Replication not support repicate the foreign keys?
> If I have the foreign keys in each replicated table, is the transacational
> replication smart enough to insert data into into the parent table first?
> Thanks
> Ed
|||In SQL 05, there is an option if I want to replicate the foreign keys. Do I
still have to include all table Dependencies? Replication is smart enough to
know which table/foreign key needs to be created first?
The reason why i ask the question is I have a Prod and a Dev Server.
Developers always ask me to transfer all tables only to the Dev Server. I am
thinking using snapshot / transactional replication to update tables/data for
them.
That's why I would like to make sure moving the tables along with the
foreign keys is good and the replication knows the sequence of creating the
keys.
Thanks
"Raymond Mak [MSFT]" wrote:
> Hi Ed,
> You can replicate fks with the initial snapshot for transactional
> replication from SQL2000 onwards by enabling the 0x200 (DriForiegnKeys)
> article schema option. There are, however a few things you need to watch out
> for:
> 1) Support on SQL2000 is admittedly a bit glitchy, this is especially the
> case if you use concurrent snapshot, have circular references (including
> self-referencing fk), or you have fks referencing from outside the
> publication.
> 2) Even though transactional replication is suposed to replay what happened
> at the publisher in the same order, an update on the primary key is
> typically translated into deletes followed by inserts. This can lead to
> temporary violation of fks referencing the pk and the only way to workaround
> this is to make sure that your fks at the subcriber has the "NOT FOR
> REPLICATION" (NFR) property on them. SQL2005 makes this easier by providing
> a schema option to mark all replicated fks NFR when they are replicated to
> the subscriber.
> -Raymond
> "Ed" <Ed@.discussions.microsoft.com> wrote in message
> news:83EE4199-8A99-4673-B999-CBF3C9151B78@.microsoft.com...
>
>
|||You don't need to include all objects referenced by fks in your publication,
and the fks should be created in the right order (they only have to be
created after the unique\primary keys that they reference). The snapshot
agent history will tell you which fks cannot be replicated because they
reference something not in the publication.
-Raymond
"Ed" <Ed@.discussions.microsoft.com> wrote in message
news:3651674B-3142-462D-BD65-FE3D911591F7@.microsoft.com...[vbcol=seagreen]
> In SQL 05, there is an option if I want to replicate the foreign keys. Do
> I
> still have to include all table Dependencies? Replication is smart enough
> to
> know which table/foreign key needs to be created first?
> The reason why i ask the question is I have a Prod and a Dev Server.
> Developers always ask me to transfer all tables only to the Dev Server. I
> am
> thinking using snapshot / transactional replication to update tables/data
> for
> them.
> That's why I would like to make sure moving the tables along with the
> foreign keys is good and the replication knows the sequence of creating
> the
> keys.
> Thanks
> "Raymond Mak [MSFT]" wrote:
|||Thanks.
While I am playing with the table "Customers" and "Orders" in Northwind
database in SQL 2000, when I checked on "Include declared referential
integrity", the foreign keys still not get copied. Am I missing something?
Ed
"Raymond Mak [MSFT]" wrote:
> You don't need to include all objects referenced by fks in your publication,
> and the fks should be created in the right order (they only have to be
> created after the unique\primary keys that they reference). The snapshot
> agent history will tell you which fks cannot be replicated because they
> reference something not in the publication.
> -Raymond
> "Ed" <Ed@.discussions.microsoft.com> wrote in message
> news:3651674B-3142-462D-BD65-FE3D911591F7@.microsoft.com...
>
>
|||The Orders table in the Northwind database has FKs referencing the Employees
table and the Shippers table as well. In SQL2000, the snapshot agent will
simply not script any foreign key constraints from the referencing table if
*any* of the referenced tables is not part of the publication. The SQL2005
snapshot agent implements a much more granular approach of analyzing each
individual foreign key so the foreign key from Orders to Customers will be
replicated with the SQL2005 snapshot agent despite the fact that you didn't
include Employees and Shippers in your publication (you will even see in the
snapshot history messages why the FKs referencing Employees and Shippers are
not scripted). Ed, I got exactly the same question (down to the same tables)
from one of our support engineers just two days ago, did you contact
Microsoft Support for this?
-Raymond
"Ed" <Ed@.discussions.microsoft.com> wrote in message
news:190B9FF0-EC95-45A1-9B7A-6D6C3713D32F@.microsoft.com...[vbcol=seagreen]
> Thanks.
> While I am playing with the table "Customers" and "Orders" in Northwind
> database in SQL 2000, when I checked on "Include declared referential
> integrity", the foreign keys still not get copied. Am I missing
> something?
> Ed
> "Raymond Mak [MSFT]" wrote:
|||It was not me but I will play with SQL 05 to see the result.
Thanks again.
"Raymond Mak [MSFT]" wrote:
> The Orders table in the Northwind database has FKs referencing the Employees
> table and the Shippers table as well. In SQL2000, the snapshot agent will
> simply not script any foreign key constraints from the referencing table if
> *any* of the referenced tables is not part of the publication. The SQL2005
> snapshot agent implements a much more granular approach of analyzing each
> individual foreign key so the foreign key from Orders to Customers will be
> replicated with the SQL2005 snapshot agent despite the fact that you didn't
> include Employees and Shippers in your publication (you will even see in the
> snapshot history messages why the FKs referencing Employees and Shippers are
> not scripted). Ed, I got exactly the same question (down to the same tables)
> from one of our support engineers just two days ago, did you contact
> Microsoft Support for this?
> -Raymond
> "Ed" <Ed@.discussions.microsoft.com> wrote in message
> news:190B9FF0-EC95-45A1-9B7A-6D6C3713D32F@.microsoft.com...
>
>
Foreign and primary keys
I have an application in which i need to get the foreign key fields
from a table and then get all the foreign keys primary key field from
the linking table. Could some one tell me how i do this using
INFORMATION_SCHEMA. I have tried and can get the foreign keys but not
sure how to get the associated primary keys.See:
http://groups.google.com/group/micr...a0218d9e069531c
from a table and then get all the foreign keys primary key field from
the linking table. Could some one tell me how i do this using
INFORMATION_SCHEMA. I have tried and can get the foreign keys but not
sure how to get the associated primary keys.See:
http://groups.google.com/group/micr...a0218d9e069531c
Razvan
Foreign And Composite Keys
Hi.
Could somebody please explain to me how to create a foreign key from a table that
has two composite keys? I have a table, UserPrecedence, with two composite keys -
up_owner, owner_userID. I have a second table, Users, that has the primary key
"emailAddress" and a userID table to which all other tables point their FK columns.
http://i103.photobucket.com/albums/m156/pbd22/Keys.jpg
I want the userID column in Users to be the FK of the owner_userID column in UserPrecedence
but the Modify Table view complains that "both sides of the relationship must have the
same number of columns" when I try to create the relationship.
I am guessing this is because its a composite primary key. Can somebody explain
to me how this is done correctly (and why)?
I appreciate your help.
Thanks.
If you want to reference the "UserPrecedence" table from the "Users" table according to the present key structure of the "UserPrecedence" table you must also include the "up_order" column in the "Users" table. However, there might be other possibilities.
First, is the "owner_userID" column of the "UserPrecedence" table a unique column? If so, you might consider changing your primary key to this column.
|||hi, thanks.
yes, owner_userID is unique but I need to keep my PK in UserPrecedence as a composite key on (up_order, owner_userID). The foreign key points from this table (source) to Users_userID (destination). Does this mean that I always have to add the composite PK column (I have many tables like this) that is not in the Users table to create the FK relationship?
thanks for your help.
|||
If your owner_userID field is unique, then you can do the following (which may not be best practice but it is an option):
You can alter your table and add a UNIQUE constraint on the owner_userID field
You can use then use the owner_userID field as a foreign key reference from other tables because you have designated it as an alternate key
The syntax for declaring the alternate MUST include explicit references
|||Thanks for your help.
I am getting the following error:
"There are no primary or candidate keys in the referenced table 'precedence' that match the referencing column list in the foreign key 'FK_users_precedence'.
(Not to confuse you but I have made some naming changes to my tables - I am trying to sync up with the ISO-11179 rules. UserPrecedence is now 'precedence'. emailAddress is now email).
Below are the two tables in question:
DBO.users:
CREATE TABLE [dbo].[users](
[registerdate] [datetime] NOT NULL,
[password] [varchar](50) NOT NULL,
[role] [char](50) NOT NULL,
[securityquestion] [varchar](50) NOT NULL,
[securityanswer] [varchar](50) NOT NULL,
[zipcode] [int] NOT NULL,
[alternateemail] [varchar](50) NULL,
[email] [varchar](50) NOT NULL,
[birthmonth] [tinyint] NOT NULL,
[birthday] [tinyint] NOT NULL,
[birthyear] [int] NOT NULL,
[userid] [int] IDENTITY(1,1) NOT NULL,
[gender] [char](10) NULL,
[city] [varchar](50) NULL,
[state] [varchar](50) NULL,
[country] [varchar](50) NULL,
[editdate] [datetime] NULL,
[lastname] [varchar](50) NULL,
[firstname] [varchar](50) NULL,
[confirmed] [bit] NULL CONSTRAINT [DF__Users__confirmed__4CC05EF3] DEFAULT ((0)),
CONSTRAINT [PK_users] PRIMARY KEY CLUSTERED
(
[userid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT [IX_email] UNIQUE NONCLUSTERED
(
[email] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
--
DBO.precedence
CREATE TABLE [dbo].[precedence](
[order] [int] NOT NULL,
[profileid] [int] NULL,
[userid] [int] NOT NULL,
[searchname] [varchar](50) NOT NULL,
CONSTRAINT [PK_precedence] PRIMARY KEY CLUSTERED
(
[searchname] ASC,
[userid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]|||OK.
A little further along...
It seems I didn't create the UNIQE constraint on userid (since userid is a composite primary key and is,
therefore, UNIQUE, is it redundant to explicitly create a UNIQUE constraint on this column?). After I
did this, I ran the following code:
Code Snippet
ALTER TABLE usersADD CONSTRAINT fk_users_precedence
FOREIGN KEY (userid)
REFERENCES precedence(userid)
and got the following error:
"The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "fk_users_precedence". The conflict occurred in database "MyDB", table "dbo.precedence", column 'userid'."
here is my updated precedence CREATE script:
Code Snippet
CREATE TABLE [dbo].[precedence]([order] [int] NOT NULL,
[profileid] [int] NULL,
[userid] [int] NOT NULL,
[searchname] [varchar](50) NOT NULL,
CONSTRAINT [PK_precedence] PRIMARY KEY CLUSTERED
(
[searchname] ASC,
[userid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT [IX_userid] UNIQUE NONCLUSTERED
(
[userid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
|||
OK. For anybody that is curious about this error and wants to see some possible solutions (if you
have a similar problem) the below thread provided a solution for me:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=478256&SiteID=1
Thanks.
Monday, March 26, 2012
Forcing Specific Distinct Columns....
Hi Guys,
I have a slight problem, a query that i have written produces data with 2 primary keys the same... however, DINSTINCT wont work in this case as the rows are still different...
Is their a way to force 1 column to always be unique?
Heres the query:
SELECT TOP 5 ORDER_ITEM.ItemID AS 'Item ID', ITEM.ItemName AS 'Item Name',
(SELECT SUM(OrdItem2.ItemQuantity) FROM ORDER_ITEM OrdItem2
WHERE OrdItem2.ItemID = ORDER_ITEM.ItemID
) AS Total_Purchased, SUM(ORDER_ITEM.ItemQuantity) AS 'Customer Purchased',
CUSTOMER.customerForename AS 'Customer Forename',
CUSTOMER.customerSurname AS 'Customer Surname'
FROM ITEM, ORDER_ITEM, ORDER_T, CUSTOMER
WHERE ITEM.ItemID = ORDER_ITEM.ItemID
AND ORDER_ITEM.OrderID = ORDER_0510096.OrderID
AND ORDER_T.CustomerID = CUSTOMER.CustomerID
GROUP BY ORDER_ITEM.ItemID, ITEM.ItemName,
CUSTOMER.customerForename, CUSTOMER.customerSurname
ORDER BY Total_Purchased DESC
The query is supposed to select the TOP 5 Products sold as well as selecting the customer that purchased the greatest amount of that item and the amount they purchased.
Currently, i will get 2 duplicate rows (except for customers name and the items the purchased. Like this:
ItemID
8 36 30 Mathew Smith
8 36 6 Tony Wattage
Which is kinda annoying... is there anyway i can prevent this?
And also apart from the Where Joins... is there a more efficient way of writing this?
thx for reading :-)
--PhilkillsDoes no one know any way around this?|||Phil,
The SQL you have posted selecting:
ORDER_ITEM.ItemID AS 'Item ID',
ITEM.ItemName AS 'Item Name',
(SELECT SUM(OrdItem2.ItemQuantity)
...does not match with the columns in the dataset you say you are getting:
ItemID
8 36 30 Mathew Smith
8 36 6 Tony Wattage
People are reluctant to respond to threads where the poster has not taken the time to accurately describe the problem.
Also, your problem sounds more like a data issue than a coding issue.|||Phil,
The SQL you have posted selecting:
ORDER_ITEM.ItemID AS 'Item ID',
ITEM.ItemName AS 'Item Name',
(SELECT SUM(OrdItem2.ItemQuantity)
...does not match with the columns in the dataset you say you are getting:
ItemID
8 36 30 Mathew Smith
8 36 6 Tony Wattage
People are reluctant to respond to threads where the poster has not taken the time to accurately describe the problem.
Also, your problem sounds more like a data issue than a coding issue.
on the contrary, i left out the data on purpose as it was irrelevent... i was only trying to point out an example of whats wrong... in the "EXAMPLE"
ItemID
8 36 30 Mathew Smith
8 36 6 Tony Wattage
The parts in bold should only appear once.... and the data it should select should be Mathew smith who purchased 30 of that item... 36 is the total amount sold, what i need the query to do... is to only display the person who bought the highest amount of the item that is part of the top 5 best selling items.
another "EXAMPLE":
ItemID Total Sold Heighest Amount sold to 1 Customer Customers Name
1.........30.................30......... ......................Jim
2.........20.................20......... ......................Jam
3.........10.................10......... ......................Flim
4.........5..................5......... .......................Flam
5.........2..................2......... .......................Stam
In this case... only 1 person purchased the items in the top 5...
However, if 5 people purchased the same item... the query would return:
ItemID Total Sold Heighest Amount sold to 1 Customer Customers Name
1.........30.................5.......... .....................Jim
1.........30.................5.......... .....................Jam
1.........30.................5.......... .....................Flim
1.........30.................5.......... .....................Flam
1.........30.................10......... .....................Stam
Notice how it returned the TOP 1 Item instead of the TOP 5 items?
The data is correct... the query is wrong... which is why i asked if i could put a constraint on the data returned saying that ItemID MUST be unique... unless ofcourse there is a better way to do it ;p
I hope this more accurately describes the problem i am having. :-)
--Philkills|||You are making this more confusing than it should be.
Lets break it down into parts.
Does this code give you the top five records that you want, without the customer information?
SELECT TOP 5
ORDER_ITEM.ItemID AS 'Item ID',
ITEM.ItemName AS 'Item Name',
SUM(ORDER_ITEM.ItemQuantity) AS Total_Purchased
FROM ITEM
INNER JOIN ORDER_ITEM ON ITEM.ItemID = ORDER_ITEM.ItemID
GROUP BY ORDER_ITEM.ItemID,
ITEM.ItemName
ORDER BY SUM(ORDER_ITEM.ItemQuantity) DESC|||You are making this more confusing than it should be.
Lets break it down into parts.
Does this code give you the top five records that you want, without the customer information?
SELECT TOP 5
ORDER_ITEM.ItemID AS 'Item ID',
ITEM.ItemName AS 'Item Name',
SUM(ORDER_ITEM.ItemQuantity) AS Total_Purchased
FROM ITEM
INNER JOIN ORDER_ITEM ON ITEM.ItemID = ORDER_ITEM.ItemID
GROUP BY ORDER_ITEM.ItemID,
ITEM.ItemName
ORDER BY SUM(ORDER_ITEM.ItemQuantity) DESC
yep that code gives the top 5 items ^^|||...and now you want the top customer for each of those items?|||...and now you want the top customer for each of those items?
correct...|||OK. I lied. This is complicated, but we are almost there...
Does this return the top CustomerID for each of those orders?:
SELECT ORDER_ITEM.ItemID AS 'Item ID',
ITEM.ItemName AS 'Item Name',
SUM(ORDER_ITEM.ItemQuantity) AS Total_Purchased,
CustomerID =
(SELECT TOP 1
ORDER_T.CustomerID
FROM ORDER_ITEM CUSTOMER_ITEM
INNER JOIN ORDER_T ON CUSTOMER_ITEM.OrderID = ORDER_T.OrderID
INNER JOIN CUSTOMER ON ORDER_T.CustomerID = CUSTOMER.CustomerID
WHERE CUSTOMER_ITEM.ItemID = ORDER_ITEM.ItemID
GROUP BY ORDER_T.CustomerID
ORDER BY SUM(CUSTOMER_ITEM.ItemQuantity) DESC,
ORDER_T.CustomerID)
FROM ITEM
INNER JOIN ORDER_ITEM ON ITEM.ItemID = ORDER_ITEM.ItemID
GROUP BY ORDER_ITEM.ItemID,
ITEM.ItemName
ORDER BY SUM(ORDER_ITEM.ItemQuantity) DESC|||OK. I lied. This is complicated, but we are almost there...
Does this return the top CustomerID for each of those orders?:
SELECT ORDER_ITEM.ItemID AS 'Item ID',
ITEM.ItemName AS 'Item Name',
SUM(ORDER_ITEM.ItemQuantity) AS Total_Purchased,
CustomerID =
(SELECT TOP 1
ORDER_T.CustomerID
FROM ORDER_ITEM CUSTOMER_ITEM
INNER JOIN ORDER_T ON CUSTOMER_ITEM.OrderID = ORDER_T.OrderID
INNER JOIN CUSTOMER ON ORDER_T.CustomerID = CUSTOMER.CustomerID
WHERE CUSTOMER_ITEM.ItemID = ORDER_ITEM.ItemID
GROUP BY ORDER_T.CustomerID
ORDER BY SUM(CUSTOMER_ITEM.ItemQuantity) DESC,
ORDER_T.CustomerID)
FROM ITEM
INNER JOIN ORDER_ITEM ON ITEM.ItemID = ORDER_ITEM.ItemID
GROUP BY ORDER_ITEM.ItemID,
ITEM.ItemName
ORDER BY SUM(ORDER_ITEM.ItemQuantity) DESC
Theres no table called CUSTOMER_ITEM... However, i have edited it to return the top customerID correctly.
SELECT ORDER_ITEM.ItemID AS 'Item ID',
ITEM.ItemName AS 'Item Name',
SUM(ORDER_ITEM.ItemQuantity) AS Total_Purchased,
CustomerID =
(SELECT TOP 1
ORDER_T.CustomerID
FROM ITEM
INNER JOIN ORDER_ITEMOrdItem2 ON ITEM.ItemID = OrdItem2.ItemID
INNER JOIN ORDER_T ON OrdItem2.OrderID = ORDER_T.OrderID
INNER JOIN CUSTOMER ON ORDER_T.CustomerID = CUSTOMER.CustomerID
WHERE OrdItem2.ItemID = ORDER_ITEM.ItemID
GROUP BY ORDER_T.CustomerID
ORDER BY SUM(ORDER_ITEM.ItemQuantity) DESC,
ORDER.CustomerID)
FROM ITEM
INNER JOIN ORDER_ITEM ON ITEM.ItemID = ORDER_ITEM.ItemID
GROUP BY ORDER_ITEM.ItemID,
ITEM_0510096.ItemName
ORDER BY SUM(ORDER_ITEM.ItemQuantity) DESC|||ok, so i can display the customer ID... now how do i display the rest of the customer info ... ^_^|||Am i assuming that no one knows the answer? :confused:
lol, kinda funny a problem that even professionals can't figure out :p|||lol, kinda funny a problem that even professionals can't figure out :poh stop
yes, there are a lot of professionals here
what makes you think they want to be your personal free development center?
ask a question, get an answer, but keep modifying your questions to get more and more and more support as you struggle your way through something that seems to be quite challenging for you... well, there eventually comes a point where people will just go on to the next person
good luck, phil
:)|||oh stop
yes, there are a lot of professionals here
what makes you think they want to be your personal free development center?
ask a question, get an answer, but keep modifying your questions to get more and more and more support as you struggle your way through something that seems to be quite challenging for you... well, there eventually comes a point where people will just go on to the next person
good luck, phil
:)
Eh?, i asked 1 question in this thread... and simply gave more info... in my other posts...
If your refering to my other questions that i've asked around the forum... well, they are pretty much unrelated to this question. I have done a lot of research on SQL queries and the only questions i've asked are related to areas that i have found very difficult to find an answer to.
So as a last resort i turned to this support forum, now i realise your doing this out of the kindness of your heart (i think) and for that im grateful. I too help people, but my area of expertise is with C++/Other languages, i on the other hand will help people regardless of the complexity of the problem, infact the more complex the better :p keeps things interesting.
In regards to my original point about no one being able to solve the problem... well, that was based on the posts inside this thread:
OK. I lied. This is complicated, but we are almost there...
He admitted himself that this was complicated, still being new to SQL i do not yet know all of its limits... but when a proffessional admits that something is complicated, i would say its a pretty good indication that it actually is complicated...
When he never replied after that post... i just assumed that he gave up and admitted defeat... i.e. its impossible to do.... atleast efficiently.
So perhaps next time, before jumping in and making assumptions... you should consider all the facts and why i might have said such words.|||When he never replied after that post... i just assumed that he gave up and admitted defeat... i.e. its impossible to do.... atleast efficiently.
I.....already put a lot of time in helping you with this.
YOU...are impatient and rude.
I....did not log in over the weekend to do your work for you.
YOU...can figure out the rest yourself, loser.|||I.....already put a lot of time in helping you with this.
YOU...are impatient and rude.
I....did not log in over the weekend to do your work for you.
YOU...can figure out the rest yourself, loser.
I.... was not intending to be rude
YOU.... Misinterpreted my point.. obviously thinking i was taking a dig at you or your skill when i was not...
I.... Appreciate all your help so far
YOU.... Probably won't even read this :p
It's up to you whether you help me or not i suppose... but just for the record... the only reason i may have seemed slightly impatient was due to the ever approaching deadline :confused:|||as the old saying goes, "failure to plan on your part does not constitute an emergency on our part"
i'm sorry about your homework assignment coming due real soon, but that's life
if you would like to start a new thread, we can close this one and let it fade into the archives...|||Several times you bumped this thread by implying that people here were either too lazy or too incompetent to help you.
And the fact that now it turns out that this was some sort of homework assignment irks me even more.|||Not to bump a thread that seems to have everyone up in arms, but...
I like to try to solve these "puzzles", just to see if I can.
I understand the question as:
Find the top 5 items sold, the top customer for that item, how much
that customer bought, and how much - in total - of the item sold.
Using my own data, I came up with this:
SELECT TOP 5 ORDER_DETAIL.PRODUCT,
ORDER_HEADER.SHIP_TO AS CUST_ID,
SUM(ORDER_DETAIL.SHIP_QTY) AS CUST_SOLD,
ITEM_TTL.TTL_SOLD
FROM ORDER_HEADER INNER JOIN
ORDER_DETAIL ON ORDER_HEADER.ORDER_ID = ORDER_DETAIL.ORDER_ID
INNER JOIN (SELECT TOP 5 ORDER_DETAIL.PRODUCT,
SUM(ORDER_DETAIL.SHIP_QTY) AS TTL_SOLD
FROM ORDER_HEADER INNER JOIN
ORDER_DETAIL ON
ORDER_HEADER.ORDER_ID=ORDER_DETAIL.ORDER_ID
WHERE (NOT (ORDER_HEADER.SHIP_DATE IS NULL))
GROUP BY ORDER_DETAIL.PRODUCT
ORDER BY SUM(ORDER_DETAIL.SHIP_QTY) DESC) AS ITEM_TTL ON ORDER_DETAIL.PRODUCT=ITEM_TTL.PRODUCT
WHERE (NOT (ORDER_HEADER.SHIP_DATE IS NULL)) AND (ORDER_DETAIL.PRODUCT IN
(SELECT TOP 5 ORDER_DETAIL.PRODUCT
FROM ORDER_HEADER INNER JOIN
ORDER_DETAIL ON
ORDER_HEADER.ORDER_ID = ORDER_DETAIL.ORDER_ID
WHERE (NOT (ORDER_HEADER.SHIP_DATE IS NULL))
GROUP BY ORDER_DETAIL.PRODUCT
ORDER BY SUM(ORDER_DETAIL.SHIP_QTY) DESC))
GROUP BY ORDER_HEADER.SHIP_TO, ORDER_DETAIL.PRODUCT, ITEM_TTL.TTL_SOLD
ORDER BY SUM(ORDER_DETAIL.SHIP_QTY) DESC
This returns:
Product Cust_ID Cust_Sold Ttl_Sold
F.........12.........1035......2700
B.........15.........672.......2457
D.........7.........1243......1972
C.........91.........814.......1757
A.........18.........593.......1549|||I suspect the TOP 5 in the outer query is redundant, since you have it included in your nested subquery.|||Several times you bumped this thread by implying that people here were either too lazy or too incompetent to help you.
And the fact that now it turns out that this was some sort of homework assignment irks me even more.
again suprise suprise you jump to conclusions that you think are right... i was NOT implying anything... if something can't be done... it can't be done... thats why i said what i did...
Not to bump a thread that seems to have everyone up in arms, but...
I like to try to solve these "puzzles", just to see if I can.
I understand the question as:
Find the top 5 items sold, the top customer for that item, how much
that customer bought, and how much - in total - of the item sold.
Using my own data, I came up with this:
Code:
SELECT TOP 5 ORDER_DETAIL.PRODUCT,
ORDER_HEADER.SHIP_TO AS CUST_ID,
SUM(ORDER_DETAIL.SHIP_QTY) AS CUST_SOLD,
ITEM_TTL.TTL_SOLD
FROM ORDER_HEADER INNER JOIN
ORDER_DETAIL ON ORDER_HEADER.ORDER_ID = ORDER_DETAIL.ORDER_ID
INNER JOIN (SELECT TOP 5 ORDER_DETAIL.PRODUCT,
SUM(ORDER_DETAIL.SHIP_QTY) AS TTL_SOLD
FROM ORDER_HEADER INNER JOIN
ORDER_DETAIL ON
ORDER_HEADER.ORDER_ID=ORDER_DETAIL.ORDER_ID
WHERE (NOT (ORDER_HEADER.SHIP_DATE IS NULL))
GROUP BY ORDER_DETAIL.PRODUCT
ORDER BY SUM(ORDER_DETAIL.SHIP_QTY) DESC) AS ITEM_TTL ON ORDER_DETAIL.PRODUCT=ITEM_TTL.PRODUCT
WHERE (NOT (ORDER_HEADER.SHIP_DATE IS NULL)) AND (ORDER_DETAIL.PRODUCT IN
(SELECT TOP 5 ORDER_DETAIL.PRODUCT
FROM ORDER_HEADER INNER JOIN
ORDER_DETAIL ON
ORDER_HEADER.ORDER_ID = ORDER_DETAIL.ORDER_ID
WHERE (NOT (ORDER_HEADER.SHIP_DATE IS NULL))
GROUP BY ORDER_DETAIL.PRODUCT
ORDER BY SUM(ORDER_DETAIL.SHIP_QTY) DESC))
GROUP BY ORDER_HEADER.SHIP_TO, ORDER_DETAIL.PRODUCT, ITEM_TTL.TTL_SOLD
ORDER BY SUM(ORDER_DETAIL.SHIP_QTY) DESC
This returns:
Product Cust_ID Cust_Sold Ttl_Sold
F.........12.........1035......2700
B.........15.........672.......2457
D.........7.........1243......1972
C.........91.........814.......1757
A.........18.........593.......1549
thank you very much mate for putting in the effort to solve this conundrum
:-). I appreciate it i really do ^^|||Am i assuming that no one knows the answer? :confused:
lol, kinda funny a problem that even professionals can't figure out :p
i on the other hand will help people regardless of the complexity of the problem,
When he never replied after that post... i just assumed that he gave up and admitted defeat...
YOU.... Probably won't even read this
You were acting like a complete twit. The fact that you seem oblivious to this I will chalk up to immaturity. Hopefully, you will soon grow up enough to be respectful to people who are volunteering their time and expertise to help you out.|||I suspect the TOP 5 in the outer query is redundant, since you have it included in your nested subquery.
I intended the top 5 in the outer query to pull the top 5 customers, while
the top 5 in the nested query is pulling just the top 5 products.sql
I have a slight problem, a query that i have written produces data with 2 primary keys the same... however, DINSTINCT wont work in this case as the rows are still different...
Is their a way to force 1 column to always be unique?
Heres the query:
SELECT TOP 5 ORDER_ITEM.ItemID AS 'Item ID', ITEM.ItemName AS 'Item Name',
(SELECT SUM(OrdItem2.ItemQuantity) FROM ORDER_ITEM OrdItem2
WHERE OrdItem2.ItemID = ORDER_ITEM.ItemID
) AS Total_Purchased, SUM(ORDER_ITEM.ItemQuantity) AS 'Customer Purchased',
CUSTOMER.customerForename AS 'Customer Forename',
CUSTOMER.customerSurname AS 'Customer Surname'
FROM ITEM, ORDER_ITEM, ORDER_T, CUSTOMER
WHERE ITEM.ItemID = ORDER_ITEM.ItemID
AND ORDER_ITEM.OrderID = ORDER_0510096.OrderID
AND ORDER_T.CustomerID = CUSTOMER.CustomerID
GROUP BY ORDER_ITEM.ItemID, ITEM.ItemName,
CUSTOMER.customerForename, CUSTOMER.customerSurname
ORDER BY Total_Purchased DESC
The query is supposed to select the TOP 5 Products sold as well as selecting the customer that purchased the greatest amount of that item and the amount they purchased.
Currently, i will get 2 duplicate rows (except for customers name and the items the purchased. Like this:
ItemID
8 36 30 Mathew Smith
8 36 6 Tony Wattage
Which is kinda annoying... is there anyway i can prevent this?
And also apart from the Where Joins... is there a more efficient way of writing this?
thx for reading :-)
--PhilkillsDoes no one know any way around this?|||Phil,
The SQL you have posted selecting:
ORDER_ITEM.ItemID AS 'Item ID',
ITEM.ItemName AS 'Item Name',
(SELECT SUM(OrdItem2.ItemQuantity)
...does not match with the columns in the dataset you say you are getting:
ItemID
8 36 30 Mathew Smith
8 36 6 Tony Wattage
People are reluctant to respond to threads where the poster has not taken the time to accurately describe the problem.
Also, your problem sounds more like a data issue than a coding issue.|||Phil,
The SQL you have posted selecting:
ORDER_ITEM.ItemID AS 'Item ID',
ITEM.ItemName AS 'Item Name',
(SELECT SUM(OrdItem2.ItemQuantity)
...does not match with the columns in the dataset you say you are getting:
ItemID
8 36 30 Mathew Smith
8 36 6 Tony Wattage
People are reluctant to respond to threads where the poster has not taken the time to accurately describe the problem.
Also, your problem sounds more like a data issue than a coding issue.
on the contrary, i left out the data on purpose as it was irrelevent... i was only trying to point out an example of whats wrong... in the "EXAMPLE"
ItemID
8 36 30 Mathew Smith
8 36 6 Tony Wattage
The parts in bold should only appear once.... and the data it should select should be Mathew smith who purchased 30 of that item... 36 is the total amount sold, what i need the query to do... is to only display the person who bought the highest amount of the item that is part of the top 5 best selling items.
another "EXAMPLE":
ItemID Total Sold Heighest Amount sold to 1 Customer Customers Name
1.........30.................30......... ......................Jim
2.........20.................20......... ......................Jam
3.........10.................10......... ......................Flim
4.........5..................5......... .......................Flam
5.........2..................2......... .......................Stam
In this case... only 1 person purchased the items in the top 5...
However, if 5 people purchased the same item... the query would return:
ItemID Total Sold Heighest Amount sold to 1 Customer Customers Name
1.........30.................5.......... .....................Jim
1.........30.................5.......... .....................Jam
1.........30.................5.......... .....................Flim
1.........30.................5.......... .....................Flam
1.........30.................10......... .....................Stam
Notice how it returned the TOP 1 Item instead of the TOP 5 items?
The data is correct... the query is wrong... which is why i asked if i could put a constraint on the data returned saying that ItemID MUST be unique... unless ofcourse there is a better way to do it ;p
I hope this more accurately describes the problem i am having. :-)
--Philkills|||You are making this more confusing than it should be.
Lets break it down into parts.
Does this code give you the top five records that you want, without the customer information?
SELECT TOP 5
ORDER_ITEM.ItemID AS 'Item ID',
ITEM.ItemName AS 'Item Name',
SUM(ORDER_ITEM.ItemQuantity) AS Total_Purchased
FROM ITEM
INNER JOIN ORDER_ITEM ON ITEM.ItemID = ORDER_ITEM.ItemID
GROUP BY ORDER_ITEM.ItemID,
ITEM.ItemName
ORDER BY SUM(ORDER_ITEM.ItemQuantity) DESC|||You are making this more confusing than it should be.
Lets break it down into parts.
Does this code give you the top five records that you want, without the customer information?
SELECT TOP 5
ORDER_ITEM.ItemID AS 'Item ID',
ITEM.ItemName AS 'Item Name',
SUM(ORDER_ITEM.ItemQuantity) AS Total_Purchased
FROM ITEM
INNER JOIN ORDER_ITEM ON ITEM.ItemID = ORDER_ITEM.ItemID
GROUP BY ORDER_ITEM.ItemID,
ITEM.ItemName
ORDER BY SUM(ORDER_ITEM.ItemQuantity) DESC
yep that code gives the top 5 items ^^|||...and now you want the top customer for each of those items?|||...and now you want the top customer for each of those items?
correct...|||OK. I lied. This is complicated, but we are almost there...
Does this return the top CustomerID for each of those orders?:
SELECT ORDER_ITEM.ItemID AS 'Item ID',
ITEM.ItemName AS 'Item Name',
SUM(ORDER_ITEM.ItemQuantity) AS Total_Purchased,
CustomerID =
(SELECT TOP 1
ORDER_T.CustomerID
FROM ORDER_ITEM CUSTOMER_ITEM
INNER JOIN ORDER_T ON CUSTOMER_ITEM.OrderID = ORDER_T.OrderID
INNER JOIN CUSTOMER ON ORDER_T.CustomerID = CUSTOMER.CustomerID
WHERE CUSTOMER_ITEM.ItemID = ORDER_ITEM.ItemID
GROUP BY ORDER_T.CustomerID
ORDER BY SUM(CUSTOMER_ITEM.ItemQuantity) DESC,
ORDER_T.CustomerID)
FROM ITEM
INNER JOIN ORDER_ITEM ON ITEM.ItemID = ORDER_ITEM.ItemID
GROUP BY ORDER_ITEM.ItemID,
ITEM.ItemName
ORDER BY SUM(ORDER_ITEM.ItemQuantity) DESC|||OK. I lied. This is complicated, but we are almost there...
Does this return the top CustomerID for each of those orders?:
SELECT ORDER_ITEM.ItemID AS 'Item ID',
ITEM.ItemName AS 'Item Name',
SUM(ORDER_ITEM.ItemQuantity) AS Total_Purchased,
CustomerID =
(SELECT TOP 1
ORDER_T.CustomerID
FROM ORDER_ITEM CUSTOMER_ITEM
INNER JOIN ORDER_T ON CUSTOMER_ITEM.OrderID = ORDER_T.OrderID
INNER JOIN CUSTOMER ON ORDER_T.CustomerID = CUSTOMER.CustomerID
WHERE CUSTOMER_ITEM.ItemID = ORDER_ITEM.ItemID
GROUP BY ORDER_T.CustomerID
ORDER BY SUM(CUSTOMER_ITEM.ItemQuantity) DESC,
ORDER_T.CustomerID)
FROM ITEM
INNER JOIN ORDER_ITEM ON ITEM.ItemID = ORDER_ITEM.ItemID
GROUP BY ORDER_ITEM.ItemID,
ITEM.ItemName
ORDER BY SUM(ORDER_ITEM.ItemQuantity) DESC
Theres no table called CUSTOMER_ITEM... However, i have edited it to return the top customerID correctly.
SELECT ORDER_ITEM.ItemID AS 'Item ID',
ITEM.ItemName AS 'Item Name',
SUM(ORDER_ITEM.ItemQuantity) AS Total_Purchased,
CustomerID =
(SELECT TOP 1
ORDER_T.CustomerID
FROM ITEM
INNER JOIN ORDER_ITEMOrdItem2 ON ITEM.ItemID = OrdItem2.ItemID
INNER JOIN ORDER_T ON OrdItem2.OrderID = ORDER_T.OrderID
INNER JOIN CUSTOMER ON ORDER_T.CustomerID = CUSTOMER.CustomerID
WHERE OrdItem2.ItemID = ORDER_ITEM.ItemID
GROUP BY ORDER_T.CustomerID
ORDER BY SUM(ORDER_ITEM.ItemQuantity) DESC,
ORDER.CustomerID)
FROM ITEM
INNER JOIN ORDER_ITEM ON ITEM.ItemID = ORDER_ITEM.ItemID
GROUP BY ORDER_ITEM.ItemID,
ITEM_0510096.ItemName
ORDER BY SUM(ORDER_ITEM.ItemQuantity) DESC|||ok, so i can display the customer ID... now how do i display the rest of the customer info ... ^_^|||Am i assuming that no one knows the answer? :confused:
lol, kinda funny a problem that even professionals can't figure out :p|||lol, kinda funny a problem that even professionals can't figure out :poh stop
yes, there are a lot of professionals here
what makes you think they want to be your personal free development center?
ask a question, get an answer, but keep modifying your questions to get more and more and more support as you struggle your way through something that seems to be quite challenging for you... well, there eventually comes a point where people will just go on to the next person
good luck, phil
:)|||oh stop
yes, there are a lot of professionals here
what makes you think they want to be your personal free development center?
ask a question, get an answer, but keep modifying your questions to get more and more and more support as you struggle your way through something that seems to be quite challenging for you... well, there eventually comes a point where people will just go on to the next person
good luck, phil
:)
Eh?, i asked 1 question in this thread... and simply gave more info... in my other posts...
If your refering to my other questions that i've asked around the forum... well, they are pretty much unrelated to this question. I have done a lot of research on SQL queries and the only questions i've asked are related to areas that i have found very difficult to find an answer to.
So as a last resort i turned to this support forum, now i realise your doing this out of the kindness of your heart (i think) and for that im grateful. I too help people, but my area of expertise is with C++/Other languages, i on the other hand will help people regardless of the complexity of the problem, infact the more complex the better :p keeps things interesting.
In regards to my original point about no one being able to solve the problem... well, that was based on the posts inside this thread:
OK. I lied. This is complicated, but we are almost there...
He admitted himself that this was complicated, still being new to SQL i do not yet know all of its limits... but when a proffessional admits that something is complicated, i would say its a pretty good indication that it actually is complicated...
When he never replied after that post... i just assumed that he gave up and admitted defeat... i.e. its impossible to do.... atleast efficiently.
So perhaps next time, before jumping in and making assumptions... you should consider all the facts and why i might have said such words.|||When he never replied after that post... i just assumed that he gave up and admitted defeat... i.e. its impossible to do.... atleast efficiently.
I.....already put a lot of time in helping you with this.
YOU...are impatient and rude.
I....did not log in over the weekend to do your work for you.
YOU...can figure out the rest yourself, loser.|||I.....already put a lot of time in helping you with this.
YOU...are impatient and rude.
I....did not log in over the weekend to do your work for you.
YOU...can figure out the rest yourself, loser.
I.... was not intending to be rude
YOU.... Misinterpreted my point.. obviously thinking i was taking a dig at you or your skill when i was not...
I.... Appreciate all your help so far
YOU.... Probably won't even read this :p
It's up to you whether you help me or not i suppose... but just for the record... the only reason i may have seemed slightly impatient was due to the ever approaching deadline :confused:|||as the old saying goes, "failure to plan on your part does not constitute an emergency on our part"
i'm sorry about your homework assignment coming due real soon, but that's life
if you would like to start a new thread, we can close this one and let it fade into the archives...|||Several times you bumped this thread by implying that people here were either too lazy or too incompetent to help you.
And the fact that now it turns out that this was some sort of homework assignment irks me even more.|||Not to bump a thread that seems to have everyone up in arms, but...
I like to try to solve these "puzzles", just to see if I can.
I understand the question as:
Find the top 5 items sold, the top customer for that item, how much
that customer bought, and how much - in total - of the item sold.
Using my own data, I came up with this:
SELECT TOP 5 ORDER_DETAIL.PRODUCT,
ORDER_HEADER.SHIP_TO AS CUST_ID,
SUM(ORDER_DETAIL.SHIP_QTY) AS CUST_SOLD,
ITEM_TTL.TTL_SOLD
FROM ORDER_HEADER INNER JOIN
ORDER_DETAIL ON ORDER_HEADER.ORDER_ID = ORDER_DETAIL.ORDER_ID
INNER JOIN (SELECT TOP 5 ORDER_DETAIL.PRODUCT,
SUM(ORDER_DETAIL.SHIP_QTY) AS TTL_SOLD
FROM ORDER_HEADER INNER JOIN
ORDER_DETAIL ON
ORDER_HEADER.ORDER_ID=ORDER_DETAIL.ORDER_ID
WHERE (NOT (ORDER_HEADER.SHIP_DATE IS NULL))
GROUP BY ORDER_DETAIL.PRODUCT
ORDER BY SUM(ORDER_DETAIL.SHIP_QTY) DESC) AS ITEM_TTL ON ORDER_DETAIL.PRODUCT=ITEM_TTL.PRODUCT
WHERE (NOT (ORDER_HEADER.SHIP_DATE IS NULL)) AND (ORDER_DETAIL.PRODUCT IN
(SELECT TOP 5 ORDER_DETAIL.PRODUCT
FROM ORDER_HEADER INNER JOIN
ORDER_DETAIL ON
ORDER_HEADER.ORDER_ID = ORDER_DETAIL.ORDER_ID
WHERE (NOT (ORDER_HEADER.SHIP_DATE IS NULL))
GROUP BY ORDER_DETAIL.PRODUCT
ORDER BY SUM(ORDER_DETAIL.SHIP_QTY) DESC))
GROUP BY ORDER_HEADER.SHIP_TO, ORDER_DETAIL.PRODUCT, ITEM_TTL.TTL_SOLD
ORDER BY SUM(ORDER_DETAIL.SHIP_QTY) DESC
This returns:
Product Cust_ID Cust_Sold Ttl_Sold
F.........12.........1035......2700
B.........15.........672.......2457
D.........7.........1243......1972
C.........91.........814.......1757
A.........18.........593.......1549|||I suspect the TOP 5 in the outer query is redundant, since you have it included in your nested subquery.|||Several times you bumped this thread by implying that people here were either too lazy or too incompetent to help you.
And the fact that now it turns out that this was some sort of homework assignment irks me even more.
again suprise suprise you jump to conclusions that you think are right... i was NOT implying anything... if something can't be done... it can't be done... thats why i said what i did...
Not to bump a thread that seems to have everyone up in arms, but...
I like to try to solve these "puzzles", just to see if I can.
I understand the question as:
Find the top 5 items sold, the top customer for that item, how much
that customer bought, and how much - in total - of the item sold.
Using my own data, I came up with this:
Code:
SELECT TOP 5 ORDER_DETAIL.PRODUCT,
ORDER_HEADER.SHIP_TO AS CUST_ID,
SUM(ORDER_DETAIL.SHIP_QTY) AS CUST_SOLD,
ITEM_TTL.TTL_SOLD
FROM ORDER_HEADER INNER JOIN
ORDER_DETAIL ON ORDER_HEADER.ORDER_ID = ORDER_DETAIL.ORDER_ID
INNER JOIN (SELECT TOP 5 ORDER_DETAIL.PRODUCT,
SUM(ORDER_DETAIL.SHIP_QTY) AS TTL_SOLD
FROM ORDER_HEADER INNER JOIN
ORDER_DETAIL ON
ORDER_HEADER.ORDER_ID=ORDER_DETAIL.ORDER_ID
WHERE (NOT (ORDER_HEADER.SHIP_DATE IS NULL))
GROUP BY ORDER_DETAIL.PRODUCT
ORDER BY SUM(ORDER_DETAIL.SHIP_QTY) DESC) AS ITEM_TTL ON ORDER_DETAIL.PRODUCT=ITEM_TTL.PRODUCT
WHERE (NOT (ORDER_HEADER.SHIP_DATE IS NULL)) AND (ORDER_DETAIL.PRODUCT IN
(SELECT TOP 5 ORDER_DETAIL.PRODUCT
FROM ORDER_HEADER INNER JOIN
ORDER_DETAIL ON
ORDER_HEADER.ORDER_ID = ORDER_DETAIL.ORDER_ID
WHERE (NOT (ORDER_HEADER.SHIP_DATE IS NULL))
GROUP BY ORDER_DETAIL.PRODUCT
ORDER BY SUM(ORDER_DETAIL.SHIP_QTY) DESC))
GROUP BY ORDER_HEADER.SHIP_TO, ORDER_DETAIL.PRODUCT, ITEM_TTL.TTL_SOLD
ORDER BY SUM(ORDER_DETAIL.SHIP_QTY) DESC
This returns:
Product Cust_ID Cust_Sold Ttl_Sold
F.........12.........1035......2700
B.........15.........672.......2457
D.........7.........1243......1972
C.........91.........814.......1757
A.........18.........593.......1549
thank you very much mate for putting in the effort to solve this conundrum
:-). I appreciate it i really do ^^|||Am i assuming that no one knows the answer? :confused:
lol, kinda funny a problem that even professionals can't figure out :p
i on the other hand will help people regardless of the complexity of the problem,
When he never replied after that post... i just assumed that he gave up and admitted defeat...
YOU.... Probably won't even read this
You were acting like a complete twit. The fact that you seem oblivious to this I will chalk up to immaturity. Hopefully, you will soon grow up enough to be respectful to people who are volunteering their time and expertise to help you out.|||I suspect the TOP 5 in the outer query is redundant, since you have it included in your nested subquery.
I intended the top 5 in the outer query to pull the top 5 customers, while
the top 5 in the nested query is pulling just the top 5 products.sql
Forcing Primary Keys
Hi all,
As our DB has no primary keys or indexes ive taken a copy of all populated tables and tried to force primary keys within a new DB.
the problem is all off the tables have multiple datasets within them, a dataset for each year. This causes all instances of ID numbers to not be unique as they are replicated for every year they are active.
Its a school database so a student who has been here for 3 years will have 3 instances of his ID number, one for each years' data set.
So how do i force primary keys if there is no unique identifier? ive been highlighting both data set and ID columns and setting that combination as the primary key.
Essentially i need to analyse the relationships between the tabls in a diagram and also run some speed tests to see how fast the db works when it has indexes and primary keys.
the reason im writing is that ive done this on ten tables and with another 160 to do im just checking im doing the right thing?
gregCreate a composite primary key of student ID and year number.|||thought so,
ta
greg|||Why do you keep enrollment info (a record for each year of enrollment) in the master table? StudentID should be the only PK in StudentsMaster, and Enrollment should have StudentID as FK.|||Could you create views for each year and put a unique index on each view?|||Yes, you CAN.
No, you SHOULDN'T.|||rdjabarov its not my design, its just the way the company programmed it, its a very bad system, ive alreday had to weed out 400+ tables that werent being used, and it seems instead of introducing foreign keys to child tables they used the studentId and the SetId,
peterlemonjello, i didnt know you could do that, well at least in sql server 2000, thought it was a 2005 feature...ill look into that
blindman, i had read it wasn't a good idea...ill think of an alternative
greg|||Where ever did you read that? Tables need primary keys, and if they don't have a natural unary key then you either create a surrogate key or use a composite key. Creating indexed views would be an odd alternative.|||well this is the thing, im not trying to fix the db so it functions- im just truying to analyse the relationships between tables and see how much faster introducing keys and indexes make my queries run...
as you can imagine the company released the software with no primary keys and expect it to work but im not about to try and fix there mistakes...its purely for my own use...
i really cant believe they have released software like this but i have to work with what i inhereted off my predecessor
greg|||It will run faster if it is indexed, especially clustered indexes as associated with primary keys.
No need to test this concept...
What's more, you can throw indexes on it without affecting the functioning of the operation. You cannot throw constraints on the tables (unique indexes, for example, or primary keys) without potentially causing failures in the crappy code which is doubtless used to access the data.|||Hmmm, really? I wouldn't be so certain, especially without seeing the database, and without knowing what indexes are to be created and what their definitions are. I've seen "index seek" being more expensive than table scan on multiple occasions (of course because of the poor db and/or query design).|||Nothing is certain in life except death and taxes, but the benefits of indexing a table come damn close.|||In general that might be true, but then you find a table with 947 indexes, all of which have the first seven columns... Then discover that only the leftmost index column is ever used in queries!
-PatP|||Yeah, yeah,...|||I've seen "index seek" being more expensive than table scan on multiple occasions (of course because of the poor db and/or query design).The only time I've seen this is as a result of parameter sniffing. Are there other reasons this can occur? ... actually thinking about it now I guess a poorly chosen index (e.g. low selectivity) and an equally poor plan on the part of the optimiser might cause this.
BTW - I am probably just being a pedant but if there are no primary keys then there are no relationships. You will not be investigating the relationships of the tables - you will be creating the relationships. I imagine this is not helpful to the issue in hand at all :)|||Hi all,
yes bit of a can of worms here, to summarize it is the relationships im interested in, i wanna see how the tables should be connected by matching up similar indexes so although ill be cretaing the relationships, as most tables only have one index, it should be pretty close to the original design...
the problem is i need to prove to the management that my systems (access mde's,ade's accessing sql backend) are faster than the db we pay for because there is no primary keys or relationships..and was hoping that by recreating the relationships i could run speed tests to compare against...
cheers
greg|||Relationships don't affect the speed of your db directly. Relationships are logical constraints - they merely ensure your data conforms to certain constraints. As such - you are quite likely to find a fair slew of invalid intries in your tables since these contraints have not existed previously.
However - relationships are typically between primary and foreign keys. Both of these should be indexed. It is these indexes that should be likely to improve the speed of your queries.
HTH
As our DB has no primary keys or indexes ive taken a copy of all populated tables and tried to force primary keys within a new DB.
the problem is all off the tables have multiple datasets within them, a dataset for each year. This causes all instances of ID numbers to not be unique as they are replicated for every year they are active.
Its a school database so a student who has been here for 3 years will have 3 instances of his ID number, one for each years' data set.
So how do i force primary keys if there is no unique identifier? ive been highlighting both data set and ID columns and setting that combination as the primary key.
Essentially i need to analyse the relationships between the tabls in a diagram and also run some speed tests to see how fast the db works when it has indexes and primary keys.
the reason im writing is that ive done this on ten tables and with another 160 to do im just checking im doing the right thing?
gregCreate a composite primary key of student ID and year number.|||thought so,
ta
greg|||Why do you keep enrollment info (a record for each year of enrollment) in the master table? StudentID should be the only PK in StudentsMaster, and Enrollment should have StudentID as FK.|||Could you create views for each year and put a unique index on each view?|||Yes, you CAN.
No, you SHOULDN'T.|||rdjabarov its not my design, its just the way the company programmed it, its a very bad system, ive alreday had to weed out 400+ tables that werent being used, and it seems instead of introducing foreign keys to child tables they used the studentId and the SetId,
peterlemonjello, i didnt know you could do that, well at least in sql server 2000, thought it was a 2005 feature...ill look into that
blindman, i had read it wasn't a good idea...ill think of an alternative
greg|||Where ever did you read that? Tables need primary keys, and if they don't have a natural unary key then you either create a surrogate key or use a composite key. Creating indexed views would be an odd alternative.|||well this is the thing, im not trying to fix the db so it functions- im just truying to analyse the relationships between tables and see how much faster introducing keys and indexes make my queries run...
as you can imagine the company released the software with no primary keys and expect it to work but im not about to try and fix there mistakes...its purely for my own use...
i really cant believe they have released software like this but i have to work with what i inhereted off my predecessor
greg|||It will run faster if it is indexed, especially clustered indexes as associated with primary keys.
No need to test this concept...
What's more, you can throw indexes on it without affecting the functioning of the operation. You cannot throw constraints on the tables (unique indexes, for example, or primary keys) without potentially causing failures in the crappy code which is doubtless used to access the data.|||Hmmm, really? I wouldn't be so certain, especially without seeing the database, and without knowing what indexes are to be created and what their definitions are. I've seen "index seek" being more expensive than table scan on multiple occasions (of course because of the poor db and/or query design).|||Nothing is certain in life except death and taxes, but the benefits of indexing a table come damn close.|||In general that might be true, but then you find a table with 947 indexes, all of which have the first seven columns... Then discover that only the leftmost index column is ever used in queries!
-PatP|||Yeah, yeah,...|||I've seen "index seek" being more expensive than table scan on multiple occasions (of course because of the poor db and/or query design).The only time I've seen this is as a result of parameter sniffing. Are there other reasons this can occur? ... actually thinking about it now I guess a poorly chosen index (e.g. low selectivity) and an equally poor plan on the part of the optimiser might cause this.
BTW - I am probably just being a pedant but if there are no primary keys then there are no relationships. You will not be investigating the relationships of the tables - you will be creating the relationships. I imagine this is not helpful to the issue in hand at all :)|||Hi all,
yes bit of a can of worms here, to summarize it is the relationships im interested in, i wanna see how the tables should be connected by matching up similar indexes so although ill be cretaing the relationships, as most tables only have one index, it should be pretty close to the original design...
the problem is i need to prove to the management that my systems (access mde's,ade's accessing sql backend) are faster than the db we pay for because there is no primary keys or relationships..and was hoping that by recreating the relationships i could run speed tests to compare against...
cheers
greg|||Relationships don't affect the speed of your db directly. Relationships are logical constraints - they merely ensure your data conforms to certain constraints. As such - you are quite likely to find a fair slew of invalid intries in your tables since these contraints have not existed previously.
However - relationships are typically between primary and foreign keys. Both of these should be indexed. It is these indexes that should be likely to improve the speed of your queries.
HTH
Friday, February 24, 2012
for each record updated
Currently in my database I have Widgets. These Widgest can be linked
together through keys in a Link table. Think in terms of hierarchy. A
parent Widget has child Widgets linked to it. These child Widgets when
linked take on properties(field values) of the parent Widgets. I have a
trigger that fires when a property(field) is changed in the parent
Widget that changes that property(field) in all the child Widgets. I
want to add to that trigger so that the trigger will also insert a new
record into a log table for each record updated. Here is some example
code:
SELECT @.widget_id = widget_id, @.status = status
FROM inserted
BEGIN
update widget
set status = @.status, updated_date = @.current_date
where widget_id in (select linked_id
from link
where topic = 'LINK'
and log_type = 'CLONE'
and id = @.widget_id)
and defect_status != 'Complete'
END
For each of these children widgets updated I want to insert a record
into a log table with the widget_id and the linked_id as fields. Anyone
have any ideas. I hope this is somewhat clear.
JakeHello Jake !
To direct you to the right solution some DDL would be very nice,
because we don=B4t know your table structure and how the data is stored.
http://www.aspfaq.com/5006
After posting we will be pleased to help you :-D
HTH, Jens Suessmeyer.|||> SELECT @.widget_id = widget_id, @.status = status
> FROM inserted
Triggers are in their essence set-based. You just made yours lose that
wonderful ability.
You don't need variables to propagate changes from one table to another:
update <destination_table>
set <dest_column_1> = <src_column_1>
..
from inserted
<join> <destination_table>
on <destination_table>.<common_key> =
inserted.<common_key>
ML
together through keys in a Link table. Think in terms of hierarchy. A
parent Widget has child Widgets linked to it. These child Widgets when
linked take on properties(field values) of the parent Widgets. I have a
trigger that fires when a property(field) is changed in the parent
Widget that changes that property(field) in all the child Widgets. I
want to add to that trigger so that the trigger will also insert a new
record into a log table for each record updated. Here is some example
code:
SELECT @.widget_id = widget_id, @.status = status
FROM inserted
BEGIN
update widget
set status = @.status, updated_date = @.current_date
where widget_id in (select linked_id
from link
where topic = 'LINK'
and log_type = 'CLONE'
and id = @.widget_id)
and defect_status != 'Complete'
END
For each of these children widgets updated I want to insert a record
into a log table with the widget_id and the linked_id as fields. Anyone
have any ideas. I hope this is somewhat clear.
JakeHello Jake !
To direct you to the right solution some DDL would be very nice,
because we don=B4t know your table structure and how the data is stored.
http://www.aspfaq.com/5006
After posting we will be pleased to help you :-D
HTH, Jens Suessmeyer.|||> SELECT @.widget_id = widget_id, @.status = status
> FROM inserted
Triggers are in their essence set-based. You just made yours lose that
wonderful ability.
You don't need variables to propagate changes from one table to another:
update <destination_table>
set <dest_column_1> = <src_column_1>
..
from inserted
<join> <destination_table>
on <destination_table>.<common_key> =
inserted.<common_key>
ML
Subscribe to:
Posts (Atom)