CODEWITHSUNDEEP
sqlsql-server
HansHarhoff
HansHarhoff

Reputation: 1997

When is the type of a column in a SQL query result determined?

When performing a select query from a data base the returned result will have columns of a certain type.

If you perform a simple query like

select name as FirstName
from database

then the type of the resulting FirstName column will be that of database.name.

If you perform a query like

select age*income
from database

then the resulting data type will be that of the return value from the age*income expression.

What happens you use something like

select try_convert(float, mycolumn)
from database

where database.mycolumn has type of nvarchar. I assume that the resulting column has type of float which is decided by the return type of the first call to try_convert.

But consider this example

select coalesce(try_convert(float, mycolumn), mycolumn)
from database

which should give a column with the values of mycolumn unchanged if try_convert fails, but mycolumn as a float when/if that is possible.

Is this determination made as the first row is handled? Or will the type always be determined by the function called independently of the data in the rows?

Is it possible to conditionally perform a conversion? I would like to convert to float in the case where this is possible for all rows and leave unchanged in case it fails for any row.

Update 1

It seems that the answer to the first part of the question is that the column type is determined by the expression at compile time which means that you cannot have a dynamic type of your column depending on the data.

I see two workaround for this

Option 1

For each column count the number of not null rows of try_convert(float, mycolumn) and if this number is 0 then do not perform conversion. This will of course read the rows many times and might be inefficient.

Option 2

Simple repeat all columns; once without conversion and once with conversion and then simply use the interesting one.

One could also perform another select statement where only columns with non-null values are included.

Background

I have a dynamically generated pivot table with many (~200 columns) of which some have string values and others have numbers.

I would like to cast all columns as float where this is possible and leave the other columns unchanged (or cast as nvarchar).

The data

The data is mostly NULL values with some columns having text string and other columns having numbers. There are no columns with "mixed" content.

Upvotes: 0

Views: 594

Answers (1)

Remus Rusanu
Remus Rusanu

Reputation: 294287

The types are determined at compile time, not at execution. try_convert(float, ...) knows exactly the type at parse/compile time, because float here is a keyword, not a value. As for expressions like COALESCE(foo, bar) the type similarly determined at compile time, following the rules of data type precedence lad already linked.

When you build your dynamic pivot you'll have to know the result type, using the same inference rules the SQL parser/compiler uses. I understand some rules are counter intuitive, when in doubt, test it out.

For the detail oriented: some expressions types can be determined at parse time, eg. N'foo'. But most have to be resolved at compile time, when the names of tables and columns are bind to actual object in the database, because only then the type is discovered.

Upvotes: 1

Related Questions