SQL Server Views - Create, Alter, Rename, Refresh and Drop
#DecodeITeS
SQL Server View
Views are virtual tables used to create a layer over the actual table.
Views can be dependent on single or multiple columns with filtered data.
Views can also be created over other views.
Views rows & columns and their data depended on underlying tables
Views increase security in multiple ways.
One is hiding actual object name from the user,
Second is providing filtered data will safeguard unnecessary access on data to users.
The third is you can grant permissions only on views to users to access data without granting permissions on dependent tables.
You can write complex business logic for business needs and can club in view. This helps secure business logic used to achieve the results. This reduces user complexity and adds security to complex business logic as well.
Views are a logical entity and do not contains data until indexed.
The view can have a maximum of 1,024 columns.
Views support cross-database and distributed queries. This means you can club tables available in cross databases or different servers to provide single point output. E.g.: You can club your all geographical stores server tables in a single view to have consolidated data.
In case tables or view dropped or undergone with schema changes like change column name or delete a column, referenced in subjected VIEW, DB engine will through the error.
Types of Views
SQL Server Provides 2 types of views.
System-defined Views: SQL Server comes up with a list of system views. System Views connect with catalog metadata. System views used to fetch SQL server internal and instance level information. You can use sys.system_views to list all system views shipped with the SQL Server setup.
User-Defined Views: SQL Server gives you the option to create a user-specific view. These views can be used to fetch filtered data or data from a single table / multiple tables / distributed tables. User-defined views can be of 4 types:-
Standard Views: Standard views are normal user views with no special functions or customizations.
Indexed Views: Views are a logical entity and do not save data until indexed. That means, Once you had created an index on your views, views can have data that can be accessed without accessing the table each time. These are also called materialized views. Indexed views may provide additional performance benefits to view performance and put extra overhead on table data.
Updatable Views: You can Insert, Update & Delete data in the underlying table using updatable views.
Partitioned Views: Partitioned views club the similar structure data from multiple tables on a single instance or distributed among multiple SQL instances. Partitioned views use UNION ALL to club all data from multiple tables.
Views Commands
Actions Syntax
Create CREATE VIEW [schema_name.]view_name AS [ WITH { ENCRYPTION | SCHEMABINDING | VIEW_METADATA } SELECT expressions FROM tables WHERE conditions;
Alter ALTER VIEW [schema_name.]view_name AS [ WITH { ENCRYPTION | SCHEMABINDING | VIEW_METADATA } SELECT expressions FROM tables WHERE conditions;
Select SELECT * FROM schema_name.]view_name WHERE conditions;
Rename EXEC SP_RENAME old_view_name, new_view_name
View Definition EXEC SP_HELPTEXT view_name
OR
SELECT definition, uses_ansi_nulls, uses_quoted_identifier, is_schema_bound FROM sys.sql_modules WHERE object_id = object_id(‘view_name' );
Refresh View Metadata Exec SP_REFRESHVIEW view_name
When you change the schema of underlying table of view, view will not get updated until you refresh the view metadata.
Drop DROP VIEW view_name;
List of Views in Databases SELECT OBJECT_SCHEMA_NAME(o.object_id) schema_name, o.name FROM sys.objects as o WHERE o.type = 'V';
Create View with Options
SQL Server Create View has 3 options. This provides extra features & functionality to views.
ENCRYPTION – You can fetch view text using sys.syscomments however view created with ENCRYPTION option encrypts the entry. Also, views created with these options are preventing from published as part of SQL Server replication.
SCHEMABINDING – This is a very useful option to bind the schema of view & tables. You need to alter the view to remove the dependency on the table or remove views before making schema level changes at the table.
VIEW_METADATA – Causes SQL Server to return to the DB-Library, ODBC, and OLE DB APIs the metadata information about the view. View created with this option hinds baseline tables details when requested by the application.
Watch video SQL Server Views - Create, Alter, Rename, Refresh and Drop online without registration, duration hours minute second in high quality. This video was added by user Decode ITES 05 October 2020, don't forget to share it with your friends and acquaintances, it has been viewed on our site 2,471 once and liked it 17 people.