Multiple inserts against single insert from temp table

Hi,

I have table (AttributeID INT NOT NULL, ValueID INT NOT NULL), both of them are foreign keys. I need replace several rows which have same AttributeID and various ValueID with new values. So I must delete all rows WHERE AttributeID=@.AttributeID and then insert new values. What is better,

for every row make one call to SP which will do single INSERT;

or supplies all ValueIDs as single parameter to SP and then extracts ValueIDs them to temp table and then do INSERT MyTable SELECT * FROM #TempTable.

Which solution will be faster ? Or do you have idea for other solution which will outperform those two ?

Much thanks for your replies.

In general, a set base solution (i.e. single dml that affects more than one row at a time) is faster and better than row-per-row. So, if you have lots of rows to be processed, putting the data into a temp table (i.e. staging table) then invoking a single dml command would give you the most bang for your buck.

Multiple Foreign Keys on Same Table

Hi,

I have an Orders Table that has Employee1 and Employee2 (one is the
sales rep the other is the telemarketing rep) Both of these fields
need to cascade update against the Employees table. I can't seem to
create the desired relationship in a Diagram and I'm not sure how best
to set this up. Any ideas?

Thanks in advance...Don't use the diagram to do this. Use a script:

alter table MyTable
add
constraint FK1_MyTable foreign key (Employee1) references Employees
(EmployeeID)
on update cascade
, constraint FK2_MyTable foreign key (Employee2) references Employees
(EmployeeID)
on update cascade

--
Tom

----------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"FreeToGolfAndSki" <wlr@.genoagroup.com> wrote in message
news:1143301309.330185.72720@.z34g2000cwc.googlegro ups.com...
Hi,

I have an Orders Table that has Employee1 and Employee2 (one is the
sales rep the other is the telemarketing rep) Both of these fields
need to cascade update against the Employees table. I can't seem to
create the desired relationship in a Diagram and I'm not sure how best
to set this up. Any ideas?

Thanks in advance...|||It isn't what you asked for, but a better solution is to have an
Employees table, A "roles" table, and a Employeesroles table.

So, one employee row for every person.
Roles would start with two rows, "Sales Rep" and "Telemarketing"
and Employeesroles would track the relationships of which people were
reps, and which were telemarketing, and which were both.|||Thanks for all the help. I will set up triggers for now but will
redesign later - that's best in the long run.

Again, many thinks for the help!!!|||Tom Moreau (tom@.dont.spam.me.cips.ca) writes:
> Don't use the diagram to do this. Use a script:
> alter table MyTable
> add
> constraint FK1_MyTable foreign key (Employee1) references Employees
> (EmployeeID)
> on update cascade
> , constraint FK2_MyTable foreign key (Employee2) references Employees
> (EmployeeID)
> on update cascade

Alas, this leads to the multiple cascade paths error:

CREATE TABLE Employees (EmployeeID int NOT NULL PRIMARY KEY)

CREATE TABLE MyTable (OrderID int NOT NULL PRIMARY KEY,
Employee1 int NULL,
Employee2 int NULL)
go
alter table MyTable
add
constraint FK1_MyTable foreign key (Employee1) references Employees
(EmployeeID)
on update cascade
, constraint FK2_MyTable foreign key (Employee2) references Employees
(EmployeeID)
on update cascade

go
DROP TABLE MyTable, Employees

Adding a Roles table as usggested by Doug may be a good idea, but it
is not going to resolve this problem, as long as both salesrep and
telemarketing rep are defined in the same table.

The simplest solution, is probably to have employeeids that cannot
change.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||I would have tried the code out, but alas - no DDL.

--
Tom

----------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns9791CE37C445BYazorman@.127.0.0.1...
Tom Moreau (tom@.dont.spam.me.cips.ca) writes:
> Don't use the diagram to do this. Use a script:
> alter table MyTable
> add
> constraint FK1_MyTable foreign key (Employee1) references Employees
> (EmployeeID)
> on update cascade
> , constraint FK2_MyTable foreign key (Employee2) references Employees
> (EmployeeID)
> on update cascade

Alas, this leads to the multiple cascade paths error:

CREATE TABLE Employees (EmployeeID int NOT NULL PRIMARY KEY)

CREATE TABLE MyTable (OrderID int NOT NULL PRIMARY KEY,
Employee1 int NULL,
Employee2 int NULL)
go
alter table MyTable
add
constraint FK1_MyTable foreign key (Employee1) references Employees
(EmployeeID)
on update cascade
, constraint FK2_MyTable foreign key (Employee2) references Employees
(EmployeeID)
on update cascade

go
DROP TABLE MyTable, Employees

Adding a Roles table as usggested by Doug may be a good idea, but it
is not going to resolve this problem, as long as both salesrep and
telemarketing rep are defined in the same table.

The simplest solution, is probably to have employeeids that cannot
change.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||The whole cascading delete issue is a major pain in the ass that I
can't believe MS didn't fix in SQL 2005. If we can manually write
triggers to do so, the SQL Server should just be able to handle the
*possibility* of cascading paths and just throw an error when there
actually *were* cascading paths.|||pb648174 (google@.webpaul.net) writes:
> The whole cascading delete issue is a major pain in the ass that I
> can't believe MS didn't fix in SQL 2005. If we can manually write
> triggers to do so, the SQL Server should just be able to handle the
> *possibility* of cascading paths and just throw an error when there
> actually *were* cascading paths.

I remember in 1998 when I attendend a roadshow for SQL Server 7, and I
complained to a Technical Evangelist that it was a pity that SQL 7 would
not have cascading updates and deletes. His reply was that he cried the
day the feature was cut.

