synonyms - ¿Por qué y cuándo debería usar SPARSE COLUMN?(SQL SERVER 2008)
sql server t sql (5)
Después de ir a través de algunos tutoriales sobre la nueva característica SPARSE COLUMN de SQL SERVER 2008, he descubierto que no ocupa espacio si el valor de la columna es 0 o nulo, pero cuando hay un valor, toma 4 veces el espacio de un valor normal (no dispersa) columna contiene.
Si mi comprensión es correcta, ¿por qué iré por eso en el momento del diseño de la base de datos? Y si uso eso, ¿en qué situación me encuentro?
También por curiosidad, ¿cómo es posible que no haya reserva de espacio cuando una columna se define como columna dispersa (quiero decir, cuál es la implementación interna para eso)?
Gracias por adelantado
De SQL SERVER - 2008 - Introducción a SPARSE Columns - Parte 2 por Pinal Dave :
Todas las columnas SPARSE se almacenan como una columna XML en la base de datos. Veamos algunas de las ventajas y desventajas de la columna SPARSE.
Las ventajas de la columna SPARSE son:
Las instrucciones INSERT, UPDATE y DELETE pueden hacer referencia a las columnas dispersas por su nombre. La columna SPARSE también puede funcionar como una columna XML.
La columna SPARSE puede aprovechar los Índices filtrados, donde los datos se rellenan en la fila.
La columna SPARSE ahorra mucho espacio en la base de datos cuando hay cero o valores nulos en la base de datos.
Las desventajas de la columna SPARSE son:
La columna SPARSE no tiene propiedad IDENTITY ni ROWGUIDCOL.
La columna SPARSE no se puede aplicar a text, ntext, image, timestamp, geometry, geography o tipos de datos definidos por el usuario.
La columna SPARSE no puede tener un valor predeterminado o regla o columna calculada.
El índice agrupado o un índice de clave principal único no se puede aplicar a la columna SPARSE. La columna SPARSE no puede formar parte de la clave del índice agrupado.
La tabla que contiene la columna SPARSE puede tener un tamaño máximo de 8018 bytes en lugar de 8060 bytes normales. Una operación de tabla que involucra columna SPARSE tiene un impacto de rendimiento sobre la columna regular.
Lo estás leyendo mal, nunca lleva 4 veces el espacio.
Específicamente, dice 4 * (4 bytes, ver nota al pie), no 4x (multiplica por 4). El único caso donde es exactamente 4x el espacio es un char (4), que vería ahorros si los NULL existen más del 64% del tiempo.
"* La longitud es igual al promedio de los datos contenidos en el tipo, más 2 o 4 bytes".
Una columna dispersa no usa 4 veces la cantidad de espacio para almacenar un valor, sino que utiliza 4 bytes adicionales (fijos) por valor no nulo. (Como ya has indicado, un NULL toma 0 espacios).
Entonces, un valor no nulo almacenado en una columna de bit sería 1 bit + 4 bytes = 4.125 bytes. Pero si el 99% de estos son NULL, sigue siendo un ahorro neto.
Un valor no nulo almacenado en una columna GUID (UniqueIdentifier) es de 16 bytes + 4 bytes = 20 bytes. Entonces, si solo el 50% de estos son NULL, eso sigue siendo un ahorro neto.
Por lo tanto, los "ahorros previstos" dependen en gran medida del tipo de columna de la que estamos hablando, y su estimación de qué proporción será nula vs no nula. Las columnas de ancho variable (varchar) son probablemente un poco más difíciles de predecir con precisión.
Esta página de libros en línea tiene una tabla que muestra qué porcentaje de diferentes tipos de datos necesitaría ser nulo para que usted termine con un beneficio.
Entonces, ¿ cuándo deberías usar una columna dispersa? Cuando espera que un porcentaje significativo de las filas tenga un valor NULO. Algunos ejemplos que vienen a la mente:
- Una columna " Fecha de devolución de pedido " en una tabla de pedidos. Es de esperar que un porcentaje muy pequeño de ventas genere productos devueltos.
- Una línea de " 4ta dirección " en una tabla de direcciones. La mayoría de las direcciones postales, incluso si necesita un nombre de Departamento y un "Cuidado de" probablemente no necesiten 4 líneas separadas.
- Una columna " Sufijo " en una tabla de clientes. Un porcentaje bastante bajo de personas tiene un "Jr." o "III" o "Esquire" después de su nombre.
| datetime NULL | datetime SPARSE NULL | datetime SPARSE NULL |
|--------------------|----------------------|----------------------|
| 20171213 (8 bytes) | 20171213 (12 bytes) | 20171213 (12 bytes) |
| NULL (8 bytes) | 20171213 (12 bytes) | 20171213 (12 bytes) |
| 20171213 (8 bytes) | NULL (0 bytes) | NULL (0 bytes) |
| NULL (8 bytes) | NULL (0 bytes) | NULL (0 bytes) |
Pierdes 4 bytes no solo una vez por fila; pero por cada celda en la fila que no sea nula.
Almacenar un nulo en una columna dispersa no ocupa espacio en absoluto.
Para cualquier aplicación externa, la columna se comportará de la misma manera
Las columnas dispersas funcionan muy bien con los índices filtrados, ya que solo querrá crear un índice para tratar los atributos no vacíos en la columna.
Puede crear una columna establecida sobre las columnas dispersas que devuelve un clip xml de todos los datos no nulos de las columnas cubiertas por el conjunto. El conjunto de columnas se comporta como una columna en sí misma. Nota: solo puede tener un conjunto de columnas por tabla.
Change Data Capture y Transactional replication funcionan, pero no la función de conjuntos de columna.
Desventajas
Si una columna dispersa tiene datos, tomará 4 bytes más que una columna normal, por ejemplo, incluso un bit (0.125 bytes normalmente) es 4.125 bytes y el identificador único aumenta de 16 bytes a 20 bytes.
No todos los tipos de datos pueden ser escasos: texto, ntext, imagen, marca de tiempo, tipo de datos definido por el usuario, geometría o geografía o varbinray (máximo) con el atributo FILESTREAM no puede ser escaso. (Modificado17 / 5/2009 gracias Alex por detectar el error tipográfico)
las columnas calculadas no pueden ser dispersas (aunque las columnas dispersas pueden participar en un cálculo en otra columna calculada)
No puede aplicar reglas o tener valores predeterminados.
Las columnas dispersas no pueden formar parte de un índice agrupado. Si necesita hacer eso, use una columna calculada basada en la columna dispersa y cree el índice agrupado sobre eso (que de alguna manera derrota al objeto).
La duplicación de mezcla no funciona.
La compresión de datos no funciona.
El acceso (leer y escribir) a columnas dispersas es más caro, pero no he podido encontrar ninguna cifra exacta sobre esto.