In some scenarios like ETL or load data into a DB, we need to insert the data into a staging table, where all the columns are String data type and on the next step to cleanse and manipulate the data. I heard a discussion about the data type of the columns in the staging table – NVARCHAR(255) or NVARCHAR(MAX). Some of the arguments were:
- Statement 1 – NVARCHAR(MAX) will return error message if we have more than 20 columns
- Statement 2 – NVARCHAR(MAX) takes much more disk space
I decided to have a little fun and reproduce the scenario when we insert random data in both tables – NVARCHAR(255) and NVARCHAR(MAX)
The steps are:
- Create databases – DB_255 and DB_MAX
- Create tables with 100 columns in each DB – Table_255 and Table_MAX
- Populate the tables with 1000 random rows
- Duplicate the rows until we have 32,000 rows in each table
- Check the size of the .mdf for each DB
Create databases
USE [master]; GO IF (DB_ID('DB_255') IS NOT NULL) BEGIN ALTER DATABASE DB_255 SET SINGLE_USER WITH ROLLBACK IMMEDIATE; DROP DATABASE DB_255; END GO CREATE DATABASE DB_255; GO IF (DB_ID('DB_MAX') IS NOT NULL) BEGIN ALTER DATABASE DB_MAX SET SINGLE_USER WITH ROLLBACK IMMEDIATE; DROP DATABASE DB_MAX; END GO CREATE DATABASE DB_MAX; GO
Create test objects
-- 255 USE DB_255; GO -- Table_255 IF (OBJECT_ID('dbo.Table_255', 'U') IS NOT NULL) BEGIN DROP TABLE dbo.Table_255; END GO DECLARE @Counter TINYINT = 1 , @DynamicSQL VARCHAR(MAX); SET @DynamicSQL = ' CREATE TABLE dbo.Table_255 ('; WHILE (@Counter <= 100) BEGIN SET @DynamicSQL += ' Col_' + CAST(@Counter AS VARCHAR(3)) + ' NVARCHAR(255),'; SET @Counter += 1; END SET @DynamicSQL = REVERSE(SUBSTRING(REVERSE(@DynamicSQL), 2, (LEN(@DynamicSQL) - 1))) SET @DynamicSQL += ' ); '; EXEC (@DynamicSQL); GO -- ViewRandHelper_255 IF (OBJECT_ID('dbo.ViewRandHelper_255', 'V') IS NOT NULL) BEGIN DROP VIEW dbo.ViewRandHelper_255; END GO CREATE VIEW dbo.ViewRandHelper_255 AS SELECT RAND() AS R; GO -- FunctionRandonString_255 IF (OBJECT_ID('dbo.FunctionRandonString_255', 'FN') IS NOT NULL) BEGIN DROP FUNCTION dbo.FunctionRandonString_255; END GO CREATE FUNCTION dbo.FunctionRandonString_255(@StringLength AS INT) RETURNS VARCHAR(MAX) AS BEGIN DECLARE @String VARCHAR(100) , @Characters VARCHAR(100) , @Counter INT; SELECT @Characters = '' , @Counter = 48; WHILE @Counter <= 122 BEGIN SET @Characters = @Characters + CAST(CHAR(@Counter) AS CHAR(1)); SET @Counter += 1; END SELECT @Counter = 0 , @String = ''; WHILE (@Counter < @StringLength) BEGIN SET @String = @String + SUBSTRING(@Characters, CAST(CEILING((SELECT R FROM ViewRandHelper_255) * LEN(@Characters)) AS INT), 1); SET @Counter += 1; END RETURN @String END GO -- MAX USE DB_MAX; GO -- Table_MAX IF (OBJECT_ID('dbo.Table_MAX', 'U') IS NOT NULL) BEGIN DROP TABLE dbo.Table_MAX; END GO DECLARE @Counter TINYINT = 1 , @DynamicSQL VARCHAR(MAX); SET @DynamicSQL = ' CREATE TABLE dbo.Table_MAX ('; WHILE (@Counter <= 100) BEGIN SET @DynamicSQL += ' Col_' + CAST(@Counter AS VARCHAR(3)) + ' NVARCHAR(MAX),'; SET @Counter += 1; END SET @DynamicSQL = REVERSE(SUBSTRING(REVERSE(@DynamicSQL), 2, (LEN(@DynamicSQL) - 1))) SET @DynamicSQL += ' ); '; EXEC (@DynamicSQL); GO -- ViewRandHelper_MAX IF (OBJECT_ID('dbo.ViewRandHelper_MAX', 'V') IS NOT NULL) BEGIN DROP VIEW dbo.ViewRandHelper_MAX; END GO CREATE VIEW dbo.ViewRandHelper_MAX AS SELECT RAND() AS R; GO -- FunctionRandonString_MAX IF (OBJECT_ID('dbo.FunctionRandonString_MAX', 'FN') IS NOT NULL) BEGIN DROP FUNCTION dbo.FunctionRandonString_MAX; END GO CREATE FUNCTION dbo.FunctionRandonString_MAX(@StringLength AS INT) RETURNS VARCHAR(MAX) AS BEGIN DECLARE @String VARCHAR(100) , @Characters VARCHAR(100) , @Counter INT; SELECT @Characters = '' , @Counter = 48; WHILE @Counter <= 122 BEGIN SET @Characters = @Characters + CAST(CHAR(@Counter) AS CHAR(1)); SET @Counter += 1; END SELECT @Counter = 0 , @String = ''; WHILE (@Counter < @StringLength) BEGIN SET @String = @String + SUBSTRING(@Characters, CAST(CEILING((SELECT R FROM ViewRandHelper_MAX) * LEN(@Characters)) AS INT), 1); SET @Counter += 1; END RETURN @String END GO
Check test objects
-- 255 USE DB_255; GO SELECT 'Top 2' AS RowType_255 , T1.ColumnName AS ColumnName_255 , T1.ColumnID AS ColimnID_255 , T1.TypeName AS TypeName_255 , T1.TypeMaxLength AS TypeMaxLength_255 FROM ( SELECT TOP 2 C.[name] AS ColumnName , C.[column_id] AS ColumnID , T.[name] AS TypeName , C.[max_length] AS TypeMaxLength FROM sys.columns AS C JOIN sys.types AS T ON C.user_type_id = T.user_type_id WHERE OBJECT_NAME(C.[object_id]) = 'Table_255' ORDER BY C.[column_id] ) AS T1 UNION ALL SELECT 'Bottom 2' , T2.ColumnName AS ColumnName_255 , T2.ColumnID AS ColimnID_255 , T2.TypeName AS TypeName_255 , T2.TypeMaxLength AS TypeMaxLength_255 FROM ( SELECT TOP 2 C.[name] AS ColumnName , C.[column_id] AS ColumnID , T.[name] AS TypeName , C.[max_length] AS TypeMaxLength FROM sys.columns AS C JOIN sys.types AS T ON C.user_type_id = T.user_type_id WHERE OBJECT_NAME(C.[object_id]) = 'Table_255' ORDER BY C.[column_id] DESC ) AS T2 ORDER BY ColumnID; SELECT dbo.FunctionRandonString_255(212) As RandomString_255; GO -- MAX USE DB_MAX; GO SELECT 'Top 2' AS RowType_MAX , T1.ColumnName AS ColumnName_MAX , T1.ColumnID AS ColimnID_MAX , T1.TypeName AS TypeName_MAX , T1.TypeMaxLength AS TypeMaxLength_MAX FROM ( SELECT TOP 2 C.[name] AS ColumnName , C.[column_id] AS ColumnID , T.[name] AS TypeName , C.[max_length] AS TypeMaxLength FROM sys.columns AS C JOIN sys.types AS T ON C.user_type_id = T.user_type_id WHERE OBJECT_NAME(C.[object_id]) = 'Table_MAX' ORDER BY C.[column_id] ) AS T1 UNION ALL SELECT 'Bottom 2' , T2.ColumnName AS ColumnName_MAX , T2.ColumnID AS ColimnID_MAX , T2.TypeName AS TypeName_MAX , T2.TypeMaxLength AS TypeMaxLength_MAX FROM ( SELECT TOP 2 C.[name] AS ColumnName , C.[column_id] AS ColumnID , T.[name] AS TypeName , C.[max_length] AS TypeMaxLength FROM sys.columns AS C JOIN sys.types AS T ON C.user_type_id = T.user_type_id WHERE OBJECT_NAME(C.[object_id]) = 'Table_MAX' ORDER BY C.[column_id] DESC ) AS T2 ORDER BY ColumnID; SELECT dbo.FunctionRandonString_MAX(212) As RandomString_MAX; GO

Populate test tables
-- 255 USE DB_255; GO DECLARE @Counter TINYINT = 1 , @DynamicSQL VARCHAR(MAX); SET @DynamicSQL = ' INSERT Table_255 ('; WHILE (@Counter <= 100) BEGIN SET @DynamicSQL += ' Col_' + CAST(@Counter AS VARCHAR(3)) + ','; SET @Counter += 1; END SET @DynamicSQL = REVERSE(SUBSTRING(REVERSE(@DynamicSQL), 2, (LEN(@DynamicSQL) - 1))) SET @DynamicSQL += ' ) SELECT TOP 1000 '; SELECT @Counter = 1; WHILE (@Counter <= 100) BEGIN SET @DynamicSQL += ' dbo.FunctionRandonString_255(212),'; SET @Counter += 1; END SET @DynamicSQL = REVERSE(SUBSTRING(REVERSE(@DynamicSQL), 2, (LEN(@DynamicSQL) - 1))) SET @DynamicSQL += ' FROM sys.all_objects AS T1 CROSS JOIN sys.all_objects AS T2 '; EXEC (@DynamicSQL); GO INSERT dbo.Table_255 SELECT * FROM dbo.Table_255; GO 5 -- MAX USE DB_MAX; GO DECLARE @Counter TINYINT = 1 , @DynamicSQL VARCHAR(MAX); SET @DynamicSQL = ' INSERT Table_MAX ('; WHILE (@Counter <= 100) BEGIN SET @DynamicSQL += ' Col_' + CAST(@Counter AS VARCHAR(3)) + ','; SET @Counter += 1; END SET @DynamicSQL = REVERSE(SUBSTRING(REVERSE(@DynamicSQL), 2, (LEN(@DynamicSQL) - 1))) SET @DynamicSQL += ' ) SELECT TOP 1000 '; SELECT @Counter = 1; WHILE (@Counter <= 100) BEGIN SET @DynamicSQL += ' dbo.FunctionRandonString_MAX(212),'; SET @Counter += 1; END SET @DynamicSQL = REVERSE(SUBSTRING(REVERSE(@DynamicSQL), 2, (LEN(@DynamicSQL) - 1))) SET @DynamicSQL += ' FROM sys.all_objects AS T1 CROSS JOIN sys.all_objects AS T2 '; EXEC (@DynamicSQL); GO INSERT dbo.Table_MAX SELECT * FROM dbo.Table_MAX; GO 5
Check test tables columns and rows count
-- 255 USE DB_255; GO SELECT TOP 2 * FROM dbo.Table_255; GO SELECT COUNT(Col_1) AS Count_255 FROM dbo.Table_255; GO -- MAX USE DB_MAX; GO SELECT TOP 2 * FROM dbo.Table_MAX; GO SELECT COUNT(Col_1) As Count_MAX FROM dbo.Table_MAX; GO

Check file size
SELECT DB_NAME([database_id]) AS [Database Name] , [type_desc] AS [File Type] , [name] AS [File Name] , physical_name AS [File Path] , (size * 8) / 1024 [File Size (MB)] FROM sys.master_files WHERE DB_NAME([database_id]) IN ('DB_255', 'DB_MAX') GO

Delete databases
USE [master]; GO IF (DB_ID('DB_255') IS NOT NULL) BEGIN ALTER DATABASE DB_255 SET SINGLE_USER WITH ROLLBACK IMMEDIATE; DROP DATABASE DB_255; END GO IF (DB_ID('DB_MAX') IS NOT NULL) BEGIN ALTER DATABASE DB_MAX SET SINGLE_USER WITH ROLLBACK IMMEDIATE; DROP DATABASE DB_MAX; END GO
Conclusion:
- Both statements failed
- There is a hint in the random function – we can’t use RAND() inside a function. The runaround is to create a view that selects RAND() and use the view inside the function
- You can use the function string as inspiration to create your own one
Keep it simple :-)