 Por Maximiliano Accotto, MVP de SQL 2005. Microsoft SQL Server 2005 es un producto con muchas novedades, si se lo compara con su antecesor, el 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 novedades que trae la Beta2 en lo que respecta a Transact-SQL, que 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 SQL 2005 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=en Recomiendo 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 AdventureWorks
Go
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 3
2 Headset Ball Bearings BE-2908 4
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: Use AdventureWorks
Go
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 product Number productId color
------- -------------- -------------- ----------- ----------
25 Touring-3000 Blue, 58 BK-T18U-58 959 Blue
26 Touring-3000 Blue, 62 BK-T18U-62 960 Blue
1 Chain CH-0234 952 Silver
2 Chainring Bolts CB-2903 320 Silver
3 Chainring Nut CN-6137 321 Silver
4 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 ADVENTUREWORKS
GO
DECLARE @NUM INT
SET @NUM = 3
SELECT TOP(@NUM) * FROM PRODUCTION.PRODUCT
En el siguiente ejercicio veremos como podemos utilizar TOP @n para eliminar registros de a grupos: Use AdventureWorks
Go
IF OBJECT_ID('GRANDE') IS NOT NULL
DROP TABLE GRANDE
GO
CREATE TABLE GRANDE (ID INT IDENTITY,NOMBRE VARCHAR(100), DIRECCION
VARCHAR(100))
GO
DECLARE @N INT
SET @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
END
GO
/* 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 USE ADVENTUREWORKS
GO
IF OBJECT_ID('EMPLEADOS') IS NOT NULL
DROP TABLE EMPLEADOS
GO
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()
END
END 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: Use AdventureWorks
Go
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 */
Use AdventureWorks
Go
if object_id('pedidos') is not null
drop table pedidos
Go
If object_id('clientes') is not null
drop table clientes
Go
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 PEDIDOS
Go
numero cliente_id
----------- ----------
1 NULL
2 . . .
..
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'
Go
SELECT * FROM PEDIDOS
numero cliente_id
----------- ----------
1 NULL
2 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 USE ADVENTUREWORKS
GO
CREATE TRIGGER DDL_1 ON DATABASE FOR ALTER_TABLE AS
RAISERROR('NO SE PUEDE ALTERAR TABLAS EN ESTA BASE DE DATOS.',16,1)
ROLLBACK
GO
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. USE ADVENTUREWORKS
GO
ALTER TABLE PRODUCTION.PRODUCT ADD COLUMNA VARCHAR(10)
GO
Ahora veremos un ejemplo donde usaremos un DDL trigger para realizar auditorias DDL USE ADVENTUREWORKS
GO
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 AS
DECLARE @V AS XML
SET @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 SQL 2005 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.
| 