About implicit conversion of SQL Server data types

Besides data type in the relational theory the fundamental concept of the domain, as sets of allowable values which can have an attribute. It is possible to tell, that the domain represents a pair {base data type, a predicate}.Thus value belongs to the domain only in the event that it has corresponding type and a predicate calculated on this value, there is a TRUE. Attributes (columns of a table) are defined on the domain that is to say besides the control of types DBMS over each change of the data should check also value of a predicate. Change will be rejected, if kept value does not satisfy to a predicate of the domain.

The domain plays one more important role more precisely the values belonging to one domain can be compared only. Let’s consider as an example PC table, namely, columns speed (clock frequency of the processor) and hd (volume of a hard disk). Both these of a columns have integer (or smallint) data type. However it is completely different characteristics. It is enough to tell, that in a subject domain for them different units of measurements - hertz and byte are used. And so, if we shall define these columns on different domains comparison of value of one column with value of other column becomes inadmissible. And it will be supervised DBMS. By analogy with categores and reference integrity such control could be named domain integrity if this term has not been occupied in SQL Server under check of CHECK constraint imposed on columns of the table. And so determined “domain integrity” in any way does not limit comparison.

Not superfluous will remind of importance of maintenance of integrity on side DBMS. Restrictions of integrity, as a rule, model the restrictions real-life in a subject domain. As these restrictions do not depend on appendices, it is natural to check them (and to write) in a place, the common for all appendices which is DBMS. It besides other:

• Relieves appendices of necessity to build (and to duplicate!) in them necessary checks;
• Guarantees higher level of safety. The restrictions which have been built - in the appendices, it is easy to bypass. To address to a database passing the application enough;
• Facilitates support and development. If restrictions of a subject domain will change, corresponding program changes will need to be made in one place, instead of in all applications working with a database.

Coming back to domains, pertinently to notice, as language standard SQL-92 does not put in concept of the domain of sense of “comparability”. That is realized by the standard, no more than an opportunity once to write down restrictions, and then repeatedly to apply them at definition of specifications columns, that is an opportunity to avoid duplications of codes.

In a chain “Theory –> Standard –> Realization” is consistently lost severity of the relational theory therefore we not can is quite transparent to cooperate with relational DBMS different producers. Here I want to show a small example of how it is necessary to address with data types in SQL Server.

So, really we have that values of one data type can be compared. For transformation of types the standard offers function CAST. I.e. generally we should transform compared values to one type, and then to carry out operation of comparison (or assignments). What will take place, if we of a variable (or column) one type shall simply to assign value of other data type? We shall consider a simple example of a code on T-SQL (SQL Server 2000 is used)

DECLARE @vc VARCHAR(10), @mn MONEY, @ft FLOAT
SELECT @vc = '499.99'
PRINT @vc
SELECT @ft = @vc
PRINT @ft

Here we describes three variables accordingly string type (VARCHAR), monetary type (MONEY) and numbers with a floating point (FLOAT). Further a string variable we assign a constant of corresponding type, and then we assign a variable such as FLOAT value of a string variable. In result we receive two identical results - 499.99 (PRINT operator carries out a conclusion to the console). That has taken place, refers to as implicit transformation data types, i.e. string value - ‘499.99’ has been automatically resulted in data type FLOAT and assigned by a variable @ft.

Let’s add in the end of a code still a pair of strings:

SELECT @mn = @vc
PRINT @mn

As a result we shall receive two similar messages about error:

SELECT @mn = CAST(@vc AS MONEY)
PRINT @mn

We got rid of the first message about error. The conclusion that the second message is given by operator PRINT arises. Instincts prompt to glance in the help. In BOL the variable which can be used in this operator is spoken about operator PRINT that should be any allowable string type (char or varchar) or should be resulted in this type implicitly. Let’s rewrite last line so

PRINT CAST(@mn AS VARCHAR)

All works. The main conclusion which we from here take consists what not all types (even if we do not see the special reasons for that) suppose implicit conversion. In particular, implicit reduction such as MONEY to types CHAR VARCHAR, NCHAR, NVARCHAR and on the contrary is not carried out.

And now about the reason, which has forced me to write very many words. On my untidiness appeared that in the basic and verifying base some equivalent columns had different data type. For example, the field price in РС table had FLOAT data type in one database and MONEY data type - in another one. During very long time it did not influence in any way work of the site, but here suddenly for the some days our two participants have decided to use implicit transformation of types to their queries with known result.:-)

I have decided to not be limited to apologies, and to write an opus about features of realization, hoping that it will bring more advantage than my apologies.

As to data types, the noticed divergence is already harmonized. Also scripts for uploading are updated.

Unfortunately, the examples resulted here cannot be executed directly on a site. It is caused by that while we did not realize an opportunity of performance of sets of operators (packages).

Therefore that who will want to check up validity told, it is necessary to take advantage Management Studio.