Funciones

Qué es una función escalar

• La cláusula RETURNS especifica el tipo de datos 
• La función se define dentro de un bloque BEGIN…END

CREATE FUNCTION Sales.SumSold(@ProductID int) RETURNS int 
AS 
BEGIN 
      DECLARE @ret int 
      SELECT @ret = SUM(OrderQty) 
      FROM Sales.SalesOrderDetail WHERE ProductID = @ProductID 
      IF (@ret IS NULL) 
        SET @ret = 0 
        RETURN @ret 
END

Se puede invocar en cualquier lugar donde se permita una expresión escalar del mismo tipo de datos.

SELECT ProductID, Name, Sales.SumSold(ProductID) AS SumSold 
FROM Production.Product

Qué es una función con valores de tabla en línea

• RETURNS especifica la tabla como el tipo de datos 
• El formato está definido por el conjunto de resultados 
• El contenido de la función es una instrucción SELECT

CREATE FUNCTION HumanResources.EmployeesForManager 
    (@ManagerId int) 
RETURNS TABLE 
AS 
RETURN ( 
        SELECT FirstName, LastName 
        FROM HumanResources.Employee Employee INNER JOIN 
                    Person.Contact Contact ON Employee.ContactID = Contact.ContactID 
       WHERE ManagerID = @ManagerId )

SELECT * FROM HumanResources.EmployeesForManager(3) 
-- OR 
SELECT * FROM HumanResources.EmployeesForManager(6)

Qué es una función con valores de tabla de varias instrucciones

• RETURNS especifica el tipo de datos de la tabla y define la estructura 
• BEGIN y END incluyen varias instrucciones

CREATE FUNCTION HumanResources.EmployeeNames 
      (@format nvarchar(9)) 
RETURNS @tbl_Employees TABLE 
      (EmployeeID int PRIMARY KEY, [Employee Name] nvarchar(100)) 
AS 
BEGIN 
       IF (@format = 'SHORTNAME') 
         INSERT @tbl_Employees 
         SELECT EmployeeID, LastName FROM HumanResources.vEmployee 
       ELSE IF (@format = 'LONGNAME') 
         INSERT @tbl_Employees 
         SELECT EmployeeID, (FirstName + ' ' + LastName) 
         FROM HumanResources.vEmployee 
      RETURN 
END

SELECT * FROM HumanResources.EmployeeNames('LONGNAME')

Control de errores
Los bloques TRY…CATCH proporcionan la estructura

• El bloque TRY contiene transacciones protegidas 
• El bloque CATCH controla errores

CREATE PROCEDURE dbo.AddData @a int, @b int 
AS 
BEGIN TRY
INSERT INTO TableWithKey VALUES (@a, @b)
 END TRY 
BEGIN CATCH 
    SELECT ERROR_NUMBER() ErrorNumber, 
                    ERROR_MESSAGE() [Message]
END CATCH

Implementación de procedimientos almacenados y funciones

Qué es un procedimiento almacenado 

• Una colección con nombre de instrucciones Transact-SQL o código de Microsoft .NET Framework. 
• Acepta parámetros de entrada y devuelve valores de parámetros de salida.
• Devuelve un valor de estado para indicar el éxito o el error.

Crear en la base de datos actual utilizando la instrucción CREATE PROCEDURE

Directrices para crear procedimientos almacenados

• Calificar nombres de objeto dentro del procedimiento
• Crear un procedimiento almacenado para una tarea 
• Crear, probar y solucionar problemas 
• Evite utilizar el prefijo sp_ en los nombres de procedimientos almacenados Utilice una configuración de conexión coherente para todos los procedimientos almacenados
• Reduzca al mínimo el uso de procedimientos almacenados temporales

Sintaxis para modificar y quitar procedimientos almacenados

ALTER PROCEDURE

 ALTER PROC Production.LongLeadProducts

 AS 

   SELECT Name, ProductNumber, DaysToManufacture 

   FROM Production.Product 

   WHERE DaysToManufacture >= 1 

   ORDER BY DaysToManufacture DESC, Name 

GO 

DROP PROCEDURE

DROP PROC Production.LongLeadProducts

Parámetros de entrada

• Proporcione valores predeterminados apropiados 
• Valide los valores de parámetros de entrada, incluyendo comprobaciones de valores nulos

Parámetros de salida

CREATE PROC HumanResources.AddDepartment 

 @Name nvarchar(50), @GroupName nvarchar(50), 

 @DeptID smallint OUTPUT 

AS

IF ((@Name = '') OR (@GroupName = ''))

RETURN -1

INSERT INTO HumanResources.Department (Name, GroupName) 

VALUES (@Name, @GroupName)

SET @DeptID = SCOPE_IDENTITY()

RETURN 0

DECLARE @dept int,@result int

EXEC @result = AddDepartment 'Refunds' , ' ', @dept OUTPUT

IF (@result = 0)

    SELECT @dept 

ELSE

    SELECT 'Error during insert' 

Usar EXECUTE para ejecutar procedimiento almacenado

Referencias Bibliográficas:

Coronel C., Morris S., Rob P. (2011). Bases de Datos Diseño, implementación y administración (Novena edición). México: Cengage Learning Editores. (Libro digital).

Implementación de vistas

Las vistas pueden considerarse como tablas virtuales. Generalmente hablando, una tabla tiene un conjunto de definiciones, y almacena datos físicamente. Una vista también tiene un conjunto de definiciones, que se construye en la parte superior de la tablas u otras vistas, y no almacena datos físicamente.

Select LastName,FirstName From Employee(Comando para crear la vista estando en modo administrador)

Select * From vEmployee(Comando para el usuario que ejecute solo la tabla vEmployee para que no llegue a los demás datos de Employee).

Vistas sirven para consultar casi siempre.

TIPOS DE VISTAS

• Vistas estándar:  Combinan datos de una o más tablas base (o vistas) en una nueva tabla virtual.
• Vistas indizadas: Materializan (almacenan) la vista mediante la creación de un índice único agrupado en la vista.
• Vistas con particiones: Combinan datos con particiones horizontales de una o más tablas base en uno o varios servidores. Poder dividir la tabla para que se guarde una parte en un archivo y la otra en otro.

VENTAJAS DE LAS VISTAS

• Centrar los datos para un usuario 
• Enmascarar la complejidad de la base de datos 
• Simplificar la administración de los permisos de los usuarios 
• Mejorar el rendimiento 
• Organizar datos para exportarlos a otras aplicaciones

CREACIÓN DE UNA VISTA

Crea una tabla virtual cuyo contenido (columnas y filas) se define mediante una consulta. Utilice esta instrucción para crear una vista de los datos de una o varias tablas de la base de datos. 

• No se pueden anidar mas de de 32 niveles.
• No mas de 1024 columnas.
• No pueden realizar COMPUTE, COMPUTE BY o INTO.
• No se puede usar ORDER BY sin TOP. 

Orígenes de información de las vistas

SINTAXIS PARA MODIFICAR Y QUITAR VISTAS

Modificar utilizando la instrucción ALTER VIEW de Transact-SQL:

Quitar utilizando la instrucción DROP VIEW de Transact-SQL:

CIFRADO DE VISTAS
Usar la opción WITH ENCRYPTION en la instrucción CREATE VIEW de Transact-SQL este comando permite:

•  Cifra la definición de vista en la tabla sys.syscomments 
•  Protege la lógica de creación de la vista

Las vistas no mantienen una copia independiente de los datos (las vistas indizadas son una excepción) 

Las actualizaciones de las vistas modifican las tablas base 

Restricciones:  

• No pueden afectar más de una tabla base  
• No pueden modificar columnas derivadas de funciones de agregado o cálculos  
• No pueden modificar columnas afectadas por cláusulas GROUP BY, HAVING o DISTINCT 

