multiple items

the key is in your post above my first post where you ask "how can I build UI
for it" - that is the key - you have to build your own custom user interface
for the report parameters. Instead of using a drop down list to display the
allowable parameter values, use a list box, which supports selecting of
multiple values. You have to go the route of using the web services, not url
access (for my solution) - which direction are you currently headed?
It is a lot of work to create the custom ui, but it does give you the
ability to pass multiple values into a stored procedure and use dynamic SQL
to fuel the dataset for the report.
I hope this helps,Actually you could still use the URL approach and code a function that parses
your parameter string. Simply encode a delimiter between the multi-select
values and use a UDF to parse the parameter in your stored procedure. Unless
the URL will be too large to pass.
--
Keith Powers, MCDBA
"Myles" wrote:
> the key is in your post above my first post where you ask "how can I build UI
> for it" - that is the key - you have to build your own custom user interface
> for the report parameters. Instead of using a drop down list to display the
> allowable parameter values, use a list box, which supports selecting of
> multiple values. You have to go the route of using the web services, not url
> access (for my solution) - which direction are you currently headed?
> It is a lot of work to create the custom ui, but it does give you the
> ability to pass multiple values into a stored procedure and use dynamic SQL
> to fuel the dataset for the report.
>
> I hope this helps,
>|||thanks Keith - would you be able to cut and paste your response into the
correct thread for mvp? I somehow posted this to the wrong area (oops!) The
correct post is 'Select Multiple Items from the Drop Down' - a couple of
threads below.
I was just trying to explain how we did it, but you are entirely correct.
"Keith Powers" wrote:
> Actually you could still use the URL approach and code a function that parses
> your parameter string. Simply encode a delimiter between the multi-select
> values and use a UDF to parse the parameter in your stored procedure. Unless
> the URL will be too large to pass.
> --
> Keith Powers, MCDBA
>
> "Myles" wrote:
> > the key is in your post above my first post where you ask "how can I build UI
> > for it" - that is the key - you have to build your own custom user interface
> > for the report parameters. Instead of using a drop down list to display the
> > allowable parameter values, use a list box, which supports selecting of
> > multiple values. You have to go the route of using the web services, not url
> > access (for my solution) - which direction are you currently headed?
> >
> > It is a lot of work to create the custom ui, but it does give you the
> > ability to pass multiple values into a stored procedure and use dynamic SQL
> > to fuel the dataset for the report.
> >
> >
> > I hope this helps,
> >
> >sql

Multiple INSERT's INTO temp table w/ Primary Key

Hi all -
I need to create a temp table containing all Items with activity for the
last five years. These records will be coming from a few different tables.
So I have the following . . .
CREATE TABLE #tt_item (item nvarchar(30) PRIMARY KEY)
INSERT INTO #tt_item
SELECT dbo.item.item
FROM Orders
. . . . which I'll have to repeat for each table in question.
My question is, with the PRIMARY KEY on the #tt_item table, how do I check
for the existence of an Item in #tt_item as I'm doing the INSERT?This depends on what you want to do when there is a conflict (Duplicate key
value)...
Do you want to ignore the dupe, (not insertt it)? If so, then
CREATE TABLE #tt_item (item nvarchar(30) PRIMARY KEY)
-- --
INSERT INTO #tt_item(item)
SELECT O.item
FROM Orders O
Where Not Exists (Select * From #tt_item
Where item = O.item)
If otoh, you want to insert them anyway, then you cannot use item as the
primary key of the temp table... You will need to make a composite key
consisting of, say, (Sourcetable, item)
"Michael.Fisher" wrote:

> Hi all -
> I need to create a temp table containing all Items with activity for the
> last five years. These records will be coming from a few different tables
.
> So I have the following . . .
> CREATE TABLE #tt_item (item nvarchar(30) PRIMARY KEY)
> INSERT INTO #tt_item
> SELECT dbo.item.item
> FROM Orders
> . . . . which I'll have to repeat for each table in question.
> My question is, with the PRIMARY KEY on the #tt_item table, how do I check
> for the existence of an Item in #tt_item as I'm doing the INSERT?|||This depends on what you want to do when there is a conflict (Duplicate key
value)...
Do you want to ignore the dupe, (not insertt it)? If so, then
CREATE TABLE #tt_item (item nvarchar(30) PRIMARY KEY)
-- --
INSERT INTO #tt_item(item)
SELECT O.item
FROM Orders O
Where Not Exists (Select * From #tt_item
Where item = O.item)
If otoh, you want to insert them anyway, then you cannot use item as the
primary key of the temp table... You will need to make a composite key
consisting of, say, (Sourcetable, item)
"Michael.Fisher" wrote:

