Joining tables based on string key - bad idea?

The data already in the tables allows me to obtain what I need by drawing a relationship based on two columns that have nvarchar values but I have noticed that generally tables are related through integer keys. I want to know whether there are downsides to doing this, specifically if the join condition could 'wrongly' fail due to the string nature of the join criteria and thus cause missing rows in my resulting table.

Its typically not that great of an idea because it is going to take SQL SErver longer to join on these fields because they are larger than integer fields. The larger the field value, the longer it takes to compare (and in this case, join).
Tim|||

As Tim indicated, string values for JOINs is generally not a good idea. It has to do with how many bytes of data that has to be stored and read from the indexes. The shorter the values, the quicker index searching becomes.

However, if the string values are 'short' ( < 10 characters ), and the columns are indexed, it will most likely perform fine for you. The variables include the total number of rows in the table, amount of table activity, etc. I would NOT allow these string keys to be easily (if ever) changed.

|||

If you use character columns...

it's also a good idea to put adequate constraints on the columns or define foreign references to help ensure the quality of the data, so you don't end up with broken relationships

JOIN outside of WHERE clause ? ? ? ?

view 1

I have a view that is drawing from two tables. Each table contains fields representing cube coordinates. The view is filtering the results based on some simple logic (where the defference between two values in the tables are greater than x) this part works fine.

view 2

notes field

I want to include a note field in my view. This field will contain the contents of a note field from another view. This second view also contains coordinates that I can use to map the notes to the appropriate rows in view 1. However, if I join the views in my FROM clause, I will end up filtering my resultset down to rows that correspond to view 2's contents.

I want to have the full contents of view 1, displayed with a note field containing the note field content from view 2 only in the rows that have corresponding notes. (some rows will have notes, some will not)

eg.

VIEW 1

row1 row2 row3 note_row (from view 2)

fsdfs sdfsdf sdfsdf <no note>

sdfs sdfsd sdfsd "note"

sdfsdf sdfsdf ssdfsd <no note>

so... my question: is there any way that I can include this field without joining the views in my FROM clause (meking my resultset exclusive)..... possibly somehow in fields list of the select statement?

THANKS!

Sounds like a UNION or UNION ALL would do the trick because you could avoid joining the two recordsets. Create that as your inner query and query it as a virtual table, maybe?

Lee Everest

www.texastoo.com/sqlblog

|||

You're looking at left join.

e.g.

Code Snippet

select v1.*, v2.notes

from v1 left join v2 on v1.id=v2.id

|||Thanks. I realized this in the middle of the night last night and tried it out... the LEFT JOIN works. Thanks.