SCRIPT: Creación de VISTAS en SQL Server 2008 R2

USE NORTHWIND

GO

--CREACION DE VISTA PROVEEDORES Y SUS PEDIDOS

CREATE VIEW VIEW_SUPPLIER_PRODUCS

AS

SELECT S.SUPPLIERID,S.COMPANYNAME,S.CONTACTNAME

,P.PRODUCTID,P.PRODUCTNAME, P.UNITPRICE

FROM SUPPLIERS AS S INNER JOIN PRODUCTS AS P

ON

S.SUPPLIERID=P.SUPPLIERID

GO

--CREACION DE UNA VISTA CON INSTRUCCION GROUP

CREATE VIEW VIEW_SUBTOTALES(CODIGO_ORDEN,SUB_TOTAL)

AS

SELECT OD.ORDERID,SUM(CONVERT(MONEY,(OD.UNITPRICE* QUANTITY*(1-DISCOUNT)/100))*100)

FROM [ORDER DETAILS] OD

GROUP BY OD.ORDERID

GO

--LLAMAR A LA CONSULTA ANTERIOR

SELECT * FROM VIEW_SUBTOTALES

--VER VISTAS

SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='VIEW'

/*

SOLO LOS DEL ROL DEL SISTEMA SYSADMIN, O DE EL ROL DE BASE DE DATOS DB_OWNER,

DB_DLADMIN HACEN CONSULTAS

O BIEN AL TENER PERMINOS CREATE VIEW Y PERMISOS DE SELECT EN LAS TABLAS A UTILIZAR

-NO SE PUEDE USAR COMPUTE Ó COMPUTE BY

-NO SE PUEDE USAR ORDER BY SO SI SE USA TOP

-NO SE PUEDE HACER REFERENCIA A TABLAS TEMPORALES

-NO SE PUEDE HACER REFERENCIA A MAS DE 1024 COLUMNAS

-NO SE PUEDE COMBINAR CREATE VIEW EN OTRO LOTE

*/

--BORRAR UNA VISTA

DROP VIEW VIEW_SUBTOTALES

--ALTERAR UNA VISTA

ALTER VIEW  VIEW_SUPPLIER_PRODUCS

AS

SELECT S.SUPPLIERID,S.COMPANYNAME,S.CONTACTNAME

,P.PRODUCTID,P.PRODUCTNAME, P.UNITPR ICE

FROM SUPPLIERS AS S INNER JOIN PRODUCTS AS P

ON

S.SUPPLIERID=P.SUPPLIERID

GO

--ESCONDER EL CODIGO DE LA VISTA NO ELIMINE LAS ENTRADAS DE syscomments

ALTER VIEW VIEW_SUPPLIER_PRODUCS

WITH ENCRYPTION

AS

SELECT S.SUPPLIERID,S.COMPANYNAME,S.CONTACTNAME

,P.PRODUCTID,P.PRODUCTNAME, P.UNITPR ICE

FROM SUPPLIERS AS S INNER JOIN PRODUCTS AS P

ON

S.SUPPLIERID=P.SUPPLIERID

GO

--CONSULTAR INFORMACION INFORMACION DE LA VISTA ATRAVEZ DE INFORMATION_SCHEMA

--SI ESTA PUESTO WITH ENRCRYPTION ESTA INFORMACION NO ESTA VISIBLE

SELECT * FROM INFORMATION_SCHEMA.TABLES --o sysobjects --Nombres de vistas y tablas

SELECT * FROM INFORMATION_SCHEMA.VIEW_TABLE_USAGE --o select * from sysdepends 
--Nombres de las tablas o vistas base.

SELECT * FROM INFORMATION_SCHEMA.VIEWS -- o select * from syscomments --Definición de como se hicieron las vistas.

SELECT * FROM INFORMATION_SCHEMA.VIEW_COLUMN_USAGE -- o syscolumns 
--tablas y vistas base y Columnas definidas en una vista.

--PARA VER EL SCRIPT DE UNA CONSULTA O INFORMATION_SCHEMA.VIEWS O SP_HELPTEXT

SP_HELPTEXT [ORDERS QRY]

SP_DEPENDS [ORDERS QRY] --NOMBRES DE TABLA Y SUS DEPENDENCIAS

--PUEDE HACER INSERCIONES EN UNA CONSULTA PERO CONSIDERE QUE

--WITH CHECK OPTION OBLIGA A QUE LAS INSTRUCCIONES DE MODIFICACION SE COMPRUEBEN CONTRA EL WHERE

