A quick review of a customer database revealed many inappropriate maximum lengths on character columns. When dealing with fixed width columns, grossly oversized columns negatively impact performance because fewer rows fit into a page, which means more pages need to be read. Here's a query you can run to compare actual data lengths to maximum defined column widths.
DECLARE @TABLE_SCHEMA NVARCHAR(128);
DECLARE @TABLE_NAME NVARCHAR(128);
DECLARE @COLUMN_NAME NVARCHAR(128);
DECLARE @PARMS NVARCHAR(100);
DECLARE @DATA_TYPE NVARCHAR(128);
DECLARE @CHARACTER_MAXIMUM_LENGTH INT;
DECLARE @MAX_LEN NVARCHAR(10);
DECLARE @TSQL NVARCHAR(4000);
DECLARE DDLCursor CURSOR FAST_FORWARD FOR
SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME IN
(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE')
AND DATA_TYPE IN ('char','nchar','varchar','nvarchar')
AND CHARACTER_MAXIMUM_LENGTH > 1
OPEN DDLCursor;
SET @PARMS = N'@MAX_LENout nvarchar(10) OUTPUT';
CREATE TABLE #space (
TABLE_SCHEMA NVARCHAR(128) NOT NULL
, TABLE_NAME NVARCHAR(128) NOT NULL
, COLUMN_NAME NVARCHAR(128) NOT NULL
, DATA_TYPE NVARCHAR(128) NOT NULL
, CHARACTER_MAXIMUM_LENGTH INT NOT NULL
, ACTUAL_MAXIMUM_LENGTH INT NOT NULL
);
-- Perform the first fetch.
FETCH NEXT FROM DDLCursor
INTO @TABLE_SCHEMA, @TABLE_NAME, @COLUMN_NAME, @DATA_TYPE, @CHARACTER_MAXIMUM_LENGTH;
-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
SET @tsql = 'select @MAX_LENout = cast(max(len(isnull('
+ QUOTENAME(@COLUMN_NAME) + ',''''))) as nvarchar(10)) from '
+ QUOTENAME(@TABLE_SCHEMA) + '.' + QUOTENAME(@TABLE_NAME);
EXEC sp_executesql @tsql, @PARMS, @MAX_LENout = @MAX_LEN OUTPUT;
IF CAST(@MAX_LEN as int) < @CHARACTER_MAXIMUM_LENGTH -- not interested if lengths match
BEGIN
SET @tsql = 'insert into #space values ('''
+ @TABLE_SCHEMA + ''','''
+ @TABLE_NAME + ''','''
+ @COLUMN_NAME + ''','''
+ @DATA_TYPE + ''','
+ CAST(@CHARACTER_MAXIMUM_LENGTH as nvarchar(10)) + ','
+ @MAX_LEN + ')';
EXEC sp_executesql @tsql;
END;
-- This is executed as long as the previous fetch succeeds.
FETCH NEXT FROM DDLCursor INTO @TABLE_SCHEMA, @TABLE_NAME, @COLUMN_NAME, @DATA_TYPE, @CHARACTER_MAXIMUM_LENGTH;
END;
CLOSE DDLCursor;
DEALLOCATE DDLCursor;
select * from #space