I am in a situation where I have a table which has Primary Key which is
actually IDENTITY Column as well. Now, I want to implement Partitioned View
on it. I went thru your different article about it in SQL Server Magazine but
I did not find how to handle this situation If I have an IDENTITY Column
which is not the part of the Partitioning column. My Partitioning column is
not part of Primary Key as well. So, how can I implement Partitioning in this
situation or it is not possible. What are other ways to deal with large no.
of data in a table?

--
Essa, M. Mughal
Software Developer
iLogic Inc., Canada

Why not partition on the IDENTITY column? This would require a bit of work.
You'll have to re-seed the IDENTITY columns for each of the partitioned
tables such that they won't overlap, and possibly update some of the tables
that already reference those columns, such that the FKs have the correct
values -- but once completed this may work quite well as a partition. The
other choice, unfortunately, will be re-working your table entirely such
that your partitioning column does participate in the PK; in this case,
you'll STILL have to re-seed the IDENTITY column for partitioning --
otherwise, you may encounter overlaps or other problems if all of your
partitioned tables each have an IDENTITY. And querying the partition by
that identity value will not give you any benefit unless you include the
partitioning column in the query -- so I'm not sure you'll be too happy with
that setup.

Does that make sense at all?


--
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--


"Essa" <essamughal (AT) hotmail (DOT) com> wrote

Hi Adam;
It is quite well answer of my question but I can't really change the
structure of the table b/c the table is main table of the system and it is
referenced in so many tables. The data cleaning and consistency would take
forever or bit long time. The application is almost mature and it been in
used for last three years so right now the reseeding is not possible at this
stage. So, I think I am not going to implement Partitioning view right now.

Do you know any other method to resolve the large data table problem?

Thanks



"Adam Machanic" wrote: