Very good point David. And it is in fact much worse for sp_executesql, since there it means that a new plan must be compiled (and cached) in order to execute a prepared statement that is exactly the same as a previous one except for casing. The reason is that for statements it is the entire statement that is matched (with a hashing function) to the one stored in the cache. For procs it is only the proc name, so SQL Server can much easier gather the correctly cased name (in fact it already has got it since it needed the proc definition to compile it) for storing. For a statement it would need to gather (and replace) all the object names used in the statement, plus use one case for all keywords. Using the following test script you can see that it does not do that.

----------------------------------

use northwind

dbcc freeproccache

declare @sql1 nvarchar(500)

set @sql1 = N'select * from dbo.Orders'

declare @sql2 nvarchar(500)

set @sql2 = N'select * FROM dbo.Orders'

exec sp_executesql @sql1

exec sp_executesql @sql2

exec sp_executesql @sql1

exec sp_executesql @sql2

exec sp_executesql @sql1

exec sp_executesql @sql2

-- Note the two plans for the same statement only differing in case for the keyword FROM

-- Also note how UseCounts show each one is used three times

-- Finally, also note that the two plans are stored in different hash buckets, so they cannot be matched of course

select * from master..syscacheobjects

----------------------------------