Navigation

Blog Home
ReSQueL (NL)
ReSQueL (EN)


Editor

Sign In

Subscribe to this blog!

RSS 2.0 | Atom 1.0 | CDF

Recent posts



# Tuesday, October 25, 2011
Report how much (var)char you actually use
 #
 

Character based data types, especially the var types varchar and nvarchar allow to declare a really big amount and just use a tiny bit and it doesn’t even hurt in SQL Server. However, it may in the application. Today I was working with a customer for the implementation of some third party software that would be doing analysis on the data. It would load the really over-dimensioned (n)varchar types in something like a (n)char.... you get the picture, huge amounts of wasted space. So from the databases side, a bit of restructuring was desirable. To see how much we can actually save, I created query to report on the character usage;

CREATE TABLE ##char_columns (
[SCHEMA] VARCHAR(128) NULL,
[TABLE] VARCHAR(128) NULL,
[COLUMN] VARCHAR(128) NULL,
[data_type] VARCHAR(128) NULL,
[max_length] INT NULL,
[is_replicated] BIT NULL,
[actual_max_length] INT NULL,
[row_count] BIGINT NULL,
[not_null_count] BIGINT NULL)
INSERT ##char_columns
SELECT s.name AS 'schema' ,
t.name AS 'table' ,
c.name AS 'column' ,
y.name AS 'data_type' ,
c.max_length ,
c.is_replicated ,
NULL ,
NULL ,
NULL
FROM sys.tables t
INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
INNER JOIN sys.columns c ON t.object_id = c.object_id
INNER JOIN sys.types y ON c.system_type_id = y.system_type_id
WHERE t.type = 'U' AND y.name IN ('varchar','char','nchar','nvarchar')
WHILE (SELECT COUNT(*) FROM ##char_columns WHERE actual_max_length IS NULL) > 0
BEGIN TRY
DECLARE @sql NVARCHAR(4000) , @actual_max_length INT , @column VARCHAR(128) , @schema VARCHAR(128) , @table VARCHAR(128)
SELECT TOP 1 @schema = [SCHEMA], @table = [TABLE], @column = [COLUMN]
FROM ##char_columns
WHERE actual_max_length IS NULL
-- Set the actual_max_length to -1 if a column has only null values or no rows.
SELECT @sql = 'UPDATE ##char_columns
SET [actual_max_length] = (SELECT ISNULL(MAX(LEN([' + @column + '])),-1) FROM [' + @schema + '].[' + @table + '])
,       [row_count] = (SELECT COUNT_BIG(*) FROM [' + @schema + '].[' + @table + '])
,       [not_null_count] = (SELECT COUNT_BIG([' + @column + ']) FROM [' + @schema + '].[' + @table + ']) 
WHERE [schema] = ''' + @schema + '''
       AND [table] = ''' + @table + '''
       AND [column] = ''' + @column + ''''
EXEC(@sql)
END TRY
BEGIN CATCH
DECLARE @err_num NVARCHAR(20), @err_msg NVARCHAR(2048), @full_msg NVARCHAR(2048)
SELECT @err_num = ERROR_NUMBER(), @err_msg = ERROR_MESSAGE()
SELECT @full_msg = 'ERROR: ' + @err_num + ' DESCR: ' + @err_msg + ' QUERY: ' + @sql
RAISERROR(@full_msg,16,0)
BREAK
END CATCH
SELECT * FROM ##char_columns
DROP TABLE ##char_columns

Be aware that varchar(max) and nvarchar(max) will give a max_length of –1 and that actual_max_length is –1 if no rows exist in the table or the column only contains NULLs (refer to the row_count and not_null_count to determine the situation).

SQL Server
Tuesday, October 25, 2011 10:37:16 PM (W. Europe Daylight Time, UTC+02:00)
Stan Segers  |  Disclaimer  |  Comments [0]  |  Related posts:
Fix a failing SQL Server installation on Windows 11
Does this GUID exist in my database
Disk space query
Missing checkpoints (windows cluster) for SQL Server 2008 R2
SQL Server 2005 cluster modifications failing
What node was SQL Server running on?
Comments are closed.