--CREAR UNA VISTA CON WITH CHECK OPTION PARA COMPROBAR EL EJERCICIO

CREATE VIEW CLIENTESARGENTINA

AS

SELECT * FROM CUSTOMERS WHERE COUNTRY='ARGENTINA'

WITH CHECK OPTION

--ESTA INSERCION DEBE PRODUCIR SOBRE LA CONSULTA ANTERIOR DEBE PRODUCIR UN ERROR.

INSERT CLIENTESARGENTINA (CUSTOMERID,COMPANYNAME,CONTACTNAME,CONTACTTITLE,CITY,COUNTRY)

VALUES

('VHCVV','UNIVERSIDAD GALILEO','VICTOR HUGO CARDENAS','SALES AGENT','GUATEMALA','GUATEMALA')

--CREACION DE UNA TABLA PARA COMPROBAR LA INSERCCION A MULTIPLES TABLAS

CREATE VIEW PROVEEDORES_PRODUCTOS

AS

SELECT S.SUPPLIERID,S.COMPANYNAME,S.CONTACTNAME,S.COUNTRY,P.PRODUCTID,P.PRODUCTNAME,P.UNITPRICE FROM SUPPLIERS AS S

INNER JOIN PRODUCTS AS P ON S.SUPPLIERID=P.SUPPLIERID

--ESTA INSERCION DEBE DE DAR ERROR PORQUE AFECTA A VARIAS TABLAS

INSERT PROVEEDORES_PRODUCTOS(COMPANYNAME,CONTACTNAME,COUNTRY,PRODUCTNAME,UNITPRICE)

VALUES('CARDENAS','VICTOR HUGO','GUATEMALA','LECHE EN POLVO',788.00)

--ESTA INSERCION A LA MISMA CONSULTA, SOLO QUE AHORA AFECTA SOLO 
UNA TABLA, EL RESULTADO ES EXITOSO

INSERT PROVEEDORES_PRODUCTOS(COMPANYNAME,CONTACTNAME,COUNTRY)

VALUES('CARDENAS','VICTOR HUGO','GUATEMALA')

--COLOCAR UN INDICE EN LA CONSULTA PUEDE OPTIMIZAR SU 
RENDIMIENTO, DEBE HACER REFERENCIA AL NOMBRE DE 2 PARTES DEL OBJETO

--DEBE AGREGAR LA OPCION WITH SCHEMABINDIGN

DROP view PROVEEDORES_PRODUCTOS

CREATE VIEW DBO.PROVEEDORES_PRODUCTOS

WITH SCHEMABINDING

AS

SELECT S.SUPPLIERID,S.COMPANYNAME,S.CONTACTNAME,S.COUNTRY,P.PRODUCTID,P.PRODUCTNAME,P.UNITPRICE FROM DBO.SUPPLIERS AS S

INNER JOIN DBO.PRODUCTS AS P ON S.SUPPLIERID=P.SUPPLIERID

--CREACION DEL INDICE, EL PRIMER INDICE DEBE SER UN INDICE UNICO AGRUPADO

CREATE UNIQUE CLUSTERED INDEX CL_SUPPLIERID

ON DBO.PROVEEDORES_PRODUCTOS (SUPPLIERID,PRODUCTID)

--CREACION DE OTROS INDICES NO AGRUPADOS

CREATE NONCLUSTERED INDEX VIEW_PRODUCTNAME

ON PROVEEDORES_PRODUCTOS(PRODUCTNAME)

--PARA REVISAR SI ES INDEXABLE UNA CONSULTA

SELECT  OBJECTPROPERTY (object_id('DBO.PROVEEDORES_PRODUCTOS'),'IsIndexable')

--VISTAS DIVIDIDAS

-- ALL Incorpora todas las filas en los resultados, incluidas las duplicadas.

--Si no se especifica lo contrario, se quitan las filas duplicadas.

SELECT COMPANYNAME, CONTACTNAME, 'CLIENTE' AS ESTADO FROM CUSTOMERS

UNION ALL

SELECT COMPANYNAME, CONTACTNAME, 'PROVEEDOR' AS ESTADO FROM SUPPLIERS

