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?

  • What is the actual definition of your user defined type? smalldatetime is 4 bytes not 9. – Martin Smith Aug 6 '16 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 – nnmmss Aug 6 '16 at 9:47
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.

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.