Creación de tablas eficaces

Completado

El diseño de tabla eficaz forma la base de cualquier base de datos. Las tablas estructuran los datos y determinan cómo las consultas acceden y modifican la información de forma eficaz.

Diseño y creación de tablas

Las tablas son los bloques de creación fundamentales de las bases de datos relacionales, organizando los datos en filas y columnas que representan entidades y sus atributos. En los sistemas relacionales, las tablas definen la estructura para almacenar datos transaccionales, aplican relaciones a través de claves externas y proporcionan la base para las consultas y los informes.

Para el análisis multidimensional, las tablas sirven como tablas de hechos que almacenan eventos medibles y tablas de dimensiones que proporcionan contexto para el análisis. Las decisiones de diseño que se toman al crear tablas (tipos de datos, ajuste de tamaño de columna, restricciones y relaciones) afectan directamente a la eficiencia del almacenamiento, el rendimiento de las consultas, la integridad de los datos y la escalabilidad en las cargas de trabajo operativas y analíticas.

Elección de los tipos de datos adecuados

Los tipos de datos son decisiones fundamentales que afectan a la base de datos. La elección incorrecta puede dar lugar a errores de almacenamiento desperdiciado, rendimiento deficiente, pérdida de datos o errores de aplicación. A diferencia del código de aplicación que puede refactorizar fácilmente, el cambio de tipos de datos de columna en bases de datos de producción suele requerir recompilaciones de tablas, lo que puede significar horas de inactividad para tablas grandes.

Seleccione los tipos de datos adecuados al diseñar el esquema inicial, ya que es el momento más fácil de obtenerlo correctamente. Además, tenga en cuenta los tipos de datos cuidadosamente cuando:

• Está almacenando datos donde la precisión es importante.
• Está trabajando con tablas de gran volumen en las que se multiplican los costos de almacenamiento.
• Va a definir columnas consultadas con frecuencia que funcionan más rápido con tipos más pequeños.

Exploración de tipos de datos comunes

Los tipos de datos adecuados afectan al almacenamiento, el rendimiento y las operaciones:

Categoría de tipo	Tipos de datos	Tamaño de almacenamiento	Instrucciones de uso	Example
Numeric	INT, BIGINT, , DECIMAL, FLOAT	4 bytes, 8 bytes, varía	Elegir en función del rango y las necesidades de precisión	Quantity INT, , Revenue DECIMAL(10,2), Population BIGINT
String	VARCHAR, , CHAR, NVARCHAR	1 byte/char, fijo, 2 bytes/char	Se usa VARCHAR para datos de longitud variable, CHAR para longitud fija, NVARCHAR para Unicode	Email VARCHAR(100), , CountryCode CHAR(2), ProductName NVARCHAR(100)
Fecha y hora	DATE, , DATETIME2, DATETIMEOFFSET	3 bytes, 6-8 bytes, 10 bytes	DATETIME2 proporciona una mayor precisión que DATETIME	BirthDate DATE, , OrderTimestamp DATETIME2, EventTime DATETIMEOFFSET
Binario	VARBINARY, IMAGE	varies	Para almacenar datos binarios como imágenes o documentos	ProfilePhoto VARBINARY(MAX), DocumentContent VARBINARY(MAX)
Especial	UNIQUEIDENTIFIER, , XML, JSON	16 bytes, varía, binario nativo	UNIQUEIDENTIFIER para GUID, XML para documentos XML ( JSON SQL 2025+) para documentos JSON en formato binario nativo	RowGUID UNIQUEIDENTIFIER, , Config XML, Settings JSON

Los matices de tipo de datos requieren atención cuidadosa. Por ejemplo, usar FLOAT para datos financieros en lugar de DECIMAL puede introducir errores de redondeo que no se pueden corregir sin recalcular todos los valores dependientes. Una clave principal UNIQUEIDENTIFIER cuando INT es suficiente triplica el tamaño del índice y ralentiza todas las operaciones JOIN. La mayoría de estas decisiones afectan al rendimiento de la base de datos y pueden determinar si las consultas se ejecutan en milisegundos o minutos.

Estimación de los requisitos de tamaño de tabla

El tamaño de la tabla no es solo sobre los costos de almacenamiento; afecta directamente a las operaciones de la base de datos. El tamaño de la tabla afecta a los tiempos de copia de seguridad y restauración, las duraciones de recompilación de índices y el rendimiento de las consultas.

Importante

Una tabla mal diseñada que almacena 200 bytes por fila en lugar de 100 bytes duplica las necesidades de almacenamiento, los tiempos de copia de seguridad y los requisitos de E/S.

Otro escenario para planear el ajuste de tamaño de la tabla es cuando se calculan los costos de almacenamiento de las bases de datos en la nube, el diseño para un espacio en disco limitado o la planificación de estrategias de archivo. Todos estos escenarios requieren estimaciones de tamaño precisas para tomar decisiones fundamentadas sobre los recursos y las operaciones.

Por ejemplo, una empresa minorista que almacena 100 millones de transacciones diariamente con un adicional de 50 bytes por fila desperdicia 5 GB al día, es decir, 1,8 TB anualmente de almacenamiento innecesario, además de aumentos proporcionales en el tiempo y los costos de copia de seguridad.

En el ejemplo siguiente se muestra cómo calcular el tamaño de la Employee tabla:

-- Estimate row size for a table
-- Fixed-length columns: sum of column sizes
-- Variable-length: estimate average size
-- Example row calculation:
CREATE TABLE Employee (
    EmployeeID INT,              -- 4 bytes
    FirstName NVARCHAR(50),      -- ~2-100 bytes (avg 40)
    LastName NVARCHAR(50),       -- ~2-100 bytes (avg 40)
    HireDate DATE,               -- 3 bytes
    Salary DECIMAL(10,2)         -- 5 bytes
);  
-- Estimated row size: 4 + 40 + 40 + 3 + 5 = ~92 bytes
-- Plus row overhead (~7 bytes) = ~99 bytes per row
-- 1 million rows ≈ 94 MB

Sugerencia

Puede usar Copilot para ayudarle a generar la estimación del tamaño de tabla.

Diseñar columnas eficaces

En el ejemplo siguiente se muestra una tabla bien diseñada Product que aplica los principios descritos en esta unidad:

CREATE TABLE Product (
    ProductID INT PRIMARY KEY IDENTITY(1,1),       -- Auto-incrementing surrogate key (4 bytes)
    ProductName NVARCHAR(100) NOT NULL,             -- Unicode support, appropriate length, enforced
    Category NVARCHAR(50) NOT NULL,                 -- Smaller than ProductName (categorization needs less space)
    Price DECIMAL(10,2) NOT NULL,                   -- Exact precision for financial data
    StockQuantity INT NOT NULL DEFAULT 0,           -- Integer sufficient for inventory, default prevents nulls
    LastRestocked DATETIME2 DEFAULT GETUTCDATE()    -- Modern date type with automatic timestamp
);

En esta tabla se muestran varios procedimientos recomendados:

• Tipos de datos adecuados: INT para la clave principal (menor que BIGINT o UNIQUEIDENTIFIER), DECIMAL(10,2) para cálculos financieros precisos en lugar de FLOAT, DATETIME2 para mejorar la precisión que la heredada DATETIME
• Columnas de tamaño correcto: NVARCHAR(100) para los nombres de producto y NVARCHAR(50) para las categorías en función de la longitud de datos esperada
• Restricciones: NOT NULL garantiza la calidad de los datos evitando que falten valores críticos.
• Valores predeterminados: los valores automáticos de StockQuantity (0) y LastRestocked (hora UTC actual) reducen la complejidad del código de la aplicación.
• Clave principal eficaz: IDENTITY genera claves secuenciales que se agrupan de forma eficaz y usan almacenamiento mínimo (4 bytes frente a 16 bytes para GUID)

Nota:

En este ejemplo se usa NVARCHAR (2 bytes por carácter) para la compatibilidad con Unicode. Si los datos son de solo ASCII, VARCHAR (1 byte por carácter) corta el almacenamiento de cadenas a la mitad. Un ProductName VARCHAR(100) usa ~30 bytes frente a ~60 bytes para NVARCHAR(100) en un nombre de 30 caracteres. En 10 millones de filas, esto ahorra aproximadamente 300 MB. Use NVARCHAR para datos internacionales; use VARCHAR cuando la eficiencia del almacenamiento importa y los datos seguirán siendo solo de ASCII.

Procedimientos recomendados de diseño

Aplique estos principios clave al diseñar e implementar tablas para garantizar el rendimiento y el mantenimiento:

• Uso de tipos de datos adecuados : los tipos de datos más pequeños reducen el almacenamiento y mejoran el rendimiento
• Considere el tamaño de la tabla al principio: calcule el tamaño de fila y el tamaño total de la tabla para planear el almacenamiento y la indexación.
• Implementación de restricciones significativas : garantizar la calidad de los datos en el nivel de base de datos
• Planear el crecimiento : diseñar tablas para controlar el volumen de datos futuro
• Indexar estratégicamente: indexar columnas usadas en las cláusulas WHERE, JOIN y ORDER BY
• Elección del almacén de columnas para análisis : uso de índices de almacén de columnas para tablas grandes con consultas analíticas
• Normalizar cuando corresponda : equilibrar la normalización con las necesidades de rendimiento de las consultas
• Supervisión de la compresión de filas y páginas : habilitación de la compresión para tablas grandes para guardar el almacenamiento

La mayoría de los problemas de rendimiento de las bases de datos proceden de decisiones de diseño deficientes tomadas al principio del desarrollo. Los tipos de datos sobredimensionados desperdician almacenamiento y consultas lentas. Los tipos de índice faltantes o incorrectos crean cuellos de botella que las actualizaciones de recursos no pueden resolver. Evite estos problemas al invertir tiempo en el diseño adecuado de objetos antes de crear o modificar tablas. Las decisiones que tome durante el diseño( elegir los tipos de datos adecuados, calcular tamaños de tabla, seleccionar los tipos de índice adecuados) tienen un efecto mucho mayor en el rendimiento y el costo a largo plazo que cualquier optimización que se pueda aplicar más adelante.