Best solution

Let say I have a table that is composed of 11 columns - one the Primary Key and the other are keys to rows in another table. Of these 10 column 2-10 are nullable. Can I get all the info in one SELECT? I can't use JOINS because columns 1-10 are keys to the same table. I am not very good at explaining these things but hopefully it makes sense.

ThanksNope, it's not making sense :-) Can you provide a small example with data to illustrate what you are trying to do?

Terri|||If I understand correctly, can you do several UNIONs and get them in turn?

Table1 join table 2 on col2 UNION
Table1 join table 2 on col3 UNION
Table1 join table 2 on col4 UNION
etc.

What it sounds like is that you should have a third table that contains a record for each possible combination of keys between table1 and table2. It sounds correcting the database structure is the best bet if you are able to do that.|||okay let me try :)

Let say I have a row that consist of the following:

TABLE 1:
key|ele1|ele2|ele3|ele4|ele5
1 6 2 5 null null

key column contains the rowID

ele1 - ele5 columns contain row IDs from the same table. ele1 is not nullable but the rest is nullable. I think if I use JOINS I will get an "ambigious error."

Table 2 ( ele ):
key|name |value
1 | "first" | 1
2 | "second" | 2
3 | "third" | 3 and so on.|||You should be able to accomplish what you need using JOINs with aliases.

SELECT
 table1.key,
 table2key.name,
 table2key.value,
 table1.ele1,
 table2ele1.name,
 table2ele1.value,
 table1.ele2,
 table2ele2.name,
 table2ele2.value,
 table1.ele3,
 table2ele3.name,
 table2ele3.value,
 table1.ele4,
 table2ele4.name,
 table2ele4.value,
 table1.ele5,
 table2ele5.name,
 table2ele5.value
FROM
 table1
LEFT OUTER JOIN
 table2 AS table2key ON table1.key = table2key.key
LEFT OUTER JOIN
 table2 AS table2ele1 ON table1.ele1 = table2ele1.key
LEFT OUTER JOIN
 table2 AS table2ele2 ON table1.ele2 = table2ele2.key
LEFT OUTER JOIN
 table2 AS table2ele3 ON table1.ele3 = table2ele3.key
LEFT OUTER JOIN
 table2 AS table2ele4 ON table1.ele4 = table2ele4.key
LEFT OUTER JOIN
 table2 AS table2ele5 ON table1.ele5 = table2ele5.key

Terri|||Thanks so much for all your help Terri!

Best Primary Key Solution?

Hi there,

Looking for a bit of help with my problem:

Say i have 3 tables-

tblClients
clientID (primary key identity/autonumber)
clientName (varchar 50)

tblCities
cityID (primary key identity/autonumber)
cityName (varchar 50)

tblClientsCities
ID (primary key identity/autonumber)
clientID (int)
cityID (int)

A client can be located in more than 1 city so i have tblClientsCities (think thats the right way to do it). Say i add a new client and the autonumber changes to "10" which is that client's identifier. How do i then add that identifier to tblClientsCities? I mean it could have been 3,7,205 absolutley anything.

I thought is would be easier to make up a unique key for each client with a script eg

client name: PJ Computers
Unique key Generated: PJCOMP58784

Now that the primary key is known in advance it can be added to tblClients and then tblClientCities. But! i was reading around and many seem to think primary key's like this will slow things down.

So my question is what's the best way of accomplishing this?