/*

WITH CHECK OPTION

Exige que todas las instrucciones de modificación de datos ejecutadas contra la vista se adhieran a los criterios establecidos en select_statement. Cuando una fila se modifica mediante una vista, WITH CHECK OPTION garantiza que los datos permanecerán visibles en toda la vista después de confirmar la modificación.

WITH ENCRYPTION

Indica que SQL Server cifra las columnas de la tabla del sistema
que contienen el texto de la instrucción CREATE VIEW. Utilizar
WITH ENCRYPTION evita que la vista se publique como parte de la 
duplicación de SQL Server.

SCHEMABINDING

Enlaza la vista al esquema. Cuando se especifica SCHEMABINDING, 
select_statement debe incluir los nombres con dos partes 
(propietario.objeto) de las tablas, vistas o funciones definidas 
por el usuario a las que se hace referencia.

Las vistas o las tablas que participan en una vista creada con 
la cláusula de enlace de esquema no se pueden quitar ni alterar, 
de forma que deja de tener un enlace de esquema. De lo contrario, 
SQL Server genera un error. Además, las instrucciones ALTER TABLE 
sobre tablas que participan en vistas que tienen enlaces de 
esquemas provocarán un error si estas instrucciones afectan a 
la definición de la vista.

VIEW_METADATA

Especifica que SQL Server devolverá a las API de DBLIB, ODBC y 
OLE DB la información de metadatos sobre la vista, en vez de 
las tablas o tabla base, cuando se soliciten los metadatos del 
modo de exploración para una consulta que hace referencia a la 
vista. Los metadatos del modo de exploración son metadatos 
adicionales devueltos por SQL Server a las API DB-LIB, ODBC y 
OLE DB del cliente, que permiten a las API del cliente 
implementar cursores actualizables en el cliente. Los metadatos
del modo de exploración incluyen información sobre la tabla 
base a la que pertenecen las columnas del conjunto de resultados.

Para las vistas creadas con la opción VIEW_METADATA, los 
metadatos del modo de exploración devuelven el nombre de 
vista en vez de los nombres de la tabla base cuando se 
describen las columnas de la vista en el conjunto de resultados.

Cuando se crea una vista WITH VIEW_METADATA, todas sus 
columnas (excepto timestamp) son actualizables si la 
vista tiene los desencadenadores INSERT o UPDATE INSTEAD 
OF. Consulte Vistas actualizables, más adelante en este capítulo.

*/

SCRIPT: CONSTRAINT EN SQL SERVER

