SQL server 2008 table paritioning how to?

sqlagent007
sqlagent007 used Ask the Experts™
on
Several new applications are launching, and the company I am working for has purchased an Enterprise Edition license for SQL 2008 R2. I know we will be storing up to 1TB of data in this new application and the data will be trickling in through a web service at about 600 inserts per minute during peak times and a lot less during non peak times. I will need to take advantage of table partitioning as we will need to categorize entities by STATE and CITY, not so much by time. I am told each STATE and CITY will have possibly 1million or more records that will need to be searchable. I will need to get up to speed with table partitions. I am looking for recommendations on great web articles or books that can help me get from beginner to novice to pro in a short amount of time. What great articles \ books have the experts read on partitioning in SQL 2008 EE R2?

Thanks experts!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
thats a good idea to implement partitioning, but with few logics to be followed
whish datatype is mostly used in your database.
text,image,video ?


this url helps a lot
http://searchsqlserver.techtarget.com/tip/Database-partitioning-methods-in-SQL-Server
sqlagent007Technology professional

Author

Commented:
Ah...yes...I should have been more specific. I will be storing [datetime], [int], [varchar(10] for the most part. We have decided we need to partition on date, probably by month. However we will have a column that is a varchar(10) it will be an alpha numeric code like this "CACB1234". In this example this code breaks down to the state of California and the City of Carlsbad, then the number is just randomly assigned. We will have billions of these records and we will need to search on them in the following ways:

1. WHERE LINE_NUM = 'CACB1234'
2. WHERE LINE_NUM LIKE '%CB12%'
3 WHERE LINE_NUM = 'CACB1234' or LINE_NUM = 'CAC81234' (in example 3, we substitute the "b" for the number "8".)

So some of the developers have worked with DB2 in the past and have stated we need something like a "multidimensional cluster", so the talk around the shop has been that we will have more than 1 partition. Like a date partition and a city partition and a state partition. However since I have never implemented this before I am not sure of what the consequences of having multiple partitions will be.

One other thing to consider is that we will potentially need to load 1 million records per day via flat file import.
sqlagent007Technology professional

Author

Commented:
Thanks!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial