Script to create a view in SQL Server

To add a new view in a database, use this script written in T-SQL to create a view in SQL Server. A SQL view allows you to display specific columns of an existing table. Think of a view as a virtual table defined by a SQL query selection. It is a set of columns and rows in a table. SQL Server developers create and manage user-defined objects and therefore views.

Here is an example of a script to create a view in a SQL Server database. Use the SQL Create View statement followed by a Select statement to add a view to a database.

In addition, there are three different types of SQL Server views:

A partitioned view joins data with a partition from multiple tables on one or more SQL Servers. Classic views are partitioned views because it is the underlying query that joins the data.
The indexed view is a materialized view that must be refreshed like a table.
A system view allows SQL users to check the metadata of the MS SQL database catalogue.

On another topic, it can be useful to list all the indexes of a SQL Server database for verification and optimization purposes.

It can be useful to list all the indexes of a SQL Server database for double checking and optimisation purposes.

Create a SQL Server view with a T-SQL script

Firstly, let’s consider the Sales table created in a previous article to show different data types available and a simple sales structure to be used in T-SQL examples.

CREATE TABLE [dbo].[SALES] 
(
	[Year] INT, 
	[MonthName] NVARCHAR(50), -- alphanumeric
	[MonthCurrent] BIT, -- Boolean, 0 or 1 , false / true
	[NumberMonth] TINYINT, -- very small integer, from 0 to 255
	[EmployeeNumber] SMALLINT, -- small integer, minimum -2^15, maximum 2^15
	[NumberOfClients] INTEGER, -- integer, minimum -2^31, maximum 2^31
	[NumberOfSales] BIGINT, -- big integer, minimum: -2^63, maximum 2^63
	[Amount_ET] NUMERIC(15,5), -- numeric, 15 digits, with 5 after the comma 
	[Amount_IT] DECIMAL(15,5) -- decimal, 15 digits, with 5 after the comma
);

Secondly, with this code, create a view to display part of the table contents. Use the script below and adjust it to the needs of the project.

CREATE VIEW [dbo].[SalesByMonth] 
AS 
SELECT 
   [MonthName], 
   [NumberMonth], 
   SUM([NumberOfClients])    AS [NumberOfClients], 
   SUM([Amount_ET])          AS [Amount_ET], 
   SUM([Amount_IT])          AS [Amount_IT] 
FROM    [dbo].[Sales] 
GROUP BY 
   [MonthName], 
   [NumberMonth] 
ORDER BY   [NumberMonth] ASC;
GO

Script to call a T-SQL view from SSMS

Then, you can use a Select command like this one, for example to select the second part of the year. That is, the months with a number greater than 6, i.e. from July to December.

SELECT 	*
FROM 	[dbo].[SalesByMonth]
WHERE 	[NumberMonth] > 6 ;

Indeed, this short tutorial on SQL Server views explains how to create a view with a script.

To conclude and learn more about MS SQL views, see the definition of SQL views on the dedicated Wikipedia page. This time it is the Standard SQL view, applicable to any ISO compliant provider.

Finally, below is an article with an example of a T-SQL script to create a function.