Table Partitioning

Question

Post reply

Table Partitioning

sanjaydut26

SSCommitted

Points: 1968
More actions
August 27, 2016 at 1:27 am

#329600

Hi,
I am new to table partitioning.
I have a large table with millions of rows, There is no Date column in the table and i have the primary key column with Identity(1,1). Can i still paritition the table without date column.
Thanks in advance,
Sanjay

Viewing 7 posts - 1 through 6 (of 6 total)

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

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

Depends. Why are you partitioning the table?

The purpose behind the partitioning will determine what your partition columns should be.

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

sanjaydut26 SSCommitted Points: 1968 More actions · Answer 2

As already mentioned the data is huge, select or update is taking long time.

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

Ok, so if you're after performance improvements, why are you wasting time looking at partitioning?

Partitioning is for data management, fast loads, archiving, that kind of thing, if if you are trying to do that then the way that data is loaded or removed will determine the partition columns

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

Jeff Moden SSC Guru Points: 1003894 More actions · Answer 4

sanjaydut26 (8/27/2016)
Hi,
I am new to table partitioning.
I have a large table with millions of rows, There is no Date column in the table and i have the primary key column with Identity(1,1). Can i still paritition the table without date column.
Thanks in advance,
Sanjay

Just to add a strong second to what Gail wrote...

Partitioning isn't a panacea-of-performance solution. In fact, it will frequently slow queries down because of the extra B-TREEs that it has to slog through for each partition. A well written query (has SARGable predicates that can actually use an index properly) executed against a properly designed, properly indexed monolithic table will usually beat the same query executed against a partitioned table even in the face of so-called "partition elimination".

The greatest irony is that the things that you must do to the table you want to partition (setting up the correct indexes being the most important) is the same thing you need to do to make the queries run faster without partitioning.

To answer your original question, yes, you can partition without a date column but you need some other immutable column to do it with and an IDENTITY column could be used but, unless the older data is completely static so that the indexes don't change on the older partitions allowing you to greatly reduce index maintenance and, possibly, backups (older partitions could be set to READ_ONLY after a final backup), there's no performance advantage to partitioning and could actually cause things to run a bit slower.

Performance is in the code. Concentrate on fixing the code.

--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)