If you defined a variable as decimal and assigned it a value of 12.5 and you attempted to compare this value to an integer column, SQL Server converted the decimal variable to integer (essentially truncating the number to 12) before carrying out the operation. This no longer is the case in SQL Server 2000. Data type conversion logic is based on a precedence level. The variable with the lower precedence is converted to the data type of the variable with higher precedence. In the example above, the value 12.5 is unchanged because the decimal data type has a higher precedence level than that of the integer data type. Instead, the execution plan calls for integer column to be converted to decimal before the comparison is carried out. The same stored procedure generates entirely different execution plan than the one generated in the previous version. On the surface, the results are the same however behind the scene you have a totally different picture. This is causing performance issues on SQL server2000. I need suggestions on resolving this problem. Temporary, SQL2000 is running under 7.0 compatibility mode.
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations wOOdy-Soft on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Upgrade from7.0 to SQL 2000 - datatype conversion problem
- Thread starter Hpatel
- Start date
- Thread starter
- #3
There are about 350 stored procedures that need to be changed and there are some Stored procedures are as long as 1000 lines. Your suggestion works but it would require each stored procedure to be touched and modified. Also, the amount of time it will take to change. I appreciate your help. Is there any other way to tell SQL 2000 to think like 7.0 when there is datatype conversion?
- Status
Similar threads
- Locked
- Question
