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?
COLLATE Latin1_General_BIN2
makes the expression onchecks.name
unsargable. For your specific query not sure how much if any difference it will make to the cardinality estimates though.COLLATE
is only there to make the range expression work correctly you could replaceN'[0-9A-F]')
withN'[0123456789ABCDEF]'
and drop theCOLLATE Latin1_General_BIN2