Is this a datatype mismatch?

Question

Post reply

Is this a datatype mismatch?

pveilleux

Ten Centuries

Points: 1363
More actions
November 16, 2011 at 1:48 pm

#390877

I seem to remember from back in the day that if two columns weren't defined exactly the same, but were used in a join, it was considered a datatype mismatch and any index that appeared to qualify was ignored by the optimizer. Assuming that is correct, I believe the following was considered a mismatch:
CREATE TABLE A ( PTY_ID CHAR(12), NAME(VARCHAR(100) )
CREATE NONCLUSTERED INDEX NC_A_PTY_ID ON A (PTY_ID)
CREATE TABLE B ( PTY_ID CHAR(11), NAME(VARCHAR(100) )
CREATE NONCLUSTERED INDEX NC_B_PTY_ID ON B (PTY_ID)
SELECT A.PTY_ID, B.NAME
FROM A, B
WHERE A.PTY_ID = B.PTY_ID
That's just pseudo-code to illustrate what I think I was remembering. I'm troubleshooting a large query that has a 78m record table as the outer table in an outer join with 12 other tables. The outer table has the join column defined as CHAR(11), but all the others are CHAR(12). All the other 12 tables are joined against this column.
So, again, not sure if I'm mis-remembering here, but I'm hoping someone can confirm or deny this for me.
thanks....

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

sturner SSC-Insane Points: 22411 More actions · Answer 1

no, you can still join on these columns.

The probability of survival is inversely proportional to the angle of arrival.

Jared SSC Guru Points: 61793 More actions · Answer 2

pveilleux (11/16/2011)
I seem to remember from back in the day that if two columns weren't defined exactly the same, but were used in a join, it was considered a datatype mismatch and any index that appeared to qualify was ignored by the optimizer. Assuming that is correct, I believe the following was considered a mismatch:
CREATE TABLE A ( PTY_ID CHAR(12), NAME(VARCHAR(100) )
CREATE NONCLUSTERED INDEX NC_A_PTY_ID ON A (PTY_ID)
CREATE TABLE B ( PTY_ID CHAR(11), NAME(VARCHAR(100) )
CREATE NONCLUSTERED INDEX NC_B_PTY_ID ON B (PTY_ID)
SELECT A.PTY_ID, B.NAME
FROM A, B
WHERE A.PTY_ID = B.PTY_ID
That's just pseudo-code to illustrate what I think I was remembering. I'm troubleshooting a large query that has a 78m record table as the outer table in an outer join with 12 other tables. The outer table has the join column defined as CHAR(11), but all the others are CHAR(12). All the other 12 tables are joined against this column.
So, again, not sure if I'm mis-remembering here, but I'm hoping someone can confirm or deny this for me.
thanks....

I believe you are thinking of a union where the datatypes have to be the same in the returned columns.

Thanks,

Jared

Jared
CE - Microsoft

okbangas SSChampion Points: 11773 More actions · Answer 3

Precision, Scale and Length

This page does not cover it entirely however, as it does not say anything about joins of text columns. However, the comparison operator for joins follow the same pattern as union/select/join, but if any of the column is in the select list, it is returned as it is stored in the database.

I hope this was a clear and precise explanation.

Ole Kristian Velstadbråten Bangås - Virinco - Facebook - Twitter

Concatenating Row Values in Transact-SQL[/url]

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 4

I believe you are thinking of a union where the datatypes have to be the same in the returned columns.

Small Correction. "The data types must be compatible."

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 5

I seem to remember from back in the day that if two columns weren't defined exactly the same, but were used in a join, it was considered a datatype mismatch and any index that appeared to qualify was ignored by the optimizer

It's not true (at least for new versions). You may use them in JOINs and Optimizer will seek an index (if available). The only catch I see here is Collation.

If detected as Collation Conflict it will raise error but if ignored I am not sure whether it will go for index.

Contained Database Collations

http://msdn.microsoft.com/en-us/library/ff929080(v=SQL.110).aspx

pveilleux Ten Centuries Points: 1363 More actions · Answer 6

Thanks for the responses, folks....

Based on what you've said, it sounds like an appropriate index should be considered in the case I described. I think what I may be thinking of was a join on char-varchar, and I also recall something about one allowing NULL and the other not allowing NULL having an effect on whether or not an index can be considered. Again, this might be with some of the older versions (2000 or so), but I'm on 2008 R2, so hopefully its irrelevant.