Tweeted twitter.com/StackDBAs/status/1138461093561622532
3 added 254 characters in body
source | link

I have a CLR scalar UDF implemented through C# code. I've noticed that using the String data type for input parameters significantly improves performance compared to the SqlString data type. In Stairway to SQLCLR Level 5: Development (Using .NET within SQL Server), Solomon Rutzky mentions the following reasons to prefer the SQL data types for strings:

A primary difference between native common language runtime (CLR) data types and SQL Server data types is that the former do not allow for NULL values, while the latter provide full NULL semantics.

...

Streaming values in can be achieved via SqlChars for N[VAR]CHAR, SqlBytes for [VAR]BINARY, and SqlXml.CreateReader() for XML...

...

When using SqlString (not string or even SqlChars) you can access the CompareInfo, CultureInfo, LCID, and SqlCompareOptions properties...

I know that my input will never be NULL, I don't need to stream the values in, and I'll never check the collation properties. Could my case be an exception where it's better to use String instead of SqlString? If I do go with that approach, is there anything in particular that I should watch out for?

If it matters, I'm using SQL Server's default collation. Here's part of my source code, with s1 being the input parameter:

fixed (char* chptr = s1)
{
    char* cp = (char*)current;

    for (int i = 0; i < s1.Length; i++)
    {
        cp[i] = chptr[i];
    }
}

I have a CLR scalar UDF implemented through C# code. I've noticed that using the String data type for input parameters significantly improves performance compared to the SqlString data type. In Stairway to SQLCLR Level 5: Development (Using .NET within SQL Server), Solomon Rutzky mentions the following reasons to prefer the SQL data types for strings:

A primary difference between native common language runtime (CLR) data types and SQL Server data types is that the former do not allow for NULL values, while the latter provide full NULL semantics.

...

Streaming values in can be achieved via SqlChars for N[VAR]CHAR, SqlBytes for [VAR]BINARY, and SqlXml.CreateReader() for XML...

...

When using SqlString (not string or even SqlChars) you can access the CompareInfo, CultureInfo, LCID, and SqlCompareOptions properties...

I know that my input will never be NULL, I don't need to stream the values in, and I'll never check the collation properties. Could my case be an exception where it's better to use String instead of SqlString? If I do go with that approach, is there anything in particular that I should watch out for?

If it matters, I'm using SQL Server's default collation.

I have a CLR scalar UDF implemented through C# code. I've noticed that using the String data type for input parameters significantly improves performance compared to the SqlString data type. In Stairway to SQLCLR Level 5: Development (Using .NET within SQL Server), Solomon Rutzky mentions the following reasons to prefer the SQL data types for strings:

A primary difference between native common language runtime (CLR) data types and SQL Server data types is that the former do not allow for NULL values, while the latter provide full NULL semantics.

...

Streaming values in can be achieved via SqlChars for N[VAR]CHAR, SqlBytes for [VAR]BINARY, and SqlXml.CreateReader() for XML...

...

When using SqlString (not string or even SqlChars) you can access the CompareInfo, CultureInfo, LCID, and SqlCompareOptions properties...

I know that my input will never be NULL, I don't need to stream the values in, and I'll never check the collation properties. Could my case be an exception where it's better to use String instead of SqlString? If I do go with that approach, is there anything in particular that I should watch out for?

If it matters, I'm using SQL Server's default collation. Here's part of my source code, with s1 being the input parameter:

fixed (char* chptr = s1)
{
    char* cp = (char*)current;

    for (int i = 0; i < s1.Length; i++)
    {
        cp[i] = chptr[i];
    }
}
2 edited tags
| link
1
source | link

Is it safe to use Strings instead of SqlStrings for CLR function input parameters?

I have a CLR scalar UDF implemented through C# code. I've noticed that using the String data type for input parameters significantly improves performance compared to the SqlString data type. In Stairway to SQLCLR Level 5: Development (Using .NET within SQL Server), Solomon Rutzky mentions the following reasons to prefer the SQL data types for strings:

A primary difference between native common language runtime (CLR) data types and SQL Server data types is that the former do not allow for NULL values, while the latter provide full NULL semantics.

...

Streaming values in can be achieved via SqlChars for N[VAR]CHAR, SqlBytes for [VAR]BINARY, and SqlXml.CreateReader() for XML...

...

When using SqlString (not string or even SqlChars) you can access the CompareInfo, CultureInfo, LCID, and SqlCompareOptions properties...

I know that my input will never be NULL, I don't need to stream the values in, and I'll never check the collation properties. Could my case be an exception where it's better to use String instead of SqlString? If I do go with that approach, is there anything in particular that I should watch out for?

If it matters, I'm using SQL Server's default collation.