Table Partition Help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Table Partition Help

Author

Topic

tooba
Posting Yak Master

224 Posts

Posted - 2012-11-27 : 14:08:22

Hi guys,

I have questions. Please advice...
I have one table that has 6million rows.For good performance i am wondering if i do table Partition.
My understanding is (Please correct me if i am wrong) when we do table partitioins
TABLE1(ORIGINAL TABLE) = ONE YEAR OLD DATA
TABLE2 (CREATE AFTER PARTITION) = REST OF THE DATA

and if i want to use below statement that is NOT SURE IN WHICH TABLE

SELECT * FROM TABLENAME
WHERE DATE = '2010/10/2'

Did above statement going to scan table1 or table2 or i have to mention which table going to sear afer table partition.

Please let me know if my questin is not clear.

Thank you in advance.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-11-27 : 14:28:58

That's not partitioning, that's archiving. If you partition a table, you tell sql that based on some function, put the rows that are captured by that function somewhere else where they don't bother anybody, but are still accessible to you.
For example, put the data that is <= '20101002' on a different filegroup because I rarely query it, but it's still the same table name

SELECT *
FROM yourTable
WHERE date > '20101002'

will work, but so will
SELECT *
FROM yourTable
WHERE date <= '20101002'

Jim

And 6 million rows isn't all that big, I'd look at indexing and query design for a performance boost first.

Everyday I learn something that somebody else already knew

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2012-11-27 : 14:40:15

quote:
Originally posted by tooba

Hi guys,

I have questions. Please advice...
I have one table that has 6million rows.For good performance i am wondering if i do table Partition.
My understanding is (Please correct me if i am wrong) when we do table partitioins
TABLE1(ORIGINAL TABLE) = ONE YEAR OLD DATA
TABLE2 (CREATE AFTER PARTITION) = REST OF THE DATA

and if i want to use below statement that is NOT SURE IN WHICH TABLE

SELECT * FROM TABLENAME
WHERE DATE = '2010/10/2'

Did above statement going to scan table1 or table2 or i have to mention which table going to sear afer table partition.

Please let me know if my questin is not clear.

Thank you in advance.

Are you talking about Horizontal or Vertical Partitioning ...well you can do that but there is lot maintenance for it. As Jimf said, 6 million table is very small for Table Partitioning.

tooba
Posting Yak Master

224 Posts

Posted - 2012-11-27 : 15:19:52

I am sorry it was typo mistake it was 30 million rows. I think i will try what Jimf said,
Jim could you please show me right path/tutorial where i can try Table Partiotining?

Thank in advance...

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-11-27 : 15:32:17

30 million records isn't all that many either. I'd try a filtered index first
CREATE INDEX StuffIuse ON dbo.myTable
WHERE DATE >= '20101002'

BooksOnLine is the best place to start learning about partitioning
ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_1devconc/html/29946a77-345a-4527-967a-5fb598947122.htm

Jim

Everyday I learn something that somebody else already knew

tooba
Posting Yak Master

224 Posts

Posted - 2012-11-27 : 15:58:47

Thank Jim, Quick question. Just want to clarefy
I have table TABLE1 has 30 m records and for sure already have some indexes (Clustered and non clustered index). If i understand right you are using 30m record is not many. Instead of Table Partition use Filtered Index.

If i use this query
CREATE INDEX StuffIuse ON dbo.myTable
WHERE DATE >= '20101002'

Its mean i am creating a filtered index on Date columns, right? and if use this query
SELECT * FROM TABLE1
WHERE DATE BETWEEN '20110101' AND '20110112' (Should be fast)

How about my query is
SELECT * FROM TABLE1
WHERE DATE BETWEEN '20090101' AND '20080101'

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-11-27 : 17:21:37

That's exactly right. I'm assuming you already have an index on date. There's no set rule for index creation, it all depends on what your query is. If you have queries that are mostly looking for data after '20101002' then a filtered index as above makes a lot of sense. If your queries have date ranges that are always a span of 1 year, then a filtered index for each year may make sense. The index basically tells SQL "Don't bother scanning thru 30 million records to find the right ones when you can find the right ones by looking at this index which is much, much smaller than the whole table".

Jim

Everyday I learn something that somebody else already knew

tooba
Posting Yak Master

224 Posts

Posted - 2012-11-27 : 18:30:24

I really appreciate to clear my doubt!

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-11-27 : 18:37:12

You're welcome!

Jim

Everyday I learn something that somebody else already knew

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources