A SQL Server view is a type of table expression. A View is stored in the database (with a specific unique name), not as a table containing data, but as a “formula” for creating an in-memory table. A View contains rows and columns, just like a real table. A View can be created using tables of the same database or different databases. A View can be created using other Views.

As SQL Server Books Online says for SQL Server 2000: “A view can be thought of as either a virtual table or a stored query.” This virtual table’s contents are defined by a query. Like a table, a view consists of a set of named columns and rows of data. The data accessed in a view is not stored in the database as a distinct object. What is stored is the SELECT query. When you execute a view, the query at this time begins execution against the tables in the SELECT query.

A database view allows you to simplify complex queries, helps limit data access to specific users, provides extra security layer, enables computed columns and enables backward comparability.

What are Views used for?

• Simplify – Join columns from multiple tables so they look like a single table
• Restrict a user to certain columns of certain tables
• Restrict a user to certain rows – filter
• Computed Columns and Aggregate information (sum, min, max and so on)

However, views can be slow if they are complex and/or working with large volumes of data. Also, Views require extra effort if the tables or Views underneath have been modified. The View will need to be modified for it to function properly.

Microsoft also says “The query that defines the view can be from one or more tables or from other views in the current or other databases. Distributed queries can also be used to define views that use data from multiple heterogeneous sources. This is useful, for example, if you want to combine similarly structured data from different servers, each of which stores data for a different region of your organization.”

Creating a View

Creating a view is easy in code (syntax) but there are some errors you may get.

create view ViewByDepartment as 
select Name, Id
from Department
where Id >= 234

You might get the following error message if you specify an order by clause in your select statement. Generally you do not include order by but adding a TOP is a way around it.

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.

Also you may need to surround your create view statement block with GO to separate the batch from other batches.

We can check to see if a view exists with the following code, or if we are looking for a specific view we can ask for it by name as shown in the next query.

select * from sys.views

select * from sys.views where name = 'ViewByDepartment'

We can delete a view, if it exists, by dropping it as follows.

drop view dbo.ViewByDepartment

We can alter or change the view by right clicking the view and running the alter script. Here is another script that checks if a view exists and drops it if it does and then creates the view.

if exists(select * from sys.views where name = 'ViewByDepartment')
    drop view 'ViewByDepartment'
create view dbo.ViewByDepartment as
select -- and so on
go

If you want to make sure dbo.ViewByDepartment exists as opposed to any ViewByDepartment then you can use the following.

select * from INFORMATION_SCHEMA.VIEWS
where [TABLE_NAME] = 'ViewByDepartment' and [TABLE_SCHEMA] = 'dbo'

Securing Views

SSMS users can see the code behind the views by scripting the view itself. They can also see the code by running the following script. They can see all of the procedures, functions and views code under the text column of the following query.

select * from sys.syscomments

If all we are interested in is the views, we can write a query like this one.

select V.name, S.text from sys.syscomments as S
inner join sys.views as V
on S.id = V.object_id

Here is another way to get the code of a view if you know the name of the view. Here we can use the object_id function if we don’t know the value of the object id. Below that is another way which gives you the code of your procedures, functions and views.

select object_definition(object_id(''dbo.ViewByDepartment))

select * from sys.sql_modules

Searching Your T-SQL Code

You could modify the above query to search for some text in all of your stored procedures, functions or views. Suppose you don’t recall where the string “June 22” is in your code. You want to know which code was created or modified on this date. You have put the date into the comments. Try the script below.

select object_id, definition 
from sys.sql_modules
where definition like '%June 22%'

Another possibility is that you want to change some code in your scripts. You have been using @@ROWCOUNT to see if any rows are returned and then take some action depending on the number. Instead you want to use IF EXISTS (SELECT…). In other words all you need to know is if any rows were returned. You could search for all instances of the string “@@ROWCOUNT” in all of your procedures, functions and views. You could modify the above code to do this.

Encryption

We can secure the view with encryption when we create it very easily by adding the two words WITH ENCRYPTION between the view name and the as keyword. The only problem is that you won’t be able to see the code either! You would have to hack the database to retrieve the information!