12

I maintain an archive database that stores historical data in partitioned views. The partitioning column is a datetime. Each table under the view stores one month of data.

We constraint the events on each table with a check constraint on the datetime column. This allows the optimizer to limit the tables that are searched for queries that filter on the event datetime column.

The names of the check constraints were generated by SQL Server, so it's hard to know what they do by looking at their name.

I want the constraint names to have the form 'CK_TableName_Partition'.

I can generate a rename script using this query and copying data from from sql_text column. The WHERE clause matches check constraints whose names look like they were generated by SQL Server:

SELECT
  checks.name AS check_name,
  tabs.name AS table_name,
  skemas.name AS schema_name,
  cols.name AS column_name,
  N'
EXECUTE sys.sp_rename
  @objname = N''' + skemas.name + N'.' + checks.name + N''',
  @newname = N''CK_' + tabs.name + N'_Partition'',
  @objtype = ''OBJECT'';' AS sql_text
FROM sys.check_constraints AS checks
INNER JOIN sys.tables AS tabs ON
  tabs.object_id = checks.parent_object_id
INNER JOIN sys.schemas AS skemas ON
  skemas.schema_id = tabs.schema_id
INNER JOIN sys.columns AS cols ON
  tabs.object_id = cols.object_id AND
  cols.column_id = checks.parent_column_id
WHERE checks.name LIKE (
  N'CK__' + SUBSTRING(tabs.name, 1, 9) +
  N'__' + SUBSTRING(cols.name, 1, 5) +
  N'__' + REPLACE(N'xxxxxxxx', N'x', N'[0-9A-F]') COLLATE Latin1_General_BIN2
)
ORDER BY table_name;

The output looks like this:

check_name  table_name  schema_name column_name sql_text
CK__tbAcquisi__Acqui__5C4299A5  tbAcquisitions_201301   Archive AcquisitionDT   EXECUTE sys.sp_rename  @objname = N'Archive.CK__tbAcquisi__Acqui__5C4299A5',  @newname = N'CK_tbAcquisitions_201301_Partition',  @objtype = 'OBJECT';
CK__tbAcquisi__Acqui__76026BA8  tbAcquisitions_201302   Archive AcquisitionDT   EXECUTE sys.sp_rename  @objname = N'Archive.CK__tbAcquisi__Acqui__76026BA8',  @newname = N'CK_tbAcquisitions_201302_Partition',  @objtype = 'OBJECT';
CK__tbAcquisi__Acqui__7D6E8346  tbAcquisitions_201303   Archive AcquisitionDT   EXECUTE sys.sp_rename  @objname = N'Archive.CK__tbAcquisi__Acqui__7D6E8346',  @newname = N'CK_tbAcquisitions_201303_Partition',  @objtype = 'OBJECT';
...
CK__tbRequest__Reque__60132A89  tbRequests_201301   Archive RequestDT   EXECUTE sys.sp_rename  @objname = N'Archive.CK__tbRequest__Reque__60132A89',  @newname = N'CK_tbRequests_201301_Partition',  @objtype = 'OBJECT';
CK__tbRequest__Reque__1392CE8F  tbRequests_201302   Archive RequestDT   EXECUTE sys.sp_rename  @objname = N'Archive.CK__tbRequest__Reque__1392CE8F',  @newname = N'CK_tbRequests_201302_Partition',  @objtype = 'OBJECT';
CK__tbRequest__Reque__1AFEE62D  tbRequests_201303   Archive RequestDT   EXECUTE sys.sp_rename  @objname = N'Archive.CK__tbRequest__Reque__1AFEE62D',  @newname = N'CK_tbRequests_201303_Partition',  @objtype = 'OBJECT';

The result of the query seems to be correct and the server executes it quickly.

But the root node of the execution plan has a warning:

Type conversion in expression (CONVERT_IMPLICIT(nvarchar(128),[o].[name],0)) may affect "CardinalityEstimate" in query plan choice

What does that mean in this context? Is such a complex filter confusing the optimizer? Is it something I should be worried about?

2
  • 2
    The COLLATE Latin1_General_BIN2 makes the expression on checks.name unsargable. For your specific query not sure how much if any difference it will make to the cardinality estimates though. Mar 6, 2013 at 19:28
  • 2
    Assuming the binary COLLATE is only there to make the range expression work correctly you could replace N'[0-9A-F]') with N'[0123456789ABCDEF]' and drop the COLLATE Latin1_General_BIN2 Mar 6, 2013 at 19:53

1 Answer 1

17

The result of the query seems to be correct and the server executes it quickly.

But the root node of the execution plan has a warning:

Type conversion in expression (CONVERT_IMPLICIT(nvarchar(128),[o].[name],0)) may affect "CardinalityEstimate" in query plan choice

What does that mean in this context? Is such a complex filter confusing the optimizer? Is it something I should be worried about?

The warning is informational. If your query performed slowly, or you noticed that cardinality estimates were incorrect, the warning would give you information about where to look for a possible cause.

The warning is triggered by the implicit conversion used for the collation change. If using the collation is the easiest way to get correct results, feel free to leave it as it is. Alternatively, if you explain more about why it is needed, someone will advise you.

As an aside, the REPLACE could be replaced with:

REPLICATE(N'[0-9A-F]', 8);

(This answer is a summary of the comments to the question.)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.