Create a View in SQL Server: Column Rules and Encryption

To create a view in SQL Server, you save a SELECT statement under a name. You then query it like a table. The view stores no rows, only the query, so it shows current data every time. Not every query can become a view, and three rules cause most of the errors.

The Sample Table

Donations holds eight gifts from four donors. Run this in any test database. Every result below comes from SQL Server 2025.

CREATE TABLE Donations (DonationID INT IDENTITY(1,1) PRIMARY KEY, DonorID INT NOT NULL, Amount DECIMAL(10,2) NOT NULL, DonatedOn DATE NOT NULL);
GO

INSERT INTO Donations (DonorID, Amount, DonatedOn) VALUES
  (1, 50.00, '2026-01-05'), (1, 75.00, '2026-02-10'), (2, 20.00, '2026-01-20'), (2, 20.00, '2026-03-02'),
  (3, 500.00, '2026-02-14'), (3, 250.00, '2026-03-15'), (4, 10.00, '2026-03-30'), (1, 25.00, '2026-04-04');
GO

Rule One: Name Every Column

This query totals the gifts for each donor. It’s a useful report, but it can’t become a view yet. The expression SUM(Amount) has no name. SQL Server stops with Msg 4511: Create View or Function failed because no column name was specified for column 2.

CREATE VIEW dbo.vDonorTotals
AS
SELECT DonorID, SUM(Amount)
FROM Donations
GROUP BY DonorID;

Give the expression an alias and create the view again. Then query it like a table.

CREATE VIEW dbo.vDonorTotals
AS
SELECT DonorID, SUM(Amount) AS TotalAmount
FROM Donations
GROUP BY DonorID;

SELECT DonorID, TotalAmount FROM dbo.vDonorTotals ORDER BY DonorID;

Rule Two: Names Must Be Unique

A join can return two columns with the same name. A view can’t, because the view needs one name per column. This view lists pairs of gifts from one donor, and both columns are called Amount. SQL Server answers with Msg 4506, which says the name Amount is specified more than once. Alias one of them to fix it.

CREATE VIEW dbo.vDonationPairs
AS
SELECT a.DonorID, a.Amount, b.Amount
FROM Donations AS a
JOIN Donations AS b ON b.DonorID = a.DonorID AND b.DonationID > a.DonationID;

Rule Three: No ORDER BY

A view is a table-like result, and a table has no order. An ORDER BY inside the view fails with Msg 1033 unless TOP, OFFSET or FOR XML is also used. Sort when you read the view instead, as the earlier query did.

CREATE VIEW dbo.vDonationsSorted
AS
SELECT DonorID, Amount
FROM Donations
ORDER BY Amount;

A View Stores a Query, Not Rows

Add a donation and query the view again. Donor 4 moves from 10.00 to 100.00, because the view runs its query each time. That makes a view a good place to keep a join or a calculation that many reports need. The report writers use the name and don’t repeat the logic.

INSERT INTO Donations (DonorID, Amount, DonatedOn) VALUES (4, 90.00, '2026-05-01');

Reading the Code of a View

Four tools show the code that created a view. The first two are sp_helptext and OBJECT_DEFINITION. The catalog view sys.sql_modules returns the same text, and the example below reports its length. The view sys.syscomments is a compatibility view for old code, so prefer the others.

EXEC sp_helptext 'dbo.vDonorTotals';

SELECT OBJECT_DEFINITION(OBJECT_ID('dbo.vDonorTotals')) AS ViewCode;

SELECT OBJECT_NAME(object_id) AS ViewName, LEN(definition) AS CodeLength FROM sys.sql_modules WHERE object_id = OBJECT_ID('dbo.vDonorTotals');

SELECT OBJECT_NAME(id) AS ViewName, encrypted AS Encrypted, LEN(text) AS CodeLength FROM sys.syscomments WHERE id = OBJECT_ID('dbo.vDonorTotals');

The two functions return the CREATE VIEW text. The catalog views report a code length of 108 characters.

Hide the Code With ENCRYPTION

To keep casual readers from seeing the code, alter the view and add WITH ENCRYPTION before the AS keyword. Run the four read queries again. sp_helptext prints a message that the text is encrypted. OBJECT_DEFINITION returns NULL, and so do the length columns. sys.syscomments shows Encrypted as 1. The view still returns the same rows.

ALTER VIEW dbo.vDonorTotals
WITH ENCRYPTION
AS
SELECT DonorID, SUM(Amount) AS TotalAmount
FROM Donations
GROUP BY DonorID;

Treat encryption as a way to keep casual readers out, not as security. You also can’t read the code back, so keep the original script in a file. To change an encrypted view, run ALTER VIEW with the full definition.

Leave out WITH ENCRYPTION in that ALTER VIEW and the encryption is gone. sp_helptext shows the code again.

ALTER VIEW dbo.vDonorTotals
AS
SELECT DonorID, SUM(Amount) AS TotalAmount
FROM Donations
GROUP BY DonorID;

Clean Up

Remove the view and the table when you finish.

DROP VIEW dbo.vDonorTotals;
DROP TABLE Donations;

What to Remember

A view needs a name for every column, unique names, and no bare ORDER BY. It stores a query, so it always shows current rows. WITH ENCRYPTION hides the text from the standard tools, and you need your own copy of the script.

To go further, read What Is a View in SQL Server?, Updatable Views: When INSERT and UPDATE Work Through a View and Schema-Bound Views: Protect Referenced Columns.

A view is not a copy of your data, it is a saved question.

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