September 15, 2014 at 8:26 am
Can we create table partition with look-up resolution from within the partitioning function… which can potentially take away the constraint of adding columns in each child level data table?
This will save time in code and schema change too.
Cheers,
Got an idea..share it !!
DBA_Vishal
September 15, 2014 at 8:33 am
The partition function is of the form:
CREATE PARTITION FUNCTION partition_function_name ( input_parameter_type )
AS RANGE [ LEFT | RIGHT ]
FOR VALUES ( [ boundary_value [ ,...n ] ] ) [ ; ]
The boundary values must evaluate to constant expressions. They can be constants, variables, functions or user-defined functions, but the values returned by those are what are used as the boundaries.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 15, 2014 at 9:39 am
Thank you Gail , in that case can I use a look up function for creating partition, my partition creation need a cluster index , hence can I use this look up function to give a value that can be used as cluster index in a table
Cheers,
Got an idea..share it !!
DBA_Vishal
September 15, 2014 at 9:41 am
The syntax for creating an index on a partition scheme is
CREATE INDEX ...
ON partition_scheme ( column_name )
You have to specify the column name directly and alone in the partition scheme, its data type must match the data type defined for the partition function.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 15, 2014 at 9:51 am
hmmm, that means I cannot use a lookup function which will on fly create a cluster index and partitioned the tables.
I have to add the column physically to the tables for to be partition key ( cluster index)
Cheers,
Got an idea..share it !!
DBA_Vishal
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply