Crear claves principales y secundarias en una tabla
En las tablas, las claves son esenciales porque las utiliza para crear registros únicos y para optimizar el rendimiento de la base de datos cuando busca o filtra datos de la tabla.
En AL, una definición clave es una secuencia de uno o varios id. de campo de una tabla. Puede definir claves en objetos de tabla y objetos de extensión de tabla, según el tipo de clave. Hay dos tipos de claves: principales y secundarias.
Claves principales
Una clave principal identifica de forma única cada registro en una tabla. Cada tabla tiene una clave principal y solo puede haber una clave principal por tabla. Las claves principales se definen únicamente en los objetos de la tabla. En SQL, los objetos de extensión de tabla heredan la clave principal del objeto de tabla que extienden (el objeto de tabla básica). Por lo tanto, cualquier clave que defina en un objeto de extensión de tabla se considera una clave secundaria.
Claves secundarias
Las claves secundarias crean índices en SQL. Se definen tanto en objetos de tabla como en objetos de extensión de tabla. Puede definir varias claves secundarias para un único objeto de tabla y objeto de extensión de tabla.
Una clave en un objeto de extensión de tabla puede incluir campos del objeto de tabla básica o del objeto de extensión de tabla.
Cada tabla de Dynamics 365 Business Central necesita una clave principal. No puede tener tablas sin claves en su aplicación. En una tabla, puede tener claves principales y secundarias.
La clave principal es siempre la primera de la lista. Siempre está activa y hace que los registros sean únicos. Puede tener hasta 40 claves para cada tabla y cada clave puede estar compuesta por un máximo de 20 campos.
La clave principal realiza un seguimiento de los datos en una tabla. La clave principal se compone de hasta 16 campos en un registro. La combinación de valores en los campos de la clave principal permite identificar de forma única cada registro. En AL, la primera clave definida en un objeto de tabla es la clave principal. La clave principal determina el orden lógico en el que se almacenan los registros, independientemente de la ubicación física de los campos en el objeto de tabla.
Lógicamente, los registros se almacenan secuencialmente en orden ascendente y se ordenan por clave principal. Antes de agregar un nuevo registro a una tabla, SQL Server comprueba si la información en los campos de clave principal del registro es única. Si es así, se inserta el registro en la posición lógica correcta. Los registros se ordenan dinámicamente para que la base de datos sea siempre estructuralmente correcta. Esta ordenación permite una rápida manipulación y recuperación de datos.
La clave principal siempre está activa. SQL Server mantiene la tabla ordenada en orden de clave principal y rechaza registros con valores duplicados en campos de clave principal. Por eso los valores de la clave principal siempre deben ser únicos. No es el valor de cada campo de la clave principal lo que debe ser único, sino la combinación de valores en todos los campos lo que constituye la clave principal.
Las teclas secundarias son opcionales y pueden ayudarle a realizar acciones de búsqueda de forma más rápida. Pueden tener un impacto en el rendimiento porque son índices en SQL Server. Una práctica recomendada es limitar las claves secundarias a un máximo de tres a cinco para cada tabla.
En un objeto de tabla, las claves definidas después de la clave principal se denominan claves secundarias. Todas las claves definidas en un objeto de extensión de tabla se consideran claves secundarias.
Una clave secundaria se implementa en SQL Server utilizando una estructura que se denomina índice. Esta estructura es como un índice que se usa en los libros de texto. Un índice de libros de texto enumera alfabéticamente los términos importantes al final de un libro. Al lado de cada término se encuentran los números de página. Puede buscar rápidamente en el índice para encontrar una lista de números de página (direcciones) y puede localizar el término buscando en las páginas especificadas. El índice es un indicador exacto que muestra dónde aparece cada término en el libro de texto.
Cuando define una clave secundaria y una marca como habilitada, se mantiene automáticamente un índice en SQL Server. El índice refleja el orden de clasificación definido por la clave. Varias claves secundarias pueden estar activas al mismo tiempo.
Una clave secundaria se puede deshabilitar para que no ocupe espacio en la base de datos ni consuma tiempo durante las actualizaciones para mantener su índice. Las claves deshabilitadas se pueden volver a habilitar, aunque esta operación puede tardar mucho tiempo porque SQL Server debe escanear toda la tabla para reconstruir el índice.
Los campos que componen las claves secundarias no siempre contienen datos únicos. SQL Server no rechaza registros con datos duplicados en campos clave secundarios. Por tanto, si dos o más registros contienen información idéntica en la clave secundaria, SQL Server usa la clave principal de la tabla para resolver este conflicto.
Cuando vea las tablas principales, como Cliente, Proveedor o Artículo, observe que la clave principal es la misma para cada tabla. Todas las tablas principales utilizan el campo N.º como su clave principal. Este campo se implementa con un tipo de datos de Código, lo que significa que los valores de la clave principal siempre se establecen en mayúsculas y la longitud se establece en 20.
Una clave principal también puede estar compuesta por más de un campo. Por ejemplo, la tabla Sales Header utiliza una combinación de campos Tipo de documento y N.º. En la tabla Sales Header, mantiene las ofertas, las facturas, los pedidos, etc.
El tipo de documento se almacena en el campo Tipo de documento. Puesto que puede tener el mismo N.º para una factura y un pedido, necesita una clave principal que contenga ambos campos.
Claves secundarias únicas
Una definición clave incluye la propiedad Unique que puede usar para crear una restricción única en la tabla de SQL Server. Una clave única garantiza que los registros de una tabla no tengan valores de campo idénticos. Con una clave única, cuando se valida la tabla, se comprueba la exclusividad del valor de la clave. Si la tabla incluye registros con valores duplicados, la validación produce un error. Otra ventaja de los índices únicos es proporcionar información al optimizador de consultas que ayuda a producir planes de ejecución más eficientes.
Puede crear claves secundarias únicas que se componen de varios campos, como con las claves principales. En este caso, es la combinación de los valores de la clave secundaria la que debe ser única. Considere la tabla Cliente a modo de ejemplo. Suponga que desea asegurarse de que no haya clientes que tengan la misma combinación de valores para los campos Nombre, Dirección y Ciudad. Puede crear una clave única para estos campos.
A diferencia de las claves principales, es posible definir varias claves secundarias únicas en una tabla.
Siempre hay una clave secundaria única en el campo SystemId.
Claves secundarias con campos incluidos
Con claves secundarias no en clúster, puede usar la propiedad IncludedFields para agregar campos que no forman parte de la propia clave. En SQL Server, estos campos no clave corresponden a lo que se denomina columnas incluidas. El uso de campos incluidos le permite crear índices que cubren más consultas y le permite omitir el número máximo de campos en una clave.
Una clave secundaria con campos incluidos puede mejorar el rendimiento de la consulta SQL, especialmente cuando el índice SQL contiene todas las columnas de la consulta, ya sea como columnas clave o como columnas incluidas. El rendimiento mejora porque el optimizador de consultas puede ubicar todos los valores de columna dentro del índice. Además, no accede a datos de tabla o de índice en clúster, lo que da como resultado menos operaciones de E/S de disco. Para obtener más información sobre las columnas incluidas en SQL, consulte Crear índices con columnas incluidas.
Claves de almacén de columnas no en clúster
Los índices de almacén de columnas no en clúster (a veces denominados NCCI) se admiten en las tablas.
Con la propiedad ColumnStoreIndex, crea un índice de almacén de columnas no en clúster en la tabla de SQL Server. El uso de una clave de almacén de columnas no en clúster puede mejorar el rendimiento de las consultas al realizar análisis en tablas grandes. Este tipo de índice utiliza el almacenamiento de datos basado en columnas y el procesamiento de consultas para lograr ganancias de hasta 10 veces el rendimiento de las consultas en consultas analíticas en comparación con el almacenamiento tradicional orientado a filas. También puede conseguir ganancias de hasta 10 veces la compresión de datos sobre el tamaño de datos sin comprimir en tablas normales.
Puede usar un índice de almacén de columnas no en clúster para ejecutar análisis operativos en tiempo real de manera eficiente en la base de datos de Business Central sin necesidad de definir índices SIFT por adelantado (y sin los problemas de bloqueo que los índices SIFT a veces imponen en el sistema). Siempre que agregue una clave SIFT en los campos para realizar operaciones de suma/recuento, use una clave de almacén de columnas no en clúster para agregar todos los campos al índice.
Para ilustrarlo, aquí mostramos un ejemplo sencillo de sustitución de dos claves SIFT por un único índice de almacén de columnas no en clúster. Suponga que ya ha implementado dos claves SIFT:
Key1: "WareHouseId, Color" SumField: "OnStock"
Key2: "WareHouseId, ItemId, Size" SumField: "OnStock"
Con un índice de almacén de columnas no en clúster, solo podría tener un índice definido como: ColumnStoreIndex = WareHouseId,Color,ItemId,Size,OnStock
Claves en clúster y no en clúster
Una definición de clave incluye la propiedad Clustered que utiliza para crear un índice en clúster. Un índice en clúster determina el orden físico en el que se almacenan los registros en la tabla. Según el valor de la clave, los registros se ordenan en orden ascendente. El uso de una clave en clúster puede acelerar la recuperación de registros.
Solo puede haber un índice en clúster por tabla. De forma predeterminada, la principal está configurada como una clave en clúster.
Cómo afectan las claves al rendimiento
La búsqueda de datos concretos es más sencilla si se definen y mantienen varias claves para la tabla que contiene los datos deseados. Los índices de cada clave proporcionan vistas específicas que permiten búsquedas rápidas y flexibles. El uso de muchas claves tiene ventajas y desventajas.
La decisión de utilizar pocas o muchas claves no es sencilla. Las claves adecuadas y el número de claves activas a utilizar es un compromiso entre la maximización de la velocidad de recuperación de datos y las actualizaciones de datos (operaciones que insertan, eliminan o modifican datos). En general, puede merecer la pena desactivar las claves complejas si rara vez se usan.
La velocidad total depende de los siguientes factores:
Tamaño de la base de datos.
Número de claves activas.
Complejidad de las claves.
Número de registros en las tablas.
Velocidad del ordenador y su disco duro.
Los índices son una característica eficaz en las bases de datos relacionales, como SQL, para acelerar el proceso de búsqueda de datos. Al indexar los datos que necesita con frecuencia, se optimiza el rendimiento general de su aplicación. Cuando necesite, por ejemplo, ver alguna información en su aplicación, se ejecuta una consulta de la base de datos en segundo plano para completar su solicitud y busca cada registro hasta que encuentre la información requerida. Este proceso de búsqueda de información lleva mucho tiempo y ralentiza el rendimiento de la aplicación. Sin embargo, si indexa los datos, como las columnas a las que se hace referencia con más frecuencia, la base de datos va directamente a dicha columna en lugar de buscar todos los registros en la tabla. Esto aumenta considerablemente la eficiencia y el rendimiento general de su aplicación.
Cuando ejecute una consulta de base de datos, el optimizador de consultas, que es un componente importante de la base de datos, analiza y elegirá el mejor plan posible para completar la instrucción. Al hacerlo, proporciona información adicional sobre la operación en curso que la operación podría realizar bien si la columna o columnas en particular están indexadas. El optimizador de consultas de SQL Server obtiene esta información de vistas de administración dinámica (DMV), en este caso, sys.dm_db_missing_index_details. Devuelve información sobre los índices que faltan, lo que lo ayuda a crear los índices correctos.
Para obtener información sobre los índices que faltan, vaya a Faltan índices en la base de datos en Business Central y verá los datos en las siguientes columnas:
Nombre de la tabla: el nombre de la tabla en la que se basan las columnas sugeridas.
ID de extensión: el ID de la aplicación con la que se relacionan estos datos.
Columnas de igualdad de índices: los datos de estas columnas se basan en consultas de igualdad. Por ejemplo:
Select * from customer where id = 021.Columnas de desigualdad de índices: los datos de estas columnas provienen de consultas, que no se basan en operaciones de igualdad. Por ejemplo:
Select * from customer where id < 200.Columnas de inclusión de índice: estas columnas tienen una copia de los datos asociados para una recuperación rápida de la información, que se basa en las columnas sugeridas en Columnas de igualdad de índices y Columnas de desigualdad de índices. Las columnas de inclusión no son columnas indexadas por sí mismas, sino que apuntan a la información adicional vinculada a las columnas indexadas. Por ejemplo, incluyen los campos en la parte Seleccionar.
La información proporcionada en la página Faltan índices en la base de datos son sugerencias y no deben tomarse como acciones obligatorias. Debe analizar dónde y cuántos índices son los más adecuados para un rendimiento óptimo de la aplicación. Los índices también ocupan espacio de almacenamiento, pueden afectar a las actualizaciones de las tablas, donde las inserciones y eliminaciones son más comunes y, por tanto, pueden ser una operación costosa si se excede.
Limitaciones y restricciones
Hay algunas limitaciones y restricciones que hay que tener en cuenta con respecto a las claves.
Claves en objetos de extensión de tabla
En los objetos de extensión de tabla, puede definir varias claves, igual que lo haría en un objeto de tabla. Sin embargo, se aplican las siguientes limitaciones:
En el segundo lanzamiento de versiones de Business Central de 2020 y lanzamientos anteriores, las claves de los objetos de extensión de tabla solo podían incluir campos del propio objeto de extensión de tabla.
En el primer lanzamiento de versiones de Business Central de 2021 y lanzamientos posteriores, las claves de los objetos de extensión de tabla pueden incluir campos del objeto de extensión de tabla y del objeto de tabla básica. Sin embargo, una única clave no puede incluir campos tanto del objeto de tabla básica como del objeto de extensión de tabla. Dicho de otro modo, cada clave debe contener campos que sean o bien del objeto de tabla básica, o bien del objeto de extensión de tabla.
Puede utilizar el mismo nombre de clave en la extensión de tabla, a menos que la clave contenga campos del objeto de tabla básica.
Número total de claves
Se pueden asociar hasta 40 claves a una tabla.
Modificaciones de claves
Al desarrollar una nueva versión de una extensión, tenga en cuenta las siguientes restricciones para evitar errores de sincronización de esquemas que impidan publicar la nueva versión:
No elimine claves principales.
No agregue ni elimine campos de claves principales, ni cambie su orden.
No cambie las propiedades de claves principales ya existentes.
No agregue más claves únicas.
No agregue más claves agrupadas.
No agregue claves que sean campos de la tabla básica.