How to Create Views in SQL Server Management Studio?

If you’re working with Microsoft SQL Server, chances are you’ve come across the concept of views. A view is essentially a saved query that you can treat like a virtual table. In this post, I’ll show you step by step how to create views in SQL Server Management Studio, explain the benefits of using them, and share some best practices from my own experience.

I’ll also explain how to alter a view in SQL Server Management Studio, show you how to use ORDER BY in view in SQL Server, and clear up a common misunderstanding about SQL Server views with parameters. For examples, we’ll stick with the good old Northwind database that we created earlier.

Benefits of Views in SQL

Views can be incredibly useful. Here are a few reasons why:

• They make your life easier: Instead of constantly rewriting long joins, you can save them as a view.
• They are secure: You can give users access to the view instead of the base tables, so they see only the data you want them to.
• They are consistent: Everyone in your organization can use the same view, making sure that the queries are standardized.

How to Create Views in SQL Server Management Studio?

It’s actually very simple to create a view in SQL Server Management Studio (SSMS). Let’s say you want a view that shows customer orders from the Northwind database. This is the query that creates the view:

CREATE VIEW vw_CustomerOrders AS
SELECT 
    c.CustomerID,
    c.CompanyName,
    o.OrderID,
    o.OrderDate,
    o.ShipCountry
FROM Customers c
JOIN Orders o ON c.CustomerID = o.CustomerID

When you run the query, you will see the following message, and the view will appear in Views list:

After you created the view, you can query it like a table:

SELECT * FROM vw_CustomerOrders

Here is what it looks like in SSMS:

How to Alter View in SQL Server Management Studio

If you need to change a view, you can use the ALTER VIEW command instead of deleting (dropping) and creating it again. For example, let’s say we want to add the ShipPostalCode column to our view:

ALTER VIEW vw_CustomerOrders AS
SELECT 
    c.CustomerID,
    c.CompanyName,
    o.OrderID,
    o.OrderDate,
    o.ShipCountry,
    o.ShipPostalCode
FROM Customers c
JOIN Orders o ON c.CustomerID = o.CustomerID

Here is a screenshot from SSMS:

Now your view includes the ship postal code for each order. Try running it again:

SELECT * FROM vw_CustomerOrders

How to Use ORDER BY in View in SQL Server

One limitation of views is that you cannot simply add ORDER BY to the end of a view definition unless you also use the TOP clause.

For example, this will not work:

CREATE VIEW vw_OrdersSorted AS
SELECT * 
FROM Orders
ORDER BY OrderDate

You will probably get the following error: The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.

But this should work:

CREATE VIEW vw_OrdersSorted AS
SELECT TOP 100 PERCENT *
FROM Orders
ORDER BY OrderDate

Here is how it looks in SSMS:

If you try to query the view you just created, you will get the following results:

Even though it looks like a trick, keep in mind that when you query a view, SQL Server does not guarantee the order of rows unless you apply ORDER BY in your final SELECT. The safest practice is to always use ORDER BY in the query, not in the view definition. Something like:

SELECT * FROM vw_CustomerOrders
ORDER BY OrderDate

SQL Server Views with Parameters

A common question is whether you can pass parameters to a view, like you can with stored procedures (we will cover them in my next post). The answer is NO – SQL Server views cannot accept parameters.

If you want parameter-like behavior, you have to use a where clause when querying the view:

SELECT * FROM vw_CustomerOrders
WHERE ShipCountry = 'USA'

Here is what it looks like in SSMS:

SQL Server Views Best Practices

Here are a few tips I’ve learned from working with views:

• Keep your views simple and focused on a single task.
• Always add clear, descriptive names to your views (e.g., vw_CustomerOrders instead of just OrdersView).
• Avoid using SELECT * in view definitions. Always specify the columns you need for clarity and performance.

Now you know how to create views in SQL Server Management Studio and why they can make your life easier. We walked through a SQL Server views tutorial with the Northwind database, discussed the benefits of views in SQL, learned how to alter view in SQL Server Management Studio, looked at the quirks of how to use ORDER BY in view in SQL Server, clarified that SQL Server views with parameters are not possible, and finished with some best practices.

Views are a great way to simplify your queries and keep your database clean and secure. In the next tutorial, I’ll show you how to work with stored procedures, which add even more flexibility.

If want to learn more about SQL, make sure you check my previous blog post on What Is Index in SQL Server?

If you would like to learn more about programming SQL queries, make sure you buy this book: T-SQL Fundamentals (Developer Reference)