Mis dos preguntas son:

¿Puedo usar índices agrupados para acelerar las inserciones masivas en tablas grandes?¿Puedo seguir utilizando eficientemente las relaciones de clave externa si mi columna IDENTIDAD ya no es el índice agrupado?

Para elaborar, tengo una base de datos con un par de tablas muy grandes (entre 100-1000 millones de filas) que contienen datos de la compañía. Por lo general, hay datos sobre 20-40 compañías en una tabla de este tipo, cada una como su propio "fragmento" marcado por "CompanyIdentifier" (INT). Además, cada compañía tiene alrededor de 20 departamentos, cada uno con su propio "subchunk" marcado por "DepartmentIdentifier" (INT).

Con frecuencia sucede que se agrega o se elimina un "fragmento" o un "subconjunto" completo de la tabla. Lo primero que pensé fue usar el Particionamiento de tabla en esos fragmentos, pero como estoy usando SQL Server 2008 Standard Edition no tengo derecho a ello. Aún así, la mayoría de las consultas que tengo se ejecutan en un "fragmento" o "subconjunto" en lugar de en la tabla en su conjunto.

He estado trabajando para optimizar estas tablas para las siguientes funciones:

Consultas que se ejecutan en subchunksConsultas de "evaluación comparativa" que se ejecutan en la tabla en su conjuntoInsertar / eliminar grandes fragmentos de datos.

Para 1) y 2) no he encontrado muchos problemas. He creado varios índices en campos clave (que también contienen CompanyIdentifier y DepartmentIdentifier cuando sea útil) y las consultas se están ejecutando bien.

Pero para 3) he luchado por encontrar una buena solución. Mi primera estrategia fue siempre deshabilitar los índices, insertar en bloque una gran parte y reconstruir los índices. Al principio fue muy rápido, pero ahora que hay muchas empresas en la base de datos, lleva mucho tiempo reconstruir el índice cada vez.

Por el momento, mi estrategia ha cambiado a dejar el índice encendido mientras lo inserto, ya que ahora parece ser más rápido. Pero quiero optimizar aún más la velocidad de inserción.

Parece haber notado que al agregar un índice agrupado definido en CompanyIdentifier + DepartmentIdentifier, la carga de nuevos "fragmentos" en la tabla es más rápida. Antes de abandonar esta estrategia a favor de agregar un índice agrupado en una columna IDENTIDAD, ya que varios artículos me señalaron que el índice agrupado está contenido en todos los demás índices, por lo que el índice agrupado debería ser lo más pequeño posible. Pero ahora estoy pensando en revivir esta vieja estrategia para acelerar las inserciones. Mi pregunta, ¿sería prudente o sufriré problemas de rendimiento en otras áreas? ¿Y esto realmente acelerará mis insertos o es solo mi imaginación?

Tampoco estoy seguro de si en mi caso realmente se necesita una columna IDENTIDAD. Me gustaría poder establecer relaciones de clave externa con otras tablas, pero ¿puedo usar algo como un esquema CompanyIdentifier + DepartmentIdentifier + [uniquifier] para eso? ¿O tiene que ser un número de IDENTIDAD fragmentado de toda la tabla?

Muchas gracias por cualquier sugerencia o explicación.