table partition using Lookup function

Question

Post reply

table partition using Lookup function

vkundar

Hall of Fame

Points: 3431
More actions
September 15, 2014 at 8:26 am

#299432

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

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

vkundar Hall of Fame Points: 3431 More actions · Answer 2

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

vkundar Hall of Fame Points: 3431 More actions · Answer 4

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