1

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?

Improve this question
2
  • What is the actual definition of your user defined type? smalldatetime is 4 bytes not 9. Commented Aug 6, 2016 at 9:43
  • you are right , but this what is in it and it is working for years, I can not change it at all Commented Aug 6, 2016 at 9:47

2 Answers 2

Reset to default
3

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.

Improve this 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.

Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.