Hello,
I have come across a really good tip if you’re using computed columns that involve a concatenation of columns and conversions within the column.
Say for instance you are creating a computed column like the one below:-
CREATE TABLE dbo.SomeTable(
ID INT NOT NULL,
StartDate DATE NOT NULL,
EndDate DATE NOT NULL,
SomeData VARCHAR(150) NOT NULL,
PartitionKey AS CONVERT(VARCHAR(3), DATEPART( DAYOFYEAR, StartDate ) ) + '|' + CONVERT( VARCHAR(3), DATEDIFF(dd, StartDate, EndDate)) PERSISTED
)
This will create a column that is computed and it will be used within a partition scheme and function (which is out of scope of this post) The data will look similar to the below if the data in column StartDate = ‘1/1/2014′ and EndDate = ‘2/1/2014′
‘1|1′ — The first part is the 1st day of the year which is from the start date. The second part is the difference between the start and end dates.
As this computed column is going to be used in the query we will have to construct a variable of the exact same, this would be done either in a one off stand alone query or stored procedure.
DECLARE @StartDate DATE = '2014-01-01';
DECLARE @EndDate DATE = '2014-01-02';
DECLARE @PartitionKey VARCHAR(8) = CONVERT(VARCHAR(3), DATEPART( DAYOFYEAR, @StartDate ) ) + '|' + CONVERT( VARCHAR(3), DATEDIFF(dd, @StartDate, @EndDate));
SELECT ID, SomeData FROM dbo.SomeTable;
WHERE PartitionKey = @PartitionKey;
When you run this query it will most definitely cause the “Type conversion in expression” noise in your query plan. See below:-
As you can see the warning is being caused by the concatenation and conversion of the computed column. Right lets remove the warning.
Firstly we need to create a schema bound scalar function, like below:-
CREATE FUNCTION dbo.CalcPartitionKey
(
@StartDate DATE,
@EndDate DATE
)
RETURNS VARCHAR(7)
WITH SCHEMABINDING
AS
BEGIN
RETURN CONVERT(VARCHAR(3), DATEPART( DAYOFYEAR, @StartDate ) ) + '|' + CONVERT( VARCHAR(3), DATEDIFF(dd, @StartDate, @EndDate))
END
This is what microsoft says about SCHEMABINDING:-
A schema-bound dependency is a relationship between two entities that prevents the referenced entity from being dropped or modified as long as the referencing entity exists. A schema-bound dependency is created when a view or user-defined function is created by using the WITH SCHEMABINDING clause. A schema-bound dependency can also be created when a table references another entity, such as a Transact-SQL user-defined function, user-defined type, or XML schema collection, in a CHECK or DEFAULT constraint or in the definition of a computed column. Specifying an object using a two-part (schema_name.object_name) name does not qualify as a schema-bound reference.
I’m not 100% sure why this resolves the issue but from now on I will always bind my computed columns to a schema bound UDF to remove the noise.
Right, next we need to recreate the table as below:-
CREATE TABLE dbo.SomeTable(
ID INT NOT NULL,
StartDate DATE NOT NULL,
EndDate DATE NOT NULL,
SomeData VARCHAR(150) NOT NULL,
PartitionKey AS dbo.CalcPartitionKey(StartDate, EndDate) PERSISTED
)
Then we need to change the stand alone query or stored procedure to the below:-
DECLARE @StartDate DATE = '2014-01-01';
DECLARE @EndDate DATE = '2014-01-02';
DECLARE @PartitionKey dbo.CalcPartitionKey(@StartDate, @EndDate);;
SELECT ID, SomeData
FROM dbo.SomeTable;
WHERE PartitionKey = @PartitionKey;
When you run your query now you will notice that the “Type conversion in expression” noise in your query plan will be gone. See below:-
Thanks for reading, please await the follow up blog regarding table partitioning, creating the partition function, and using a computed column in the clustered index with a non-clustered primary key on the table…
Thanks
Andrew