--Las restricciones se almacenan en syscomments, sysreferences y sysconstraints. /* CREATE TABLE Tabla ( { < definiciónColumna > | < restricciónTabla > } [ ,...n ]) < definiciónColumna > ::= { columnatipoDeDatos } [ [ DEFAULT expresiónConstante ] [ < restricciónColumna > ] [ ,..n] < restricciónColumna > ::= [ CONSTRAINT nombreRestricción ] | [ { PRIMARY KEY | UNIQUE } [ CLUSTERED | NONCLUSTERED ] ] | [ [ FOREIGN KEY ] REFERENCES tablaRef [ ( columnaRef ) ] [ ON DELETE { CASCADE | NO ACTION } ] [ ON UPDATE { CASCADE | NO ACTION } ]] | CHECK ( expresiónLógica ) } < restricciónTabla > ::= [ CONSTRAINT nombreRestricción ] { [ { PRIMARY KEY | UNIQUE } [CLUSTERED | NONCLUSTERED] { ( columna [ ASC | DESC ] [ ,...n ] ) } ] | FOREIGN KEY [ ( columna [ ,...n ] ) ] REFERENCES tablaRef [ ( columnaRef [ ,...n ] ) ] [ ON DELETE { CASCADE | NO ACTION } ] [ ON UPDATE { CASCADE | NO ACTION } ] | CHECK ( condicionesBúsqueda ) } */ USE northwind CREATE TABLE dbo.Products ( ProductID int IDENTITY (1,1) NOT NULL, ProductName nvarchar (40) NOT NULL, SupplierID int NULL, CategoryID int NULL, QuantityPerUnit nvarchar (20) NULL, UnitPrice money NULL CONSTRAINT DF_Products_UnitPrice DEFAULT(0), UnitsInStock smallint NULL CONSTRAINT DF_Products_UnitsInStock DEFAULT(0), UnitsOnOrder smallint NULL CONSTRAINT DF_Products_UnitsOnOrder DEFAULT(0), ReorderLevel smallint NULL CONSTRAINT DF_Products_ReorderLevel DEFAULT(0), Discontinued bit NOT NULL CONSTRAINT DF_Products_Discontinued DEFAULT(0), CONSTRAINT PK_Products PRIMARY KEY CLUSTERED (ProductID), CONSTRAINT FK_Products_Categories FOREIGN KEY (CategoryID) REFERENCES dbo.Categories (CategoryID) ON UPDATE CASCADE, CONSTRAINT FK_Products_Suppliers FOREIGN KEY (SupplierID) REFERENCES dbo.Suppliers (SupplierID) ON DELETE CASCADE, CONSTRAINT CK_Products_UnitPrice CHECK (UnitPrice >= 0), CONSTRAINT CK_ReorderLevel CHECK (ReorderLevel >= 0), CONSTRAINT CK_UnitsInStock CHECK (UnitsInStock >= 0), CONSTRAINT CK_UnitsOnOrder CHECK (UnitsOnOrder >= 0) ) GO --devuelve todos los tipos de restricciones use northwind go sp_helpconstraint customers --informacion de esquema Select * from information_schema.table_constraints Select * from information_schema.check_constraints Select * from information_schema.referential_constraints go --CREACION DE CONSTRAINTS DROP TABLE INSTRUCTORES CREATE TABLE INSTRUCTORES (CODIGO INT IDENTITY(1,1) NOT NULL, NOMBRE VARCHAR(50), APELLIDO VARCHAR(50), TELEFONO VARCHAR(10) ) --LLAVE PRIMARIA ALTER TABLE INSTRUCTORES ADD CONSTRAINT PK_INSTRUCTORES PRIMARY KEY CLUSTERED (CODIGO) --VALOR PREDETERMINADO ALTER TABLE INSTRUCTORES ADD CONSTRAINT DF_NOMBRE DEFAULT 'SIN NOMBRE' FOR NOMBRE --AGREGAR UNA COLUMNA ALTER TABLE INSTRUCTORES ADD  NACIMIENTO SMALLDATETIME --RESTRICCION CHECK PARA NACIMIENTO ALTER TABLE INSTRUCTORES ADD CONSTRAINT CH_CUMPLEAÑOS CHECK (NACIMIENTO>='1995-01-01' AND NACIMIENTO<=GETDATE()) --VALORES UNICOS ALTER TABLE INSTRUCTORES ADD CONSTRAINT U_APELLIDO UNIQUE NONCLUSTERED (APELLIDO) --LLAVE FORANEA [CONSTRAINT nombreRestricción] [FOREIGN KEY] [(columna[,…n])] REFERENCES tablaRef [(columnaRef [,…n])]. [ ON DELETE { CASCADE | NO ACTION } ] [ ON UPDATE { CASCADE | NO ACTION } ] --BORRAR UN CONSTRAINT ALTER TABLE INSTRUCTORES DROP CONSTRAINT CH_CUMPLEAÑOS --NO COMPRUEBA LOS DATOS EXISTENTES UNA RESTRICCION SOLO SE PUEDE cHECK O FOREINGKEY USE Northwind ALTER TABLE dbo.Employees WITH NOCHECK------------------------------------------------->NO COMPRUEBA LOS DATOS EXISTENTES ADD CONSTRAINT FK_Employees_Employees FOREIGN KEY (ReportsTo) REFERENCES dbo.Employees( USE Northwind ALTER TABLE dbo.Employees WITH NOCHECK ADD CONSTRAINT FK_Employees_Employees FOREIGN KEY (ReportsTo) REFERENCES dbo.Employees(EmployeeID) --CARGAR NUEVOS DATOS SIN QUE SE VERIFIQUE LA RESTRICCION USE Northwind ALTER TABLE dbo.Employees NOCHECK CONSTRAINT FK_Employees_Employees USE Northwind ALTER TABLE dbo.Employees NOCHECK CONSTRAINT FK_Employees_Employees --CREAR UNA REGLA CREATE RULE AÑOS AS @NACIMIENTO>='1995-01-01' AND @NACIMIENTO<=GETDATE() --VINCULAR LA REGLA EXEC sp_bindrule 'AÑOS', 'INSTRUCTORES.NACIMIENTO' --DESVINCULAR LA REGLA EXEC sp_unbindrule   'INSTRUCTORES.NACIMIENTO' --BORRAR LA REGLA DROP RULE AÑOS --VALOR DEFAULT CREATE DEFAULT NADA AS 'unknown' --VINCULAR EL DEFAULT sp_bindefault NADA, 'INSTRUCTORES.NOMBRE' --DESVINCULAR sp_unbindefault 'INSTRUCTORES.NOMBRE' use northwind go sp_helpconstraint employees