Hey guys!
All good ?

Today I'm going to talk about a problem I recently encountered, where a routine used the sys.syscolumns catalog view to obtain information about the columns of a Fact table (BI) and from a certain point on, when trying to execute the SELECT * FROM sys.syscolumns query, SQL Server returned the following message:

Msg 220, Level 16, State 1, Line 1
Arithmetic overflow error for data type smallint, value = 40003.

This was the first time I saw a simple query in a bank catalog view return an error. So let's analyze our scenario:

    1) The error message tells us that there was an overflow in a smallint field, whose value would be 40003 (smallint limit: 32767)
    2) As it is a catalog view, there is no option to change it to increase the column size
    3) The columns of the sys.syscolumns view that have the type smallint are: xusertype, length, colid, xoffset, colstat, number, colorder, offset, usertype, prec
    4) Analyzing the smallint columns, we can observe that the only columns where we can influence the values ​​are the colid and colorder columns, which are column identifiers within the table (parent object) and column order identifier (generally the two columns have the same value), which can vary according to the number of columns in our table

Suspecting these columns and keeping in mind that they are filled with auto-increment, I imagined that for some reason, this BI routine could be deleting and recreating columns in the Fact table daily, causing this number to only increase and at a certain point, reach the limit of the smallint data type. I checked with the analyst who was responsible for the routine that presented this failure and he confirmed my suspicion. The routine really erases and creates columns daily.

After confirming the origin problem, we moved on to the solution for now, which was to create another table with the same structure as the original table, copy the data to this other table, delete the original table and rename the new table to the original name. This way, the colid and colorder columns of this table would be “reset” to 1, 2, 3… The final solution must be done in the routine, but at least, we were able to get the routine to work and not let other routines/users be unable to use this bank catalog view because of a table.

A quick solution that could also solve the problem for now would be to change the ETL routine to use one of the options below, which have the int type order definition column, whose limit is 2,147,483,647:

  • sys.all_columns (column_id)
  • sys.columns (column_id)
  • INFORMATION_SCHEMA.COLUMNS (ORDINAL_POSITION)

If you want to simulate this case, follow the script below:

-- Consultando a view
SELECT * FROM sys.syscolumns


-- Criando a estrutura da tabela
IF (OBJECT_ID('Testes.dbo.Estouro_Sys_Columns') IS NOT NULL) DROP TABLE Testes.dbo.Estouro_Sys_Columns
CREATE TABLE Testes.dbo.Estouro_Sys_Columns (
    Id INT IDENTITY(1,1),
    Nome VARCHAR(50),
    Tmp_Coluna1 VARCHAR(20),
    Tmp_Coluna2 VARCHAR(20),
    Tmp_Coluna3 VARCHAR(20),
    Tmp_Coluna4 VARCHAR(20)
)

CREATE CLUSTERED INDEX SK01_Id ON dbo.Estouro_Sys_Columns(Id)



-- Simulando a rotina sendo executada várias vezes

DECLARE @Contador INT = 1, @Total INT = 10000

WHILE(@Contador <= @Total)
BEGIN
    
    ALTER TABLE Testes.dbo.Estouro_Sys_Columns DROP COLUMN
        Tmp_Coluna1,
        Tmp_Coluna2,
        Tmp_Coluna3,
        Tmp_Coluna4
        
        
    ALTER TABLE Testes.dbo.Estouro_Sys_Columns ADD
        Tmp_Coluna1 VARCHAR(20),
        Tmp_Coluna2 VARCHAR(20),
        Tmp_Coluna3 VARCHAR(20),
        Tmp_Coluna4 VARCHAR(20)
        
    
    SET @Contador = @Contador + 1
    
END


-- Tentando consultar a view novamente
SELECT * FROM sys.syscolumns -- ERRO! Arithmetic overflow error for data type smallint, value = 40003.


-- CORRIGINDO O PROBLEMA - Criação da estrutura igual à tabela original
IF (OBJECT_ID('Testes.dbo.Estouro_Sys_Columns2') IS NOT NULL) DROP TABLE Testes.dbo.Estouro_Sys_Columns2
CREATE TABLE Testes.dbo.Estouro_Sys_Columns2 (
    Id INT IDENTITY(1,1),
    Nome VARCHAR(50),
    Tmp_Coluna1 VARCHAR(20),
    Tmp_Coluna2 VARCHAR(20),
    Tmp_Coluna3 VARCHAR(20),
    Tmp_Coluna4 VARCHAR(20)
)

CREATE CLUSTERED INDEX SK01_Id ON dbo.Estouro_Sys_Columns2(Id)


-- CORRIGINDO O PROBLEMA - Inserindo os dados da tabela (caso haja)
SET IDENTITY_INSERT dbo.Estouro_Sys_Columns2 ON

INSERT INTO dbo.Estouro_Sys_Columns2 ( 
    Id,
    Nome,
    Tmp_Coluna1,
    Tmp_Coluna2,
    Tmp_Coluna3,
    Tmp_Coluna4 
)
SELECT * FROM dbo.Estouro_Sys_Columns


-- CORRIGINDO O PROBLEMA - Apagando a tabela antiga e renomeando a nova
DROP TABLE dbo.Estouro_Sys_Columns
EXEC sp_rename 'dbo.Estouro_Sys_Columns2', 'Estouro_Sys_Columns'


-- Consultando a view
SELECT * FROM sys.syscolumns -- WHERE id = OBJECT_ID('dbo.Estouro_Sys_Columns')