Foreign Key

I would like to create a foreign key but the Primary table has 2 fields as it Primary Key. Is there a way to create a Foreign Key that links only on one field of the primary key.

Ex: table 1: id int , language char(2), description varchar(100) PK = ID + language

table 2 : id int, idlanguage int PK = id FK (idLanguage refers to id from table 1)

This cause an error because the foreign key does not include all part of the primary key.

Rufen

If your table1.id is unique, then create primary key only on that column, if not, then you should add laguage in your table2 column because there will be no way to distinguish between languages that have the same id.
|||

I know that there will be no way to distinguish all records that have the same id, but that is what I want. When I delete a record from Table 1, I want to delete all record from table 2 that have this id (foreign from table 1).

|||

You can implement the foreign key logic using triggers.

For eg. For Delete

CREATE TRIGGER trg

ON table1

FOR DELETE

AS

BEGIN

DELETE FROM table2

WHERE idlanguage in (SELECT id FROM deleted)

END

You can have similar trigger for insert and update

Foreign Key

I would like to create a foreign key but the Primary table has 2 fields as it Primary Key. Is there a way to create a Foreign Key that links only on one field of the primary key.

Ex: table 1: id int , language char(2), description varchar(100) PK = ID + language

table 2 : id int, idlanguage int PK = id FK (idLanguage refers to id from table 1)

This cause an error because the foreign key does not include all part of the primary key.

Rufen

If your table1.id is unique, then create primary key only on that column, if not, then you should add laguage in your table2 column because there will be no way to distinguish between languages that have the same id.
|||

I know that there will be no way to distinguish all records that have the same id, but that is what I want. When I delete a record from Table 1, I want to delete all record from table 2 that have this id (foreign from table 1).

|||

You can implement the foreign key logic using triggers.

For eg. For Delete

CREATE TRIGGER trg

ON table1

FOR DELETE

AS

BEGIN

DELETE FROM table2

WHERE idlanguage in (SELECT id FROM deleted)

END

You can have similar trigger for insert and update

sql

foreign key

i want to make primary key and foreign key relationship of table A1 and table B1 but A1 exist in database A and B1 exist in database B
column name u can pretain as C1, C2

'Normally', related tables live within the same database.

The Foreign Key constraint declaration doesn't go outside the scope of the database, so in this case you can't declare a FK constraint.

AFAIK, the option you have to enforce cross-database FK relationships, is by using triggers.

/Kenneth

|||

You can’t create a constraint across the database. But there is a workaround available to fix your issue. Using Instead of trigger / for after trigger. But I recommend to use the Instead of Trigger rather than after trigger..

Code Snippet

Use DB1

Go

Create table A

(

ID int Primary Key,

Name varchar(100)

)

Go

Code Snippet

Use DB2

Go

Create table BB

(

Id int,

[Desc] varchar(100)

)

Go

CreateTrigger BB_Triger

on BBInstead of Insert

as

Begin

Insert Into BB

Select * from Inserted as ins Where Exists (Select 1 From DB1..A a Where a.id = ins.id)

End

/*

--use any one

Create Trigger BB_Triger

on BBAfter Insert

as

Begin

Delete from BB

Where NOT EXISTS (Select 1 From DB1..A a Where a.id = BB.id)

End

*/

GO

Code Snippet

Insert Into DB1..A values(1,'One')

Insert Into DB2..A values(2,'Two')

Code Snippet

Insert Into DB2..BB values(1,'Valid')

select * from BB

Insert Into DB2..BB values(4,'In Valid')

select * from BB

|||Thanks good idea

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

Razvan

foreign and primary key question

OK - I have a two tables in a database. Table one contains an ID, 'oneID', field as the primary key. It is auto-incremented. Table two has ID field, 'twoID', as the primary key. This field also auto-increments. Table two also has 'oneID' as the foreign key.

Now, my question is, how do I get the foreign key in table two to auto-increment in conjuction with table one's primary key? They are after all the same data. Do I have to manually code to get table one data and save it to table two data?

thanks

Yes you'd have to manually INSERT the data into the other table. By setting up the PL-FK constraint you are just setting up a "relation" between the tables so any inserts/updates/deletes into the tables are checked for their data consistency.

Assuming your first INSERT is going through a stored proc, get the ID of the value just inserted via SCOPE_IDENTITY() and immediately do the INSERT into the second table. You could also do this via triggers but I dont recommend it. they are a big performance overhead and drag your system.

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

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

FOR XML

Hello
I'm performing a query against a db with "for xml auto, elements" clause
Something like
Table
Field 0 Setas Primary Key
Field 1
Field 2
SELECT field1, field2 FROM table FOR XML AUTO,ELEMENTS
If I run the query into Query Analyzer I get the result I aspect
<table>
<field1>value</field1>
<field2>value</field2>
<table>
<table>
<field1>value</field1>
<field2>value</field2>
<table>
but if I run the same query from a .vbs script (using wscript to launch it)
I get the following
<table>
<field0>value</field0>
<field1>value</field1>
<field2>value</field2>
<table>
<table>
<field0>value</field0>
<field1>value</field1>
<field2>value</field2>
<table>
The primary key is automatically inserted in the xml
Does somebody know the reason? how to workaround it? or to control it?
ThanksPerhaps your process needs to more specifically map the elements:
http://www.eggheadcafe.com/articles/20030804.asp
2005 Microsoft MVP C#
Robbe Morris
http://www.robbemorris.com
http://www.masterado.net/home/listings.aspx
"Denis" <dzoddi@.mvmnet.com> wrote in message
news:%23%23A2vhuKFHA.4092@.tk2msftngp13.phx.gbl...
> Hello
> I'm performing a query against a db with "for xml auto, elements" clause
> Something like
> Table
> Field 0 Setas Primary Key
> Field 1
> Field 2
> SELECT field1, field2 FROM table FOR XML AUTO,ELEMENTS
> If I run the query into Query Analyzer I get the result I aspect
> <table>
> <field1>value</field1>
> <field2>value</field2>
> <table>
> <table>
> <field1>value</field1>
> <field2>value</field2>
> <table>
> but if I run the same query from a .vbs script (using wscript to launch
> it) I get the following
> <table>
> <field0>value</field0>
> <field1>value</field1>
> <field2>value</field2>
> <table>
> <table>
> <field0>value</field0>
> <field1>value</field1>
> <field2>value</field2>
> <table>
> The primary key is automatically inserted in the xml
> Does somebody know the reason? how to workaround it? or to control it?
> Thanks
>
>