100 Things I Hate About Views: Undeclared Data Types in Columns

Views let you do dumb things by accident in SQL Server. Then they make you have to think way too hard to fix them.

Most of the time when people create views, they start by refining a SELECT query, then turn it into a view. People also often create multiple views that pull different slices of data and UNION the results together.

Combined, these two things easily lead to undeclared datatypes in views with problematic implicit conversions.

SQL Server lets you define a view with columns with undeclared data types

You can even have it return a null if you’re going to UNION that view with other things.

It doesn’t look very weird at all, it just looks like this…

CREATE OR ALTER VIEW dbo.i_am_view
AS
	SELECT 
		null AS some_column
        /* column2,
        column3
    FROM dbo.Sometable, etc
    */
    
GO

Let’s say we have that view, plus a related table

Here’s the table and its rows:

CREATE TABLE dbo.i_am_table (
	some_column VARCHAR(100),
	last_modified_date DATETIME2(0)	
);
GO

INSERT dbo.i_am_table (some_column, last_modified_date)
VALUES ('1', '2023-01-01'),
	 ('ONE', null),
	 (NULL, '2024-01-01'),
	 ('', '2025-01-01')
GO

SELECT from the view and UNION with the table

Here’s the query

SELECT some_column, '' AS last_modified_date
FROM dbo.i_am_view
UNION
SELECT some_column, last_modified_date
FROM dbo.i_am_table
WHERE last_modified_date IS NOT NULL
ORDER BY last_modified_date;
GO

The results are odd

Notable bits:

• Row 1 came from the view. The last_modified_date column, which was also not given a data type in our query, returned January 1, 1900– it got a magic default value.
• Row 4 has 0 as the value for some_column. This was an empty string in the table, but it’s been converted to a numeric 0.

The query execution plan explains more

Let’s look at that warning on the SELECT operator, plus that compute scalar operator after we read the table.

We’re warned about an implicit conversion

I find these messages really hard to read with the text all crammed together. Here it is with a lot of spacing added:

 Type conversion in expression (
 CONVERT_IMPLICIT 
    (int,
    [views].[dbo].[i_am_table].[some_column]
    ,0)
 ) may affect "CardinalityEstimate" in query plan choice

SQL Server decided that it needed to convert the some_column values in dbo.i_am_table from the datatype they are stored as– VARCHAR(100) – into the INT datatype.

Looking at the compute_scalar operator confirms this.

Expr1005 is defined as the result of that CONVERT_IMPLICIT function, which is converting values from some_column to integers. Expr1005 is returned from this operator along with the last_modified_date column.

If we remove the WHERE clause, the query fails

If we let that row where some_column has the value “one” through, the query fails with the error:

Msg 245, Level 16, State 1, Line 52 Conversion failed when converting the varchar value ‘ONE’ to data type int.

I guess we can’t implicitly convert words to integers.

Why an integer?

When we created the view, we defined it with:

	SELECT 
		null AS some_column

We didn’t give SQL Server anything to go on, so it guessed int.

The same thing happens if we use this syntax to create a temp table.

We shouldn’t leave this to chance

I wish SQL Server wouldn’t let you create a view this way, but it does. However, we’re much better off specifying a data type, which we can do with the syntax:

CREATE OR ALTER VIEW dbo.i_am_view
AS
   SELECT 
       cast(null as varchar(100)) AS some_column;
GO

This avoids errors with data type conversions, not to mention unexpected results and performance issues with type conversions.