Implementing variable character encoding for strings

[shueybubbles]

This discussion is to generate a proper interface to implement #40.

Problem statement

Go-mssqldb sends all string parameters as UTF16 strings, causing SQL Server to choose poor execution plans when the parameter type is a varchar type.

Solution options

A connection string parameter to control encoding of all strings

The property name could be stringParameterEncoding.

Rather than a simple boolean value like JDBC uses, we'd define an enumeration.

package mssql

const (
  EncodingUTF16 = "utf16",
  EncodingUTF8 = "utf8",
  EncodingAscii = "ascii"
)

Advantages

• No code change required for apps to use it, just a connection string change
• Can optimize performance for databases that use UTF8 collections by eliminating the client-side conversion of string runes (Go strings are UTF8 natively)
• Allows for extending support for other encodings in the future

Disadvantages

• Doesn't allow for mixing nvarchar and varchar parameter types in the same connection.
• Without full support for SQL Server collations in the client, whatever arbitrary conversion to ASCII we choose to use is only suitable for use on certain servers.

Define a new data type

To supplement the new connection string parameter, we can allow further customization by defining a new struct that acts like a variant for parameters. It can be rather simple:

type Parameter {
  Value interface{}
  Type int
}

We could make the typeXXX constants from types.go public.

When stringParameterEncoding is not set and the app passes an instance of Parameter or of type string, go-mssqldb would behave as it does now and convert all string types (varchar, nvarchar) to UTF16 to send.

When stringParameterEncoding is non-empty, go-mssqldb would use the specified value for the encoding of string and Parameter instances whose Type field specifies a varchar type. If the Type field specifies an nvarchar type, go-mssqldb would encode it as UTF16.

Advantages

• Allows mixing of UTF16 and non-UTF16 string parameters in the same connection
• Opens up extensibility for more custom data types which are missing from the current go sql namespace.

Disadvantages

• The number of allowed Type and Value combinations could quickly get huge and difficult to define and document. EG, should we allow a byte array for every possible type for apps that want full control over what's being sent? How can an app writer easily discover the supported go types associated with each value of Parameter.Type? Should we limit the scope to just strings?

I'm interested to hear alternatives!

[shueybubbles]

Supplement to the above!

In addition to our constants for UTF16 and UTF8 encoding, we could support names of encoders from the charmap package

To encode varchar strings as Windows code page 1252, you'd use:

stringParameterEncoding="Windows1252"

There are also packages for encoding Asian characters.

[shueybubbles]

This problem gets more important to solve with Always Encrypted coming online (see #129 ).
Combined with SQL2019+ supporting UTF8 collations, allowing apps to send the right bits over the wire will improve both performance and breadth of functionality.