Transact+Sql+2005

Transact SQL 2005

Este documento describe las nuevas caracteristicas de T-SQL para SQL Server 2005.

Introduccion

Microsoft Sql Server 2005 (YUKON) es un producto con muchas novedades comparándolo con su antecesor SQL 2000. Sin duda que uno de sus mayores cambios ha sido la inclusión del CLR dentro del motor de base de datos.

En este artículo nos concentraremos en algunas de las tantas novedades que nos trae Microsoft SQL 2005 en lo que respecta a Transact-SQL, las cuales serán de mucha utilidad para los desarrolladores de aplicaciones como así también para los DBA.

Transact SQL (TSQL) es el lenguaje que usamos para escribir : Store Procedures – Triggers – Querys – Etc.Sin dudas que TSQL no dispone de las mismas habilidades y potencia que puede tener un lenguaje como C# o VB.NET. En Sql2005 veremos un cambio significativo en TSQL el cual nos ayudara en nuestro trabajo diario.

Antes de empezar les recomiendo que descarguen la versión Beta de Sql-Server June 2005 del siguiente link:http://www.microsoft.com/downloads/details.aspx?FamilyId=B414B00F-E2CC-4CAB-A147-EACA26740F19&displaylang=enRecomiendo que dicha instalación se realice en un ambiente de testing como podría ser una maquina Virtual.

Numerando Registros (RowId):

En muchas ocasiones es necesario poder obtener una columna con el número de registro o también poder generar un ranking. Hasta SQL2000 este tipo de operaciones no eran tan simples de realizar y no disponíamos de instrucciones directas. En TSQL 2005 disponemos de una serie de instrucciones las cuales nos hacen el trabajo mucho mas simple y eficiente. Veamos de qué se trata ello.

Row_Number: Esta nueva función de TSQL nos permitirá numerar los resultados de una query.

El siguiente ejemplo muestra el uso de esta función:

Use AdventureWorksGo

Select row_number() over(order by name) as rowid,name,productNumber,productId from Production.product where productnumber like 'be%'order by name

El resultado obtenido de esta query es el siguiente:

rowid name productNumber productId-------- -------------------------------------------------- ------------------------- ---------1 BB Ball Bearing BE-2349 32 Headset Ball Bearings BE-2908 4

http://www.microsoft.com/downloads/details.aspx?FamilyId=B414B00F-E2CC-4CAB-A147-EACA26740F19&displaylang=en

http://www.microsoft.com/downloads/details.aspx?FamilyId=B414B00F-E2CC-4CAB-A147-EACA26740F19&displaylang=en

A las funciones de numeración como Row_number() le podemos agregar la cláusula Partition la cual nos permitirá numerar pero haciendo un corte y reiniciando el numerador a partir de ese corte. En el siguiente ejemplo hacemos uso de ello:


Select row_number() over(partition by color order by name) as rowid, name,productNumber,productId,color from Production.product where color in ('Blue','Silver') order by color,name

Este ejemplo nos retornara un listado de productos donde el color sea Blue o Silver y los numerara con rowid pero al cambiar el color el numerador volverá a comenzar.

rowid name productNumber productId color------- --------------------------------------------- ------------------------- ----------- ---------------…25 Touring-3000 Blue, 58 BK-T18U-58 959 Blue26 Touring-3000 Blue, 62 BK-T18U-62 960 Blue1 Chain CH-0234 952 Silver2 Chainring Bolts CB-2903 320 Silver3 Chainring Nut CN-6137 321 Silver4 Freewheel FH-2981 332 Silver…

Conclusiones: La instrucción Row_number() nos da la habilidad de poder numerar los resultados de nuestras querys, este tipo de tareas eran muy solicitadas por los usuarios en la versión 2000 y no existía una solución directa como la que podemos tener ahora con Sql2005. De todas maneras vale aclarar un concepto: Los motores de base de datos están pensados para trabajar en conjunto de registros y no registró a registro, por lo cual les recomiendo que el uso de numeradores lo utilicen con criterio y que no se lo utilice para recorrer registró a registro (cursores).

TOP @n:

Tsql2005 incorpora una nueva funcionalidad a la instrucción TOP, hasta la versión 2000 no se le podía pasar un parámetro variable a la misma, en la versión 2005 esta funcionalidad se ha agregado. El siguiente ejemplo nos muestra como se implementa:

USE ADVENTUREWORKSGO

DECLARE @NUM INTSET @NUM = 3

SELECT TOP(@NUM) * FROM PRODUCTION.PRODUCT

En el siguiente ejercicio veremos como podemos utilizar TOP @n para eliminar registros de a grupos:


IF OBJECT_ID('GRANDE') IS NOT NULL DROP TABLE GRANDEGO

CREATE TABLE GRANDE (ID INT IDENTITY,NOMBRE VARCHAR(100), DIRECCION VARCHAR(100))GO

DECLARE @N INTSET @N = 1

WHILE @N <= 50000 BEGIN INSERT INTO GRANDE (NOMBRE,DIRECCION) VALUES ('SQL' + CONVERT(VARCHAR(10),@N),'DIR' + CONVERT(VARCHAR(10),@N)) SET @N = @N+1 ENDGO

/* BORRAMOS POR PARTES (DE A 500) USANDO ROWCOUNT Y TOP */

SET ROWCOUNT 500

DELETE TOP(500) FROM GRANDE WHILE @@ROWCOUNT > 0 DELETE TOP(500) FROM GRANDE SET ROWCOUNT 0

Conclusiones: Sin lugar a dudas que muchos de los desarrolladores y DBA que venimos trabajando con Sql2000 estábamos reclamando esta funcionalidad del TOP, con la misma como se podrá observar se pueden lograr muchas funcionalidades que antes se debían resolver quizás con SQL-Dinámico.

Control de errores:

Tsql 2005 incorpora al control de errores los bloques TRY..CATH. La administración de errores de esta manera es conocida por los desarrolladores de la actualidad ya que .NET administra de la misma manera. En esta sección les mostrare varios ejemplos de su utilización. Para poder realizar estos ejercicios he creado una tabla de prueba


IF OBJECT_ID('EMPLEADOS') IS NOT NULL DROP TABLE EMPLEADOSGO

CREATE TABLE EMPLEADOS (ID INT PRIMARY KEY, NOMBRE VARCHAR(30) NOT NULL)GO

En nuestro primer ejemplo veremos como poder capturar un error al intentar realizar una operación de insert sobre nuestra tabla donde el campo Nombre no admite valores nulos.

BEGIN TRY

INSERT INTO EMPLEADOS VALUES (1,NULL)

PRINT 'PASO EL INSERT'END TRY

BEGIN CATCH PRINT 'TENEMOS EL ERROR NUMERO:' + CONVERT(VARCHAR(10),ERROR_NUMBER())END CATCH

Como podemos observar al suceder un error SQL sale a la sección CATCH donde capturamos el mismo y podemos informar al usuario con algún mensaje. En este ejemplo se hace uso de la función ERROR_NUMBER() las cual nos retorna el número de error, pero también podríamos hacer uso de las siguientes funciones:

ERROR_LINE() : Retorna el número de línea donde se genero el error. El resultado es NULL cuando el error se genero fuera del bloque Try.

ERROR_MESSAGE(): Retorna el texto del error.

ERROR_PROCEDURE(): Retorna el nombre del Procedimiento Almacenado o el Trigger desde donde se ha generado el error en el bloque Try Catch

ERROR_SEVERITY(): Retorna la severidad del error

El siguiente ejemplo genera un error en el primer insert y automáticamente se pasa a la sección Catch sin ejecutar la segunda instrucción, además en el modulo Catch evaluamos el número de error y personificamos el mensaje emitido al usuario.

BEGIN TRY

INSERT INTO EMPLEADOS VALUES ('1',NULL) INSERT INTO EMPLEADOS VALUES ('1','MAXI')

PRINT 'PASO EL INSERT'END TRY

BEGIN CATCH IF ERROR_NUMBER() = 515 BEGIN PRINT ' HAY ALGUNAS COLUMNAS QUE NO ADMITEN NULOS: ' + ERROR_MESSAGE() END ELSE IF ERROR_NUMBER() = 8152 BEGIN PRINT ' SE HA SOBREPASADO EL MAXIMO DE UN CAMPO: ' + ERROR_MESSAGE() END ELSE BEGIN PRINT 'TENEMOS EL ERROR NUMERO:' + CONVERT(VARCHAR(10),ERROR_NUMBER()) + ' ' + ERROR_MESSAGE() ENDEND CATCH

Conclusiones: El manejo de los errores en sql2005 ha mejorado considerablemente comparado con su antecesor, ahora como se podrá observar no es necesario ir controlando la variable @@error por cada instrucción que realizamos, con el uso de los bloques Try.. Cacth es mucho más simple y además mantiene la metodología de .NET.

En este articulo no hemos tratado como manejar errores cuando existen transacciones de por medio, pero el manejo de las mismas en sql2005 es mucho mas simple y amigable que en la versión 2000. Si se desea ampliar sobre este tema les recomiendo que lean sobre la función XACT_STATE en sus libros online (BOL).

MAX para columnas dinámicas:

En sql2005 disponemos de la característica MAX para los tipos de datos VARCHAR, NVARCHAR y VARBINARY. La idea de esta característica es poder ampliar la capacidad de estos tipos de datos. En sql2000 estos tipos de datos disponían un máximo de 8000 y 4000 (para los Nvarchar), con MAX se puede almacenar hasta 2GB de información en estos tipos de datos. La idea seria reemplazar donde se pueda este tipo de datos por los viejos IMAGE , TEXT y NTEXT.

El siguiente ejemplo nos muestra la utilización de esta característica:


Create table emp (id int identity, nombre varchar(50), descripcion varchar(max))go

Insert into emp (nombre,descripcion)values ('A1','esto es una prueba de Max')Go

Update emp set descripcion='nuestro primer update'Go

Conclusiones: Esta nueva capacidad en los tipos de datos Varchar, Nvarchar y Narbinary es mucho más simple que los viejos Image, Text y Ntext. De todas maneras habrá que analizar bien cuando es conveniente migrar a este tipo de datos, si la aplicación es nueva yo recomendaría utilizarlos en lugar de sus pares de la versión 2000.

SET Default y SET NULL (DRI Actions):SQL Server 2005 Beta2 incluye soporte para dos (2) nuevas DRI actions (Declarative Referential Integrity). Estas nuevas DRI Actions se utilizaran en las relaciones Foreign Key tanto para UPDATE como para DELETE.

El siguiente ejemplo nos muestra como usar SET DEFAULT y SET NULL:

/*DRI (Declarative Referencial Constraint) SET DEFAULT and SET NULL */


if object_id('pedidos') is not null drop table pedidosGo

If object_id('clientes') is not null drop table clientesGo

Create table Clientes (id varchar(10) Primary Key, nombre varchar(100))Go

INSERT INTO CLIENTES VALUES ('MICROSOFT','MICROSOFT')INSERT INTO CLIENTES VALUES ('IBM','IBM')

INSERT INTO CLIENTES VALUES ('SAP','SAP')INSERT INTO CLIENTES VALUES ('HP','HP')Go

Create Table Pedidos (numero int Primary Key,cliente_id varchar(10) default('MICROSOFT'), fecha datetime, constraint fk_1 Foreign Key (Cliente_id) References Clientes(id) ON DELETE SET NULL ON UPDATE SET DEFAULT)

Go

Hasta aquí hemos creados dos(2) tablas, una llamada Clientes y la otra Pedidos, en esta ultima hemos agregado una relación con la primera y se ha definido que ante una acción de DELETE (en clientes) se introduzca Null en el campo Cliente_id de Pedidos y ante una acción de UPDATE se introduzca el valor Default de dicho campo.

INSERT INTO PEDIDOS VALUES (1,'IBM',GETDATE())INSERT INTO PEDIDOS VALUES (2,'HP',GETDATE()+10)INSERT INTO PEDIDOS VALUES (3,'SAP',GETDATE()-65)INSERT INTO PEDIDOS VALUES (4,'IBM',GETDATE()+365)INSERT INTO PEDIDOS VALUES (5,'MICROSOFT',GETDATE()+95)INSERT INTO PEDIDOS VALUES (6,'SAP',GETDATE()+110)GO

Ahora que tenemos los registros en nuestras dos (2) tablas, haremos una acción Delete en Clientes y observaremos que ha sucedido con esos registros en la tabla Pedidos

DELETE FROM CLIENTES WHERE ID ='IBM'GO

SELECT numero,cliente_id FROM PEDIDOSGo

