{"id":310,"date":"2017-05-24T23:55:25","date_gmt":"2017-05-24T23:55:25","guid":{"rendered":"http:\/\/www.manualsqlserver.com\/?p=310"},"modified":"2020-05-21T00:03:19","modified_gmt":"2020-05-21T00:03:19","slug":"vistas-en-sql-server","status":"publish","type":"post","link":"https:\/\/manualsqlserver.com\/?p=310","title":{"rendered":"Vistas en SQL Server"},"content":{"rendered":"\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"300\" height=\"314\" src=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_VistasD.png\" alt=\"\" class=\"wp-image-1404\" srcset=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_VistasD.png 300w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_VistasD-287x300.png 287w\" sizes=\"auto, (max-width: 300px) 100vw, 300px\" \/><\/figure>\n\n\n\n<h1 class=\"wp-block-heading\"><strong>Vistas en SQL Server<\/strong><\/h1>\n\n\n\n<ul class=\"wp-block-list\"><li>La vista es un objeto en la base de datos creado como resultado de una consulta.<\/li><li>La vista se puede crear con datos de una o varias tablas de la base de datos.<\/li><li>Las vistas pueden guardar listados en la base de datos con un conjunto de registros de consulta frecuente.<\/li><li>Restringir la visualizaci\u00f3n de algunas columnas que est\u00e1n presente en tablas.<\/li><li>Para mantener compatibilidad con versiones anteriores de SQL Server.<\/li><\/ul>\n\n\n\n<!--more-->\n\n\n\n<h2 class=\"wp-block-heading\"><strong>Instrucci\u00f3n Create View<\/strong><\/h2>\n\n\n\n<p><strong>Permite crear una vista<\/strong><\/p>\n\n\n\n<p class=\"has-medium-font-size\"><strong>Sintaxis: <\/strong><\/p>\n\n\n\n<p>Create View&nbsp; [ Esquema .] NombreVista<br>AS Instrucci\u00f3n Select<\/p>\n\n\n\n<p>Donde:<\/p>\n\n\n\n<p>Esquema.NombreVista: especifica el nombre de la vista a crear.<\/p>\n\n\n\n<p>Instrucci\u00f3n Select: es la instrucci\u00f3n Select que define la vista.<\/p>\n\n\n\n<script async src=\"\/\/pagead2.googlesyndication.com\/pagead\/js\/adsbygoogle.js\"><\/script>\n<ins class=\"adsbygoogle\"\n     style=\"display:block; text-align:center;\"\n     data-ad-layout=\"in-article\"\n     data-ad-format=\"fluid\"\n     data-ad-client=\"ca-pub-2636008503986218\"\n     data-ad-slot=\"1902193434\"><\/ins>\n<script>\n     (adsbygoogle = window.adsbygoogle || []).push({});\n<\/script>\n\n\n\n<h2 class=\"wp-block-heading\"><strong>Instrucci\u00f3n Alter View<\/strong><\/h2>\n\n\n\n<p><strong>Permite modificar una vista<\/strong><\/p>\n\n\n\n<p>Alter View [ Esquema . ] NombreVista<br>As Instrucci\u00f3n Select<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><strong>Instrucci\u00f3n Drop View<\/strong><\/h2>\n\n\n\n<p><strong>Permite eliminar una vista<\/strong><\/p>\n\n\n\n<p>Drop view [ Esquema . ] NombreVista<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Ejercicios<\/h3>\n\n\n\n<p><strong>Usando Northwind<\/strong><br>use Northwind<br>go<\/p>\n\n\n\n<p><strong>1. Vista con los Productos descontinuados<\/strong><\/p>\n\n\n\n<p>Create view VistaProductosDescontinuados<br>As<br>Select P.ProductID As &#8216;C\u00f3digo&#8217;, P.ProductName As &#8216;Descripci\u00f3n&#8217;,<br>P.UnitPrice As &#8216;Precio&#8217;, P.UnitsInStock As &#8216;Stock&#8217;,<br>P.UnitsInStock As &#8216;En Orden&#8217;<br>from Products As P<br>where P.Discontinued = 1<br>go<\/p>\n\n\n\n<p><strong>2. Vista con los empleados<\/strong><\/p>\n\n\n\n<p>Create view VistaEmpleadosListado<br>As<br>select EmployeeID As &#8216;C\u00f3digo&#8217;,<br>Empleado = LastName + SPACE(1) + FirstName,<br>Format(BirthDate,&#8217;dd\/MM\/yyyy&#8217;) As &#8216;Fecha Nac.&#8217;<br>from Employees<br>go<\/p>\n\n\n\n<p><strong>3. Para listar los registros de la vista de Empleados<\/strong><\/p>\n\n\n\n<p>select * from VistaEmpleadosListado<br>order by [Fecha Nac.]<br>go<\/p>\n\n\n\n<p><strong>4. Vista de Productos y Categorias<\/strong><\/p>\n\n\n\n<p>Create View vistaProductosyCategoria<br>As<br>select P.ProductID As &#8216;C\u00f3digo&#8217;, P.ProductName,<br>P.UnitPrice As &#8216;Precio&#8217;, C.CategoryName<br>from Products As P<br>join Categories As C on P.CategoryID = C.CategoryID<br>go<\/p>\n\n\n\n<p><strong>5. Cambiar la vista con mas campos<\/strong><\/p>\n\n\n\n<p>Alter View vistaProductosyCategoria<br>As<br>select P.ProductID As &#8216;C\u00f3digo&#8217;, P.ProductName,<br>P.UnitPrice As &#8216;Precio&#8217;, C.CategoryName,<br>P.UnitsInStock As &#8216;Stock&#8217;,<br>P.UnitsOnOrder As &#8216;En Orden&#8217;<br>from Products As P<br>join Categories As C on P.CategoryID = C.CategoryID<br>go<\/p>\n\n\n\n<p><strong>6. Vista con los Clientes que compraron mas de 2000<\/strong><\/p>\n\n\n\n<p>Create view VistaClientesTopComprasMas2000<br>As<br>select C.CustomerID As &#8216;C\u00f3digo Cliente&#8217;, C.CompanyName As &#8216;Cliente&#8217;,<br>C.Address As &#8216;Direcci\u00f3n&#8217;, C.Country As &#8216;Pa\u00eds&#8217;,<br>SUM(O.Freight) As &#8216;Total Comprado&#8217;<br>from Customers As C<br>join Orders As O on C.CustomerID = O.CustomerID<br>Group by C.CustomerID, C.CompanyName,C.Address, C.Country<br>Having SUM(O.Freight) &gt; 2000<br>go<br>&#8212; Ver <a href=\"http:\/\/www.manualsqlserver.com\/?p=207\">Funciones de Agregado<\/a>, <a href=\"http:\/\/www.manualsqlserver.com\/?p=215\">Joins<\/a>, <a href=\"http:\/\/www.manualsqlserver.com\/?p=225\">Agrupamientos<\/a><\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Importante<\/h3>\n\n\n\n<ul class=\"wp-block-list\"><li>En la definici\u00f3n de la consulta no se puede usar Order By salvo que se incluya Top.<\/li><li>Todas las columnas de la definici\u00f3n de la vista deben tener nombre.<\/li><li>El select de la definici\u00f3n de la vista no puede tener la cl\u00e1usula Into Tabla.<\/li><li>La vista puede tener hasta 1,024 columnas.<\/li><\/ul>\n\n\n\n<script async src=\"\/\/pagead2.googlesyndication.com\/pagead\/js\/adsbygoogle.js\"><\/script>\n<ins class=\"adsbygoogle\"\n     style=\"display:block; text-align:center;\"\n     data-ad-layout=\"in-article\"\n     data-ad-format=\"fluid\"\n     data-ad-client=\"ca-pub-2636008503986218\"\n     data-ad-slot=\"1902193434\"><\/ins>\n<script>\n     (adsbygoogle = window.adsbygoogle || []).push({});\n<\/script>\n\n\n\n<p><strong>7. Vista con Order by y Top<\/strong><\/p>\n\n\n\n<p>Create view VistaClientesTopComprasMas2000Solo3<br>As<br>select Top 3 C.CustomerID As &#8216;C\u00f3digo Cliente&#8217;, C.CompanyName As &#8216;Cliente&#8217;,<br>C.Address As &#8216;Direcci\u00f3n&#8217;, C.Country As &#8216;Pa\u00eds&#8217;,<br>SUM(O.Freight) As &#8216;Total Comprado&#8217;<br>from Customers As C<br>join Orders As O on C.CustomerID = O.CustomerID<br>Group by C.CustomerID, C.CompanyName,C.Address, C.Country<br>Having SUM(O.Freight) &gt; 2000<br>order by SUM(O.Freight) desc<br>go<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Vistas en SQL Server La vista es un objeto en la base de datos creado como resultado de una consulta. La vista se puede crear con datos de una o varias tablas de la base de datos. Las vistas pueden guardar listados en la base de datos con un conjunto de registros de consulta frecuente. &hellip; <\/p>\n<p><a class=\"more-link btn\" href=\"https:\/\/manualsqlserver.com\/?p=310\">Seguir leyendo<\/a><\/p>\n","protected":false},"author":1,"featured_media":1404,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[9],"tags":[22,53,64],"class_list":["post-310","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-registros-vistas","tag-create-view","tag-sqlserver","tag-vistas","nodate","item-wrap"],"_links":{"self":[{"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/posts\/310","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=310"}],"version-history":[{"count":1,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/posts\/310\/revisions"}],"predecessor-version":[{"id":1405,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/posts\/310\/revisions\/1405"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/media\/1404"}],"wp:attachment":[{"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=310"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=310"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=310"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}