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.
- 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.
- 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.
¿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
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.
- Reduzca al mínimo el uso de procedimientos almacenados temporales.
Parámetros de entrada
- Proporcione valores predeterminados apropiados.
- Valide los valores de parámetros de entrada, incluyendo comprobaciones de valores nulos.
Parámetro de salida
Tipos de Funciones:
- Funciones escalares
- Similar a una función integrada
- Devuelven un único valores
- Funciones con valores de tabla en línea
- Similares a una vista con parámetros
- Devuelven una tabla como el resultado de una única instrucción SELECT.
- Funciones con valores de tabla de varias instrucciones
- Similares a un procedimiento almacenado
- Devuelven una nueva tabla como el resultado de instrucciones INSERT.
Función Escalar
Función con valores de tabla en línea
Funciones con valores de tabla de varias instrucciones
