Diseo y programacin de bases dedatos con SQL Server 2008

Pedro Lpez-Belmonte / Aroa Solana

Sipnosis

En este manual se tratan las diferentes tareas necesa-rias para llevar a cabo el diseo de una base de da-tos, y su implementacin en SQL Server 2008.

Desde las tareas de diseo de los diferentes mode-los, hasta la implementacin fsica de los diseos,pasando por la creacin de objetos de base de datos,procedimientos almacenados, funciones definidaspor el usuario, desencadenadores o el tipo de datoXML, se abordan todos los temas necesarios paradejar en perfecto funcionamiento cualquier base dedatos sobre SQL Server 2008.

Tambin se exploran las caractersticas que aporta laltima versin del sistema gestor de base de datos deMicrosoft, como la integracin con .NET 3.5 (obje-tos SQL CLR), o las nuevas aplicaciones del servi-dor, como los Reporting Services, Notification Ser-vices o los Integration Services, entre otras muchasnovedades como los nuevos tipos de datos espacia-les muy tiles para almacenar datos cartogrficos.

Pedro Lpez-Belmonte es Licen-ciado en Ciencias Fsicas por laUniversidad Complutense de Ma-drid, con ms de 8 aos de expe-riencia en el mundo del desarrollo,consultora y formacin. Es espe-cialista en herramientas de desarro-llo .NET y bases de datos. Haparticipado en numerosos pro-yectos de desarrollo en entornos

corporativos de mediano y gran tamao con .NET, SQLServer y SharePoint.

Cuenta con varias certificaciones, entre las que se encuen-tran MCAD, MCTS SQL Sever & SharePoint. Tambincuenta con la certificacin MCT, y desde el comienzo desu carrera profesional ha impartido formacin, una de suspasiones.

Aroa Solana Ruiz es MicrosoftCertified IT Professional en Data-base Developer, tambin MicrosoftCertified Technology Specialist enSQL Server 2005, .NET Frame-work 3.5, Windows Forms Appli-cations, SQL Server 2005, al igualque Database Development en SQLServer 2008.

Actualmente ostenta la certificacin MCT (MicrosoftCertified Trainer) que la permite impartir un gran nmerode cursos oficiales de Microsoft y trabajar como formado-ra en Alhambra-Eidos.

Ha impartido formacin sobre la tecnologa .NET, desa-rrollo de aplicaciones Windows, Web, ADO, Distribui-das, WPF, WCF, WWF, Silverlight y UML, por otraparte imparte formacin en administracin de bases dedatos SQL Server y desarrollo de bases de datos SQLServer, tanto en 2005 como en 2008. Adems posee expe-riencia en J2SE y J2EE. Dentro de la gestin de procesosposee la certificacin ITIL V2 Foundations.

Luarna

Diseo y programacin de bases de datos con SQL Ser-ver 2008 Pedro Lpez-Belmonte Alhambra Eidos Aroa Solana - Alhambra Eidos De esta edicin: 2009, Luarna Ediciones, S.L.

www.luarna.com

Madrid, octubre de 2009

ISBN: 978-84-92684-59-5

Versin 1.0 (21-10-2009)

Cualquier forma de reproduccin, distribucin, comunicacin pblica o transformacin de estaobra solo puede ser realizada con la autorizacin de sus titulares, salvo excepcin prevista por laley. Dirjase a CEDRO (Centro Espaol de Derechos Reprogrficos, www.cedro.org) si necesitafotocopiar, escanear o hacer copias digitales de algn fragmento de esta obra.

Dedicatoria de Aroa Solana

Este libro, se lo dedico a las amigas que hiceen la Facultad, sin ellas hubiera acabado loca,

adems de que siempre han estado ah tantopara lo bueno como para lo malo,

por lo que quiero decirles:Gracias Elena, Laura, Ana y Vita!.

Diseo y programacin de bases de datoscon SQL Server 2008

Pedro Lpez-BelmonteAroa Solana

Indice

1. INTRODUCCIN .................................................. 27

INTRODUCCIN ........................................................... 27INTRODUCCIN A SQL SERVER 2008 ............................... 31NUEVAS CARACTERSTICAS ............................................. 35

Funciones Bsicas ............................................... 36Funciones de Business Intelligence ...................... 39

HERRAMIENTAS DE SQL SERVER 2008 ............................. 40Libros en pantalla ............................................... 40SQL Server Configuration Manager...................... 41El SQL Server Management Studio ....................... 41

2. CREACIN DE LOS MODELOS CONCEPTUAL,LGICO Y FSICO ......................................................... 43

MODELO CONCEPTUAL ................................................. 43ETAPAS DEL DISEO CONCEPTUAL .................................... 45EL MODELO ENTIDAD / RELACIN ..................................... 47EJEMPLOS PRCTICOS DE DISEO CONCEPTUAL .................... 62MODELO LGICO ........................................................ 77

PASO DEL ESQUEMA CONCEPTUAL AL ESQUEMA LGICOESTNDAR .................................................................. 78ETAPAS EN EL DISEO LGICO ......................................... 84PARTICIONAMIENTO HORIZONTAL DE RELACIONES ................ 86PARTICIONAMIENTO VERTICAL DE RELACIONES ..................... 98PARTICIONAMIENTO MIXTO .......................................... 107TEORA DE LA NORMALIZACIN ...................................... 110EJEMPLOS PRCTICOS DE NORMALIZACIN ....................... 124PROCESO DE DESNORMALIZACIN .................................. 142MODELO FSICO ........................................................ 145ESTRATEGIAS EN EL DISEO FSICO .................................. 147CONCEPTOS BSICOS SOBRE GESTIN DE FICHEROS ............. 149ORGANIZACIN DE FICHEROS ........................................ 152TCNICAS PARA EL AUMENTO DE EFICIENCIA ...................... 154

3. SEGURIDAD ...................................................... 162

INICIOS DE SESIN ...................................................... 163ROLES ..................................................................... 187

Funciones fijas de servidor ................................. 188Funciones de base de datos ............................... 192Funciones de aplicacin ..................................... 195

ESQUEMAS ............................................................... 205

4. CREACIN DE UNA BASE DE DATOS ................. 213

QU ES UNA BASE DE DATOS? .................................... 214Archivos de una Base de Datos .......................... 214Pginas ............................................................. 217Extensiones ....................................................... 222Objetos de la Base de Datos .............................. 224

BASES DE DATOS DE SISTEMA ....................................... 228Master .............................................................. 228TempDB ............................................................ 230Model ............................................................... 232MSDB ............................................................... 233

ELEMENTOS BSICOS PARA LA CREACIN DE BASES DE DATOS 233Management Studio ......................................... 234CREATE DATABASE ............................................ 240Grupos de archivos............................................ 266

Creacin de un grupo de archivos ......................... 268Uso de los grupos de archivos ............................... 271

Esquemas ......................................................... 278

5. CREACIN DE TABLAS ...................................... 284

TIPOS DE DATOS EN SQL SERVER 2008 ........................... 285CHAR ................................................................ 287

NCHAR .............................................................. 287VARCHAR .......................................................... 288NVARCHAR........................................................ 289TEXT ................................................................. 290NTEXT ............................................................... 290IMAGE .............................................................. 291INT .................................................................... 292BIGINT .............................................................. 292SMALLINT ......................................................... 292TINYINT ............................................................. 293DECIMAL/NUMERIC .......................................... 293FLOAT ............................................................... 294REAL ................................................................. 295MONEY ............................................................. 295SMALLMONEY ................................................... 296DATETIME ......................................................... 296SMALLDATETIME............................................... 297TIMESTAMP ...................................................... 297UNIQUEIDENTIFIER ........................................... 298BINARY ............................................................. 298VARBINARY ....................................................... 299BIT .................................................................... 300

XML .................................................................. 300CURSOR ............................................................ 300TABLE ............................................................... 301SQL_VARIANT ................................................... 301

CREACIN DE TABLAS .................................................. 302MODIFICAR TABLAS .................................................... 306ELIMINAR TABLAS ...................................................... 308PROPIEDADES DE LAS COLUMNAS ................................... 310

Valores DEFAULT .............................................. 311Valores IDENTITY .............................................. 314

INTEGRIDAD DE LOS DATOS ........................................... 317Integridad de Entidad........................................ 318Integridad de Dominio ...................................... 320Integridad Referencial ....................................... 321Tipos de restricciones ........................................ 323

PRIMARY KEY ....................................................... 323FOREIGN KEY ........................................................ 329Restriccin UNIQUE .............................................. 341Restricciones CHECK ............................................. 346Objetos RULE y DEFAULT ...................................... 353

RULE ................................................................ 354DEFAULT .......................................................... 357

Consideraciones sobre las restricciones ............. 358

6. CREACIN DE NDICES ...................................... 363

QU ES UN NDICE? .................................................. 363TIPOS DE NDICES ....................................................... 368

ndices Agrupados (Clustered) ........................... 368ndices No Agrupados (Nonclustered) ................ 373

ndices no agrupados sobre una tabla ................... 375ndices no agrupados sobre un ndice agrupado .... 376ndices nicos ....................................................... 377

CREACIN DE NDICES ................................................. 379Especificar orden para los campos ..................... 385

Include ................................................................. 386Factor de relleno .................................................. 389Rellenar ndice ...................................................... 392Pasar por alto claves duplicadas ............................ 394Volver a generar el ndice...................................... 395Volver a calcular estadsticas ................................. 397Almacenar resultados de orden intermedio en labase de datos tempdb .......................................... 399Permitir procesamiento en lnea deinstrucciones DML ................................................ 402Usar bloqueos de fila y de pgina altener acceso al ndice ........................................... 403Grado mximo de paralelismo .............................. 404

Almacenamiento del ndice .................................. 407PARTICIN DE NDICES Y TABLAS .................................... 408

Funciones de particin ...................................... 410Esquemas de particin ...................................... 413Crear tablas e ndices particionados .................. 415Modificar funciones y esquemas de particin .... 419

Insertar y extraer tablas de particiones ................. 421Mantenimiento de Particiones .............................. 425

7. CREACIN DE VISTAS ....................................... 426

COMO CREAR VISTAS .................................................. 427Encriptacin de la definicin .............................. 439Enlazar a un esquema ....................................... 440Comprobar criterios .......................................... 441

MODIFICAR Y ELIMINAR VISTAS ...................................... 443VISTAS INDEXADAS ..................................................... 445

8. PROCEDIMIENTOS ALMACENADOS .................. 450

CREACIN DE PROCEDIMIENTOS ALMACENADOS ............... 453Llamadas a procedimientos ............................... 456Procedimientos almacenados parametrizados ... 457Valor de Retorno ............................................... 463

Opciones Adicionales ......................................... 465ESTRUCTURAS DE CONTROL DE FLUJO Y ERRORES ................ 469

IFELSE ............................................................. 470WHILEBREAK .................................................. 472CASE ................................................................. 474WAITFOR .......................................................... 476

GESTIN DE ERRORES ................................................. 478La variable @@ERROR ...................................... 480RAISERROR........................................................ 489

Mensaje ............................................................... 490Severidad ............................................................. 494Estado .................................................................. 494WITH .................................................................... 495

CONSIDERACIONES DE SEGURIDAD Y RENDIMIENTO ............. 500Seguridad.......................................................... 503Rendimiento...................................................... 504Depuracin ....................................................... 506

9. FUNCIONES DEFINIDAS POR EL USUARIO ......... 515

FUNCIONES ESCALARES ................................................ 518FUNCIONES DE TABLA .................................................. 526FUNCIONES DETERMINISTAS Y NO DETERMINISTAS ............. 536