VALUES Type Inference: Cast Before Combining Rows

VALUES type inference combines each column under a common type rather than preserving a separate type for every row. I cast before combining when text identity matters. A numeric-looking string can lose its formatting during an implicit conversion.

Combine a text value with an integer deliberately

The first query supplies text 007 in one row and integer nine in another. The source text has an explicit varchar(8) type. The integer has an explicit int type.

The constructor must choose a common type for CombinedValue. Integer has higher precedence than varchar for this combination. The expected output column is int.

The first expected result value is seven rather than the original three-character text. The second is nine. The leading zeros disappeared when the text became a numeric value.

I retain a unique CaseId beside the combined column. That makes the two expected rows easy to compare with the original expressions. The constructor’s type belongs to the whole column rather than its first listed row.

SELECT v.CaseId,v.CombinedValue
FROM (VALUES (1,CAST('007' AS varchar(8))),(2,CAST(9 AS int))) AS v(CaseId,CombinedValue)
ORDER BY v.CaseId;

Convert the integer to text before the constructor

The second query casts both supplied values to varchar(8) before VALUES combines them. Its expected output column is varchar. The first result preserves 007 as text.

The second result is the text 9. That conversion is deliberate. Both inputs now participate in a shared text contract instead of asking type precedence to choose between unrelated families.

Casting the already combined integer output to varchar afterward would produce 7, not 007. The earlier implicit numeric conversion has already discarded the original formatting. A later cast cannot reconstruct it.

I place the two complete queries beside each other because the location of the cast matters. The intended source representation must survive the combination step. Merely choosing a final display type is insufficient.

SELECT v.CaseId,v.CombinedValue
FROM (VALUES (1,CAST('007' AS varchar(8))),(2,CAST(9 AS varchar(8)))) AS v(CaseId,CombinedValue)
ORDER BY v.CaseId;

Compatible numeric text can conceal the inference rule

The text 007 happens to convert successfully to int. That makes the first query executable while still exposing a representation change. Successful conversion does not prove that text identity was preserved.

A nonnumeric text value in the same mixed column would fail conversion to int. The type conversion is supported, but that value cannot represent an integer. The numeric-looking input shows inference without introducing this value error.

If the source represents an identifier, leading zeros may belong to its identity. If it represents a quantity, an integer may be the intended contract. Choose that meaning before building the sample rowset.

I would not resolve every mixed constructor by converting everything to text. Calculations may require a numeric type. The correct common type comes from the column’s intended use.

Treat sample rows as a typed table source

The derived table has explicit column aliases and equal column counts in every row. Those names define the review projection. They do not declare types independently of the supplied expressions.

The constructor combines types the way UNION ALL does. That is about type combination, not duplicate removal, so this article does not teach set-operation multiplicity.

A typed NULL should also follow the intended column contract when added to the sample rows. Leaving a type decision implicit can create another inference question. Make source casts part of the saved example.

Inspect the combined column’s type as well as its displayed values. Leading zeros belong to the original text representation. Preserve them before the constructor when they are meaningful to the identifier.

When adapting this example, inspect each column across every row expression. Keep formatting-sensitive input before the combination step. Then compare the expected common type with the column’s intended meaning.

Cast first, and the formatting survives.

A successful cast is not preserved identity, it is a conversion that can drop formatting.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.