November 10, 2020 at 8:26 pm
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 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? My first post so please have mercy! 🙂
November 10, 2020 at 8:29 pm
you need to explicitly set the parameters db datatype - see https://stackoverflow.com/questions/19891999/how-do-i-force-sqlcommand-to-not-encode-parameters-as-unicode-nvarchar/19892033 for an example
November 10, 2020 at 8:35 pm
I think you're correct, you should do this for all tables. No sense wasting resources scanning a 900-row table either. Of course that 900 rows could be far larger than another table with 20K rows, depending on row size.
Taking a step back, it would be even more efficient to change the columns to varchar if they don't need to be nvarchar, since nvarchars use twice as many bytes!
In passing, I'll note that the other way is not usually a problem. That is, a varchar parameter against an nvarchar column, the param should be changed to nvarchar implicitly and the index should still be able to be used.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
November 10, 2020 at 9:24 pm
I think you're correct, you should do this for all tables. No sense wasting resources scanning a 900-row table either. Of course that 900 rows could be far larger than another table with 20K rows, depending on row size.
Taking a step back, it would be even more efficient to change the columns to varchar if they don't need to be nvarchar, since nvarchars use twice as many bytes!
In passing, I'll note that the other way is not usually a problem. That is, a varchar parameter against an nvarchar column, the param should be changed to nvarchar implicitly and the index should still be able to be used.
from the OP the tables are varchar (as expected) but the parameters are being defined, by default, as NVARCHAR - this is how C# behaves when not specifically told about the correct datatype.
November 10, 2020 at 9:30 pm
What is the best way for C# to specifically be told the correct datatype? Is this a configuration setting or is this a setting that must be set, and updated, for every affected query being submitted with the incorrect definitions?
November 10, 2020 at 11:32 pm
it is on each query as per example on the link I supplied
November 10, 2020 at 11:40 pm
Thank you Frederico! I realized it was explained in the link right after I hit reply. I appreciate all your help!
November 11, 2020 at 12:13 am
ScottPletcher wrote:I think you're correct, you should do this for all tables. No sense wasting resources scanning a 900-row table either. Of course that 900 rows could be far larger than another table with 20K rows, depending on row size.
Taking a step back, it would be even more efficient to change the columns to varchar if they don't need to be nvarchar, since nvarchars use twice as many bytes!
In passing, I'll note that the other way is not usually a problem. That is, a varchar parameter against an nvarchar column, the param should be changed to nvarchar implicitly and the index should still be able to be used.
from the OP the tables are varchar (as expected) but the parameters are being defined, by default, as NVARCHAR - this is how C# behaves when not specifically told about the correct datatype.
I now realize I wasn't fully clear.
What I meant by "you should do this for all tables" was that OP should correct the data type of params coming in for all tables, not just those of 1000 rows or more, like some of his co-workers suggested. Tell C# the correct data type. That I do not know how to do, as I'm now a DBA and not a developer any longer.
The other part was that, if some tables do have nvarchar rather than varchar, review them to see if nvarchar is really necessary, and, if not, change them to varchar. Even if this means they have to also change the C# data type for those tables.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
November 11, 2020 at 12:36 am
Thank you Scott.... I knew what you meant. These are my feelings as well. I'm just trying to see what others think as well. Just out of curiosity, what is an OP?
November 11, 2020 at 8:52 pm
OP = "original poster", i.e., the person that originally asked the q (or, rarely used and only by some people, "original post")
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply