SQL input parameters filtering on indexes are defaulting to nvarchar causing full table scans

Joe_Gleason_15473836 0
Viewed 37

When our applications are submitting SQL via ADO.Net, with input parameters, the parameter definitions are defaulting to nvarchar. If that field is defined as a varchar, and an index, on the database table, the index is not used resulting in a scan instead of a seek. We are converting from Teradata to SQL Server so this is a systemic issue at this point in the conversion. The applications team submitted this to me:

When we define anything as a String in Code, the ADO.Net provider automatically assumes that is a NVarchar in SQLServer.

One of their solutions is to only remedy this for tables over 1,000 rows. I think this is faulty on so many levels, but am looking for some additional input.

I am a Teradata DBA transitioning to a MSSQL DBA.

I would assume this setting in ADO.Net would be configurable. To me it is obvious that the Input parameter definition needs to match the field definition in the table, especially if it is part of an index resulting a a full table scan.

Can anybody help me with (1) how to set the input parameter definition to match the table definition?, and (2) if this is systemic, why would it be a bad idea to only fix those parameters and queries if the table is over 1,000 records in size?

query-optimization sql-server
1 Answers

As you can see in the code below, you can specify in that form the datatype, of the parameter

    connection.Open();
    SqlCommand command = new SqlCommand(null, connection);

    // Create and prepare an SQL statement.
    command.CommandText =
        "INSERT INTO Region (RegionID, RegionDescription) " +
        "VALUES (@id, @desc)";
    SqlParameter idParam = new SqlParameter("@id", SqlDbType.Int, 0);
    SqlParameter descParam =
        new SqlParameter("@desc", SqlDbType.Text, 100);
    idParam.Value = 20;
    descParam.Value = "First Region";
    command.Parameters.Add(idParam);
    command.Parameters.Add(descParam);

see Microsoft descriction of prepared statements

user_51935360
Related