SQLServer --- data type conversion during query, sqlserver Data Type

SQLServer --- data type conversion during query, sqlserver Data Type
SQLServer --- data type conversion during Query

 

Two days ago, when maintaining the Personnel Archive Management System of a talent service center in a city, we found this problem. The new archive numbering rule is date + maximum number of existing archive + 1 (six digits, less than six digits fill in the intermediate zero) (CONVERT ([varchar] (9), [createTime], (112) + '') + right (100000000000 .) + [num], (6) Example: 20150511007841. To tell the truth, I really don't know why I used the maximum number instead of the total number of records + 1 (no data deletion exists). Let's talk about the problems and solutions.

Problem Reproduction

Total number of records in the query table + 1

select count(number)+1 from T_UniversityStudent

Execution result

Query the maximum data in the table + 1

select MAX(number)+1fromT_UniversityStudent

Execution result

Cause

Why is there 7840 data records in the database and 1 data record each time? Why?

I think experienced developers already know if there is a problem with the data type. In the string size comparison, 9> 1000 is true, that is, 999> 7840 is also correct.

We sorted to verify whether the above statement is correct.

select number from T_UniversityStudentorderbynumber asc

Execution result

Problem Solving

Since we already know that there are problems in the field design process, the simplest thing is to change the field data type, however, this method is not the best because we do not know whether modifying the data type will cause adverse consequences.

Now we can use the cast and convert functions.

The usage is as follows:

Sort by converting varchar type to int type (use the convert function)

select number from T_UniversityStudentorderbyconvert(int,number)asc

Execution result

Convert varchar in the query field to int

select MAX(cast(numberasint))+1fromT_UniversityStudent

Execution result

It should be noted that the convert function can only be used in SQLServer, while the cast function can be used in oracle and SQL Server. If it is supported in other databases, you need to try it yourself.