I realized I wasn’t! We run a couple of systems that I know stick a mass of records through on a daily basis. Better start doing this then or I might end up doing a whoopsie!

Here’s a script I’ve quickly knocked up to make checking this simple. This script will check the specified tables TINYINT, SMALLINT, INT & BIGINT values against the allowable maximum.

A few things to note about this script;

  • Don’t run on a live system.
  • To check all tables in a database you can remove the line specifying TABLE_NAME in the cursor. Be aware this may take a long time and stress your server.
  • % are expressed as a value between 0 and 1 i.e. 0.5 = 50%.
  • % may be slightly off but should not matter considering the scales involved.
  • % also ignores negative numbers since we typically start IDENTITY columns at 1.
  • Change @used_warn_level_percent to a sensible value according to your system.
  • If you observe “Warning: Null value is eliminated by an aggregate or other SET operation.” then your table has a column only containing nulls.
DECLARE @table_schema VARCHAR(50),
		@table_name VARCHAR(50),
		@column_name VARCHAR(50),
		@data_type VARCHAR(30),
		@max_id BIGINT,
		@used_warn_level_percent DECIMAL(4,2);

SET @used_warn_level_percent = 49.0;

SET NOCOUNT ON;

DECLARE myCursor CURSOR LOCAL FAST_FORWARD FOR SELECT TOP 10 TABLE_SCHEMA,
													   TABLE_NAME,
													   COLUMN_NAME,
													   DATA_TYPE
												FROM INFORMATION_SCHEMA.COLUMNS
												WHERE DATA_TYPE IN ('tinyint', 'smallint', 'int', 'bigint')
												AND TABLE_CATALOG = DB_NAME()
												AND TABLE_NAME = 'test_numbers';

OPEN myCursor;
FETCH NEXT FROM myCursor INTO @table_schema,
							  @table_name,
							  @column_name,
							  @data_type;

DECLARE @max_id_table TABLE
(
	id BIGINT
);

WHILE (@@FETCH_STATUS = 0)
BEGIN

	INSERT INTO @max_id_table
	EXEC('SELECT MAX(' + @column_name + ') FROM ' + @table_schema + '.' + @table_name);

	SET @max_id = (SELECT id FROM @max_id_table);

	IF(@data_type = 'bigint')
	BEGIN
		IF(@max_id > FLOOR(((9223372036854775807 / 100.0) * @used_warn_level_percent)))
		BEGIN
			PRINT @column_name + '.' + @table_schema + '.' + @table_name + ' max id is greater than warning level: ' + CONVERT(VARCHAR(30), @max_id);
		END
		PRINT @column_name + '.' + @table_schema + '.' + @table_name + ': ' + CONVERT(VARCHAR(30), CAST(@max_id AS FLOAT) / CAST(9223372036854775807 AS FLOAT)) + ' int space used.';
	END
	ELSE IF (@data_type = 'int')
	BEGIN
		IF(@max_id > FLOOR(((2147483647 / 100.0) * @used_warn_level_percent)))
		BEGIN
			PRINT @column_name + '.' + @table_schema + '.' + @table_name + ' max id is greater than warning level: ' + CONVERT(VARCHAR(30), @max_id);
		END
		PRINT @column_name + '.' + @table_schema + '.' + @table_name + ': ' + CONVERT(VARCHAR(30), CAST(@max_id AS FLOAT) / CAST(2147483647 AS FLOAT)) + ' int space used.';
	END
	ELSE IF (@data_type = 'smallint')
	BEGIN
		IF(@max_id > FLOOR(((32767 / 100.0) * @used_warn_level_percent)))
		BEGIN
			PRINT @column_name + '.' + @table_schema + '.' + @table_name + ' max id is greater than warning level: ' + CONVERT(VARCHAR(30), @max_id);
		END
		PRINT @column_name + '.' + @table_schema + '.' + @table_name + ': ' + CONVERT(VARCHAR(30), CAST(@max_id AS FLOAT) / CAST(32767 AS FLOAT)) + ' int space used.';
	END
	ELSE IF (@data_type = 'tinyint')
	BEGIN
		IF(@max_id > FLOOR(((255 / 100.0) * @used_warn_level_percent)))
		BEGIN
			PRINT @column_name + '.' + @table_schema + '.' + @table_name + ' max id is greater than warning level: ' + CONVERT(VARCHAR(30), @max_id);
		END
		PRINT @column_name + '.' + @table_schema + '.' + @table_name + ': ' + CONVERT(VARCHAR(30), CAST(@max_id AS FLOAT) / CAST(255 AS FLOAT)) + ' int space used.';
	END

	FETCH NEXT FROM myCursor INTO @table_schema,
								  @table_name,
								  @column_name,
								  @data_type;

	DELETE FROM @max_id_table;

END;

CLOSE myCursor;
DEALLOCATE myCursor;

Now we’re ready for testing!

CREATE TABLE test_numbers
(
	t_int TINYINT,
	s_int SMALLINT,
	i_int INT,
	b_int BIGINT
);

-- 100% usage
INSERT INTO test_numbers VALUES (255, 32767, 2147483647, 9223372036854775807);

Run the script and we should be warned!

t_int.dbo.test_numbers max id is greater than warning level: 255
t_int.dbo.test_numbers: 1 int space used.
s_int.dbo.test_numbers max id is greater than warning level: 32767
s_int.dbo.test_numbers: 1 int space used.
i_int.dbo.test_numbers max id is greater than warning level: 2147483647
i_int.dbo.test_numbers: 1 int space used.
b_int.dbo.test_numbers max id is greater than warning level: 9223372036854775807
b_int.dbo.test_numbers: 1 int space used.

TRUNCATE TABLE test_numbers;
-- 99% (ish)
INSERT INTO test_numbers VALUES (252, 32439, 2126008810, 9131138316486228048);

t_int.dbo.test_numbers max id is greater than warning level: 252
t_int.dbo.test_numbers: 0.988235 int space used.
s_int.dbo.test_numbers max id is greater than warning level: 32439
s_int.dbo.test_numbers: 0.98999 int space used.
i_int.dbo.test_numbers max id is greater than warning level: 2126008810
i_int.dbo.test_numbers: 0.99 int space used.
b_int.dbo.test_numbers max id is greater than warning level: 9131138316486228048
b_int.dbo.test_numbers: 0.99 int space used.

And finally;

TRUNCATE TABLE test_numbers ;
-- 50% (ish)
INSERT INTO test_numbers VALUES (127, 16383, 1073741823, 4611686018427387903);

t_int.dbo.test_numbers max id is greater than warning level: 127
t_int.dbo.test_numbers: 0.498039 int space used.
s_int.dbo.test_numbers max id is greater than warning level: 16383
s_int.dbo.test_numbers: 0.499985 int space used.
i_int.dbo.test_numbers max id is greater than warning level: 1073741823
i_int.dbo.test_numbers: 0.5 int space used.
b_int.dbo.test_numbers max id is greater than warning level: 4611686018427387903
b_int.dbo.test_numbers: 0.5 int space used.

-- clean up
DROP TABLE test_numbers;