Back in the days of C (before C#) and in environments where memory was very limited or expensive (or both), there was a benefit to reducing repetitive use of a literal as it would reduce memory usage and executable size, as each copy of the string would appear in the compiled executable. (In C# I think it is a best practice to use a resource file, but I could be wrong.)
In modern times, is it better in a lengthy T-SQL stored procedure to use a literal many times or to assign the literal to a variable and then use that in the code?
For example, let's assume with have a stored procedure where there are dozens of queries that cannot be consolidated due to different joins and/or additional WHERE clauses where we have in each, and there is an index with [Key] as the key column along with any other selective column used in an equality predicate, and [Type] and all other needed columns are INCLUDE columns in an non-clustered index:
WHERE x.[Key] = @Value1 AND x.[Type] NOT IN ('Project', <list of a dozen literals>)
Would it be better to have something like:
DECLARE @Project CHAR(7) = 'Project'; <repeat for each of a dozen literals>
and then have
WHERE x.[Key] = @Value1 AND x.[Type] NO IN (@Project, <list of a dozen variables>)
in each query? Use of the variables would prevent having useful statistics for the optimizer, but in the case of a NOT IN with many entries it probably couldn't make us of them anyway.
What would be the advantage(s) or disadvantage(s) of each approach?
Does the answer change if it is an IN instead of a NOT IN (with the understanding that when I use "dozen", I mean approximately 12 but definitely 15 or fewer)?
