SQL Server Partitioning Column – Best Practices and Tips

partitioningsql server

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?

Best Answer

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.

Related Question