MANUAL DE SQL SERVER

MANUAL DE SQL SERVER

INTRODUCCIÓN

INFORMATICA 64, S.L.

Qué es SQL Server____________________________________________________ 10 Arquitectura Cliente/Servidor________________________________________ 10 Sistema de Gestión de Bases de Datos Relacionales ______________________ 10 Transact-SQL _____________________________________________________ 10 Plataformas de SQL Server __________________________________________ 10 Integración de SQL Server con Windows NT ___________________________ 11 Servicios de SQL Server ____________________________________________ 11 Software de SQL Server ____________________________________________ 13 ARQUITECTURA DE SQL SERVER ____________________________________ 15 Comunicación _____________________________________________________ 15 Administración ____________________________________________________ 16 SEGURIDAD DE SQL SERVER ________________________________________ 17 Figuras para mantener el sistema seguro: ______________________________ 17 Capas __________________________________________________________ 17 Figuras de identificación ____________________________________________ 18 a) Identificador de inicio de sesión (login id): acceso a SQL Server. _________ 18 b) Usuario de la BDS: para usar una BDS se tiene que haber sido declarado como usuario de la misma. _______________________________________________ 18 c) Funciones de servidor de la BDS. __________________________________ 18 Identificadores de inicio de sesión_____________________________________ 20 Usuarios de la BDS _________________________________________________ 22 Funciones de la BDS ________________________________________________ 23 Permisos a Objetos _________________________________________________ Desde el P.D.V. del Objeto _________________________________________ Desde el P.D.V. del Usuario_________________________________________ Desde el P.D.V. de las Sentencias ____________________________________

24 24 25 25

BASES DE DATOS DE SQL SERVER ___________________________________ 26 Bases de datos del sistema ___________________________________________ 26 Objetos de las bases de datos _________________________________________ 27 Tablas del sistema__________________________________________________ 28 Recuperación de metadatos __________________________________________ 29 Gestión de Datos _____________________________________________________ 31 Aspectos Básicos ___________________________________________________ Qué es una Base de Datos en SQL Server ______________________________ Dónde ubicar una base de datos ______________________________________ Relación de transacciones___________________________________________ Base de Datos Temporal (tempdb) ____________________________________

31 31 31 31 31

Aspectos básicos de la creación de bases de datos ________________________ 32 Dónde se almacenan las bases de datos ________________________________ 32 INFORMATICA 64 SL

1

INTRODUCCIÓN


Tamaño de una BDS_______________________________________________ 33 Permisos de creación de bases de datos ________________________________ 33 Propiedad de la Base de Datos _______________________________________ 33 Registro de Transacciones (transaction log) ____________________________ Qué es __________________________________________________________ Proceso de escritura _______________________________________________ Dimensiones del transaction log______________________________________

34 34 34 34

Transacciones _____________________________________________________ 35 Creación de bases de datos __________________________________________ Aspectos previos__________________________________________________ Nombre de la base de datos. Identificadores. ____________________________ Secuencia de creación______________________________________________

36 36 36 36

Creación de la BDS_________________________________________________ Asistente para la creación de Bases de Datos____________________________ Creación desde el Administrador Corporativo ___________________________ Creación con Transact-SQL (CREATE DATABASE) ____________________

37 37 40 42

Modificación de bases de datos _______________________________________ Expansión de bases de datos_________________________________________ Expansión con Transact-SQL (ALTER DATABASE) ____________________ Reducción del tamaño de bases de datos _______________________________ Reducción con Transact-SQL________________________________________

45 45 46 47 48

Configuración de la base de datos_____________________________________ 49 Renombrar bases de datos __________________________________________ 50 Recabar información sobre bases de datos ______________________________ 50 Eliminación de bases de datos ________________________________________ 51 Tablas ______________________________________________________________ 52 Creación de Tablas _________________________________________________ Nombres de tabla _________________________________________________ Nombres de columna ______________________________________________ Tipos de datos____________________________________________________ Tipos de datos definidos por el usuario ________________________________ Permitir valores nulos______________________________________________ Valor predeterminado ______________________________________________ Identidad ________________________________________________________ Inicialización de identidad __________________________________________ Incremento de identidad ____________________________________________ Es Rowguid _____________________________________________________

52 53 53 53 55 55 55 55 55 55 55

Restricciones ______________________________________________________ Clave primaria ___________________________________________________ a. Relaciones_____________________________________________________ b. Índices y claves_________________________________________________ c. Tablas ________________________________________________________ Modificación de Tablas ____________________________________________ Eliminación de Tablas _____________________________________________

56 56 56 56 57 58 58

Vistas ______________________________________________________________ 59

INFORMATICA 64 SL

2

INTRODUCCIÓN


Aspectos básicos ___________________________________________________ 59 Consulta y actualización____________________________________________ 59 Creación de Vistas _________________________________________________ 60 Creación de Vistas _________________________________________________ 62 Creación de Vistas _________________________________________________ 65 Eliminación de Vistas _______________________________________________ 68 Diagramas __________________________________________________________ 69 Creación de Diagramas de Bases de Datos______________________________ 69 Elementos de un diagrama __________________________________________ Tablas __________________________________________________________ Claves primarias __________________________________________________ Relaciones entre las tablas __________________________________________ Comentarios _____________________________________________________

70 70 71 71 72

Diagramas y tablas _________________________________________________ 72 Crear una tabla desde un diagrama____________________________________ 72 Añadir una tabla desde un diagrama___________________________________ 72 Documentación del modelo __________________________________________ 72 Índices _____________________________________________________________ 73 Tipos de índices____________________________________________________ 73 Selectividad de los índices ___________________________________________ 73 Creación de índices_________________________________________________ 73 Creación con el comando CREATE INDEX ____________________________ 74 Asistente para la creación de índices __________________________________ 75 Actualización de estadísticas _________________________________________ 77 Restricciones ______________________________________________________ Primary Key _____________________________________________________ Foreign Key _____________________________________________________ Unique _________________________________________________________ Default _________________________________________________________ Check __________________________________________________________

78 78 78 79 79 79

Restricciones mediante el administrador corporativo ____________________ 79 Reglas____________________________________________________________ Creación ________________________________________________________ Vinculación______________________________________________________ Desvinculación ___________________________________________________ Eliminar reglas ___________________________________________________

80 80 81 82 82

Valores predeterminados ____________________________________________ Creación ________________________________________________________ Vinculación______________________________________________________ Desvinculación ___________________________________________________ Eliminación______________________________________________________

83 83 84 84 85

INFORMATICA 64 SL

3

INTRODUCCIÓN


ELEMENTOS DE LAS SENTENCIAS SQL ______________________________ 86 Componentes______________________________________________________ 86 Tipos de sentencias según el tipo de operación __________________________ 87 Ejecución de las sentencias SQL ______________________________________ 87 CONSULTAS SENCILLAS ____________________________________________ 88 Sentencia SELECT simplificada ______________________________________ 88 Orden de resolución ________________________________________________ 88 Eliminación de filas repetidas ________________________________________ 89 Inclusión de filas repetidas __________________________________________ 89 Inclusión de valores superiores _______________________________________ 89 Lista de columnas __________________________________________________ 89 AliasColumna _____________________________________________________ 89 Uso de la base de datos desde otra base de datos_________________________ 90 EXPRESIONES______________________________________________________ 91 EXPRESIONES ___________________________________________________ 91 PREDICADOS_______________________________________________________ 92 1. Predicados simples _______________________________________________ 92 Predicado NULL _________________________________________________ 92 PREDICADO BETWEEN __________________________________________ 93 PREDICADO LIKE ________________________________________________ PREDICADO IN _________________________________________________ PREDICADOS CUANTIFICADOS __________________________________ PREDICADO EXISTS_____________________________________________

93 94 94 94

2. Predicados compuestos____________________________________________ 95 FUNCIONES ESCALARES ____________________________________________ 96 Funciones de manejo de caracteres____________________________________ 96 CHARINDEX (T-SQL) ____________________________________________ 96 LEFT (T-SQL) ___________________________________________________ 97 RIGHT (T-SQL) __________________________________________________ 98 LOWER (T-SQL) _________________________________________________ 98 PATINDEX (T-SQL) ______________________________________________ 99 REPLACE (T-SQL) ______________________________________________ 100 REPLICATE (T-SQL) ____________________________________________ 101 SPACE (T-SQL)_________________________________________________ 101 STUFF (T-SQL) _________________________________________________ 102 SUBSTRING (T-SQL) ____________________________________________ 102 FECHAS Y HORAS_______________________________________________ 104 FECHAS Y HORAS_______________________________________________ Valores alfanuméricos como valores de tiempo_________________________ Formatos _______________________________________________________ Operaciones de comparación _______________________________________ INFORMATICA 64 SL

104 104 104 104 4

INTRODUCCIÓN


Registros especiales ______________________________________________ GETDATE (T-SQL)______________________________________________ DATEADD (T-SQL) _____________________________________________ DATEDIFF (T-SQL) _____________________________________________ DATENAME (T-SQL)____________________________________________ DATEPART (T-SQL) ____________________________________________ Funciones para descomponer un valor de tiempo _______________________ YEAR (T-SQL) _________________________________________________ MONTH (T-SQL) _______________________________________________ fecha __________________________________________________________ DAY (T-SQL) __________________________________________________

104 105 105 106 107 108 109 109 109 109 109

Conversión de tipos de datos ________________________________________ 110 CAST _________________________________________________________ 110 CONVERT _____________________________________________________ 110 Funciones matemáticas ____________________________________________ Abs ___________________________________________________________ Ceiling ________________________________________________________ Floor __________________________________________________________ Sign___________________________________________________________ SQRT _________________________________________________________ Power _________________________________________________________

114 114 114 114 114 114 114

FUNCIONES NILADIC ___________________________________________ CURRENT_TIMESTAMP ________________________________________ CURRENT_USER (T-SQL) _______________________________________ SESSION_USER (T-SQL)_________________________________________ SYSTEM_USER (T-SQL) _________________________________________ USER (T-SQL) __________________________________________________ USER_NAME (T-SQL) ___________________________________________

115 115 115 116 116 117 118

FUNCIONES COLECTIVAS __________________________________________ 119 Reglas y formatos _________________________________________________ 120 Formato 1 _______________________________________________________ 120 Formato 2 _______________________________________________________ 120 Formato 3 _______________________________________________________ 120 AGRUPAMIENTO DE FILAS_________________________________________ 121 CLÁUSULA GROUP BY __________________________________________ 121 CLAUSULA HAVING_____________________________________________ 121 Filas Resumen ____________________________________________________ 122 WITH ROLLUP _________________________________________________ 122 COMPUTE Y COMPUTE BY _____________________________________ 126 RESUMEN DE SELECT CON AGRUPAMIENTO ____________________ 130 SECUENCIA DE RESOLUCIÓN DE LAS CLÁUSULAS _______________ 130 CONSULTAS COMPUESTAS _________________________________________ 131 CALIFICACIÓN DE NOMBRES ___________________________________ 131 Nombre de correlación ____________________________________________ 131 INFORMATICA 64 SL

5

INTRODUCCIÓN


Producto cartesiano_______________________________________________ 133 INNER JOIN ___________________________________________________ 133 OUTER JOIN ___________________________________________________ 134 SENTENCIAS SELECT SUBORDINADAS___________________________ 136 SENTENCIAS SELECT CORRELACIONADAS ______________________ 136 COMPOSICIÓN DE CONSULTAS Y SELECT COMPLETO _______________ 137 CONSULTAS COMPUESTAS ______________________________________ 137 FORMATOS DE SELECT _________________________________________ 138 Formato 1: Select básico ___________________________________________ 138 Formato 2: Select compuesto________________________________________ 138 DEFINICIÓN DE TABLAS ___________________________________________ SENTENCIAS PARA DEFINICIÓN DE DATOS ______________________ IDENTIFICADORES_____________________________________________ SENTENCIA PARA DEFINIR TABLAS _____________________________

139 139 139 139

SENTENCIAS PARA MODIFICAR DATOS__________________________ 140 Añadir nuevas filas: INSERT _______________________________________ 140 Formato 1 _______________________________________________________ 140 Formato 2 _______________________________________________________ 140 Crear nuevas tablas: Select into _____________________________________ 141 Borrar filas: DELETE _____________________________________________ 141 Modificar registros: UPDATE ______________________________________ 141 Conceptos previos: sentencias de control de flujo y declaración de variables 142 Variables ________________________________________________________ 142 Variables locales_________________________________________________ 142 Variables globales _______________________________________________ 143 Sentencias de control de flujo _______________________________________ 143 IF...ELSE ______________________________________________________ 143 BEGIN...END___________________________________________________ 143 Procedimientos almacenados y triggers _______________________________ 144 Procedimientos almacenados________________________________________ Compilación de procedimientos almacenados: el plan____________________ Características básicas: ____________________________________________ Tipos: _________________________________________________________

144 144 145 145

Triggers _________________________________________________________ Triggers y DRI __________________________________________________ Acciones que motivan la ejecución de un trigger________________________ Utilidad de los triggers ____________________________________________

145 145 146 146

Creación de Procedimientos ________________________________________ 147 Transact-SQL ___________________________________________________ 147 Sentencias admisibles en un procedimiento almacenado __________________ 149

INFORMATICA 64 SL

6

INTRODUCCIÓN


Creación de Procedimientos ________________________________________ 150 Administrador Corporativo ________________________________________ 150 Creación de Procedimientos ________________________________________ 151 Asistente _______________________________________________________ 151 Ejecución de procedimientos almacenados ____________________________ 154 Sentencia EXECUTE _____________________________________________ 154 Triggers _________________________________________________________ 156 Creación de Triggers ______________________________________________ Transact-SQL ___________________________________________________ Acciones que desatan la ejecución del trigger __________________________ Ejecución condicional_____________________________________________ Cifrado ________________________________________________________ Sentencias en un trigger ___________________________________________

156 156 156 157 157 157

Creación de Triggers ______________________________________________ Administrador Corporativo ________________________________________ Eliminación de Triggers ___________________________________________ Utilización de triggers para validar actualizaciones de tablas ______________ Triggers e inserción condicional_____________________________________ 1. Creación de la base de datos______________________________________ 2. Creación de las tablas ___________________________________________ 3. Definición de procedimientos almacenados __________________________ 4. Definición de Triggers __________________________________________

157 157 158 158 158 160 160 160 161

COPIAS DE SEGURIDAD____________________________________________ 162 1. Conceptos básicos _______________________________________________ A) Copia de seguridad ____________________________________________ B) Recuperación _________________________________________________ C) Registro de transacciones _______________________________________ D) Dispositivo de copia de seguridad_________________________________ E) Tipos de copias de seguridad _____________________________________ F) Plan de copias de seguridad ______________________________________

162 162 162 162 163 163 164

2. Dispositivos de copia de seguridad _________________________________ a) Tipos de dispositivos de copia de seguridad _________________________ b) Creación de dispositivos de copia de seguridad_______________________ C) Dinámica de almacenamiento ____________________________________

165 165 166 167

3. Realizar copias de seguridad ______________________________________ 168 Automatización de tareas asociadas a la copia__________________________ 171 Asistente para copias de seguridad ___________________________________ 171 4. Recuperación de datos desde una copia de seguridad _________________ 173 MÉTODO ______________________________________________________ 174 Automatización de Tareas Administrativas _______________________________ 176 Motivos de la Automatización _______________________________________ 176 Llevar a cabo tareas programadas ___________________________________ 176 Reconocer y reaccionar ante problemas potenciales _____________________ 176 Fundamentos de la Automatización __________________________________ 176

INFORMATICA 64 SL

7

INTRODUCCIÓN


Coordinación de SQL Server con el Registro de la Aplicación. ____________ 177 Preparación de la automatización____________________________________ 177 Asegurarse de que SQL Server Agent está arrancado ____________________ 177 Asegurarse de que la cuenta de acceso de SQL Server Agent tiene la función sysadmin _______________________________________________________ 177 Aspectos básicos __________________________________________________ El Agente de SQL Server __________________________________________ Figuras de administración centralizada _______________________________ Entornos multiservidor ____________________________________________ La base de datos msdb ____________________________________________

179 179 179 180 180

Operadores ______________________________________________________ 181 Mecanismos de comunicación con los operadores_______________________ 181 Creación de operadores ___________________________________________ 181 Trabajos_________________________________________________________ Etapas (steps) ___________________________________________________ Creación de trabajos ______________________________________________ Ejecución de trabajos _____________________________________________

183 183 184 186