Las actualizaciones de las vistas están restringidas por el uso de WITH CHECK OPTION

Consideraciones de rendimiento para las vistas

Las vistas introducen una sobrecarga de rendimiento porque se resuelven dinámicamente Las vistas anidadas presentan posibles problemas de rendimiento  

• Examine la definición de las vistas anidadas no cifradas  
• Utilice el Analizador de SQL Server para examinar el rendimiento 

Las vistas indizadas pueden mejorar el rendimiento

BIBLIOGRAFÍA:

• Microsoft. (21 de 02 de 2016). SQL CREATE VIEW. Obtenido de: https://www.1keydata.com/es/sql/sql-create-view.php

Triggers (Desencadenadores) en Sql Server

Triggers (Desencadenadores) en Sql Server

Aspectos básicos

Los triggers son un tipo especial de procedimientos almacenados que se ejecutan automáticamente al producirse una modificación de una tabla. Los triggers poseen la utilidad de integrar en la base de datos decisiones de negocio asociadas a los propios datos, sin que sea necesario programar procedimientos externos. Permiten mantener coherencia semántica en la base de datos.

Se suelen utilizar para:
-Implementar restricciones complejas de seguridad o integridad.
-Prevenir transacciones erróneas.
-Implementar reglas administrativas complejas.
-Generar automáticamente valores derivados.
-Auditar las actualizaciones e, incluso, enviar alertas.
-Gestionar replicas remotas de la tabla.

Los disparadores se ejecutan DESPUES de la ejecución de una instrucción "insert", "update" o "delete" en la tabla en la que fueron definidos. Las restricciones se comprueban ANTES de la ejecución de una instrucción "insert", "update" o "delete". Por lo tanto, las restricciones se comprueban primero, si se infringe alguna restricción, el desencadenador no llega a ejecutarse.

Los triggers se crean con la instrucción "create trigger". Esta instrucción especifica la tabla en la que se define el disparador, los eventos para los que se ejecuta y las instrucciones que contiene.

Cómo funciona un desencadenador INSERT

1 Se ejecuta la instrucción INSERT 

2 Se registra la instrucción INSERT

3 Se ejecuta el desencadenador AFTER INSERT 

CREATE TRIGGER [insrtWorkOrder] ON [Production].[WorkOrder]
AFTER INSERT AS
BEGIN
 SET NOCOUNT ON;
 INSERT INTO [Production].[TransactionHistory](
 [ProductID],[ReferenceOrderID],[TransactionType]
 ,[TransactionDate],[Quantity],[ActualCost])
 SELECT inserted.[ProductID],inserted.[WorkOrderID]
 ,'W',GETDATE(),inserted.[OrderQty],0 FROM inserted;
End

Cómo funciona un desencadenador DELETE

1 Se ejecuta la instrucción DELETE 

2 Se registra la instrucción DELETE 

3 Se ejecuta el desencadenador AFTER DELETE  



CREATE TRIGGER [delCustomer] ON [Sales].[Customer] 

AFTER DELETE AS 

BEGIN 

 SET NOCOUNT ON; 

 EXEC master..xp_sendmail 

 @recipients=N'SalesManagers@Adventure-Works.com', 

 @message = N'Customers have been deleted!!'; 

END; 




Cómo funciona un desencadenador UPDATE

1 Se ejecuta la instrucción UPDATE 

2 Se registra la instrucción UPDATE  

3 Se ejecuta el desencadenador AFTER UPDATE 




CREATE TRIGGER [updtProductReview] ON 

[Production].[ProductReview] 

AFTER UPDATE NOT FOR REPLICATION AS 

BEGIN 

 UPDATE [Production].[ProductReview]  

 SET [Production].[ProductReview].[ModifiedDate] = 

 GETDATE() FROM inserted 

 WHERE inserted.[ProductReviewID] = 

 [Production].[ProductReview].[ProductReviewID]; 

END; 




Cómo funciona un desencadenador INSTEAD OF

1. Se ejecuta la instrucción UPDATE, INSERT o DELETE.

2. La instrucción ejecutada no se realiza.

3. Se ejecutan las instrucciones del desencadenador
INSTEAD OF.



CREATE TRIGGER [delEmployee] ON [HumanResources].[Employee] 

INSTEAD OF DELETE NOT FOR REPLICATION AS
BEGIN 

 SET NOCOUNT ON;
 DECLARE @DeleteCount int; 

 SELECT @DeleteCount = COUNT(*) FROM deleted; 

 IF @DeleteCount > 0
 BEGIN

 … 

 END; 

END; 




Consideraciones sobre los desencadenadores
recursivos




Están deshabilitados de forma predeterminada. 




ALTER DATABASE AdventureWorks SET RECURSIVE_TRIGGERS ON



Para habilitarlos:
Consideraciones:  

Puede superar el límite de anidamiento de 32 niveles
sin necesidad de realizar un diseño cuidadoso
y unas pruebas exhaustivas 
Puede resultar difícil controlar el orden de
las actualizaciones de tabla 
Se puede reemplazar con lógica no recursiva

Implementación de integridad de datos

 La integridad de Datos garantiza la calidad de los datos de la base de datos. Por ejemplo, si se especifica para un empleado el valor de identificador de 123, la base de datos no debe permitir que ningún otro empleado tenga el mismo valor de identificador. Si tiene una columna employee_rating para la que se prevean valores entre 1 y5, la base de datos no debe aceptar valores fuera de ese intervalo. Si en la tabla hay una columna dept_id en la que se almacena el número de departamento del empleado, la base de datos sólo debe permitir valores que correspondan a los números de departamento de la empresa.

Las restricciones le permiten definir la manera en que Motor de base de datos exigirá automáticamente la integridad de una base de datos. En el uso de restricciones es preferible usar desencadenadores, reglas, valores predeterminados, entre otros. Tal como se indica en la siguiente tabla.

RESTRICCIONES

Las restricciones definen reglas relativas a los valores permitidos en las columnas y constituyen el mecanismo estándar para exigir la integridad. 

Tipos de Restricciones

PRIMARY KEY: Es utilizado para identificar una o más columnas de una tabla que constituyen una clave principal, este valor debe ser único en las columnas constituyentes y no se aceptan valores nulos en las columnas.

DEFAULT: Se encarga de definir un valor de la columna predeterminado cuando no se proporciona ningún valor

CHECK: Este tipo de restricciones se encargan de restingir los valores que se pueden introducir en una columna con INSERT o con UPDATE, puede hacer referencia a columnas de la misma tabla y en esta restricción no puede haber subconsultas.

Implementación de restricciones:

Restricciones PRIMARY KEY

Una restricción PRIMARY KEY identifica una o más columnas de una tabla que constituyen una clave principal Se permite una restricción PRIMARY KEY por tabla El valor debe ser único en las columnas constituyentes No se permiten valores nulos en las columnas constituyentes.

Restricciones DEFAULT

Una restricción DEFAULT define un valor de columna predeterminado cuando no se proporciona ningún valor Cada columna sólo puede tener una restricción DEFAULT Sólo es aplicable a instrucciones INSERT Se permiten algunas funciones suministradas por el sistema.

Restricciones CHECK

Las restricciones CHECK restringen los valores que se pueden introducir en una columna con INSERT o con UPDATE Puede definir varias restricciones CHECK por columna Puede hacer referencia a columnas de la misma tabla No puede contener subconsultas.

Restricciones UNIQUE

Las restricciones UNIQUE aseguran que todos los valores de una columna son únicos Sólo se permite un valor nulo en una columna única Pueden incluir una o más columnas.

Restricciones FOREIGN KEY

Las restricciones FOREIGN KEY garantizan integridad referencial entre columnas de la misma tabla o de tablas diferentes Deben hacer referencia a una restricción PRIMARY KEY o UNIQUE El usuario debe tener permiso REFERENCES en la tabla a la que se hace referencia

Consideraciones sobre la comprobación de restricciones

Asignar nombres significativos a las restricciones 
Crear, cambiar y eliminar restricciones sin necesidad de eliminar y volver a crear la tabla 
Realizar comprobación de errores en las aplicaciones y las transacciones
Deshabilitar las restricciones CHECK y FOREIGN KEY: Para mejorar el rendimiento al ejecutar trabajos por lotes grandes.
Para evitar comprobar los datos existentes al agregar nuevas restricciones a una tabla.

BIBLIOGRAFÍA:

• Microsoft. (7 de 6 de 2016). Restricciones. Obtenido de: https://technet.microsoft.com/es-es/library/ms189862(v=sql.105).aspx

TIPOS DE DATOS

Un tipo de datos es un atributo que especifica el tipo de datos que el objeto puede contener: datos de enteros, datos de caracteres, datos de moneda, datos de fecha y hora, cadenas binarias, etc. En la siguiente tabla se indica mas detallado los tipos de datos:

Tipos de datos alias: Se basan en los tipos suministrados por el sistema, por lo general se usan para elementos de datos comunes con un formato específico y se crean con la instrucción CREATE TYPE .  

Consideraciones para la creación de tablas

• Intercalación de columnas 
• Capacidad de aceptar valores NULL de columnas 
• Tipos de columna especiales: calculadas, identidad, timestamp y uniqueidentifier

Los tipos definidos por el usuario obtienen sus características de los métodos y los operadores de una clase que se crean mediante uno de los lenguajes de programación compatibles con .NET Framework. SQL organiza los datos en filas de la siguiente manera tal como se muestra en la imagen:

Estos datos se ordenan, en donde el encabezado va primero que tendrá asignado 4 bytes.

BIBLIOGRAFÍA:

• Microsoft. (18 de 10 de 2013). Tipos de Datos. Obtenido de: https://msdn.microsoft.com/es-ec/library/ms187752.aspx

Creación de Base de datos

Todas las bases de datos tienen dos archivos importantes que son: ldf(log database file). y mdf(master database file).

Los datos se almacenan en el mdf.

Siempre hay que estimar cuanto queremos que crezca la base de datos.

Las bases de datos del usuario son creadas para usos específicos por cualquier aplicación o usuario. Cuando el administrador de una base de datos crea una nueva, SQL Server crea el archivo MDF y LDF correspondiente a esa base de datos. SQL Server los coloca inicialmente en su directorio por defecto, o el administrador puede definir dónde van a ser almacenados. Después de la creación, pueden ser movidos a diferentes ubicaciones por razones de conveniencia, desempeño o almacenamiento.

SQL Server no exige las extensiones de archivo .mdf, .ndf, .ldf, pero estas extensiones ayudan a identificar las distintas clases de archivo.

Modelo 1: Creación de base de datos y archivos de base de datos:

Creación de base de datos:

Consideraciones para la creación de una base de datos:

• Propósito del almacenamiento de datos 
• Rendimiento de transacciones 
• Crecimiento potencial del almacenamiento físico de datos 
• Ubicación de los archivos 

Opciones de base de datos 

Establezca las opciones de base de datos utilizando:

• SQL Server Management Studio
•  Instrucción ALTER DATABASE 

Automática: Controla los comportamientos automáticos como estadísticas, cierre de base de datos y reducción

Disponibilidad: Controla si la base de datos tiene o no conexión, quién puede conectar con ella y si la base de datos es de sólo lectura

Cursor : Controla el comportamiento y el ámbito del cursor

Recuperación: Controla el modelo de recuperación de la base de datos

SQL: Controla las opciones de compatibilidad con ANSI como valores nulos ANSI y desencadenadores recursivos.

Cuándo crear grupos de archivos

• Uso de varios archivos en un único grupo de archivos para mejorar el rendimiento 
• Uso de varios grupos de archivos para controlar la colocación física de los datos