Category / Base de Datos

T-SQL: TRUNCATE TABLE y DELETE FROM

By Juan Carlos Heredia Mayer 08/05/2013 Base de Datos

Una duda bastante habitual entre los desarrolladores, es cuál es la diferencia entre la sentencia TRUNCATE TABLE y DELETE FROM TABLE. Este post está orientado al modo de trabajo en SQL Server (que es el motor de base de datos que más uso), sin embargo, la mayoría de las diferencias entre ambas sentencias se aplican también a cualquier motor de bases de datos (Oracle, MySQL, DB2, etc).

Veamos primero las diferencias y luego analizamos el porqué de éstas.

TRUNCATE TABLE DELETE FROM
  • Es una operación DDL.
  • Es una operación DML.
  • No permite el borrado selectivo. TRUNCATE TABLE elimina todo el contenido de la tabla.
  • Permite el borrado selectivo, mediante la clausula WHERE.
  • No se puede ejecutar, si la tabla tiene asociadas, aun si no existiesen registros en la tabla que contiene la FK.
  • Se puede ejecutar si hay FK asociadas a la tabla, pero siempre y cuando no tenga registros asociados o la FK este deshabilitada.
  • Es la forma más rápida de eliminar el contenido de una tabla.
  • Es más lenta.
  • No se activa ningún trigger al ejecutarse (a partir de SQL Server 2005, es posible capturar el evento mediante un DDL trigger, pero a modo de auditoría, no es posible tener acceso a los valores que fueron eliminados).
  • Puede activarse el trigger de ON DELETE y poder determinar que registros están siendo eliminados. siendo eliminados.
  • En caso que la tabla tuviese un campo Identity, se resetea el valor a 1 (o al valor base determinado en el campo).
  • No resetea el valor del campo Identity, en caso que la tabla tuviese uno.
  • TRUNCATE TABLE desasocia (deallocate) las páginas de datos de la tabla.
  • DELETE FROM marca cada registro afectado, como eliminado.
  • El logueo en el transaction log es mínimo. Solo registra el dealloc de las páginas de datos.
  • Loguea cada operación sobre los registros afectados.
  • No puede ser ejecutado si la tabla tiene asociadas vistas indexadas.
  • Se puede ejecutar si la tabla tiene vistas indexadas.

Como se puede ver, TRUNCATE TABLE es bastante más restrictivo que DELETE, al punto que en muchas situaciones, aun si queremos eliminar todo el contenido de la tabla, no podemos hacerlo.

Ahora bien, ¿qué es lo que hace TRUNCATE y porque es mas rápido que su “competidor”?  Para eso primero voy a hacer una brevísima introducción a como almacena internamente SQL Server los datos.

En SQL Server, los registros de una tabla, son agrupados en una estructura física de datos, que se llama página. Cada página tiene un tamaño fijo de 8060 bytes, y puede almacenar uno o cientos de registros, dependiendo del tamaño del mismo. Cuando se intenta insertar más registros en una tabla y la página de datos está llena, se crea otra donde se inserta el nuevo registro y así sucesivamente.

El comando TRUNCATE, lo que hace es desasociar (deallocate) las páginas de datos de la tabla, sin alterar los registros en sí mismo, mientras que el comando DELETE FROM recorre cada uno de los registros y los marca como borrados, por lo tanto, hace muchas más operaciones de lectura y escritura (I/O), que aumenta exponencialmente en relación con TRUNCATE, a medida que aumenta el tamaño de la tabla.

Otra razón que explica la diferencia de rendimiento, es que TRUNCATE TABLE solo registra en el fichero de transacciones (transaction log), el deallocate de las paginas con la tabla (por lo tanto, es posible hacer un rollback de un TRUNCATE (algunos creen que no), mientras que el DELETE FROM manda al transaction log todos los registros afectados, lo que es obviamente mucho más costoso a nivel recursos de I/O.

Por último, al ejecutar el comando TRUNCATE se produce un bloqueo de la tabla, a diferencia del DELETE FROM que hace un bloqueo por pagina o registro, el consumo de memoria para almacenar los objetos bloqueados es mucho menor.

#SQL Server#Trucos

Máquina virtual de Team Foundation Server 2012 y System Center 2012

By Juan Carlos Heredia Mayer 21/03/2013 Base de Datos

Para quienes están deseando aprender y probar Team Foundation Server 2012 y System Center 2012 Operations Manager, he aquí una máquina virtual que tiene mucho contenido técnico y algo más que los típicos laboratorios de ALM. Especialmente en este caso, hay un laboratorio que permite ver la interacción real entre System Center Operation Manager 2012 y Team Foundation Server 2012.

Esta máquina virtual está configurada con:

  • Microsoft Windows Server 2012 Datacenter Evaluation
  • Microsoft Visual Studio Ultimate 2012 Update 1
  • Microsoft Visual Studio Team Foundation Server 2012 Update 1
  • Microsoft Visual Studio Team Foundation Server 2012 Update 1 Power Tools
  • Microsoft System Center 2012 – Operations Manager SP1 with Update Rollup 1
  • Microsoft SQL Server 2008 R2
  • Microsoft SQL Server 2012 Express
  • Sample data required to support the hands-on-lab / demo script.

¡A disfrutarlo!

Más información aquí (Blog de Brian Keller – Evangelista de Visual Studio ALM)

Máquinas virtuales de Team Foundation Server 2012 y System Center 2012 Operations Manager

#Hand-on-Labs#Máquinas Virtuales#Recursos#Visual Studio

T-SQL: Usuarios huérfanos

By Juan Carlos Heredia Mayer 13/03/2013 Base de Datos

SQL Server usuarios huérfanos

Alguna vez nos ha pasado que cuando recuperamos una base de datos en otro servidor o instancia de SQL Server, también se recuperan los usuarios de la base de datos, pero no tienen un vínculo con los inicios de sesión del servidor. Por lo tanto nos encontramos con usuarios huérfanos.

Un usuario de base de datos cuyo inicio de sesión de SQL Server correspondiente está sin definir o se ha definido de forma incorrecta en una instancia de servidor no podrá iniciar una sesión en la instancia. Es lo que se denomina un usuario huérfano de la base de datos en esa instancia de servidor. Un usuario de base de datos puede convertirse en huérfano si se quita el inicio de sesión de SQL Server correspondiente. También puede convertirse en huérfano si una base de datos se restaura o se conecta a otra instancia de SQL Server. Otra manera de convertirse en huérfano es que el SID al que se asigna el usuario de la base de datos no esté presente en la nueva instancia de servidor.

Para detectar usuarios huérfanos

Ejecutamos las siguientes instrucciones de Transact-SQL:

USE <database_name>
GO 
sp_change_users_login @Action='Report'
GO

En los resultados se enumeran los usuarios y sus identificadores de seguridad (SID) correspondientes, que se encuentran en la base de datos actual y no están vinculados a ningún inicio de sesión de SQL Server.

Para resolver un usuario huérfano

Seguimos el siguiente procedimiento:

  • El siguiente comando vuelve a vincular la cuenta de inicio de sesión de servidor especificada en <login_name> con el usuario de la base de datos especificado por <database_user>.
USE <database_name>
GO
sp_change_users_login 
     @Action='update_one', 
     @UserNamePattern='<database_user>', 
     @LoginName='<login_name>'
GO
  • Una vez ejecutado el código del paso anterior, el usuario podrá obtener acceso a la base de datos. El usuario puede modificar la contraseña de la cuenta de inicio de sesión <login_name> mediante el procedimiento almacenado sp_password del siguiente modo:
USE master 
GO
sp_password @old=NULL, @new='password', @loginame='<login_name>';
GO
#SQL Server#Trucos