[SQL] don't let SQL Server help you automatically convert data types in SQL commands

server | sqlserver | data | data type | conversion Why not let SQL Server automatically convert the data type in SQL command for you
Report Date: 12/5/2003

Prepared by: Zheng Ye

Article last modified on 2004-2-16

The information in this article applies to:

ü Microsoft SQL Server 2000, 7.0
Problem statement:
One day, execute
SELECT * FROM XXX_ORIGINAL_20031205
where msgid = 62010388000012
Statement, as a result SQL Server reported an error: "An error occurred while converting the data type varchar to numeric."

What does it mean?
The type of this Msgid field is: varchar (30).
 
surroundings:
Database server: Microsoft SQL Server 2000 and 7.0;
Database server patch: Microsoft SQL Server 2000 ServicePack1;


Cause Analysis:
It's not that SQL Server suddenly cannot automatically convert from a number to a string, but that there is a problem with the value of this field alone, which is also related to the actual stored string in this field.


You see, there is no problem when I execute this SQL statement, it can be automatically converted:
SELECT * FROM XXXX_ORIGINAL_20031205
where recordid = 62010388000012

The type of the recordid field is also: varchar (30).
Why is this possible?
why?
This is because the real value of the msgid field is similar to the string "12051113280101053509". Since your SQL command requires that the string match the number 62010388000012 provided by us, SQL Server defaults to so many "12051113280101053509" first All converted to numbers, and then matched with 62010388000012.

(First of all, this involves an efficiency issue. How much waste would be to convert so many msgids into numbers and then match your numbers)

Of course, this time SQLServer cannot be turned over, because the number "12051113280101053509" is too large to exceed the range, so you see that SQLServer reports "An error occurred when converting the data type varchar to numeric". He was referring to Historical data "12051113280101053509" This varchar (30) cannot be converted to numeric, instead of converting the parameter 62010388000012 passed by your SQL script failed.


Let's look at another form of error to make it clearer:
We execute
SELECT * FROM XXXX_ORIGINAL_20031205
where msgid = 120

Command will get the error:
Conversion of varchar value '12050003010101026986' overflowed int column. Exceeded maximum integer value.

Does this error clearly indicate what SQL Server is doing when it executes SQL commands for you?

He tries to help you actively convert this field in the record to the data type you specified in the SQL command.

 
My advice:
Many times, we are too lazy to see what type a field is, char, tinyint, bool, or varchar, so we just write a number and let the smart SQL Server determine what to turn into.

However, first, SQL Server does not convert the values in your script commands, but converts the existing historical data to the type you specify, so it will increase the execution time; second, it is easy to convert errors.

Therefore, don't let SQLServer judge for itself, it will automatically help you convert, it will reduce the execution efficiency and increase the probability of error. If you can tell SQL Server your data type explicitly, please do so.


Writen by zhengyun.NoJunk (at) tomosoft.dot.com
Disclaimers:
The information contained in this document represents the current views of ZhengYun on the issues discussed at the date of publication, and Zhengyun does not guarantee the accuracy of the information given after the date of publication.

This document is for reference only. Zhengyun makes no warranties, express or implied, with regard to the information in this document.

Users must comply with all applicable copyright laws. Without limiting the rights provided by the copyright law, without the express written permission of zhengyun and CSDN.Net, you must not use it for any purpose, in any form or by any means (electronic, mechanical, photocopying, recording, etc. ) Copy, disseminate any part of this article, and do not store or introduce it into the retrieval system.