Alertas __________________________________________________________ 187 Creación de alarmas ______________________________________________ 187 Creación de Páginas Web __________________________________________ 189 CONEXIÓN ODBC __________________________________________________ 190 A) Arquitectura de ODBC __________________________________________ A.1) Componentes de ODBC _______________________________________ A.2) Niveles de conformidad de API ODBC ___________________________ A.3) Niveles de reconocimiento de lenguaje ANSI-SQL__________________

190 190 191 191

Fuentes de datos ODBC ____________________________________________ 192 Tipos de fuentes de datos ODBC ____________________________________ 192 Creación de fuentes de datos ODBC _________________________________ 192 Transferencia de Datos _______________________________________________ 196 ♦ Introducción a la Importación y Exportación de Datos ________________ 196 Por qué Transformar datos _________________________________________ 197 Herramientas de SQL Server para Importar y Exportar Datos______________ 198 ♦ Intro. a los Servicios de Transformación de Datos ____________________ Intro a DTS _____________________________________________________ Proceso del Servicio de Transformación de Datos_______________________ Herramientas de DTS _____________________________________________

199 199 199 199

♦ Transformación de Datos con DTS_________________________________ Creación de Paquetes de DTS ______________________________________ Transformación de Datos __________________________________________ Demostración ___________________________________________________

200 200 200 201

La Duplicación _____________________________________________________ 204 Consideraciones para distribuir datos_________________________________ 204 ♦ Modelo publicador-suscriptor_____________________________________ 205

INFORMATICA 64 SL

8

INTRODUCCIÓN


♦ Elementos a tener en cuenta para distribuir los datos _________________ 206 Modelos de sincronización _________________________________________ 206 ♦ El compromiso autonomía-coherencia transaccional __________________ 207 Coherencia de las transacciones _____________________________________ 207 ♦ Componentes del proceso_________________________________________ 208 ♦ Tipos de Duplicación en SQL Server _______________________________ Duplicación íntegra (snapshot replication)_____________________________ Duplicación transaccional (transactional replication) ____________________ Duplicación de fusión (merge replication) _____________________________

209 209 210 211

♦ Configuración de la duplicación ___________________________________ Configuración del distribuidor ______________________________________ Configuración de la publicación de una BDS __________________________ Creación de artículos _____________________________________________ Configuración de Suscripciones _____________________________________ Creación de suscripciones _________________________________________ Deshabilitar la publicación en un servidor _____________________________ Seguridad ______________________________________________________ Mantenimiento de la Duplicación____________________________________ Administrador Prevenido Vale por Dos _______________________________

213 213 217 220 222 222 227 228 229 230

INFORMATICA 64 SL

9

INTRODUCCIÓN


Qué es SQL Server SQL Server es un sistema gestor de bases de datos relacionales cliente/servidor que utiliza Transact-SQL para enviar peticiones entre el cliente y el servidor.

Arquitectura Cliente/Servidor SQL Server utiliza la arquitectura cliente/servidor para separar la carga de trabajo entre tareas que se realizan en máquinas servidores y tareas que se ejecutan en máquinas cliente. •

El cliente se encarga de la lógica de negocio y de presentar los datos al usuario. Normalmente, el cliente se ejecuta en uno o varios ordenadores cliente, pero también puede ejecutarse en el servidor.

•

Como servidor, SQL Server gestiona las bases de datos y asignan los recursos de los que dispone el servidor (memoria, ancho de banda de la red y operaciones de disco) entre consultas múltiples.

Sistema de Gestión de Bases de Datos Relacionales Como gestor, se encarga de: Mantener las relaciones entre los datos de la base de datos. Asegurarse de almacenar correctamente los datos (que no se violen las reglas que definen las relaciones entre datos). Recuperar todos los datos que sean coherentes si se produce un fallo del sistema.

Transact-SQL SQL Server utiliza Transact-SQL, un dialecto del SQL, como lenguaje de consultas y programación. SQL es un conjunto de comandos que permiten definir la información que se desea recuperar o modificar. Con Transact-SQL se puede acceder a los datos y lanzar consultas, actualizar y gestionar sistemas de bases de datos relacionales. Transact-SQL se basa en el ANSI SQL (publicado por el American National Standards Institute y el International Standards Organization), pero aporta varias extensiones.

