SQL Server joining to different data type perfomance

Iv got a table called custo开发者_如何学JAVAmers where the pk is an int.

Is there any performance problems or issues with joining this to a table where the field is a BIGINT, when i say joining i mean inner join.

I know this is bad practice to have different types but it is not my project.

Thanks

Yes. You'll get an implicit widening conversion according to datatype precedence rules.

Any index in the int column will most likely be ignored. And given this is the PK it could be very poorly performing indeed. The same applies if you explicitly CAST too.

Unfortunately, the options would be either fix the design or add a computed, indexed column so it's bigint JOIN bigint. If you can't change the table, then run it and see: if you have a few 100s or 1000s of rows, then you may be OK. If it's millions, you're what's technically known as bollixed.

Sounds like there is nothing you can do about it (except copy the data to a table with matching data types), so I'm not sure what to say. It will have an impact on performance, but probably not nearly as bad as converting from varchar or double.

If your primary key is int and this is meant to be a foreign key bigint, then the bigint should never hold anything outside of the range of int, so the option of casting the bigint down to int (instead of upcasting from int to bigint) is never going to product a problem.

Also, depending upon your query and its execution plan, the performance hit might be minimized - depending on things like direction of join, inner or outer join, cardinality/statistics, which indexes are available, etc.

My advice would be to have explicit conversion and use computed column and an index on that to resolve any performance issues. The computed column is nothing but the bigint column value converted to int.

Refer to the following link -

http://www.sqlservercentral.com/scripts/T-SQL+Aids/31906/

I have no idea as I have never built a system like that! All I could suggest is clone the tables, profile the system, change the data types to match and then re-profile.

However my gut feeling is that there will be some overhead in converting types. So perhaps you could also mock this the other way around by profiling a known system (say PK int to int) and then insert an explicit cast (say PK int to int-cast-as-bigint) in the join clause and see what happens