numero cliente_id----------- ----------1 NULL2 . . ...Como podemos observar el registro (1) que contenía el cliente IBM , ahora el campo de referencia tiene un valor Null ya que hemos eliminado su maestro de la tabla Clientes y nuestro DRI actions indicaba que se debía introducir Null.

Ahora veremos que sucede al realizar un Update sobre nuestra tabla Clientes

UPDATE CLIENTES SET ID='CITRIX' WHERE ID ='HP'GoSELECT * FROM PEDIDOS

numero cliente_id----------- ----------1 NULL2 MICROSOFT. . . . . .Como podemos observar el realizar un Update sobre el campo llave de la tabla Clientes, en nuestra tabla hija (Pedidos) se ha cambiado al valor Default del campo (‘Microsoft’)

Conclusiones: Sin ninguna duda que el agregado de estas dos (2) nuevas funciones de DRI nos ayudaran considerablemente al momento de realizar

nuestras relaciones padres – hijos, que hasta la versión 2000 realizar este mismo tipo de operatoria como hemos visto en los ejemplos era una tarea muy incomoda.

DDL Triggers:

En la versión 2000 de MS SQL-Server solo disponíamos de triggers (Desencadenadores) DML para las instrucciones Insert – Update y Delete. SQL 2005 incorpora un nuevo tipo de triggers llamados DDL Triggers, los cuales pueden tener efecto sobre las instrucciones DDL como por ej: CREATE TABLE.En esta sección veremos dos (2) ejemplos sobre estos nuevos tipos de triggers, uno de ellos hará una auditoria de instrucciones DDL y el otro no permitirá la ejecución de una instrucción ALTER TABLE


CREATE TRIGGER DDL_1 ON DATABASE FOR ALTER_TABLE ASRAISERROR('NO SE PUEDE ALTERAR TABLAS EN ESTA BASE DE DATOS.',16,1)ROLLBACKGO

En este ejemplo hemos creado un trigger DDL en la base de datos AdventureWorks el cual impedirá que se realicen operaciones ALTER TABLE sobre dicha base de datos.Si deseamos realizar la siguiente operación recibiremos un error y no se realizara la misma.


ALTER TABLE PRODUCTION.PRODUCT ADD COLUMNA VARCHAR(10)GO

Ahora veremos un ejemplo donde usaremos un DDL trigger para realizar auditorias DDL


CREATE TABLE DDL_AUDIT (LSN INT NOT NULL IDENTITY,POSTTIME DATETIME NOT NULL,EVENTTYPE SYSNAME NOT NULL, LOGINNAME SYSNAME NOT NULL,OBJECTNAME SYSNAME NOT NULL,TARGETOBJECTNAME SYSNAME NOT NULL)GO

El primer paso ha sido crear una tabla donde registraremos las auditorias DDL.

CREATE TRIGGER DDL_2 ON DATABASE FOR DDL_DATABASE_LEVEL_EVENTS ASDECLARE @V AS XMLSET @V = EVENTDATA()INSERT INTO DDL_AUDIT (POSTTIME,EVENTTYPE,LOGINNAME,OBJECTNAME,TARGETOBJECTNAME) VALUES (CAST(@V.query('data(//PostTime)') as varchar(23)),CAST(@V.query('data(//EventType)') as sysname),CAST(@V.query('data(//LoginName)') as sysname),CAST(@V.query('data(//ObjectName)') as sysname),CAST(@V.query('data(//TargetObjectName)') as sysname))GO

Este segundo paso es el que genera el trigger DDL el cual registrara en nuestra tabla cada acontecimiento que suceda.

Ahora solo nos resta por probar el funcionamiento del trigger

CREATE TABLE PP (ID INT)GO

SELECT * FROM DDL_AUDIT

Conclusiones: Los DDL triggers son una nueva herramienta muy poderosa de verdad, la cual nos permitirá desde ahora poder tener control sobre las sentencias DDL como así también poder realizar auditorias a las mismas.

Conclusiones Generales:

En este artículo hemos visto algunas de las novedades de Sql2005 en lo que respecta a T-SQL. Aun quedan muchas otras funcionalidades por mostrar pero debo indicar que esta nueva versión del popular motor de base de datos es muy distinta a su antecesora incorporando muchas mejoras tanto para el desarrollador como así también para los DBA.

Autor: Maximiliano Acotto MVP SQL Server [email protected]

Technology

Transact+Sql+2005