Main page

About implicit conversions of SQL Server 2000 data types

S. Moiseenko

Besides the data types in the relational theory there is a fundamental concept of the domain, as set of allowable values the attribute can take. It is possible to tell, that the domain represents a pair {base data type, a predicate}.Thus the fact the value belongs to the domain only in the case that it has respective type and predicate calculated on this value is TRUE. Attributes (columns of the table) are defined on the domain, so besides the control of the types DBMS should also check values of a predicate over each changing of the data. Changes will be rejected, if the new value does not satisfy to the predicate of the domain.

The domain plays one more significant role: the values can be compared only if they belong to one domain. Let's consider as an example the following table PC including columns speed (clock frequency of the processor) and hd (volume of the hard disk). Both of these columns have an integer type (or smallint). But they are completely different characteristics. It is enough to say, that in their subject matter the different units are used - hertzs and bytes . So, if we define these columns in the different domains, it will be impossible to compare one field value with another. And it will be checked-up by DBMS. By analogy with the categories and reference integrity such control could be named by domain integrity if this term is not occupied in SQL Server under the check of restriction CHECK imposed on the columns of the table. And so determined "domain integrity" doesn't limit the comparison in any way.

It will not be superfluous reminding about importance of maintenance of integrity on the DBMS side. As a rule, the restrictions of integrity model the limitations existing in real problem domain. As these restrictions do not depend on applications, it is natural to check them (and to write) in the common for all appendices place, which is the DBMS. In addition it makes such doings:

  • Relieving the applications from the necessity to build (and to duplicate!) necessary checks inside them;
  • Guarantying the higher level of security. The built in the appendices restrictions are easy to bypass. It is enough to accessing to the database directly, passing the application.
  • Lightening support and development. If the problem domain restrictions change, it will be necessary to make changes in one place in the corresponding program, instead modifying all application s working with the database.

Coming back to domains it is appropriate mention here that the language standard SQL 92 does not put in concept of the domain the sense of the "comparability". That is realized by the standard, no more than an opportunity once writing down the restrictions, and then using them at definition of columns specifications, i.e. the opportunity of avoiding the code duplication.

In a "Theory - Standard - Realization" chain the severity of the relational theory is consistently lost. Therefore we cannot quite transparent to cooperate with relational DBMS of different producers.

Here I want to show a small example of appropriately using the SQL Server data types.

So, materially we have the fact values of one data type can be compared. For type conversion the standard offers the CAST function. I.e. generally we should convert compared values to one type, and then we can carry out operation of comparison (or assignments). What will be, if we simply assign a variable (or column) by another data type? Let's consider a simple example of a code on T-SQL:

DECLARE @vc VARCHAR(10), @mn MONEY, @ft FLOAT

SELECT @vc = '499.99'
PRINT @vc
SELECT @ft = @vc
PRINT @ft

Here we describe the three variables in accordance of string type (VARCHAR), money type (MONEY) and numbers with a floating point (FLOAT). Further we assign a string variable by the constant of corresponding type. Then we assign a FLOAT variable by a string variable. As a result we receive two identical results - 499.99 (operator PRINT carries out a conclusion to the console). This reorganization is called by implicit conversion of data types, i.e. string value - '499.99' has been automatically converted to the FLOAT data type and assigned by a variable @ft.

Let's add the couple in of strings to the end of a code:

SELECT @mn = @vc
PRINT @mn

As a result we'll receive two similar messages about error:

Implicit conversion from data type varchar to money is not allowed. Use the CONVERT function to run this query.

and

Implicit conversion from data type money to nvarchar is not allowed. Use the CONVERT function to run this query.

One tell us that implicit conversion to money type is not allowed, and another about inadmissibility of the opposite (to varchar) conversions. As one would expect, we are offered to use evident conversion by the COVERT function. However to be closer to the standard, let's use the CAST function:

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

We got rid from the first error message. The conclusion arises that the second message is given by operator PRINT. Instincts prompt to glance in the help. It is said in the BOL about operator PRINT, that the variable which can be used in this operator can be any allowable string type (char or varchar) or must be resulted in this type implicitly.

Let's copy the last line as

PRINT CAST(@mn AS VARCHAR)

Everything works. The main conclusion which we take from here is that not all the types (even if we don't see the special reasons for that) allow implicit conversion.

In particular, implicit conversion with MONEY is not carried to types CHAR VARCHAR, NCHAR, NVARCHAR and vice versa.

And now let's talk about the reason, which made me to write so many words. It had turned out on my untidiness that in the basic and verifying base some equivalent columns had different data type. For example, the field price in table РС had the FLOAT type in one base and type MONEY - in another. During the very long time it made no influence for working of the site, but suddenly for the last some days our two participants have decided to use implicit conversions of types to the inquiries with known result.:-)

I have decided to not be limited to apologies, so I wrote an opus about the features of realization, hoping, that it will bring more advantages, than my apologies.

Concerning the types, the noticed divergence is already harmonized. Also the scripts are updated for uploading.

» Unfortunately, the examples placed here cannot be executed directly on a site. It is caused by the fact we did not realize an opportunity of performance for sets of operators (packages).  Therefore who will want to check up the validity of aforesaid should to use Query Analyzer.

Main page


Usage of any materials of this site is possible
only under condition of mandatory allocation of the direct link to a site
http://www.sqlbooks.ru
on each page where used materials are placed.

 Main   Articles    Books 
Рейтинг@Mail.ru Rambler's Top100 Alt Упражнения по SQL: обучение, тестирование, сертификация по языку SQL Copyright c 2002-2006. All rights reserved.