When I eventually arrived on SQL 2000 (we were stuck on SQL 6.5 a little
too long), I no longer had any desire for them. All our foreign constraints
are NO ACTION, and we don't use triggers to implement cascading deletes.
Cascading updates? We hardly ever update primary keys. (When it happens,
it's a matter of special-case jobs).

That said, the restrictions on cascading updates/deletes in SQL Server
are indeed a bit ridiculous, and some of them have a smell of that
the SQL Server team ran out of time for SQL 2000, and had to be more
conservative than necessary. But that's nor really an excuse for SQL 2005.

Anyway, what you should to is to go
http://lab.msdn.microsoft.com/productfeedback/ and submit a suggestion
that the rules for cascading should be relaxed for the next release.
I thought that there would already be such suggestions, but strangely
there is not.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||I have created a new one... Peoples rise up!|||This problem is harder than people think to do in the general case.
Ever have a course in Graph Theory? Remember some of the NP-Complete
problems at the end of that course?

Consider a set of paths between two nodes { A -> B, B -> A }. Pretty
easy to see that you can have an endless loop with just two tables. But
I will need to dtect ALL cycles of ANY size in any schema to avoid this
for the genral case

Consider a set of paths on one node { A -> A } , the smallest cycle
possible. You wipe out a whole table, set everything to one value or
hang in a loop. A.x changes A.y, and A.y changes A.x .. what does the
table look like after the constraints fire?

Consider a set of paths among three nodes { A -> B, A -> C, B-> C }
since C can be changed by both A and B, which one takes effect in a
declarative language whose statements are supposed to be independent of
an order of execution?

An early version of DB2 would allow this particular set of REFERENCES,
but the results were unpredictable -- the last change would persist.
The reason that you can do some of this with triggers is that they are
procedural and have a fixed order of execution. Of course the
optimizer cannot use them in a plan and it is non-declarative.|||Well that's why they get paid more than I do...|||I am afraid that throwing money at an NP-Complete problem is not a good
idea :)

Multiple Foreign Keys on Same Table

Hi,

I have an Orders Table that has Employee1 and Employee2 (one is the
sales rep the other is the telemarketing rep) Both of these fields
need to cascade update against the Employees table. I can't seem to
create the desired relationship in a Diagram and I'm not sure how best
to set this up. Any ideas?

Thanks in advance...Hi,

look here:

http://support.microsoft.com/kb/321843

Perhaps you might have a look on triggers.

HTH, Jens Suessmeyer.

--
http://www.sqlserver2005.de
--

multiple foreign keys on same field, based on other field

I have a table called BidItem which has another table called
BidAddendum related to it by foreign key. I have another table called
BidFolder which is related to both BidItem and BidAddendum, based on a
column called RefId and one called Type, i.e. type 1 is a relationship
to BidItem and type 2 is a relationship to BidAddendum.

Is there any way to specify a foreign key that will allow for the
different types indicating which table the relationship should exist
on? Or do I have to have two separate tables with identical columns
(and remove the type column) ?? I would prefer not to have multiple
identical tables.On 2 Mar 2005 15:29:16 -0800, pb648174 wrote:

>I have a table called BidItem which has another table called
>BidAddendum related to it by foreign key. I have another table called
>BidFolder which is related to both BidItem and BidAddendum, based on a
>column called RefId and one called Type, i.e. type 1 is a relationship
>to BidItem and type 2 is a relationship to BidAddendum.
>Is there any way to specify a foreign key that will allow for the
>different types indicating which table the relationship should exist
>on? Or do I have to have two separate tables with identical columns
>(and remove the type column) ?? I would prefer not to have multiple
>identical tables.

Hi pb648174,

If I understand you correctly, each row in BidFolder is related to
either one row in BidItem or to one row in BidAddendum. Correct so far?

Am I also correct that both BidItem and BidAddendum have RefId as either
PRIMARY KEY or UNIQUE column, so that this column can be used in a
FOREIGN KEY constraint?

The way I would implement this, is to have two RefId columns in the
BidFolder table (of course appropriately named), with a CHECK constraint
to ensure that exactly one of them is populated and the other is NULL:

CREATE TABLE BidFolder
( ......
, ......
, Item_RefId ? DEFAULT NULL -- Replace ? with
, Addendum_RefId ? DEFAULT NULL -- the correct type
, ....
, PRIMARY KEY (...)
, FOREIGN KEY (Item_RefId) REFERENCES BidItem
, FOREIGN KEY (Addendum_RefId) REFERENCES BidAddendum
, CHECK ((Item_RefId IS NULL AND Addendum_RefID IS NOT NULL)
OR (Item_RefId IS NOT NULL AND Addendum_RefID IS NULL))
)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||I am not totally sure i understand what you are trying to do, but
could you do someting like

BidItem
PK RefId
PK Type - Set default to 2
FK BidAddendum_RefId
FK BidAddendum_Type

BidAddendum
PK RefId
PK Type - Set default to 1
FK BidItem_RefId
FK BidItem_Type

BidFolder
PK Whatever
FK RefId
FK Type

"pb648174" <google@.webpaul.net> wrote in message news:<1109806156.005319.202280@.l41g2000cwc.googlegroups. com>...
> I have a table called BidItem which has another table called
> BidAddendum related to it by foreign key. I have another table called
> BidFolder which is related to both BidItem and BidAddendum, based on a
> column called RefId and one called Type, i.e. type 1 is a relationship
> to BidItem and type 2 is a relationship to BidAddendum.
> Is there any way to specify a foreign key that will allow for the
> different types indicating which table the relationship should exist
> on? Or do I have to have two separate tables with identical columns
> (and remove the type column) ?? I would prefer not to have multiple
> identical tables.|||No, BidItem and Addendum do not have the type and refid fields. The
Type and RefId columns are only in the BidFolder table and are used to
associate one or more BidFolders with either a BidItem (Type 1) or
BidAddendum (Type 2). I don't htink I can do a compound foreign key
based on the type of 1 or 2, so I'm wondering how I set a foreign key
in this scenario.

Another person suggested using two different columns in the BidFolder
table, which would work but would not be very flexible moving forward,
since if I had another relationship to map, I would have to update all
the tables and stored procs instead of just adding BidFolder entries
with a type of 3.|||
That works, but if I want to add a third relationship I have to update
the table and all associated stored procedures.. Is there any way to
accomplish it with the existing Type and RefId columns?

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||On 3 Mar 2005 09:59:52 -0600, P B wrote:

>That works, but if I want to add a third relationship I have to update
>the table and all associated stored procedures.

Hi P B,

Adding a column to a table is not that much work. One ALTER TABLE
statement for the column and one ALTER TABLE statement for the
constraint is all you need. You might also need to run an UPDATE to fill
the new columns with the correct starting data, but you'd need to do
that anyway, regardless of the chosen representation.

Stored procedures that have to do something functional with the third
relationship need to be updated anyway. Stored procedures that don't
need to handle the third relationship don't need to be updated (unless
you use INSERT without column list or SELECT * - but both are bad
practice in a production system anyway).

> Is there any way to
>accomplish it with the existing Type and RefId columns?

Yes, it's pointed out by Linn. Here's a link to a more verbose
explanation of the same principle by Joe Celko:
http://groups-beta.google.com/group...db18c87e1743165
(beware of possible line wrapping)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||It is a huge amount of work when there is thousands of line of existing
code - I don't want to go and change all the function and stored
procedures referencing these tables - I just want to get foreign key
relationships on the existing tables, so as long as the data structure
changes don't affect current stored procedures, the application code
won't need to be changed either.

The solution you posted a link to is a neat idea, but it is modeling an
"is-a" relationship whereas this is a "has-a" relationship. Let's say I
did the following, to follow the suggestions given so far: (the types
don't exist right now in the Item and Addendum tables, but could be
added easily since they have a default value and will not be referenced
in existing stored procedures)

BidItem
Type 1(default value)
Id
BidAddendum
Type 2(default value)
Id

BidFolder
Type 1 or 2
RefId (references Id in Item or Addendum tables based on Type)

A particular BidItem or BidAddendum will have multiple BidFolder
entries. If I could, I would like to put multiple foreign keys on the
BidFolder table to reference the BidItem and BidAddendum tables, but I
can't do that - I also don't think placing the foreign keys on the
BidItem/Addendum tables will work since the relationship is one to many
from that perspective instead of many to one.|||P B (developersdex@.webpaul.net) writes:
> That works, but if I want to add a third relationship I have to update
> the table and all associated stored procedures.. Is there any way to
> accomplish it with the existing Type and RefId columns?

If you arrive to this situation - or if you think you can arrive at
this situation - then maybe you need to take a broader look at your
database design.

One alternative is to create a supertable to the parents, and then have
the FK to refer to that table. That table would look like:

CREATE TABLE mothertable (refid ...,
type ...,
PRIMARY KEY(refid),
UNIQUE (refid, type))

The seemingly superfluous UNIQUE constraint, permits you to use an
FK from you lower table.

You could also give up on DRI, and use a trigger instead.

When I have encountered this, I have gone for Hugo's solution in
most cases.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||pb648174 (google@.webpaul.net) writes:
> It is a huge amount of work when there is thousands of line of existing
> code - I don't want to go and change all the function and stored
> procedures referencing these tables - I just want to get foreign key
> relationships on the existing tables, so as long as the data structure
> changes don't affect current stored procedures, the application code
> won't need to be changed either.

Thousands of line of code? That's not much. :-)

If you don't want to change the code, but use the tables as they are,
you will have to go for a trigger.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||I was hoping to use foreign keys so that replication would be smart
enough to pick up all the relationships.. Will replication correctly
handle triggers automatically or will I need to setup something special
for this scenario?

How would you even accomplish Hugo's suggestion for a "has-a"
relationship? His involves setting the foreign key on the
BidItem/Adendum tables which would be a one to many instead of a many
to one relationship.|||On 3 Mar 2005 14:44:41 -0800, pb648174 wrote:

>It is a huge amount of work when there is thousands of line of existing
>code - I don't want to go and change all the function and stored
>procedures referencing these tables

Hi pb,

Wrong arguments. :-)

Always strive for a good design. A design that saves you work now but is
not the best design for the situation will almost certainly cost you (or
your company) dearly in the long run.

"Why is there never the time/money to do it good, but always the
time/money to do it over?"

>The solution you posted a link to is a neat idea, but it is modeling an
>"is-a" relationship whereas this is a "has-a" relationship.

First, I'm not sure if having one BidFolder table is the correct design
in your case. Does the real world that you attempt to model really have
one entity BidFolders that belong either to a BidItem or to a
BidAddendum, but are of the same type otherwise? Or are you using one
table to store information about two different things, just because
their structure is so similar that it looks more convenient?

If the correct design really involves one BidFolder table, then I'd
definitely favor the solution in my first post, with seperate columns
for the relationship to BidItem and the relationship to BidAddendum,
since they are different things that should be stored in different
columns. If you store information about persons, and you store either
weight (in kg) or age (in years), but never both, would you combine both
in one integer column, with a seperate Type column to indicate whether
the 68 stored in one column is a weight or an age? I really hope you
wouldn't - so why would you treat foreign key columns differently?

This being said, Celko's subtype-solution I posted a link to can be
adapted for 1 to many relationships. See the example below. But keep in
mind that if you worked for me and came up with this solution, you
better had some very good arguments why it's the best solution in this
specific case, or you'd run a high risk of losing your bonus and maybe
even your job.

CREATE TABLE BidItem
( RefId int NOT NULL
, Type tinyint NOT NULL DEFAULT 1
, ...
, PRIMARY KEY (RefId)
, UNIQUE (RefId, Type)
, CHECK (Type = 1)
)
CREATE TABLE BidAddendum
( RefId int NOT NULL
, Type tinyint NOT NULL DEFAULT 2
, ...
, PRIMARY KEY (RefId)
, UNIQUE (RefId, Type)
, CHECK (Type = 2)
)
CREATE TABLE BidFolder
( ......
, ......
, RefId int NOT NULL
, Type tinyint NOT NULL
, ....
, PRIMARY KEY (...)
, FOREIGN KEY (RefId, Type) REFERENCES BidItem (RefId, Type)
, FOREIGN KEY (RefId, Type) REFERENCES BidAddendum (RefId,
Type)
, CHECK (Type IN (1, 2))
)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||>From this vague narrative, it sounds like you are mimicking paper forms
with tables. The name suggest paper forms, but the data sounds like
it should be a history of a bid or that there are many logically
different kinds of bids that need their own tables.

