Solución:
El límite al que se está enfrentando no tiene nada que ver con los datos almacenados en la página. El cálculo se realiza en función de los tipos de datos de las columnas. Es por eso que se encuentra con el error sin ningún dato en la tabla. La compresión empeora este límite. Puede leer sobre los detalles técnicos detrás de los gastos generales aquí.
Puede solucionar este problema mediante el uso de columnas SPARSE. Eso significa que será posible que las inserciones fallen dependiendo de lo que inserte, pero puede omitir el límite de 8060 bytes. El siguiente código muestra que puede crear 1023 columnas sin problemas:
drop table t1
GO
create table t1(c1 decimal(26, 8) null)
GO
declare @i int = 2;
declare @sql varchar(100);
while @i <= 1023
begin
set @sql="alter table t1 add c" + convert(varchar, @i) + ' decimal(26, 8) SPARSE null';
execute (@sql);
set @i += 1;
end;
GO
Sin embargo, todas las restricciones que lo rodean (lea el artículo vinculado) pueden hacer que esto no sea adecuado para su caso de uso. Específicamente, solo NULL valores (no 0) están optimizados para ocupar muy poco espacio. Si intenta insertar demasiados 0s en una sola fila obtendrá un error. Esto es lo que veo cuando intento insertar 1023 0 valores:
Msg 511, Level 16, State 1, Line 1 No se puede crear una fila de tamaño 17402 que sea mayor que el tamaño de fila máximo permitido de 8060.
Supongo que si se desesperara mucho, podría crear las columnas como VARCHAR(27) en lugar de. Las columnas de longitud variable se pueden mover fuera de la página para que pueda exceder el límite de 8060 bytes en la definición de la tabla, pero la inserción de ciertas combinaciones de valores fallará. SQL Server le advierte de esto al crear la tabla:
Advertencia: Se ha creado la tabla “t1”, pero su tamaño máximo de fila supera el máximo permitido de 8060 bytes. INSERT o UPDATE en esta tabla fallará si la fila resultante excede el límite de tamaño.
La compresión de página o fila puede ser útil si opta por VARCHAR(27) Acercarse. Eso minimizará el espacio utilizado por ambos 0 y NULL. Con VARCHAR(27) Puedo insertar 1023 0 valores muy bien.
Fuera de los aspectos técnicos y la solución alternativa propuesta (utilizando VARCHAR(27) columnas) discutidas en la respuesta de @ Joe, cuestiono el “necesitar crear [a] tabla amplia desnormalizada “según lo expresado por el PO A menos que exista algún requisito técnico extraño de que todas estas columnas debe estar en una sola tabla, sugeriría / recomendaría distribuirlos en tantas tablas de “hermanos” como sea necesario. Las tablas hermanas son tablas que:
- tienen una relación de 1 a 1 entre ellos,
- todos tienen exactamente la misma clave principal,
- solo uno tiene el
IDENTITYcolumna (y no FK a los demás) - el resto tiene una clave externa (en la columna PK) que apunta al PK de la tabla que tiene la
IDENTITY
Aquí está dividiendo la fila lógica en dos o más tablas físicas. Pero eso es esencialmente lo que es la normalización de todos modos, y para lo que están diseñadas las bases de datos relacionales.
En este escenario, incurrirá en algo de espacio adicional utilizado al duplicar el PK, y una cierta complejidad de consulta adicional debido a la necesidad de INNER JOIN las mesas juntas (con frecuencia, pero no siempre, a menos que todos SELECT consultas usan todas las columnas, pero eso no suele suceder) o crean una Transacción explícita para INSERT o UPDATE ellos juntos (DELETE se puede manejar a través de ON DELETE CASCADE establecido en el FK).
SIN EMBARGO, obtiene los beneficios de tener un modelo de datos adecuado con tipos de datos nativos adecuados y sin trucos que puedan tener consecuencias imprevistas más adelante. Incluso si usa VARCHAR(27) permite que esto funcione a nivel técnico, pragmáticamente, no creo que almacenar decimales como cadenas sea lo mejor para usted / el proyecto.
Por lo tanto, si solo “necesita” una sola tabla debido a que no se da cuenta de que una sola entidad lógica no necesita estar representada físicamente en un solo contenedor, entonces no intente forzar todo esto en una sola tabla cuando funcione. con gracia en varias tablas.
El siguiente ejemplo ilustra el concepto básico:
CONFIGURACIÓN
CREATE TABLE tempdb.dbo.T1
(
[ID] INT NOT NULL IDENTITY(11, 2) PRIMARY KEY,
[Col1] VARCHAR(25),
[Col2] DATETIME NOT NULL DEFAULT (GETDATE())
);
CREATE TABLE tempdb.dbo.T2
(
[ID] INT NOT NULL PRIMARY KEY
FOREIGN KEY REFERENCES tempdb.dbo.T1([ID]) ON DELETE CASCADE,
[Col3] UNIQUEIDENTIFIER,
[Col4] BIGINT
);
GO
CREATE PROCEDURE #TestInsert
(
@Val1 VARCHAR(25),
@Val4 BIGINT
)
AS
SET NOCOUNT ON;
BEGIN TRY
BEGIN TRAN;
DECLARE @InsertedID INT;
INSERT INTO tempdb.dbo.T1 ([Col1])
VALUES (@Val1);
SET @InsertedID = SCOPE_IDENTITY();
INSERT INTO tempdb.dbo.T2 ([ID], [Col3], [Col4])
VALUES (@InsertedID, NEWID(), @Val4);
COMMIT TRAN;
END TRY
BEGIN CATCH
IF (@@TRANCOUNT > 0)
BEGIN
ROLLBACK TRAN;
END;
THROW;
END CATCH;
SELECT @InsertedID AS [ID];
GO
PRUEBA
EXEC #TestInsert 'aa', 454567678989;
EXEC #TestInsert 'bb', 12312312312234;
SELECT *
FROM tempdb.dbo.T1
INNER JOIN tempdb.dbo.T2
ON T2.[ID] = T1.[ID];
Devoluciones:
ID Col1 Col2 ID Col3 Col4
11 aa 2017-07-04 10:39:32.660 11 44465676-E8A1-4F38-B5B8-F50C63A947A4 454567678989
13 bb 2017-07-04 10:41:38.180 13 BFE43379-559F-4DAD-880B-B09D7ECA4914 12312312312234