Bases de Datos: enero 2017

Creación de base de datos

Todas las bases de datos tienen dos archivos principales que son:

MDF (Main data file): Almacenamiento de datos.
LDF (Log data file): Almacenamiento de transacciones.
NDF (Num data file): Se tiene la posibilidad de crear de 1 a N registros en la base de datos.

Al crear una base de datos necesitamos especificar el tamaño de ésta en base a las necesidades y calculando (en base a la cantidad de transacciones) un futuro crecimiento (en tamaño o en porcentaje).

Creación de bases de datos y archivos de base de datos

VCreación de bases de datos:

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

Vamos a tener una base para el análisis de los datos en la cual el propósito es el almacenamiento de datos.
Se va a ver el rendimiento de transacciones (número de transacciones)
Crecimiento potencial del almacenamiento físico de los datos (Determinar un crecimiento de los datos)
Ubicación de los archivos (dependiendo los datos)

Registro de transacciones:

Primero va a modificar los datos mediante el envío de la aplicación.
Luego las páginas de datos se encuentran en o se leen en la caché de buffer y se modifican.
Tercero se guarda la modificación dentro del registro de transacciones de disco.
El punto de comprobación escribe las transacciones confirmadas dentro de la base de datos.

Opciones de base de datos:

Establezca las opciones de base de datos utilizando:

SQL Server Management Studio
Instrucción: ALTER DATABASE
Puede ser:

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.
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.

Orígenes de información de las bases de datos:

SQL Server Management Studio
Vistas de Catálogo
Funciones de metadatos.
Procedimientos de almacenamiento del sistema

Creación de grupos de archivos

Todas las bases de datos tienen dos archivos del sistema: archivo de datos y archivo de registro.
Uso de varios archivos en un único grupo de archivos, esto mejora el rendimiento.
Uso de varios grupos de archivos para controlar la colocación física de los datos.

Creación de esquemas

Agrupación lógica separada por módulos, nos sirve para dar orden y para asignar permisos.

Creación de tipos de datos

Creación de tablas
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.

Integridad de los datos

Una restricción le permiten definir la manera en que Motor de base de datos exigirá en la integridad de una base de datos. En el uso de restricciones es preferible usar desencadenadores, reglas, valores predeterminados, etc.

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: Se usa para identificar una o más columnas de una determinada tabla, este valor debe ser único en las columnas constituyentes y no se aceptan valores nulos.

DEFAULT: Define un valor de la columna predeterminado cuando no se proporciona ningún valor.

CHECK: Restringe 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 pueden existir subconsultas.

UNIQUE: Aseguran que todos los valores de una columna sean únicos

Sólo se permite un valor nulo en una columna única

Pueden incluir una o más columnas.

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 las restricciones sin tener que eliminar y volver a crear la tabla.
Realizar comprobación de errores en las aplicaciones y las transacciones.

Desencadenadores:

Procedimientos almacenados especiales que se ejecutan cuando instrucciones ISERT, UPDATE o DELETE modifican una tabla.
Dos categorías:

Los desencadenadores TRIGGER se ejecutan después de una instrucción INSERT, UPDATE o DELETE.
Los desencadenadores INSTEAD OF se ejecutan en lugar de una instrucción INSERT, UPDATE o DELETE.

El desencadenador y la instrucción de inicio forman parte de una única instrucción.

¿Cómo funciona un desencadenador INSERT?

Se ejecuta la instrucción INSERT
Se registra la instrucción INSERT
Se ejecuta el desencadenador AFTER INSERT

¿Cómo funciona un desencadenador DELETE?

Se ejecuta la instrucción DELETE
Se registra la instrucción DELETE
Se ejecuta el desencadenador AFTER DELETE

¿Cómo funciona un desencadenador UPDATE?

Se ejecuta la instrucción UPDATE
Se registra la instrucción UPDATE
Se ejecuta el desencadenador AFTER UPDATE

¿Cómo funciona un desencadenador INSTEAD OF?

Se ejecuta la instrucción UPDATE, INSERT o DELETE
La instrucción ejecutada no se realiza
Se ejecutan las instrucciones del desencadenador INSTEAD OF

Implementación de vistas

¿Qué es una vista?:

Es mostrar una porción de la tabla, guardar ésta información y se puede utilizar como una tabla, ya que se puede acceder a esos datos.

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.

Sintaxis para crear vistas:

Restricciones:

No se pueden anidar más de 32 niveles de profundidad.
No pueden contener más de 1024 columnas.
No pueden utilizar COMPUTE, COMPUTE BY o INTO.
No pueden utilizar ORDER BY sin TOP.

Sintaxis para modificar vistas:

Sintaxis para eliminar vistas:

Como afectan a las vistas las cadenas de propiedad:

Orígenes de información de las vistas.

SQL Server Management Studio

Explorador de objetos:

Lista de vistas de la base de datos.

Acceso a las columnas, los desencadenadores, los índices y las estadísticas definidos en las vistas.

Ver el cuadro de diálogo Propiedades:

Propiedades de vistas individuales.

Transact-SQL.

Ssys.views: Lista de vistas de la base de datos.

sp_helptext: Definición de vistas no cifradas.

sys.sql_dependencies: Objetos (incluyendo vistas) que dependen de otros objetos.

Cifrado de vistas:

Usar la opción WITH ENCRYPTION en la instrucción CREATE VIEW de Transact-SQL

Cifra la definición de vista en la tabla sys.syscomments.

Protege la lógica de creación de la vista.

CREATE VIEW [HumanResources].[vEmployee]

WITH ENCRYPTION AS SELECT e.[EmployeeID],c.[Title],c.[FirstName],c.[MiddleName] ,c.[LastName],c.[Suffix],e.[Title] AS [JobTitle] ,c.[Phone],c.[EmailAddress]

FROM [HumanResources].[Employee] e

INNER JOIN [Person].[Contact] c ON c.[ContactID] = e.[ContactID]

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 al error

Crear un Procedimiento almacenado

Usar EXECUTE para ejecutar procedimiento almacenado

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.