Provide a way of detecting or specifying correct datatype for strings.

[aidapsibr]

Implicit casts from VARCHAR to NVARCHAR in the execution plan occur. Updates to the parameter definition from NVARCHAR to VARCHAR in the query, sees the index hit without the implicit cast, and the query reads drop significantly. Tested this on a few different queries and found SIGNIFICANT performance improvements by changing the parameter type.

In one test logical reads dropped from 10k+ to 22. In another (included below) logical reads dropped from 5.6 million to 3965, CPU dropped from 10k to 547, and duration dropped by a factor of 6

[aidapsibr]

Susanoo doesn't scan the tables or anything like that and I have avoided introducing attributes as they violate separation of concerns. Specifying a default is likely the best thing to do and allow overrides when needed.

[aidapsibr]

From a current parameter named String in Susanoo tests:

    DbType: String
    Direction: Input
    InternalMetaType: null
    IsNull: '((System.Data.SqlClient.SqlParameter)(info.Parameters[1])).IsNull' threw an exception of type 'System.NullReferenceException'
    IsNullable: false
    LocaleId: 0
    Offset: 0
    ParameterIsSqlType: false
    ParameterName: "String"
    ParameterNameFixed: "@String"
    Precision: 0
    PrecisionInternal: 0
    Scale: 0
    ScaleInternal: 0
    Size: 1
    SizeInferred: true
    SourceColumn: "Value"
    SourceColumnNullMapping: false
    SourceVersion: Default
    SqlDbType: NVarChar

[aidapsibr]

SqlServer ADO.NET defaults all DbType.String instances to NVARCHAR by default.

[porterchris]

.NET strings are unicode compatible so the conversion to NVARCHAR is a natural path. In a DB where NVARCHAR provides little or no value, it could be assumed that VARCHAR would be optimal. A global configuration value during initialization would allow the developer to define the format that they are using the DB (for the most part) without being too structured or rigid.