Diario de SQL Server

script

Replicación funciona, pero sigue marcando error

Escrito por qwalgrande 19-11-2011 en General. Comentarios (0)


El monitor de replicación es tremendamente útil, pero no por ellos perfecto. Es frecuente que tras reparar un error, nos queden agentes o réplicas que, aun estando funcionando como se espera, persisten en aparecer con el aspa roja del error. Hay una forma rápida de forzar a que se actualice la tabla que el monitor de replicación utiliza para leer los estados, que consiste en ejecutar manualmente el procedimiento sp_MSLoad_replication_status (sin parámetros, ejecutado en la base de datos publicada). De esta manera conseguimos que se quiten esas marcas de error y que nuestro Replication Monitor vuelva a ser de utilidad.

 

La explicación es un extraño comportamiento de los sistemas de monitorización de replicación, se basan en el estado de la última ejecución de los jobs (viene siendo así desde SQL Server 7). El log reader o cualquier agente de suscripción transaccional siempre están corriendo. Sólo se paran si hay algún problema de comunicación, algún dato que no puede ser aplicado. Y cuando se paran, o se han detenido o han finalizado con error. Así, aunque se vuelvan a arrancar, la última ejecución fue un error, y así se queda.

 

Internamente, cualquier puede comprobarlo, se hace una consulta a una tabla que sólo se recarga si está vacía. Si ya tiene datos, sólo se actualizan los mismos. Si resulta que se eliminó una suscripción para volver a crearla después, se eliminan y crean los jobs que se ocupan de la acción, pero de esta tabla de estados no se borran, los registros quedan huérfanos. Como el estado general es el peor de los estados de los distintos agentes implicados, es frecuente que se marque un error por un job que ya ni siquiera existe. El procedimiento almacenado mencionado permite purgar esa tabla, se recarga con lo que haya en ese momento y ya está, tenemos nuestro estado de replicación marcando lo que está teniendo lugar exactamente.

 

 

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.

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).

 

 

Una lección de T-SQL

Escrito por qwalgrande 21-07-2011 en General. Comentarios (1)

 

En determinadas ocasiones, se hace difícil explicar cosas sencillas, como por ejemplo, por qué es mejor utilizar una sintaxis u otra que a primera vista pueden parecer equivalentes. En este caso, Eladio Rincón nos da una lección muy sencilla para discernir qué método usar a la hora de hacer un "IN" en una sentencia SQL.

 

http://blogs.solidq.com/ElRinconDelDBA/Post.aspx?ID=184&title=Alguna+curiosidad+sobre+la+palabra+clave+(keyword)+IN+en+SQL+Server