> Hi all -
> I need to create a temp table containing all Items with activity for the
> last five years. These records will be coming from a few different tables
.
> So I have the following . . .
> CREATE TABLE #tt_item (item nvarchar(30) PRIMARY KEY)
> INSERT INTO #tt_item
> SELECT dbo.item.item
> FROM Orders
> . . . . which I'll have to repeat for each table in question.
> My question is, with the PRIMARY KEY on the #tt_item table, how do I check
> for the existence of an Item in #tt_item as I'm doing the INSERT?|||INSERT INTO #tt_item (item)
SELECT O.item
FROM Orders AS O
LEFT JOIN #tt_item AS I
ON O.item = I.item
WHERE I.item IS NULL
OR:
INSERT INTO #tt_item (item)
SELECT item
FROM Orders
UNION
SELECT item
FROM Foo
UNION
SELECT item
FROM Bar
UNION
..
David Portas
SQL Server MVP
--|||Many thanks David, as well as CBretana, for providing these timely, helpful
examples. Looks like any of these will work.
"David Portas" wrote:

> INSERT INTO #tt_item (item)
> SELECT O.item
> FROM Orders AS O
> LEFT JOIN #tt_item AS I
> ON O.item = I.item
> WHERE I.item IS NULL
> OR:
> INSERT INTO #tt_item (item)
> SELECT item
> FROM Orders
> UNION
> SELECT item
> FROM Foo
> UNION
> SELECT item
> FROM Bar
> UNION
> ...
> --
> David Portas
> SQL Server MVP
> --
>

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 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

Multiple columns as the pivot key

Hello,

Here is a sample of the data that I am trying to pivot;

rec_id sequence field_name value

1 1 cat_nbr Granrier

1 1 cat_page pg 21

1 2 cat_nbr H&S

1 2 cat_page pg234

2 1 cat_nbr Ford

2 1 cat_page pg5

I need to pivot on rec_id and sequence to get an output like this:

rec_id sequence cat_nbr cat_page

1 1 Granrier pg21

1 2 H&S pg234

2 1 Ford pg5

All I seem to be able to get thoug is this:

rec_id sequence cat_nbr cat_page

1 1 Granrier

1 1 pg21

1 2 H&S

1 2 pg234

2 1 Ford pg5

It seems to me that the pivot transform can only pivot around one key value column. What am I missing?

Thanks.

This is an easy SQL statement -- no need for SSIS: (I'm assuming your table is named "table" -- change accordingly)

SELECT a.rec_id, a.sequence, a.cat_nbr, b.cat_page
FROM

(SELECT rec_id, sequence, value as cat_nbr
FROM table
WHERE field_name = 'cat_nbr') a,

(SELECT rec_id, sequence, value as cat_page
FROM table
WHERE field_name = 'cat_page') b

WHERE a.rec_id = b.rec_id
AND a.sequence = b.sequence|||you can also use a derived column and combine the various elements of the compound key into a single key field. i.e., rec_id + "_" + sequence.|||I was having the same problem. Add a Sort transform before the pivot in which you sort by the key columns. That should eliminate the duplicates.

Multiple column primary key

Hi,
I'd like to know what is a good reason to use a primary key on more
than one field. In a many-to-many relationship, where I have these
tables :
ITEMS
item_id (pk)
...
...
PROPS
prop_id (pk)
...
...
ITEMS_PROPS
item_id
prop_id
For the table ITEMS_PROPS, is it a good idea to use a primary key on
both fields? Or is it better not to have any primary key at all?
Thanks a lotIt the table is truly many-to-many, then you must include both fields in the
primary key unless you create a surrogate key (which I do not recommend for
this situation). I would strongly discourage not having a primary key on
the table. In fact, for this situation, I can't imagine why you wouldn't
want one.
Regards,
John Opincar
"ibiza" <lambertb@.gmail.com> wrote in message
news:1154876795.909121.193220@.m73g2000cwd.googlegroups.com...
> Hi,
> I'd like to know what is a good reason to use a primary key on more
> than one field. In a many-to-many relationship, where I have these
> tables :
> ITEMS
> item_id (pk)
> ...
> ...
> PROPS
> prop_id (pk)
> ...
> ...
> ITEMS_PROPS
> item_id
> prop_id
> For the table ITEMS_PROPS, is it a good idea to use a primary key on
> both fields? Or is it better not to have any primary key at all?
> Thanks a lot
>

Multiple column primary key

Hi,
I'd like to know what is a good reason to use a primary key on more
than one field. In a many-to-many relationship, where I have these
tables :
ITEMS
item_id (pk)
...
...
PROPS
prop_id (pk)
...
...
ITEMS_PROPS
item_id
prop_id
For the table ITEMS_PROPS, is it a good idea to use a primary key on
both fields? Or is it better not to have any primary key at all?
Thanks a lotIt the table is truly many-to-many, then you must include both fields in the
primary key unless you create a surrogate key (which I do not recommend for
this situation). I would strongly discourage not having a primary key on
the table. In fact, for this situation, I can't imagine why you wouldn't
want one.
Regards,
John Opincar
"ibiza" <lambertb@.gmail.com> wrote in message
news:1154876795.909121.193220@.m73g2000cwd.googlegroups.com...
> Hi,
> I'd like to know what is a good reason to use a primary key on more
> than one field. In a many-to-many relationship, where I have these
> tables :
> ITEMS
> item_id (pk)
> ...
> ...
> PROPS
> prop_id (pk)
> ...
> ...
> ITEMS_PROPS
> item_id
> prop_id
> For the table ITEMS_PROPS, is it a good idea to use a primary key on
> both fields? Or is it better not to have any primary key at all?
> Thanks a lot
>