>> I would prefer not to have multiple identical tables. <<

That would be a major design flaw; tables that are
identical model the same kinds of things in two places.|||Well all I'll say is that if solution 1 offer maintenance costs lower
than solution 2 with identical performance, then it's a pretty easy
sell for me to use the solution with lower maintenance costs. Users and
management couldn't care what the underlying structure is as long as it
works and performs well.

Regarding the above solution, is there an OR relationship implicit with
multiple foreign keys? I tried looking up that information in the SQL
books but didn't find anything on it. How would you model an AND
relationship with multiple foreign keys if that is the case? (Just
curious)|||pb648174 (google@.webpaul.net) writes:
> Well all I'll say is that if solution 1 offer maintenance costs lower
> than solution 2 with identical performance, then it's a pretty easy
> sell for me to use the solution with lower maintenance costs. Users and
> management couldn't care what the underlying structure is as long as it
> works and performs well.
> Regarding the above solution, is there an OR relationship implicit with
> multiple foreign keys? I tried looking up that information in the SQL
> books but didn't find anything on it. How would you model an AND
> relationship with multiple foreign keys if that is the case? (Just
> curious)

I think Hugo's made a mistake his table. I would be very difficult
to insert anything at all in BidFolder with that design! This is probably
what he had in mind:

CREATE BidParents (Refid int NOT NULL,
Type tinyint,
PRIMARY KEY (RefId),
UNIQUE (RefId, Type)

CREATE TABLE BidItem
( RefId int NOT NULL
, Type tinyint NOT NULL DEFAULT 1 CHECK (Type = 1)
, ...
, PRIMARY KEY (RefId)
, FOREIGN KEY (RefId, Type) REFERENCES BidParents(RefId, Type)
, CHECK (Type = 1)
)
CREATE TABLE BidAddendum
( RefId int NOT NULL
, Type tinyint NOT NULL DEFAULT 2 CHECK (Type = 2)
, ...
, PRIMARY KEY (RefId)
, FOREIGN KEY (RefId, Type) REFERENCES BidParents(RefId, Type)
,
)
CREATE TABLE BidFolder
( ......
, ......
, RefId int NOT NULL
, ....
, PRIMARY KEY (...)
, FOREIGN KEY (RefId, Type) REFERENCES BidParents (RefId)
)

Since you know your business domain better than we know, you can
say whether this makes any sense at all.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||pb648174 (google@.webpaul.net) writes:
> I was hoping to use foreign keys so that replication would be smart
> enough to pick up all the relationships.. Will replication correctly
> handle triggers automatically or will I need to setup something special
> for this scenario?

It was very long ago since I looked at replication, and that was in
6.5 days. I will have to admit that I have little knowledge of the
implication of both triggers and foreign keys for replication.

> How would you even accomplish Hugo's suggestion for a "has-a"
> relationship? His involves setting the foreign key on the
> BidItem/Adendum tables which would be a one to many instead of a many
> to one relationship.

I'm not sure exactly which suggestion you are thinking of. But see
my other post for tonight for possible scheme for your case. (I still
prefer to have two different columns, though.)

Anyway, "has-a" is object-orienting thinking, but you are in a relational
database now. It's not really the same thing as Kansas.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||On 4 Mar 2005 07:04:21 -0800, pb648174 wrote:

>Regarding the above solution, is there an OR relationship implicit with
>multiple foreign keys? I tried looking up that information in the SQL
>books but didn't find anything on it. How would you model an AND
>relationship with multiple foreign keys if that is the case? (Just
>curious)

Hi PB,

As Erland pointed out, I made an error. That'll teach me to post
solutions without testing them first. <hangs head in shame
The solution I posted, with two foreign keys, will attempt to enforce
them both. And since the CHECK constraints in the two references tables
are mutually exclusive, you'll never be able to enter any data at all.

I don't see other solutions than the one suggested by Erland. Of course,
that changes the model from the two "has-a" relationships you asked
about to one "has-a" relationship to a supertype, that has two "is-a"
relationships to it's subtypes. Not quite the same :-)

All the more reason to go for my original solution with one column for
each foreign key relationship, methinks. (Unless, of course, the
BidItems and the BidAddendums are indeed conceptually subtypes of one
common supertype).

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Yeah, this is a big mess. This is one of my more simpler relationships
being modeled in SQL - I was doing it as a test to see if I could map
foreign keys for all the relationships. I think I'm just going to leave
it alone and let it be done in application logic.|||On 4 Mar 2005 22:42:58 -0800, pb648174 wrote:

>Yeah, this is a big mess. This is one of my more simpler relationships
>being modeled in SQL - I was doing it as a test to see if I could map
>foreign keys for all the relationships.

Hi PB,

It doesn't need to be a mess. It can actually be quite simple. It only
becomes complicated if you try to use a single column for more than one
relationship.

If you just add seperate columns for each relationship and define a
FOREIGN KEY constraint on each of those columns, you'll have a simple
design that's easy to use and easy to understand.

>I think I'm just going to leave
>it alone and let it be done in application logic.

That is never a good idea. Applications can fail. Always use declarative
referential integrity (DRI) in the database to make sure that your data
can't get corrupted by application errors, or by wizz-kids bypassing the
application.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hugo Kornelis (hugo@.pe_NO_rFact.in_SPAM_fo) writes:
> It doesn't need to be a mess. It can actually be quite simple. It only
> becomes complicated if you try to use a single column for more than one
> relationship.

And most of all, it will be a mess if you try squeeze squared SQL Server
pegs through object-oriented circular holes. I have a feeling that this
is what is the crux of the biscuit in this case.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Is it all that unreasonable to want to add a different type of item
without having to rewrite all the stored procedures and modify all the
application logic? Adding a column is a big deal, not because of the
SQL statement to add the column, but because of all the consequences.
This is a fairly simple example, I have some much more complicated
ones, with a table referencing itself and one with 8 or 9 types
(instead of 2).

If taking care of the relationship in the application is a bad idea,
it's the best bad idea I see so far.|||pb648174 (google@.webpaul.net) writes:
> Is it all that unreasonable to want to add a different type of item
> without having to rewrite all the stored procedures and modify all the
> application logic? Adding a column is a big deal, not because of the
> SQL statement to add the column, but because of all the consequences.
> This is a fairly simple example, I have some much more complicated
> ones, with a table referencing itself and one with 8 or 9 types
> (instead of 2).

You will have to understand that neither I nor Hugo has very poor knowledge
about your business domain. All we know are the names of the tables, and
it does not tell me that much. What we can to is to build from our own
experiences. In the database I work with, there are quite a few cases of
mutually exclusive columns. The number is almost always two, although I
recall that somewhere the number is three. Given what you told us, this
appeared to be the correct solution.

Now you say that you have 8-9 different tables you can refer to. In this
case, I doubt that it is a good idea to have 8-9 different columns with
a constraint specifying that exactly one must be non-NULL. I'm tempted
to say that there might be reason to review the entire data model. And
surely some variation of the suprertype concept is more apt here.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||On 5 Mar 2005 16:57:39 -0800, pb648174 wrote:

> Adding a column is a big deal, not because of the
>SQL statement to add the column, but because of all the consequences.

Hi PB,

Could you elaborate on this? I've been trying to think what consequences
you refer to, but I don't see them. I might be missing the obvious, of
course - but there's also the possibility that there's a very simple
solution to what you perceive as a problem. If you post it, others can
try to help you!

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hugo Kornelis (hugo@.pe_NO_rFact.in_SPAM_fo) writes:
> Could you elaborate on this? I've been trying to think what consequences
> you refer to, but I don't see them. I might be missing the obvious, of
> course - but there's also the possibility that there's a very simple
> solution to what you perceive as a problem. If you post it, others can
> try to help you!

Of course, you don't add a column to a table only to have it sit
there, but there will be some code that will have to be added to
retrieve, display, and update the new column.

If you use SELECT *. INSERT without no column lists, and other bad
programming constructs, the impact can be even harder.

So, if it really is the case that tomorrow a third table can be
targeted, then this solution is not a good one. But I also think
that there is something with the data model that needs fixing in
this case.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Ok, forget about the example I posted and consider how you would model
this then: You have an audit log which logs the exact same information
for every single module in an application. In addition to viewing the
log, one requirement is that users be able to click on "details" for
each audit log line item and go directly to the module the audit log
involves.

So obviously there needs to be a relationship between the audit log
line item and all the modules in the system(20-30). Furthermore, new
modules are introduced every quarter, so it should be relatively easy
to add new modules to the auditing system without having to touch the
existing code (so it doesn't have to be re-tested).

How would you model it? And better yet, how would you do the foreign
keys?|||pb648174 (google@.webpaul.net) writes:
> Ok, forget about the example I posted and consider how you would model
> this then: You have an audit log which logs the exact same information
> for every single module in an application. In addition to viewing the
> log, one requirement is that users be able to click on "details" for
> each audit log line item and go directly to the module the audit log
> involves.
> So obviously there needs to be a relationship between the audit log
> line item and all the modules in the system(20-30). Furthermore, new
> modules are introduced every quarter, so it should be relatively easy
> to add new modules to the auditing system without having to touch the
> existing code (so it doesn't have to be re-tested).
> How would you model it? And better yet, how would you do the foreign
> keys?

The hour is late, and the descrption is brief, so I'll be brief too.

1) For an audit log, I could consider scrapping referential integrity. Or
have some table that is specific to the audit look to server as lookup
for id:s of the modules.

2) If DRI to the audited rows is absolutely desired, I would consider of
adding the table "auditable_items" that would hold all items that are
common to audited tables. All auditied tables would refer to that
table by the trick that I demonstrated earlier. (And of which credits
for the idea goes to Joe Celko.)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Well it's refreshing to hear the same idea suggested that I used when
creating it... Especially after all this talk of bad design.

I am a bit unclear on what you mean by this though:
"Or
have some table that is specific to the audit look to server as
lookup
for id:s of the modules."|||pb648174 (google@.webpaul.net) writes:
> Well it's refreshing to hear the same idea suggested that I used when
> creating it... Especially after all this talk of bad design.
> I am a bit unclear on what you mean by this though:
> "Or have some table that is specific to the audit look to server as
> lookup for id:s of the modules."

One hardly blame you for not getting sense in this gibberish. I did say
that it was late at night, didn't I? :-)

I think that what I meant was that rather having an audit table that
looks like:

CREATE TABLE auditlog (modulename varchar(40) NOT NULL,
...

You would have

CREATE TABLE auditlog (moduleid int NOT NULL,
...

And then moduleid would be an FK to the modules table, which probably
should be specific for the audting. The main advantage is that you gain
some size with the log table.

Actually, in our system we have one general log table where you can
log about everything in datachanges. This table is very simple. We
have (tablename, keyvalue1, keyvalue2) to identify the changed row,
and "colname" to identify the column. There is no referential integrity
at all from the log table.

But you may have more heavy-duty requirements than we have.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Multiple Foreign Keys

I am constructing a db in sql server 2000 that will score cross-country running meets. I have an individual results table that needs to only contain participants that are entered as participants but are specific to a certain race as well. Can I have this table be linked back to TWO other tables via the PK-FK relationship and what issues might I have doing that?

Thanks!yes, you can, and no, there won't be any|||Hai rudy,

If i link a column in one table (say table A) with columns in two different tables(say B and C) using foreign key relation ship, does the columns in both those tables(B and C) should be of the same datatype..?

Thnks in advance|||for the sake of efficiency, i think they should be, however, i have no idea if you can actually link the same column in one table to two different other tables

why don't you try it and see if it works? let us know, thanks|||Thnks rudy, i will try it and let u know what happened...|||I think this is the setup you want. [RaceParticipant] is linked back to [Participant] by a ParticipantID, and to [Race] by a RaceID. ParticipantID and RaceID do not need to be the same datatype, but ParticipantID should be the same datatype in both the [Participant] and [RaceParticipant] tables, and [RaceID] should be the same datatype in both the [Participant] and [RaceParticipant] tables.

...[Participant]\
................[RaceParticipant]
.........[Race]/sql

Multiple Foreign Keys

What are the possible issues I could run in to having multiple foreign keys in a table. Here is why I ask. I have a db (sql server) that has a participant table, a forum table, and a forum reply table. Every record in the forum reply table is associated with the forum table via a PK-FK relationship w/cascading updates/deletes. The participants who post in these tables are not tied back to the participant table via a PK-FK relationship w/cascading updates/deletes. Should they be?

The problem I ran in to is that one particpant was deleted from the participant table but a post with their partid still existed in the forum or forum reply tables.

My feeling is that anytime a participant is deleted, everything that pertains to them should go too, right? If I am right, what do I have to be careful of if I do that?

Let me know! Thanks!!My feeling is that anytime a participant is deleted, everything that pertains to them should go too, right?

I'd suggest you don't even delete the participant much less the replies. Forum threads would be rendered useless if you start deleting replies. Why not just add a flag to the participant table to indicate active/inactive status?

Multiple Foreign Keys

I am having trouble creating multiple foreign keys on a table so that I can set up cascading update and cascading delete from two different primary tables. I am using the diagram to do this but when I try to save the diagram I get the following error.

ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER TABLE statement conflicted with COLUMN FOREIGN KEY constraint 'FK_IndResults_RaceData'. The conflict occurred in database 'VIRA', table 'RaceData', column 'RaceID'.

What would cause this to happen? Is it possible that I have records in the foreign table that do not transfer back to the primary table?

Thanks!Is it possible that I have records in the foreign table that do not transfer back to the primary table?Not just possible, nearly guaranteed.

The best answer is to create a query that will show you the offending rows, then go and fix them. You can often pick a value that makes a reasonable default (sometimes NULL works well, meaning there isn't any relationship). Sometimes you've got to figure out what FK value you need, which can be a lot of work.

-PatP|||Thanks! That was exactly what it was and it is fixed!

Multiple foreign keys

Hi,
I wonder if it is possible to have multiple foreign keys between to tables
and delete data automatically. We have on table with a number of locations
and another table with a number of routes between them. The foreign keys go
from location to the beginning and the end of the route. How can I secure
that (1) only data from location is used in route and (2) if a dataset in
location is deleted both routes to and from are deleted. I can't have a
cascading key on both relations and i couldn't make a trigger work either.
Any suggestions?
Holgerjust a try...
parent table A
child Table B
grandchild table C :)
you have C refering to B refering to A
Create an ondelete cascade to table A
, Now write an intead of delete trigger for table B and table C each which
will delete from table A the corresponding row..
Let me know how it goes.. I am interested in finding out too :)|||Why wouldn't it be possible for both the 'from' and 'to' columns in the rout
e
table to reference the location column in the location table, with ON DELETE
CASCADE?
"HE" wrote:

> Hi,
> I wonder if it is possible to have multiple foreign keys between to tables
> and delete data automatically. We have on table with a number of locations
> and another table with a number of routes between them. The foreign keys g
o
> from location to the beginning and the end of the route. How can I secure
> that (1) only data from location is used in route and (2) if a dataset in
> location is deleted both routes to and from are deleted. I can't have a
> cascading key on both relations and i couldn't make a trigger work either.
> Any suggestions?
> Holger
>
>|||Why wouldn't it be possible for both the 'from' and 'to' columns in the rout
e
table to reference the location column in the location table, with ON DELETE
CASCADE?
"HE" wrote:

> Hi,
> I wonder if it is possible to have multiple foreign keys between to tables
> and delete data automatically. We have on table with a number of locations
> and another table with a number of routes between them. The foreign keys g
o
> from location to the beginning and the end of the route. How can I secure
> that (1) only data from location is used in route and (2) if a dataset in
> location is deleted both routes to and from are deleted. I can't have a
> cascading key on both relations and i couldn't make a trigger work either.
> Any suggestions?
> Holger
>
>|||I think the OP has something like this...
The second on delete cascade, on update cascade causes an error.
Maybe I am missing something in RD Theory, but why would this not be
allowed?
Create table Location
(
LocationID varchar(10) primary key not null
,LocationName varchar(30) not null
)
go
create Table Routes
(
FromLocationID varchar(10) not null
,ToLocationID varchar(10) not null
, RouteName varchar(30) not null
)
go
ALTER TABLE dbo.Routes ADD CONSTRAINT
PK_Routes PRIMARY KEY CLUSTERED
(
FromLocationID,
ToLocationID
)
GO
ALTER TABLE dbo.Routes ADD CONSTRAINT
FK_Routes_FromLocation FOREIGN KEY
(
FromLocationID
) REFERENCES dbo.Location
(
LocationID
) ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE dbo.Routes ADD CONSTRAINT
FK_Routes_ToLocation FOREIGN KEY
(
ToLocationID
) REFERENCES dbo.Location
(
LocationID
) ON UPDATE CASCADE
ON DELETE CASCADE
GO
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:80BE38F5-A266-4593-A4C5-A69DAD837B89@.microsoft.com...
> just a try...
> parent table A
> child Table B
> grandchild table C :)
> you have C refering to B refering to A
> Create an ondelete cascade to table A
> , Now write an intead of delete trigger for table B and table C each
which
> will delete from table A the corresponding row..
> Let me know how it goes.. I am interested in finding out too :)
>|||Hi Jim,
Why do I have a feeling we both are beating the wrong bush (No pun
intended :)
Holger: Of course if its not too much of a pain for you, Can you give the
table definitions and the foriegn key contraints
with a few sample data and your delete statement and what should it inturn
delete and then we will try to find a way out.|||I was thinking along the same lines; I don't think there's anything that
would prevent two columns in one table referencing the same column in anothe
r
table, with both constraints created with ON DELETE CASCADE.
"Jim Underwood" wrote:

