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

Joining table to itself

In myTable I have
RequiredAmount
RequiredDate
GrantedAmount
GrantedDate
I wish to obtain one view showing the sum per year.
For the required part it would be:
SELECT YEAR(RequiredDate) AS myYear, SUM(RequiredAmount) AS reqAmount
FROM myTable
GROUP BY YEAR(RequiredDate)
And for the Granted part:
SELECT YEAR(GrantedDate) AS myYear, SUM(GrantedAmount) AS grantAmount
FROM myTable
GROUP BY YEAR(GrantedDate)
Now, what I need is a presentation with three columns:
myYear, reqAmount, grantAmount
I'm fooling around with the table joined to itself, but can't seem to
get it right...maybe wrong approach?
Regards /SnedkerRead about Cross-tab reports in sql server help file
Madhivanan
Morten Snedker wrote:
> In myTable I have
> RequiredAmount
> RequiredDate
> GrantedAmount
> GrantedDate
> I wish to obtain one view showing the sum per year.
> For the required part it would be:
> SELECT YEAR(RequiredDate) AS myYear, SUM(RequiredAmount) AS reqAmount
> FROM myTable
> GROUP BY YEAR(RequiredDate)
>
> And for the Granted part:
> SELECT YEAR(GrantedDate) AS myYear, SUM(GrantedAmount) AS grantAmount
> FROM myTable
> GROUP BY YEAR(GrantedDate)
>
> Now, what I need is a presentation with three columns:
> myYear, reqAmount, grantAmount
> I'm fooling around with the table joined to itself, but can't seem to
> get it right...maybe wrong approach?
>
> Regards /Snedker|||use a full outer join (self)..
something like this..
untested..
SELECT COALESCE(A.myYear,B.myYear), COALESCE(A.reqAmount,0),
COALESCE(B.grantAmount,0)
FROM
(SELECT YEAR(RequiredDate) AS myYear, SUM(RequiredAmount) AS reqAmount
FROM myTable
GROUP BY YEAR(RequiredDate)
) A FULL OUTER JOIN
(SELECT YEAR(GrantedDate) AS myYear, SUM(GrantedAmount) AS grantAmount
FROM myTable
GROUP BY YEAR(GrantedDate)) B
ON A.myYear= B.myYear
Hope this helps.
-Omni|||Can you see if this works for you
SELECT
YEAR(tDate) as TransYear,
SUM(CASE WHEN tType = 'R' then tAmt else 0 end) as SumReq,
SUM(CASE WHEN tType = 'G' then tAmt else 0 end) as GraReq
FROM
(
select 'R' as tType,reqAmount as tAmt,ReqDate as tDate FROM Mytable
UNION ALL
select 'G' as tType,GrantAmount as tAmt,GrantDate as tDate FROM Mytable
) x
GROUP BY YEAR(tDate)
- Sha Anand
"Morten Snedker" wrote:

> In myTable I have
> RequiredAmount
> RequiredDate
> GrantedAmount
> GrantedDate
> I wish to obtain one view showing the sum per year.
> For the required part it would be:
> SELECT YEAR(RequiredDate) AS myYear, SUM(RequiredAmount) AS reqAmount
> FROM myTable
> GROUP BY YEAR(RequiredDate)
>
> And for the Granted part:
> SELECT YEAR(GrantedDate) AS myYear, SUM(GrantedAmount) AS grantAmount
> FROM myTable
> GROUP BY YEAR(GrantedDate)
>
> Now, what I need is a presentation with three columns:
> myYear, reqAmount, grantAmount
> I'm fooling around with the table joined to itself, but can't seem to
> get it right...maybe wrong approach?
>
> Regards /Snedker
>|||Hi,
Nice solution.. But Why do you need a case? Can't it be something simple
like this.
select TransYear, sum(GrantAmount) SumGrant, sum(reqAmount) SumReq
from
(select 0 as GrantAmount,reqAmount ,year(ReqDate) as TransYear FROM Mytable
UNION ALL
select GrantAmount,0 as reqAmount, year(GrantDate) as TransYear FROM
Mytable) x
group by TransYear
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/|||If your a consultant you know your clients couldn't give
a whit about how you provide a solution just as long as you
give them one.Perhaps we can help.Check out RAC for easy
solutions to all kinds of data manipulation problems including
crosstabs on sql server.
www.rac4sql.net