Any help would be much appreciated, thanks :)Check out @.@.identity (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_globals_50u1.asp) and/or scope_identity (http://msdn.microsoft.com/library/en-us/tsqlref/ts_sa-ses_6n8p.asp). These allow you to work with IDENTITY columns.

"Smart key" values like you suggested are bad for many reasons. The biggest practical problem is key colisions. The biggest theoretical problem is data changes and how those affect the smart key. There are many other problems, these are just the tip of the iceberg.

If you want to pursue an avenue a lot like your "smart key" approach that does not have the problems, consider using GUID values using NewId (http://msdn.microsoft.com/library/en-us/tsqlref/ts_na-nop_4pt0.asp) and UniqueIdentifier (http://msdn.microsoft.com/library/en-us/tsqlref/ts_ua-uz_6dyq.asp) columns.

-PatP

Best Practices Question?

Have a question about which method is the more accepted method.
I have two tables: table1 and table2.
They are joined by a primary and foreign key field:
table1
table1ID (Primary Key)
Room
NotAvailable (bit field: 1 will be not available and 0 will be available)
table2
table2ID (Primary Key)
table1ID (Foreign Key to table1)
DateUsed
There will only be records in table2 for rooms that are not available, so
when the two tables are inner joined, no matter how many records there are i
n
table1, the only records that will show up is what is matched in table2.
Question is: Is it good practice to use a field such as NotAvailable, to kno
w
that a room is not available, or use the results of the join to set a
NotAvailable property field in my code?
Thank you for any responses.
Note: I cannot use our company's actual field names, so disregard what the
names are and other ways to show a room as not available. Just want to show
the structure for the question I am asking.The problem with the NotAvailable field is that it requires modification
whenever there is an INSERT or Update in another table. And then the
question arises: "What exactly does NotAvailable mean since there is no time
period included". Is it NotAvailable today, tomorrow, next week, etc. That
provides opportunities for de-synching of the data -unless there is a
Trigger on the Table2.
Using a query joining the two tables (perhaps including a calendar table for
future dates) 'should' always provide an accurate presentation of data. (I'm
thinking of tables for Rooms and Reservations -therefore the Calendar table
is needed.)
It's not a 'Best Practice' to store data that is the result of some
manipulation of other data. But with careful planning, sometimes it has to
be done.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
news:AA6DB4DC-1E7E-4007-AEDA-C8355D385A28@.microsoft.com...
> Have a question about which method is the more accepted method.
> I have two tables: table1 and table2.
> They are joined by a primary and foreign key field:
> table1
> table1ID (Primary Key)
> Room
> NotAvailable (bit field: 1 will be not available and 0 will be available)
> table2
> table2ID (Primary Key)
> table1ID (Foreign Key to table1)
> DateUsed
> There will only be records in table2 for rooms that are not available, so
> when the two tables are inner joined, no matter how many records there are
> in
> table1, the only records that will show up is what is matched in table2.
> Question is: Is it good practice to use a field such as NotAvailable, to
> know
> that a room is not available, or use the results of the join to set a
> NotAvailable property field in my code?
> Thank you for any responses.
> Note: I cannot use our company's actual field names, so disregard what the
> names are and other ways to show a room as not available. Just want to
> show
> the structure for the question I am asking.|||Mike Collins wrote:
> Have a question about which method is the more accepted method.
> I have two tables: table1 and table2.
> They are joined by a primary and foreign key field:
> table1
> table1ID (Primary Key)
> Room
> NotAvailable (bit field: 1 will be not available and 0 will be available)
> table2
> table2ID (Primary Key)
> table1ID (Foreign Key to table1)
> DateUsed
> There will only be records in table2 for rooms that are not available, so
> when the two tables are inner joined, no matter how many records there are
in
> table1, the only records that will show up is what is matched in table2.
> Question is: Is it good practice to use a field such as NotAvailable, to k
now
> that a room is not available, or use the results of the join to set a
> NotAvailable property field in my code?
> Thank you for any responses.
> Note: I cannot use our company's actual field names, so disregard what the
> names are and other ways to show a room as not available. Just want to sho
w
> the structure for the question I am asking.
You should rely on the data in table2 to determine if a room is
available. Using the bit field, you're exposing yourself to potentially
out-of-sync data, and you're duplicating the room status.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Thank you to both of you for your replies. That is the way I was thinking it
should be just needed a better way to explain it and get my point across.
"Mike Collins" wrote:

> Have a question about which method is the more accepted method.
> I have two tables: table1 and table2.
> They are joined by a primary and foreign key field:
> table1
> table1ID (Primary Key)
> Room
> NotAvailable (bit field: 1 will be not available and 0 will be available)
> table2
> table2ID (Primary Key)
> table1ID (Foreign Key to table1)
> DateUsed
> There will only be records in table2 for rooms that are not available, so
> when the two tables are inner joined, no matter how many records there are
in
> table1, the only records that will show up is what is matched in table2.
> Question is: Is it good practice to use a field such as NotAvailable, to k
now
> that a room is not available, or use the results of the join to set a
> NotAvailable property field in my code?
> Thank you for any responses.
> Note: I cannot use our company's actual field names, so disregard what the
> names are and other ways to show a room as not available. Just want to sho
w
> the structure for the question I am asking.

Best Practices Question?

Have a question about which method is the more accepted method.
I have two tables: table1 and table2.
They are joined by a primary and foreign key field:
table1
table1ID (Primary Key)
Room
NotAvailable (bit field: 1 will be not available and 0 will be available)
table2
table2ID (Primary Key)
table1ID (Foreign Key to table1)
DateUsed
There will only be records in table2 for rooms that are not available, so
when the two tables are inner joined, no matter how many records there are in
table1, the only records that will show up is what is matched in table2.
Question is: Is it good practice to use a field such as NotAvailable, to know
that a room is not available, or use the results of the join to set a
NotAvailable property field in my code?
Thank you for any responses.
Note: I cannot use our company's actual field names, so disregard what the
names are and other ways to show a room as not available. Just want to show
the structure for the question I am asking.The problem with the NotAvailable field is that it requires modification
whenever there is an INSERT or Update in another table. And then the
question arises: "What exactly does NotAvailable mean since there is no time
period included". Is it NotAvailable today, tomorrow, next week, etc. That
provides opportunities for de-synching of the data -unless there is a
Trigger on the Table2.
Using a query joining the two tables (perhaps including a calendar table for
future dates) 'should' always provide an accurate presentation of data. (I'm
thinking of tables for Rooms and Reservations -therefore the Calendar table
is needed.)
It's not a 'Best Practice' to store data that is the result of some
manipulation of other data. But with careful planning, sometimes it has to
be done.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
news:AA6DB4DC-1E7E-4007-AEDA-C8355D385A28@.microsoft.com...
> Have a question about which method is the more accepted method.
> I have two tables: table1 and table2.
> They are joined by a primary and foreign key field:
> table1
> table1ID (Primary Key)
> Room
> NotAvailable (bit field: 1 will be not available and 0 will be available)
> table2
> table2ID (Primary Key)
> table1ID (Foreign Key to table1)
> DateUsed
> There will only be records in table2 for rooms that are not available, so
> when the two tables are inner joined, no matter how many records there are
> in
> table1, the only records that will show up is what is matched in table2.
> Question is: Is it good practice to use a field such as NotAvailable, to
> know
> that a room is not available, or use the results of the join to set a
> NotAvailable property field in my code?
> Thank you for any responses.
> Note: I cannot use our company's actual field names, so disregard what the
> names are and other ways to show a room as not available. Just want to
> show
> the structure for the question I am asking.|||Mike Collins wrote:
> Have a question about which method is the more accepted method.
> I have two tables: table1 and table2.
> They are joined by a primary and foreign key field:
> table1
> table1ID (Primary Key)
> Room
> NotAvailable (bit field: 1 will be not available and 0 will be available)
> table2
> table2ID (Primary Key)
> table1ID (Foreign Key to table1)
> DateUsed
> There will only be records in table2 for rooms that are not available, so
> when the two tables are inner joined, no matter how many records there are in
> table1, the only records that will show up is what is matched in table2.
> Question is: Is it good practice to use a field such as NotAvailable, to know
> that a room is not available, or use the results of the join to set a
> NotAvailable property field in my code?
> Thank you for any responses.
> Note: I cannot use our company's actual field names, so disregard what the
> names are and other ways to show a room as not available. Just want to show
> the structure for the question I am asking.
You should rely on the data in table2 to determine if a room is
available. Using the bit field, you're exposing yourself to potentially
out-of-sync data, and you're duplicating the room status.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Thank you to both of you for your replies. That is the way I was thinking it
should be just needed a better way to explain it and get my point across.
"Mike Collins" wrote:
> Have a question about which method is the more accepted method.
> I have two tables: table1 and table2.
> They are joined by a primary and foreign key field:
> table1
> table1ID (Primary Key)
> Room
> NotAvailable (bit field: 1 will be not available and 0 will be available)
> table2
> table2ID (Primary Key)
> table1ID (Foreign Key to table1)
> DateUsed
> There will only be records in table2 for rooms that are not available, so
> when the two tables are inner joined, no matter how many records there are in
> table1, the only records that will show up is what is matched in table2.
> Question is: Is it good practice to use a field such as NotAvailable, to know
> that a room is not available, or use the results of the join to set a
> NotAvailable property field in my code?
> Thank you for any responses.
> Note: I cannot use our company's actual field names, so disregard what the
> names are and other ways to show a room as not available. Just want to show
> the structure for the question I am asking.sql

Best Practice: Primary key in joing table

hi there,

i have the following joining table (many-to-many relationship)...

CREATE TABLE [dbo].[products_to_products_swatch] (
[products_to_products_swatch_id] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
[product_id] [int] NOT NULL ,
[products_swatch_id] [int] NOT NULL
) ON [PRIMARY]
GO

question: do i need to include a primary key in this table - being that it is a joing table?

thanks
mikejoing table ? i mean joining table :o)|||If this is simply implementing a many-to-many join, then there is no need for a surrogate key. Just declare a composite primary key consisting of the foreign keys to both tables.
If you are storing additional information regarding the relationship (timestamp, notes, modifier, whatever) you may want to include a surrogate key for developmental consistency with your other tables, but it is not required.

Best practice for conditional insert else select?

I have several places where I need to get the id (primary key) of a resource, inserting a row if the resource does not exist (i.e. an artificial key to be used as an FK for another table). I should probably change this varchar key lookup to use a hash index, but that is beside the point.

So the table is essentially like:

CREATE TABLE MyLookup(id int identity primary key nonclustered, mykey varchar(256));

CREATE CLUSTERED INDEX mylookup_cidx_mykey ON MyLookup(mykey);

I see two main approaches for how I can do my get-id-with-insert-if-needed.

(Approach 1)

DECLARE @.id INT;

SELECT @.id = id FROM MyLookup WHERE mykey = 'some key value';

IF (@.id is null)

BEGIN

INSERT MyLookup ('some key value');

SET @.id = SCOPE_IDENTITY();

END

(Approach 2)

DECLARE @.id INT;

INSERT MyLookup SELECT 'some key value' WHERE NOT EXISTS (SELECT id FROM MyLookup WHERE mykey = 'some key value');

IF (@.@.ROWCOUNT = 0)

SELECT @.id = id FROM MyLookup WHERE mykey = 'some key value';

ELSE

SET @.id = SCOPE_IDENTITY();

From some quick tests in profiler, approach 2 seems to be a bit faster and have lower resource utilization. But I'm not sure if it maybe takes some more aggressive locks even in the unnecessary case where the mykey row value of 'some key value' already exists. Approach 2 also looks cleaner to me, but I don't mind a bit of extra code if it gives me better scalability through less lock contention.

Any tip on what is considered the best practice for a conditional insert like this, or a tip on how to get detailed lock info for a query? The lock info for profiler was all greek to me, it just had a hex value with each lock acquired/released, so I have no idea what it was telling me. Is my only solution to just run exhaustive tests and look at the perf numbers from the black box?

I went ahead and did some testing, no big surprises. Although the tests were single client, so it doesn't give me any info about locking.

I found unsurprisingly that the more sparse the table, the narrower the gap between the two options. But as the likelihood of needing an insert went down, the first approach became more effective. In my production environment I would hazard a guess that I need an insert around 5% of the time, so I'll probably go with approach 1 in general. Although I did change my approach 1 to actually incorporate approach 2 with in - first I do a select, and then if the id was null then I do a conditional insert. There is a slight increase in maintenance cost since I have to duplicate the code for the existential check, but I think it is worthwhile in my cases that are fairly high traffic.

Any other points of view on this?

Best GUID Storage

Because of the problem getting IDENTITY primary key values back when
inserting batches of rows, I would like to experiment with using
GUIDs. Within an application, I would like to assign the primary keys
to the rows and then pass them into the INSERT statements. Then I
wouldn't have to worry about using triggers or Identity scope to
determine the new primary keys.
My question is basically, what's the best datatype to store the GUIDs
in the column? From what I've read so far, it looks like
UniqueIdentifier or CHAR(40) are my options. Is there any drawback to
using UniqueIdentifier?If you are storing a GUID, then why not use Uniqueidentifier data type.
In SQL Server Books Online, read the page titled "Using uniqueidentifier
Data". This page discusses the advantages and disadvantages of this
datatype.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"- TW" <Thumper@.kqrsrocks.com> wrote in message
news:151a6e6b.0402231323.29f24d45@.posting.google.com...
Because of the problem getting IDENTITY primary key values back when
inserting batches of rows, I would like to experiment with using
GUIDs. Within an application, I would like to assign the primary keys
to the rows and then pass them into the INSERT statements. Then I
wouldn't have to worry about using triggers or Identity scope to
determine the new primary keys.
My question is basically, what's the best datatype to store the GUIDs
in the column? From what I've read so far, it looks like
UniqueIdentifier or CHAR(40) are my options. Is there any drawback to
using UniqueIdentifier?|||TW,
I've used both, and it nearly always comes down to interoperability.
Some systems cannot deal with binary so you have to go varchar(36)/char(36).
Where did you get 40, incidentally?
James Hokes
"- TW" <Thumper@.kqrsrocks.com> wrote in message
news:151a6e6b.0402231323.29f24d45@.posting.google.com...
> Because of the problem getting IDENTITY primary key values back when
> inserting batches of rows, I would like to experiment with using
> GUIDs. Within an application, I would like to assign the primary keys
> to the rows and then pass them into the INSERT statements. Then I
> wouldn't have to worry about using triggers or Identity scope to
> determine the new primary keys.
> My question is basically, what's the best datatype to store the GUIDs
> in the column? From what I've read so far, it looks like
> UniqueIdentifier or CHAR(40) are my options. Is there any drawback to
> using UniqueIdentifier?|||Excellent choice to use Guids, imho.
Vyas has already pointed you to a good article on the topic, but here are a
couple of extra things not mentioned in that article:
(a) A benefit of using Guids instead of Identities is that if you ever need
to implement horizontal partitioning on the table, it will be substantially
easier with Guids. With Guids, the partitioning process is virtually
seemless to the application but partitioning tables with Identities nearly
always breaks the application.
(b) On the other hand, a problem with using Guids which is not mentioned in
that article is that T-SQL has no ISGUID() type function which causes minor
coding issues. Of course, it's possible to roll your own though.
Regards,
Greg Linwood
SQL Server MVP
"- TW" <Thumper@.kqrsrocks.com> wrote in message
news:151a6e6b.0402231323.29f24d45@.posting.google.com...
> Because of the problem getting IDENTITY primary key values back when
> inserting batches of rows, I would like to experiment with using
> GUIDs. Within an application, I would like to assign the primary keys
> to the rows and then pass them into the INSERT statements. Then I
> wouldn't have to worry about using triggers or Identity scope to
> determine the new primary keys.
> My question is basically, what's the best datatype to store the GUIDs
> in the column? From what I've read so far, it looks like
> UniqueIdentifier or CHAR(40) are my options. Is there any drawback to
> using UniqueIdentifier?

Best GUID Storage

Because of the problem getting IDENTITY primary key values back when
inserting batches of rows, I would like to experiment with using
GUIDs. Within an application, I would like to assign the primary keys
to the rows and then pass them into the INSERT statements. Then I
wouldn't have to worry about using triggers or Identity scope to
determine the new primary keys.
My question is basically, what's the best datatype to store the GUIDs
in the column? From what I've read so far, it looks like
UniqueIdentifier or CHAR(40) are my options. Is there any drawback to
using UniqueIdentifier?If you are storing a GUID, then why not use Uniqueidentifier data type.
In SQL Server Books Online, read the page titled "Using uniqueidentifier
Data". This page discusses the advantages and disadvantages of this
datatype.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"- TW" <Thumper@.kqrsrocks.com> wrote in message
news:151a6e6b.0402231323.29f24d45@.posting.google.com...
Because of the problem getting IDENTITY primary key values back when
inserting batches of rows, I would like to experiment with using
GUIDs. Within an application, I would like to assign the primary keys
to the rows and then pass them into the INSERT statements. Then I
wouldn't have to worry about using triggers or Identity scope to
determine the new primary keys.
My question is basically, what's the best datatype to store the GUIDs
in the column? From what I've read so far, it looks like
UniqueIdentifier or CHAR(40) are my options. Is there any drawback to
using UniqueIdentifier?|||TW,
I've used both, and it nearly always comes down to interoperability.
Some systems cannot deal with binary so you have to go varchar(36)/char(36).
Where did you get 40, incidentally?
James Hokes
"- TW" <Thumper@.kqrsrocks.com> wrote in message
news:151a6e6b.0402231323.29f24d45@.posting.google.com...
> Because of the problem getting IDENTITY primary key values back when
> inserting batches of rows, I would like to experiment with using
> GUIDs. Within an application, I would like to assign the primary keys
> to the rows and then pass them into the INSERT statements. Then I
> wouldn't have to worry about using triggers or Identity scope to
> determine the new primary keys.
> My question is basically, what's the best datatype to store the GUIDs
> in the column? From what I've read so far, it looks like
> UniqueIdentifier or CHAR(40) are my options. Is there any drawback to
> using UniqueIdentifier?|||Excellent choice to use Guids, imho.
Vyas has already pointed you to a good article on the topic, but here are a
couple of extra things not mentioned in that article:
(a) A benefit of using Guids instead of Identities is that if you ever need
to implement horizontal partitioning on the table, it will be substantially
easier with Guids. With Guids, the partitioning process is virtually
seemless to the application but partitioning tables with Identities nearly
always breaks the application.
(b) On the other hand, a problem with using Guids which is not mentioned in
that article is that T-SQL has no ISGUID() type function which causes minor
coding issues. Of course, it's possible to roll your own though.
Regards,
Greg Linwood
SQL Server MVP
"- TW" <Thumper@.kqrsrocks.com> wrote in message
news:151a6e6b.0402231323.29f24d45@.posting.google.com...
> Because of the problem getting IDENTITY primary key values back when
> inserting batches of rows, I would like to experiment with using
> GUIDs. Within an application, I would like to assign the primary keys
> to the rows and then pass them into the INSERT statements. Then I
> wouldn't have to worry about using triggers or Identity scope to
> determine the new primary keys.
> My question is basically, what's the best datatype to store the GUIDs
> in the column? From what I've read so far, it looks like
> UniqueIdentifier or CHAR(40) are my options. Is there any drawback to
> using UniqueIdentifier?