Partition Table by Varchar column

Question

Partition Table by Varchar column

aYasser97

SSC Enthusiast

Points: 181
More actions
September 8, 2022 at 4:57 pm

#4085504

I have a table that I want to partition by postcode (example values are: 7420_FMIL, ND8_1ZE, C7_4JM) where each rows for a certain postcode would get inserted into its own respective filegroup (Rows for Postcode 7420_FMIL would be in Filegroup FG_7420_FMIL for example) .
What would the partition function look like?
CREATE PARTITION FUNCTION PF_Postcode(varchar(100))
AS RANGE LEFT FOR VALUES
(
7420_FMIL,
ND8_1ZE,
C7_4JM
)
Would that work? Any help would be much appreciated
September 29, 2022 at 9:41 am

This was removed by the editor as SPAM

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

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

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 1

Before going down the path of partitioning - what are you expecting to achieve by doing so? What is the purpose of partitioning - and more to the point, why are you wanting to separate the data into separate filegroups and files?

You don't have to use separate filegroups for partitioning - and there are some benefits to having all partitions in the same filegroup. If you setup multiple filegroups where the files are all on the same drive then there really isn't any benefit to multiple filegroups.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

ScottPletcher SSC Guru Points: 101098 More actions · Answer 2

I think it would be as below. You must put the partition values in alpha order.

CREATE PARTITION FUNCTION PF_Postcode(varchar(100))

AS RANGE LEFT FOR VALUES

(

'7420_FMIL',

'C7_4JM',

'ND8_1ZE'

)

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Jeff Moden SSC Guru Points: 1004343 More actions · Answer 3

Millicentsa wrote:

Much appreciated so a phenomenal plan, your thought worked for me.

I smell spam cooking.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)