Is there any performance implication from using varchar(max) over varchar(n) in cast/convert?

All we need is an easy explanation of the problem, so here it is.

Consider the following expression, which truncates (does not round) the milliseconds from a date-time value:

declare @now datetime2 = sysdatetime();
select @now;
select convert(datetime2, convert(varchar(20), @now, 120));

-- Output
2021-07-30 09:38:33.5566666
2021-07-30 09:38:33.0000000

Notice the varchar(20). I don’t like that specific length value because if I should change my datatypes there could be data loss:

declare @now datetimeoffset = sysdatetimeoffset() at time zone 'Pacific Standard Time';
select @now;
select convert(datetimeoffset, convert(varchar(20), @now, 120));

-- Output
2021-07-30 02:39:12.7200000 -07:00
2021-07-30 02:39:12.0000000 +00:00 -- oops, we lost the time zone too!

Hence I’d much rather use the following:

declare @now datetimeoffset = sysdatetimeoffset() at time zone 'Pacific Standard Time';
select @now;
select convert(datetimeoffset, convert(varchar(max), @now, 120)); -- note MAX not N

-- Output
2021-07-30 02:41:16.4566666 -07:00
2021-07-30 02:41:16.0000000 -07:00

My question is, is there any sort of meaningful performance implication in using varchar(max) over varchar(N) – including but not limited to memory allocations?

I’m aware there are implications for query performance if using (max) datatypes over (N) datatypes in predicates, but in my particular examples I’m not doing that – just allocating the varchars then throwing them away after converting them back to the desired datatype.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

First off, to answer your question:

Yes, it can affect performance, as space needs to be allocated to hold large values in the query engine.

In your case, you could also use a suitably large size such as varchar(50) which would easily hold whatever you needed.

But you shouldn’t be doing any of this in the first place.

When rounding dates, you should not convert to varchar and back, as this has poorer performance, and issues involving culture/style.

Instead, use standard rounding techniques:

declare @now datetimeoffset = sysdatetimeoffset() at time zone 'Pacific Standard Time';
declare @epoch datetimeoffset = CAST(@now AS date);
select dateadd(
    datediff(second, @epoch, @now at time zone 'utc'),
    @epoch) at time zone 'Pacific Standard Time';

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from or, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply