How to create a view with SQL Server to display specific columns from an existing table? Consider a view as a virtual table defined by a SQL query. It’s a set of columns and rows from a table. SQL Server developers manages the user defined view objects. Use the MS SQL create view statement and a select statement to add a view to a database.
To clarify, let talk about the three different types of views exist:
- Indexed view is a materialized view that needs to be refreshed like a table.
- A partitioned view allows to join data with partition from multiple tables on one or multiple SQL servers.
- A system view allows SQL users to check metadata from the MS SQL database catalog.
It can be useful to list all the indexes of a SQL Server database for double checking and optimisation purposes.
How to 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 );
To create a view displaying a part of the table content, use the script below and adjust it.
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
How to call a MS SQL view from SSMS ?
Secondly, simply use a select command like this one, for example to select the second part of the year:
SELECT * FROM [dbo].[SalesByMonth] WHERE [NumberMonth] > 6;
To conclude and learn more about MS SQL views, check the SQL view definition in Wikipedia dedicated page. This time about the SQL Standard view, applicable to any vendor respecting the ISO standard.
Below an article with a T-SQL create function example.