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