I've been reading up on query plans and how to minimise duplicate plans being created by SQL Server for the same basic query. For example, if I understand correctly, sending both these query strings will result in 2 different query plans:
"SELECT FirstName FROM Members WHERE LastName = 'Lee'"
"SELECT FirstName FROM Members WHERE LastName = 'MacGhilleseatheanaich'"
Using a stored procedure avoids this, as the query plan is the same, and "LastName" is passed as a variable, eg:
CREATE PROCEDURE sp_myStoredProcedure
@LastName varchar(100)
AS
SELECT FirstName FROM Members WHERE LastName = @LastName
Go
Now, my question is whether the same applies to the Command object (eg. SQLClient.SQLCommand in ADO.NET). The reason I ask is that string parameters don't have a defined max length, as in the code above. Take the following code:
MyCmd.CommandText = "SELECT FirstName FROM Members WHERE LastName = @LastName"
MyCmd.Parameters.AddWithValue("@LastName", "Lee")
Then later:
MyCmd.Parameters.Clear()
MyCmd.Parameters.AddWithValue("@LastName", "MacGhilleseatheanaich")
Since @LastName hasn't been declared to SQL Server as having a defined maximum length, will SQL Server create a new query plan for every different value when I execute the command this way?
