Diario de SQL Server

Mover datos de tablas con identities

Escrito por qwalgrande 15-10-2011 en General. Comentarios (0)

 

Supongamos un escenario en el que se tienen varias tablas relacionadas, donde la clave primaria de esas tablas es un identity, que aparece luego como foreign key en la tabla relacionada, que también a su vez está en otra tercera tabla, incluso que existen tablas que cuentan con dos o tres de esos campos, originalmente identity todos ellos. En estos escenarios, igualar los datos de un entorno a otro puede resultar un poco complicado si ambos entornos han evolucionado de forma paralela.

El ejemplo, sería el de una aplicación que lleva en producción un tiempo, con sus altas, eliminaciones de registros, y pasado ese tiempo, es necesario volcar registros adicionales, de varias tablas, todas relacionadas. El problema está en que los identificadores que hay que pasar ya existen en el entorno de destino. Y al usarse esos identity como foreign key en otras tablas, la alternativa a la que puede estar uno abocado es a realizar ese traspaso de datos de forma manual, permitiendo que se vayan asignando los identificadores en las tablas, para luego usarlos en las relacionadas. Si hay que pasar miles de registros, hacerlo a mano es inviable.

Existen tres alternativas adicionales (al menos). Una pasa por preparar un conjunto de procedimientos almacenados que nos ayuden en la tarea. Es la opción más ordenada, pero también puede suponer un esfuerzo importante si no se cuenta ya previamente con dichos procedimientos (usados en la propia aplicación para el mantenimiento de esas tablas). La segunda sería construir tablas de traducción o equivalencias entre identificadores. Esta vía, si hay una o dos tablas es fácil, pero si hay más, puede ser bastante tortuoso.

La otra forma, que es la que quiero exponer, consiste en generar un salto fijo en los identity de todas las tablas implicadas, por ejemplo, sumándole 10000 a los valores de todos los identity de los que partimos inicialmente.

El objetivo de esto es poder mantener las relaciones entre los valores del entorno de origen y llevarlos al de destino, de forma ordenada y contando con que puedo seguir relacionándolos, mediante una sencilla suma (o resta) de 10.000.

Cómo haríamos esto.

TablaMaestro: IdMaestro, Descripcion, FechaAlta

TablaDetalle: IdDetalle, IdMaestro, Descripcion

TablaDesglose: IdDesglose, IdDetalle, Descripcion

 

set identity_insert TablaMaestro on

insert TablaMaestro (IdMaestro, Descripcion, FechaAlta)

select IdMaestro = IdMaestro + 10000, Descripcion, FechaAlta

from [DESARROLLO].MiBD..TablaMaestro

where FechaAlta > '20110901' --> Estos son los datos a pasar, los dados de alta desde que se separaron los entornos, y todos los relacionados

set identity_insert TablaMaestro off

 

set identity_insert TablaDetalle on

insert TablaDetalle (IdDetalle, IdMaestro, Descripcion)

select IdDetalle = IdDetalle + 10000, IdMaestro = IdMaestro + 10000, Descripcion

from [DESARROLLO].MiBD..TablaDetalle

where IdMaestro in (Select IdMaestro from [DESARROLLO].MiBD..TablaMaestro where FechaAlta > '20110901')

set identity_insert TablaDetalle off

 

set identity_insert TablaDesglose on

insert TablaDesglose (IdDesglose, IdDetalle, Descripcion)

select IdDesglose = IdDesglose + 10000, IdDetalle = IdDetalle + 10000, Descripcion

from [DESARROLLO].MiBD..TablaDesglose

where IdDetalle in (select IdDetalle from [DESARROLLO].MiBD..TablaDetalle where IdMaestro in (Select IdMaestro from [DESARROLLO].MiBD..TablaMaestro where FechaAlta > '20110901'))

set identity_insert TablaDesglose off

 

 

Y así con todas las tablas que se debieran subir. Luego, si procede, hay que recolocar el valor por el que crece el identity, con DBCC CHECKIDENT.

 

 

Cuánto ocupa un nulo (tipos int y varchar)

Escrito por qwalgrande 02-09-2011 en General. Comentarios (0)

Aquí la prueba en cuestión (hecha en SQL Server 2005 y en Denali CTP3, con idéntico resultado):

 

create table nulos (ident int not null identity(1,1), id int null);

create table ceros (ident int not null identity(1,1), id int null);

create table miles (ident int not null identity(1,1), id int null);

create table nonulos (ident int not null identity(1,1), id int not null);

create table cadenas (ident int not null identity (1,1), id varchar(10) null);

create table cadnulas (ident int not null identity (1,1), id varchar(10) null);

go

set nocount on

go

insert nulos (id) values (null);

insert ceros (id) values (0);

insert miles (id) values (1000000000);

insert nonulos (id) values (1000000000);

insert cadenas (id) values ('0');

insert cadnulas (id) values (null);

go 100000

exec sp_spaceused nulos   --> 1768 KB

exec sp_spaceused ceros   --> 1768 KB

exec sp_spaceused miles   --> 1768 KB

exec sp_spaceused nonulos --> 1768 KB

exec sp_spaceused cadenas --> 1872 KB

exec sp_spaceused cadnulas--> 1352 KB

go

 

Y el resultado:

  • Un int ocupa lo mismo si es nulo o no, si está relleno o no. Como todos los campos de tamaño fijo.
  • Un varchar ocupa menos si es nulo.

ODBC Sí, OLE DB No

Escrito por qwalgrande 31-08-2011 en General. Comentarios (0)

 

Me entero ayer por Twitter (que cada día me gusta un poco más) que Microsoft ha decidido alinearse con ODBC, y abandonar la línea de OLE DB, que será descontinuado, siendo SQL Server Denali la última versión que tendrá soporte OLE DB:

 

http://blogs.msdn.com/b/sqlnativeclient/archive/2011/08/29/microsoft-is-aligning-with-odbc-for-native-relational-data-access.aspx
http://social.technet.microsoft.com/Forums/en/sqldataaccess/thread/e696d0ac-f8e2-4b19-8a08-7a357d3d780f

 

No es que sea un trauma, se me ocurren 1000 razones que causen en mí mayor indignación en estos días, pero sigo sin salir de mi asombro. Desde luego, las explicaciones que se aportan en ese artículo son sumamente vagas, con lo que se acaba de abrir la veda de un más que probable aluvión de críticas a Microsoft por este motivo.

 

¿Que por qué van a lloverle las crítica? Fácil: Durante muchos años, Microsoft nos ha insistido a todos que OLE DB era mucho mejor, más rápido, más flexible y que era el futuro del acceso a datos, más mejor en todo que ODBC. De hecho, Integration Services (de todos es conocido que en cuanto a velocidad se refiere, está primero la luz, segundo Fernando Alonso y tercero SSIS) es OLE DB al 100%. Y ahora nos encontramos con este volantazo, un "Donde dije digo, digo Diego" cum laude, que a los que tanto tiempo hemos proclamado a los cuatro vientos la consigna previa nos deja en fuera de juego. Ummm, ¿acaso no era así? ¿No era mejor OLE DB?

 

A la espera que se aporte una explicación más "oficial" (y publicable) al respecto que tenga algo de solidez, lo que a uno primero se le viene a la mente es que es una cuestión de pasta, como siempre (y así debe ser), que veía lejana la extinción de ODBC y ante la duda, cierra el grifo: resulta que habíamos "comprado" un video Beta…

 

Ojo, que a Denali le ha tenido que pillar tan de sorpresa como a los demás, porque, que sepamos, sigue siendo pro-OLE DB, así que lo normal es que esto sea una autopista y tengamos que seguir en la dirección contraria otros 20 kilómetros antes del siguiente cambio de sentido. En fin, una risa. Pero bueno, si nos dan margen, nos adaptaremos. Como siempre.

 

En resumidas cuentas, tenemos 7 u 8 años por delante para retornar a ODBC, siempre y cuando por el camino no vuelva a producirse otro nuevo cambio de rumbo. Espero que Microsoft tome las medidas oportunas para no dejarnos en medio del barro. Y lo que sí debemos hacer entre todos es darle publicidad al tema, que sea de dominio público lo antes posible, de cara a tenerlo presente de cara a los nuevos proyectos que se inicien a corto plazo.

 

LAG y LEAD

Escrito por qwalgrande 20-08-2011 en General. Comentarios (0)

 

Entre las nuevas funcionalidades de la siguiente versión de SQL Server, Denali, hay una que está llamándome mucho la atención, como son las bases de datos autocontenidas. Es una de esas cosas que saldrán en todas las presentaciones de tipo "What's new?", que las habrá y muchas.

Pero luego hay un par de funciones nuevas, en lo tocante a T-SQL, que vienen a resolver una problemática de lo más común: comparar un registro con el siguiente o el anterior. Ejemplo: obtener en la misma línea las ventas de Agosto y su comparativa con las del mes de Julio. Supongamos el siguiente caso:

 

use tempdb

go

 

CREATE TABLE MonthlyProfit

(

yearid int,

Monthid int,

Profit bigint,

constraint PK_MonthlyProfit primary key clustered (yearid, monthid)

)

 

go

 

INSERT INTO MonthlyProfit (yearid, Monthid, Profit)

VALUES

(2010,1,1766), (2010,2,100), (2010,3,1500), (2010,4,15000), (2010,5,900), (2010,6,45),

(2010,7,1766), (2010,8,9100), (2010,9,-100), (2010,10,50), (2010,11,900000), (2010,12,6575)

 

go

 

Hasta ahora, lo que tocaba era cruzar la tabla consigo misma, pero para el periodo anterior, lo que comúnmente se llama running total:

 

SELECT

t1.yearid,

t1.Monthid,

t1.Profit as ProfitThisMonth,

isnull(t2.Profit, 0) as ProfitLastMonth

FROM MonthlyProfit as t1

LEFT JOIN MonthlyProfit as t2 on (t1.yearid = t2.yearid) AND (t1.Monthid = t2.Monthid+1)

 

Pero ahora tenemos acceso a las funciones LAG (la fila que viene antes) y LEAD (la fila que viene después), esto es, el registro anterior y el registro siguiente, obviamente agrupando y ordenando por lo que se precise:

 

SELECT

yearid,

monthid,

Profit as ProfitThisMonth,

LAG(Profit, 1, 0) OVER(Order by yearid, monthid) as ProfitLastMonth

FROM MonthlyProfit

 

Es decir, dame el campo Profit, pero 1 registro antes, y si no existe, le ponemos un 0. Se puede ver detalle de la sintaxis en:

    LAG: http://msdn.microsoft.com/en-us/library/hh231256%28v=sql.110%29.aspx

    LEAD: http://msdn.microsoft.com/en-us/library/hh213125(v=sql.110).aspx

Y encima, rinde estupendamente, mejor que cruzando la tabla consigo misma, con lo que no habrá que restringir su uso a las demos. Menos lecturas que la consulta del running total:

 

(12 row(s) affected)

Table 'MonthlyProfit'. Scan count 2, logical reads 27, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(12 row(s) affected)

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'MonthlyProfit'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 

Ahora bien, el observador atento habrá notado que estamos hablando del registro anterior o siguiente dentro del recordset que se devuelve. Entonces, si yo únicamente quiero los datos de un mes, pero que también me salga el dato del mes anterior en la misma línea, ¿cómo lo hago, ya que filtré otras líneas del conjunto de registros? Bueno, la respuesta es simple, con una cte que nos devuelva previamente los registros que se precisen y luego filtrándola, es decir, con una subconsulta:

 

;with cte as (

SELECT

yearid,

monthid,

Profit as ProfitThisMonth,

LAG(Profit, 1, 0) OVER(Order by yearid, monthid) as ProfitLastMonth

FROM MonthlyProfit

)

select * from cte

where yearid = 2010 and monthid = 8

 

Al contrario de lo que podría parecer, el rendimiento no se ve penalizado por esta acción, ya que no hay que leer dos veces, sólo una vez (comparando de nuevo con la sentencia clásica con el mismo filtro aplicado).

 

(1 row(s) affected)

Table 'MonthlyProfit'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'MonthlyProfit'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 

En resumidas cuentas, es seguro que nadie podrá decidir realizar una migración por este par de funciones, pero ello no quita para que, en caso de disponer de la nueva versión, las podamos disfrutar desde el primer día. Y vale para ir al registro anterior o a 15 más atrás, claro está, con lo que permitirá cubrir un buen número de casos más.

 

(Cito la fuente en la que me basé para los ejemplos de este post: http://www.sqlservercentral.com/blogs/sqlservernotesfromthefield/archive/2011/08/02/lag_1920_n_1920_lead-_1320_-new-t_2D00_sql-features-in-sql-server-denali-ctp3.aspx).

 

 

Resolución problemas de DTC

Escrito por qwalgrande 30-07-2011 en General. Comentarios (2)

 

Como la mayoría de los problemas de DTC, éstos se atribuyen al servidor de bases de datos, pero no es así, nos cuestiones de este servicio, cuyas transacciones pueden o no pasar por SQL Server. Eso no le libra al DBA de tener que resolverlos. A la larga, la razón es simple, lo hacemos porque podemos (y sabemos).

 

En general, la resolución de los problemas del DTC es bastante compleja porque es muy oscura y no hay muchas herramientas que permitan aportar datos concretos sobre qué está pasando con estas transacciones que no empiezan o no terminan.

 

Lo que yo suelo hacer es ir en tres pasos, cuando estemos hablando de transacciones que pasen por un servidor de bases de datos:

  • 1.- Comprobar el acceso al servidor con un ping al nombre del servidor SQL Server. Ejemplo: ping MiServidor
  • 2.- Comprobar el acceso al puerto en el que escucha SQL Server (suele ser el puerto 1433), con un telnet. Ejemplo: telnet MiServidor 1433
  • 3.- Comprobar las transacciones con dtctester (http://support.microsoft.com/kb/293799/es).

 

Cuando el problema está en el punto 3, el error que se arroja casi siempre es el mismo, que no se puede finalizar la transacción. Ahí entramos en un amplio compendio de posibles problemas, en su mayoría de ocasiones, problemas de comunicación, algún firewall que impide la comunicación. Si hay una DMZ por medio, pues más difícil todavía. Lo único bueno es que en cada casa, los problemas suelen ser muy repetitivos. Resuelto uno o dos casos, los demás se le parecerán mucho.