Sql 2016 updating a substring in a char field
For best performance we recommend that text, ntext and image data be inserted or updated in chunks sizes that are multiples of 8,040 bytes.In SQL Server, in-row text pointers to text, ntext, or image data may exist but may not be valid.From: Sharon Cannon To: Ted Holt I know that with SQL it is very easy to update a field, but is it possible to substring part of a field and update that partial field? You can use the SUBSTR operator to get a substring from a field.For example, to get bytes 3-7 of a field called MYNAME, use the expression SUBSTR(MYNAME,3,5).Table names and column names must comply with the rules for identifiers.Specifying the database name and owner names is optional.
Avoid using this feature in new development work, and plan to modify applications that currently use this feature. The stream must be provided by the tool at the TDS protocol level.
Returns nvarchar if one of the input arguments is of the nvarchar data type; otherwise, REPLACE returns varchar. If must be explicitly cast to a large-value data type.
REPLACE performs comparisons based on the collation of the input.
CREATE DATABASE My String Test DB; GO USE My String Test DB; GO DROP TABLE IF EXISTS dbo. String Test ( [The String] varchar(15) ); GO INSERT INTO dbo.
All of the values are in fact different but all match the predicate. I added expecting this to be a clear win for the 130 estimator as the 120 struggles on with a memory grant for one row but this minor change was sufficient to bring the estimated rows down to 1.03913 in the 130 case too.
Is there any documentation or research about changes in SQL Server 2016 to how cardinality is estimated for predicates containing SUBSTRING() or other string functions? Adding reverts the estimate going into the sort to 100,000 but the memory grant doesn't increase and the estimates coming out the sort are still based on the table distinct values.