> I think the OP has something like this...
> The second on delete cascade, on update cascade causes an error.
> Maybe I am missing something in RD Theory, but why would this not be
> allowed?
> Create table Location
> (
> LocationID varchar(10) primary key not null
> ,LocationName varchar(30) not null
> )
> go
> create Table Routes
> (
> FromLocationID varchar(10) not null
> ,ToLocationID varchar(10) not null
> , RouteName varchar(30) not null
> )
> go
> ALTER TABLE dbo.Routes ADD CONSTRAINT
> PK_Routes PRIMARY KEY CLUSTERED
> (
> FromLocationID,
> ToLocationID
> )
> GO
> ALTER TABLE dbo.Routes ADD CONSTRAINT
> FK_Routes_FromLocation FOREIGN KEY
> (
> FromLocationID
> ) REFERENCES dbo.Location
> (
> LocationID
> ) ON UPDATE CASCADE
> ON DELETE CASCADE
> GO
> ALTER TABLE dbo.Routes ADD CONSTRAINT
> FK_Routes_ToLocation FOREIGN KEY
> (
> ToLocationID
> ) REFERENCES dbo.Location
> (
> LocationID
> ) ON UPDATE CASCADE
> ON DELETE CASCADE
> GO
>
> "Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
> news:80BE38F5-A266-4593-A4C5-A69DAD837B89@.microsoft.com...
> which
>
>|||Logically, I don't see an issue, but I may be missing something. I do know
that SQL Server will not allow this...
From BOL "ON UPDATE CASCADE"
The series of cascading referential actions triggered by a single DELETE or
UPDATE must form a tree containing no circular references. No table can
appear more than once in the list of all cascading referential actions that
result from the DELETE or UPDATE.
******
The tree of cascading referential actions must not have more than one path
to any given table.
******
Any branch of the tree is terminated when it encounters a table for which NO
ACTION has been specified or is the default.
The problem here is we have two paths to the same table, so one would expect
two deletes to be performed, one for each reference, or one delete where
either reference exists. I'm not sure if this is a DBMS thing or a SQL
Server implementation thing. I know that this must be a common issue. I
figure a trigger could accomplish the same thing (which I think omni was
suggesting) but it seems to go against DB design to resort to a trigger when
a simple constraint should be sufficent.
"Mark Williams" <MarkWilliams@.discussions.microsoft.com> wrote in message
news:EBA9B0E2-CA08-4277-8397-3298494C6F15@.microsoft.com...
> I was thinking along the same lines; I don't think there's anything that
> would prevent two columns in one table referencing the same column in
another
> table, with both constraints created with ON DELETE CASCADE.
>
> "Jim Underwood" wrote:
>|||Technically, this is okay in Standard SQL because of the constraints
that force one and only one execution path.
CREATE TABLE Locations
(location_id INTEGER NOT NULL PRIMARY KEY,
location_name VARCHAR(30) NOT NULL);
CREATE TABLE Routes
(route_name VARCHAR(30) NOT NULL PRIMARY KEY,
start_location_id INTEGER NOT NULL
CONSTRAINT starting
REFERENCES Locations (location_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
final_location_id INTEGER NOT NULL
CONSTRAINT ending
REFERENCES Locations (location_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
UNIQUE(start_location_id, final_location_id),
CHECK (start_location_id <> final_location_id));
A smart SQL engine will detect that {ending, starting} and {starting,
ending} will give the same results. This would be legal and deleting a
location removing a node from a graph -- the edges would also
disappear.|||Hi Jim,
"Jim Underwood" <james.underwoodATfallonclinic.com> schrieb im Newsbeitrag
news:Oj6rIiReGHA.3996@.TK2MSFTNGP04.phx.gbl...
> Logically, I don't see an issue, but I may be missing something. I do
> know
> that SQL Server will not allow this...
> From BOL "ON UPDATE CASCADE"
> The series of cascading referential actions triggered by a single DELETE
> or
> UPDATE must form a tree containing no circular references. No table can
> appear more than once in the list of all cascading referential actions
> that
> result from the DELETE or UPDATE.
> ******
> The tree of cascading referential actions must not have more than one path
> to any given table.
> ******
> Any branch of the tree is terminated when it encounters a table for which
> NO
> ACTION has been specified or is the default.
>
> The problem here is we have two paths to the same table, so one would
> expect
> two deletes to be performed, one for each reference, or one delete where
> either reference exists. I'm not sure if this is a DBMS thing or a SQL
> Server implementation thing. I know that this must be a common issue. I
> figure a trigger could accomplish the same thing (which I think omni was
> suggesting) but it seems to go against DB design to resort to a trigger
> when
> a simple constraint should be sufficent.
>
That's exactly my problem. The database looks like those tables you
described earlier and was ported from Oracle where it was no problems having
two cascading actions. I tried using an ON DELETE CASCADE on FromLocation ID
and additionally an AFTER DELETE trigger on Location but that would work
first after the deletion was successful. The deletion can't be succesful as
long as there are entries in Route referencing ToLocationID.
Holger
> "Mark Williams" <MarkWilliams@.discussions.microsoft.com> wrote in message
> news:EBA9B0E2-CA08-4277-8397-3298494C6F15@.microsoft.com...
> another

Multiple Columns Index/Key (does it free me from creating a single column indexes?)

I hope i'm in the right place, but thanks anyway....

Actually i have 2 questions (regarding sql-server Indices/Keys):

1) I have an index, which is consisted of 4 columns.

I've read elsewhere that this index functions (as well) as an index (single column

index) on the first column of this multi-column index.

Does this mean that if i'd like to have (in addition) Indices on all of the 4 columns

seperately i need to define only 3?

2) I have a unique key consisted of multiple columns.

I'd like to save an index to this combination of columns as well (to speed up

things in DB...).

Does the definition of a multiple-columns key free me from defining the multiple-

columns index?

can anyone explain the main diference between Keys and Indices?

thanks,

Ran Kizi

1. Yes, the multi-column index will also function as an index for the first column. In many situations, it will also be an quasi-efficient index for the second, and perhaps other columns. I would get some response measurements before assuming that indexes were required on the lesser columns.

2. A 'unique key' is in fact, a 'Unique CONSTRAINT.

A Primary Key can be created for one or more columns, requires indexing to be efficient, and an index is automatically generated when the Primary Key is created. A Primary Key requires a value, and that the value is unique.

There is no 'Unique Key', but there is a Unique CONSTRAINT. (Perhaps in your question, you were referring to a Primary Key, and if so, the above applies.) A Unique CONSTRAINT can be created for one or more columns, and automatically creates a matching index. A Unique CONSTRAINT does not require a value, but if a value is provided, it must be unique.

A Foreign Key 'should have' an index to be efficient -but the index is NOT automatically generated when the Foreign Key is created. It is recommended that a matching index be created when the Foreign Key is created. A Foreign Key does NOT require a value, BUT if a value is provided, that value must also exist in the table with the Primary Key relationship.

|||

1. Indexes on multiple columns can also be used as index on first column. and in my experience it can not be used as index on second and following columns. you would need to create seprate indexes on those column if your queries will only use second or third column without using first column in queries.

There is a workaround even if you dont have to use first column in your query you can still use COLUMN1 = COLUMN1 only to make optimizer use index as in best of my knowledge if column1 is not present in your query index will be used for second or third column.

2. when you create UNIQUE constraint on multiple columns SQL Server will ultimately create compound unique index on those colunms to make sure uniquenes. for selectivity of index above will apply.

Primary Key does create index automaticaly (if index type is not mentioned and table is heap then will create CLUSTERED INDEX). FORIEGN KEY does not create index automatically and it is highly recomemded to create index on foriegn key column. The one unique reason is when deleting record in primary key table sql server perform a look up in all tables having foriegn key on that table and if no index presented then it will perform clustered index scan or table scan that will ultimately slow down DELETE operation for primary key table.

Multiple Columns Index/Key (does it free me from creating a single column indexes?)

I hope i'm in the right place, but thanks anyway....

Actually i have 2 questions (regarding sql-server Indices/Keys):

1) I have an index, which is consisted of 4 columns.

I've read elsewhere that this index functions (as well) as an index (single column

index) on the first column of this multi-column index.