Plataformas de SQL Server SQL Server opera sobre las siguientes plataformas: Windows 95/98 Windows NT Workstation y Server Windows 3.x y MS-DOS no soporta componentes de servidor, pero sí de cliente. Terceras aplicaciones como UNIX y Macintosh no soporta componentes de servidor, pero sí de cliente.

INFORMATICA 64 SL

10

INTRODUCCIÓN


Integración de SQL Server con Windows NT SQL Server está completamente integrado en Windows NT, y aprovecha muchas de sus características. Seguridad Con un único nombre y contraseña se puede acceder a SQL Server y Windows NT. Aparte, SQL Server tiene su propia seguridad para clientes externos a Windows. Soporte multiproceso SQL Server incorpora el multiproceso de Windows NT. Visor de Sucesos de Microsoft SQL Server envía mensajes a los registros de sucesos de aplicaciones, seguridad y del sistema de Windows NT para visualizar y trazar posibles problemas. Servicios de Windows NT SQL Server se ejecuta como un servicio de Windows NT, lo que permite detener y arrancar SQL Server de forma remota. Monitor de Rendimiento de Windows NT SQL Server envía datos de rendimiento al monitor de rendimiento de Windows NT, lo que permite visualizar el rendimiento del sistema de SQL Server.

Servicios de SQL Server SQL proporciona funcionalidad mediante una serie de servicios que se ejecutan independientemente del inicio de sesión en la máquina en la que se encuentra instalado en el servidor. En Windows NT los servicios de SQL Server están integrados con el sistema de control de servicios de Windows NT. Como servicios que son: -

es posible cerrar una sesión de Windows NT sin finalizar la ejecución de SQL Server

-

el servidor puede iniciarse como una aplicación

-

se puede configurar la ejecución automática del servidor cuando se inicia el sistema operativo

-

cualquier servicio de SQL Server puede iniciarse o detenerse como cualquier otro, utilizando el comando net start/stop o mediante el icono Servicios del Panel de Control.

-

SQL Server cuenta con un Administrador de Servicios de SQL Server que se integra en la barra de tareas y permite controlar los servicios más cómodamente

-

la ejecución de los servicios también puede iniciarse desde el Administrador Corporativo

Los servicios de SQL Server son el MSSQLServer, el SQLServerAgent y el Coordinador de Transacciones Distribuidas (MSDTC). Aunque los servicios de SQL Server se ejecutan como servicios en Windows NT, también pueden ejecutarse como aplicaciones.

INFORMATICA 64 SL

11

INTRODUCCIÓN


MSSQLServer Service Motor de la base de datos: elemento que gestiona el almacenamiento de las bases de datos del servidor y procesa las consultas y sentencias en lenguaje SQL que se le envían. Tiene que estar iniciado para añadir datos o recibir consultas. Si se interrumpe este servicio, los usuarios que estén conectados a SQL Server mantienen la conexión, pero no se permiten nuevos accesos. Finalidad: interrupción del servicio unos minutos antes de la desconexión del servidor para permitir que los usuarios conectados finalicen sus tareas. Otras tareas: -

mantenimiento de la seguridad y los permisos

-

optimización de las consultas

-

gestión de concurrencias y bloqueos

-

mantenimiento de la coherencia de datos

-

mantenimiento de las reglas de negocio (expresadas en los procedimientos y triggers)

SQLServer Agent SQLServer Agent es un servicio que funciona junto a SQL Server para crear y gestionar trabajos, alertas y operadores, ya sean locales o distribuidos (de distintos servidores). Consiste en un conjunto de objetos, servicios y componentes, utilizados para gestionar la capacidad del servidor para realizar tareas a intervalos prefijados, responder a eventos o generarlos. No es necesario para ejecutar SQL Server, pero se necesita para aplicar un calendario de tareas, implantar mecanismos de duplicación, generar alertas, etc. Funciona mediante los siguientes elementos de arquitectura: -

Trabajos: tareas programarse.

-

Alertas: medidas que se adoptan como respuesta a eventos, normalmente producidos por errores. Los eventos pueden ser definidos por el administrador o generados automáticamente por el sistema. Normalmente las medidas adoptadas serán comunicar la situación al administrador o ejecutar un trabajo.

-

Operadores: identifican a un usuario que recibirá una comunicación ante una alerta.

INFORMATICA 64 SL

expresadas

en

Transact-SQL

cuya

ejecución

puede

12