If you’re getting an error that reads “Create View or Function failed because no column name was specified…” in SQL Server, perhaps you’re using a function or an arithmetic expression in the SELECT list, but you haven’t provided a name for the column.

As the error message alludes to, you need to provide a column name when doing stuff like that.

By “stuff like that”, there are a few other cases where we need to explicitly provide column names. Here’s what Microsoft says about it:

A column name is required only when a column is derived from an arithmetic expression, a function, or a constant; when two or more columns may otherwise have the same name, typically because of a join; or when a column in a view is specified a name different from that of the column from which it is derived. Column names can also be assigned in the SELECT statement.

So to fix this issue, provide a column name for all columns that fall under those use cases.

Example of Error

Suppose we try to create a simple view like this:

CREATE VIEW MyView AS
    SELECT 
        SUM(Price)
    FROM dbo.Sales;

Output:

Msg 4511, Level 16, State 1, Procedure MyView, Line 2
Create View or Function failed because no column name was specified for column 1.

We get the error.

We’ll get the same error when doing any of the following:

-- Other function
CREATE VIEW MyView AS
    SELECT 
        SYSDATETIME();

-- Arithmetic expression
CREATE VIEW MyView AS
    SELECT 
        Quantity * Price
    FROM dbo.Sales;

-- Constant
CREATE VIEW MyView AS
    SELECT 
        'Test';

In all cases, the problem is caused by not naming the columns.

Solution

To fix this issue, simply provide column aliases for any columns that fall under the categories outlined by Microsoft in the aforementioned quote.

Example:

CREATE VIEW MyView AS
    SELECT 
        SUM(Price) AS TotalPrice
    FROM dbo.Sales;

Output:

Commands completed successfully.

This time it worked.

This problem can easily manifest itself when we get another error that results in us having to add COUNT_BIG(*) to the SELECT list (SQL Server requires us to do this in some cases, such as when using the GROUP BY clause in an indexed view). In such cases, we might add COUNT_BIG(*) as suggested by the relevant error message, but forget (or not know) to provide a column alias.

In any case, it’s easily fixed, as outlined above.