Does this mean that if i'd like to have (in addition) Indices on all of the 4 columns

seperately i need to define only 3?

2) I have a unique key consisted of multiple columns.

I'd like to save an index to this combination of columns as well (to speed up

things in DB...).

Does the definition of a multiple-columns key free me from defining the multiple-

columns index?

can anyone explain the main diference between Keys and Indices?

thanks,

Ran Kizi

1. Yes, the multi-column index will also function as an index for the first column. In many situations, it will also be an quasi-efficient index for the second, and perhaps other columns. I would get some response measurements before assuming that indexes were required on the lesser columns.

2. A 'unique key' is in fact, a 'Unique CONSTRAINT.

A Primary Key can be created for one or more columns, requires indexing to be efficient, and an index is automatically generated when the Primary Key is created. A Primary Key requires a value, and that the value is unique.

There is no 'Unique Key', but there is a Unique CONSTRAINT. (Perhaps in your question, you were referring to a Primary Key, and if so, the above applies.) A Unique CONSTRAINT can be created for one or more columns, and automatically creates a matching index. A Unique CONSTRAINT does not require a value, but if a value is provided, it must be unique.

A Foreign Key 'should have' an index to be efficient -but the index is NOT automatically generated when the Foreign Key is created. It is recommended that a matching index be created when the Foreign Key is created. A Foreign Key does NOT require a value, BUT if a value is provided, that value must also exist in the table with the Primary Key relationship.

|||

1. Indexes on multiple columns can also be used as index on first column. and in my experience it can not be used as index on second and following columns. you would need to create seprate indexes on those column if your queries will only use second or third column without using first column in queries.

There is a workaround even if you dont have to use first column in your query you can still use COLUMN1 = COLUMN1 only to make optimizer use index as in best of my knowledge if column1 is not present in your query index will be used for second or third column.

2. when you create UNIQUE constraint on multiple columns SQL Server will ultimately create compound unique index on those colunms to make sure uniquenes. for selectivity of index above will apply.

Primary Key does create index automaticaly (if index type is not mentioned and table is heap then will create CLUSTERED INDEX). FORIEGN KEY does not create index automatically and it is highly recomemded to create index on foriegn key column. The one unique reason is when deleting record in primary key table sql server perform a look up in all tables having foriegn key on that table and if no index presented then it will perform clustered index scan or table scan that will ultimately slow down DELETE operation for primary key table.

Multiple Columns Index/Key (does it free me from creating a single column indexes?)

I hope i'm in the right place, but thanks anyway....

Actually i have 2 questions (regarding sql-server Indices/Keys):

1) I have an index, which is consisted of 4 columns.

I've read elsewhere that this index functions (as well) as an index (single column

index) on the first column of this multi-column index.

Does this mean that if i'd like to have (in addition) Indices on all of the 4 columns

seperately i need to define only 3?

2) I have a unique key consisted of multiple columns.

I'd like to save an index to this combination of columns as well (to speed up

things in DB...).

Does the definition of a multiple-columns key free me from defining the multiple-

columns index?

can anyone explain the main diference between Keys and Indices?

1. I'm sure you are in the wrong place, try the SQL Server forums.

2. A Key implies unique data in the column(s) (Unique Key / Primary Key) where as an index doesn't necessarily have to have unique data in the column(s).

3. An index and a key that use the same definitions (same columns etc.) would be redundant, cost space (and possibly performance) and not desired. The optimizer can use either.

4. The first column of an index is always the most important and determines whether the index will be used or not. If you have no better indexes of the first column then it will be used.

BTW - Don't over do it with indexes!

Please ask such question in the SQL Server Forum, this forum is only for Team Edition for Database Professionals.

Alle