partitioning column

Question

I have a database with a few tables in it, one of which is quite a big. I decided to partition it. So after studying how I should I do that, I decided to do it by a wizard. In the section which I had to select a column for partitioning, I noticed that just a part of the columns of a table are visible there are fields for which user defined type are not visible for selecting as the partitioning column. For example I have a field named "Date_Register" with Type "Date_Short:smalldatetime". "Date_Short:smalldatetime" is a user defined type with the following specification:

Name: Date_Short
Data type: smalldatetime
precision: 16
storage : 9 byte

is it possible to select Date_Register as partitioning column?

Answer 1

From CREATE PARTITION FUNCTION

input_parameter_type Is the data type of the column used for partitioning. All data types are valid for use as partitioning columns, except text, ntext, image, xml, timestamp, varchar(max), nvarchar(max), varbinary(max), alias data types, or CLR user-defined data types.

So looks like you're out of luck.

I'm curious about the definition of DateShort anyway though and whether it in fact adds any value over just using smalldatetime directly.

Answer 2

Martin is right, but a workaround might be to add a computed column casting your udt as something acceptable as a partitioning key.

ALTER TABLE your_table ADD computed_col AS CONVERT(SMALLDATETIME, your_udt_col) PERSISTED

You could use a different data type, I'm using small date time because that's how your udt is defined.

Background here.