Best Practices: Defining Explicit Length for Varchar/Nvarchar

SA0080 : Do not use VARCHAR or NVARCHAR data types without specifying length. Level: Warning

When using varchar/nvarchar it should be explicitly defined. This can be a very nasty bug to track down as often nothing will be thrown if not checked in an application. Instead, ensure your script explicitly defines the smallest length that fits your requirements. The reason I rate this as a very dangerous practice, is that no error is thrown. Instead, the results being returned will be shorter than expected and if validation checks aren't implemented this behavior can lead to partial results returned and used. Make sure to always explictly define length!

Here's an short example script that demonstrates the behavior.

	if object_id('tempdb..#temp', 'U') is not null
	begin
	print 'Dropped table #temp per existed';
	drop table #temp;
	end;
	-- Helpful Snippet from Stackoverflow: "Crafty DBA" who provided this on
	-- sql server - create a list of the alphabet via SQL - Stack Overflow http://bit.ly/2r0f6IO
	declare @val varchar(max);
	with cte_tally as (
	select n = row_number() over (order by (select 1)) from sys.all_columns
	)
	select
	alpha = cast(char(cte_tally.n) as varchar(max))
	into
	#temp
	from
	cte_tally
	where (cte_tally.n > 64
	and cte_tally.n < 91
	)
	or (cte_tally.n > 96
	and cte_tally.n < 123
	);
	select
	@val =
	(select * from #temp for xml path(''), type).value('.', 'varchar(max)');
	select
	QueryDescription = 'Implicit Truncation. You just caused nuclear meltdown'
	,Val = cast(@val as varchar)
	union all
	select
	QueryDescription = 'Explicit 30 Length Should be Same'
	,Val = cast(@val as varchar(30))
	union all
	select QueryDescription = 'Max Length', Val = cast(@val as varchar(max));

view raw ImplicitTruncationDemo.sql hosted with ❤ by GitHub