{"id":41,"date":"2011-08-20T02:18:49","date_gmt":"2011-08-19T20:48:49","guid":{"rendered":"http:\/\/beginsql.wordpress.com\/?p=41"},"modified":"2015-07-11T08:48:14","modified_gmt":"2015-07-11T03:18:14","slug":"views-in-sql-server","status":"publish","type":"post","link":"https:\/\/sqlhints.com\/2011\/08\/20\/views-in-sql-server\/","title":{"rendered":"Views in Sql Server"},"content":{"rendered":"<p style=\"text-align: justify;\"><strong>Views<\/strong> are nothing but saved SQL statements, and are sometimes referred as Virtual Tables. Keep in mind that Views cannot store data rather they only refer to data present in tables.<\/p>\n<h3>Benefits of Views:<\/h3>\n<p style=\"text-align: justify;\">A view can be useful when there are multiple users with different levels of access, who all need to see portions of the data in the database (but not necessarily all the data). Views can do the following:<\/p>\n<ul>\n<li>Restrict access to specific rows in a table<\/li>\n<li>Restrict access to specific columns in a table<\/li>\n<li>Join columns from multiple tables and present them as though they are part of a single table<\/li>\n<li>Present aggregate information (such as the results of the COUNT function)<\/li>\n<\/ul>\n<h3>Syntax: Creating a view<\/h3>\n<pre class=\"brush: sql; gutter: false\">CREATE VIEW &lt;View_Name&gt;\r\nAS\r\n&lt;SELECT Statement&gt;<\/pre>\n<h3>Let us create a Sample DataBase\u00a0with Tables to understand\u00a0Views Concepts:<\/h3>\n<p style=\"text-align: justify;\">By using the below script we are creating a Sample DataBase Named:ViewDemo. Then in this database we are creating two tables Customers and Orders\u00a0 and in these tables populating the sample data.<\/p>\n<pre class=\"brush: sql; gutter: false\">Create DataBase SqlHintsViewDemo\r\nGO\r\nUSE SqlHintsViewDemo\r\nGO\r\nCreate Table dbo.Customers\r\n( CustomerID int Identity(1,1),\r\n  FirstName Varchar(50), LastName VarChar(50),\r\n Phone varchar(50), City Varchar(50) )\r\nGO\r\nCreate Table Orders\r\n(\r\nOrderId int Identity(1,1),\r\nCustomerId int\r\n)\r\nGO\r\nInsert INTO dbo.Customers\r\nValues (&#039;Kalpana&#039;, &#039;Biradar&#039;, &#039;2727272727&#039;, &#039;Bangalore&#039;),\r\n\t(&#039;Basavaraj&#039;,&#039;Biradar&#039;,&#039;1616161616&#039;,&#039;Mysore&#039;)\r\nGO\r\nINSERT INTO Orders\r\nValues(1), (2), (2), (2), (2)<\/pre>\n<h3>Create Simple View:<\/h3>\n<p style=\"text-align: justify;\">The below script creates a view named vwSample:<\/p>\n<pre class=\"brush: sql; gutter: false\">\r\nCREATE VIEW dbo.vwSample\r\nAs\r\n SELECT CustomerID, FirstName, LastName\r\n FROM dbo.Customers\r\nGO<\/pre>\n<p style=\"text-align: justify;\">We can use a statement like below to return all the customer records with three columns: CustomerID, FirstName, LastName.<\/p>\n<pre class=\"brush: sql; gutter: false\">SELECT * from dbo.vwSample<\/pre>\n<h3>We can use the statement like below to see the content of view:<\/h3>\n<pre class=\"brush: sql; gutter: false\">Sp_helptext vwGetCustomers<\/pre>\n<h3>Different Uses of Views:<\/h3>\n<p style=\"text-align: justify;\">Views can also be used to insert, update and delete data from a table.<\/p>\n<p><strong>Example 1: Insert View Example<\/strong><\/p>\n<pre class=\"brush: sql; gutter: false\">INSERT INTO dbo.vwSample\r\nVALUES (&#039;Test1&#039;,&#039;Test1&#039;)\r\n\r\nSELECT * from dbo.vwSample\r\nSELECT * from dbo.Customers<\/pre>\n<p><strong>Example 2: Update View Example<\/strong><\/p>\n<pre class=\"brush: sql; gutter: false\">UPDATE dbo.vwSample\r\nSET LastName = &#039;B&#039;\r\nWHERE CustomerID = 1\r\n\r\nSELECT * from dbo.vwSample\r\nSELECT * from dbo.Customers<\/pre>\n<p><strong>Example 3: Delete View Example<\/strong><\/p>\n<pre class=\"brush: sql; gutter: false\">DELETE FROM dbo.vwSample \r\nWhere CustomerID &gt; 2\r\n\r\nSELECT * from dbo.vwSample\r\nSELECT * from dbo.Customers<\/pre>\n<h3>Difference Between Views and User Defined Functions:<\/h3>\n<p style=\"text-align: justify;\">Views and User-Defined Functions almost serve the same purpose. But the major difference is that User-Defined Function can accept parameters, where as Views cannot. And also the output of the User Defined Function can be directly used in the SELECT clause, whereas you cannot do it with a View.<\/p>\n<h3>Addition of New Column&#8217;s in the Underlying Table will not\u00a0automatically reflect in the existing views:<\/h3>\n<p style=\"text-align: justify;\">Let us prove this behaviour by creating a view vwGetCustomers\u00a0 which returns all customer details with all the columns in the customer table:<\/p>\n<p>\u00a0<\/p>\n<pre class=\"brush: sql; gutter: false\">Create View dbo.vwGetCustomers\r\nAS\r\nSELECT *\r\nFROM Customers\r\nGO\r\nSelect * FROM vwGetCustomers<\/pre>\n<p style=\"text-align: justify;\">Now add one more column Country\u00a0to the Customers table:<\/p>\n<pre class=\"brush: sql; gutter: false\">ALTER Table Customers\r\nADD Country Varchar(30)<\/pre>\n<p style=\"text-align: justify;\">Execute the below statement and observe that the new column country\u00a0added in the Customers table is not present in the result.<\/p>\n<pre class=\"brush: sql; gutter: false\">SELECT * From dbo.vwGetCustomers<\/pre>\n<p style=\"text-align: justify;\">The only way to reflect this new column in the view is to drop and create back the view as below:<\/p>\n<pre class=\"brush: sql; gutter: false\">Drop View dbo.vwGetCustomers\r\nGO\r\nCreate View dbo.vwGetCustomers\r\nAS\r\n SELECT *\r\n FROM dbo.Customers\r\nGO\r\nSELECT * From dbo.vwGetCustomers\r\nGO<\/pre>\n<h3>Below is an example view where it returns the data from multiple tables by joining:<\/h3>\n<pre class=\"brush: sql; gutter: false\">Create View dbo.vwGetCustomerOrders\r\nAS\r\n SELECT C.FirstName,O.OrderId\r\n FROM dbo.Customers C \r\n   INNER JOIN dbo.Orders O\r\n    ON C.CustomerId = O.CustomerId\r\nGO\r\nSelect * from dbo.vwGetCustomerOrders<\/pre>\n<p><strong>[ALSO READ] <a href=\"https:\/\/sqlhints.com\/2015\/07\/03\/how-to-check-if-a-view-exists-in-sql-server\/\" title=\"How to check if a VIEW exists in Sql Server\" target=\"_blank\">How to check if a VIEW exists in Sql Server<\/a><\/strong><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Views are nothing but saved SQL statements, and are sometimes referred as Virtual Tables. Keep in mind that Views cannot store data rather they only refer to data present in tables. Benefits of Views: A view can be useful when there are multiple users with different levels of access, who all need to see portions &hellip; <a href=\"https:\/\/sqlhints.com\/2011\/08\/20\/views-in-sql-server\/\" class=\"more-link\">Continue reading <span class=\"screen-reader-text\">Views in Sql Server<\/span> <span class=\"meta-nav\">&rarr;<\/span><\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[3,10],"tags":[640,631,630,638,321,633,629,637,632,635,986,636,642,627,641,628,678,634,625,626,639],"class_list":["post-41","post","type-post","status-publish","format-standard","hentry","category-sql-server","category-views","tag-addition-of-new-columns-in-the-underlying-table-will-not-automatically-reflect-in-the-existing-views","tag-benefits-of-views","tag-definition-of-views","tag-difference-between-views-and-user-defined-functions","tag-sql","tag-sql-check-view-content","tag-sql-create-view-syntax","tag-sql-delete-view","tag-sql-how-to-check-view-definition","tag-sql-insert-view","tag-sql-server","tag-sql-update-view","tag-sql-view-with-multiple-table-joins","tag-sql-views","tag-sql-views-examples","tag-sql-views-syntax","tag-sql-what-is-a-view","tag-uses-of-views","tag-views","tag-views-in-sql-server","tag-views-vs-user-defined-functions"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p3xNAz-F","_links":{"self":[{"href":"https:\/\/sqlhints.com\/wp-json\/wp\/v2\/posts\/41","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/sqlhints.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sqlhints.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sqlhints.com\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/sqlhints.com\/wp-json\/wp\/v2\/comments?post=41"}],"version-history":[{"count":2,"href":"https:\/\/sqlhints.com\/wp-json\/wp\/v2\/posts\/41\/revisions"}],"predecessor-version":[{"id":3499,"href":"https:\/\/sqlhints.com\/wp-json\/wp\/v2\/posts\/41\/revisions\/3499"}],"wp:attachment":[{"href":"https:\/\/sqlhints.com\/wp-json\/wp\/v2\/media?parent=41"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sqlhints.com\/wp-json\/wp\/v2\/categories?post=41"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sqlhints.com\/wp-json\/wp\/v2\/tags?post=41"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}