For a project at work, I’ve been asked to help optimize our ETL process. Part of that involves taking an existing table, and partitioning it along two existing columns. Since the partitioning process only supports partitioning on one column, the obvious choice is to use a computed column. I ran into some issues along the way, and thought I would share them here.
First, when partitioning a table on a computed column, you must persist that column. Persistence is a new feature for SQL Server, introduced in SQL Server 2005 (and still available in 2008). If you’re familiar with the concept of a computed column in SQL 2000, the basic concept is the same; however, persisted computed columns are columns that are physically stored in the database (the engine updates the values whenever values change in the source column) as opposed to virtual columns.
A persisted computed column is defined like so:
CREATE TABLE Sample (DateID int, TimeID int);
INSERT INTO Sample (DateID, TimeID)
VALUES (1,1);
ALTER TABLE SAMPLE
ADD DateIDTimeID AS DATEADD(minute, TimeID, DATEADD(day, DateID, '20090101'))
PERSISTED;
Of course, if you just ran that sample, you got the following error message:
(1 row(s) affected)
Msg 4936, Level 16, State 1, Line 6
Computed column ‘DateIDTimeID’ in table ‘SAMPLE’ cannot be persisted because the column is non-deterministic.
This was the first challenge; although the DATEADD function is always deterministic, the problem lies with the character representation of the date (e.g., ‘20090101’). This behavior is different when running the code on a SQL 2005 server under the SQL 2000 compatibility mode (80) than when running it at level 90 or above; frankly, I was surprised that the code even compiled under SQL 2000 compatibility mode, since persistence of a computed column was not introduced until SQL 2005.
The way that I determined it was the character representation was I checked out Microsoft Connect (http://connect.microsoft.com), which listed the following issue:
From the response, let me quote:
Hi
The reason for the behavior you observe is because the conversion from string to datetime is not deterministic – it will depend on date format settings.
A work around may be to specify the date constant as a bigint (convert your date to bigint).
The behavior with the string constant is by design.
– Christian Kleinerman
Following Christian’s advice, I sought to do what he suggested, only the programmer in me couldn’t distinguish between CONVERT (the T-SQL function) and convert (the English command); in other words, I tried this:
ALTER TABLE SAMPLE
ADD DateIDTimeID AS DATEADD(minute, TimeID, DATEADD(day, DateID, CONVERT(bigint, '20090101')))
PERSISTED;
Which, of course, gives a whole different error message:
Msg 8115, Level 16, State 2, Line 2
Arithmetic overflow error converting expression to data type datetime.
The statement has been terminated.
Stepping out of programming mode for a minute, I realized that Christian meant that I should replace the string constant I was using to represent the date of January 1, 2009 with the bigint (the number of days since January 1, 1753). I ran the following:
SELECT CONVERT(bigint, CONVERT(datetime, '20090101'))
Which gave me the result of 39812, which I then plugged into my ALTER TABLE statement, like so:
ALTER TABLE SAMPLE
ADD DateIDTimeID AS DATEADD(minute, TimeID, DATEADD(day, DateID, 39812))
PERSISTED;
SELECT *
FROM Sample
And the results were much closer to my liking:
| DateID |
TimeID |
DateIDTimeID |
| 1 |
1 |
2009-01-02 00:01:00.000 |
Of course, I now had to deal with the fact that when I had designed my date and time lookup tables, I had used a seed of 1 instead of 0, so now my results were off by a day and a minute, but simple math could adjust for that (future design tip; always use 0-based keys OR use a smart value for the date, like 20090101). Anyway, partitioning now works, and I can alter the table as needed.
Just confirmed today with