Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Muhammad Shujaat... » Partitioning in SQL Server 2008
Add to briefcase
38 posts, Page 2 of 4
««1234»»»

Partitioning in SQL Server 2008 Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
SID-746731
Posted Wednesday, April 29, 2009 9:59 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, March 04, 2013 12:23 PM
Points: 7, Visits: 147
Could you tell me what do you mean by create a non-aligned index on that field. Is there any example.

I used following code:

--------------This took 6 minutes on partition table

use mydb

go



declare @P1 char(9)

set @P1=' '





SELECT top 1000 a.t_hrea,a.t_bpid,a.t_bprt,a.t_dtbl,a.t_dtrl,a.t_erdt,a.t_lobl,a.t_lorl,a.t_opon,a.t_orno,a.t_pono,a.t_pstc,a.t_rlam,a.t_rlty,a.t_sqnb

FROM dbo.ttdsls421900_par a

WHERE (a.t_orno > @P1) ORDER BY 10,11,15,1,4 OPTION (FAST 5)









--------------This took seconds on non-partition table

use mydb

go



declare @P1 char(9)

set @P1=' '





SELECT top 1000 a.t_hrea,a.t_bpid,a.t_bprt,a.t_dtbl,a.t_dtrl,a.t_erdt,a.t_lobl,a.t_lorl,a.t_opon,a.t_orno,a.t_pono,a.t_pstc,a.t_rlam,a.t_rlty,a.t_sqnb

FROM dbo.ttdsls421900 a

WHERE (a.t_orno > @P1) ORDER BY 10,11,15,1,4 OPTION (FAST 5)


---------------------

If I remove ORDER BY Clause from code and run against partition table it will give same result as non partition table.
Post #707377
Gabriel P
Posted Wednesday, April 29, 2009 10:06 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 10:06 AM
Points: 168, Visits: 635
Aligned index (separate indexes split by partition):
create index ix_pt_index on partitioned_table (indexed_value)
ON [ps_PartitionScheme](partition_key) --Name of partition scheme

Non-aligned index (one index that covers all values across all partitions):
create index ix_index on partitioned_table (indexed_value)
ON [FG_Index] --Name of filegroup
Post #707378
SID-746731
Posted Wednesday, April 29, 2009 10:14 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, March 04, 2013 12:23 PM
Points: 7, Visits: 147
You mean non partition index. I did created cluster index ( non allign) on partition table and after that Partition table became non partition table.
Post #707380
Gabriel P
Posted Wednesday, April 29, 2009 10:18 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 10:06 AM
Points: 168, Visits: 635
Yes I meant a nonclustered index.
Post #707381
SID-746731
Posted Wednesday, April 29, 2009 10:27 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, March 04, 2013 12:23 PM
Points: 7, Visits: 147
for example when I creted cluster index, partition table lost the partition scheme and becomes non partion.
And when I created just the non cluster index , execution plan doesnt take this index and keeps go to sort 92%.

Thanks
Post #707385
rashid.inbox
Posted Thursday, April 30, 2009 3:08 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, April 30, 2009 2:59 PM
Points: 1, Visits: 0
Great article presented with very easy to follow and implement approach.

Rashid Akhter, PMP
Project Manager, Scrum Master
Post #708086
RBarryYoung
Posted Monday, May 04, 2009 8:36 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Saturday, May 04, 2013 11:13 AM
Points: 9,855, Visits: 9,374
Very nice article. Easy to read and very instructive. Thanks.

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc. "Performance is our middle name."
Post #709409
robert rogers-466427
Posted Thursday, September 17, 2009 12:41 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, May 06, 2013 9:00 AM
Points: 9, Visits: 496
Do you plan to update your article with the correct t-sql?
Post #789915
tomtait415
Posted Wednesday, September 23, 2009 12:06 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, November 20, 2009 8:03 AM
Points: 1, Visits: 7
Does any one know if partitioning on two fields is possible? I guess worst case I'd could combine two fields into one and do it that way but then I have to have all of the TSQL written with the concatenation...pretty ugly. I haven't been able to find anything on this on the web. I'd like to partition a table by geography and year. Thanks for any help.
Post #792852
Don Day
Posted Thursday, January 14, 2010 3:38 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, March 06, 2013 9:30 PM
Points: 60, Visits: 96
tomtait415 (9/23/2009)
Does any one know if partitioning on two fields is possible? I guess worst case I'd could combine two fields into one and do it that way but then I have to have all of the TSQL written with the concatenation...pretty ugly. I haven't been able to find anything on this on the web. I'd like to partition a table by geography and year. Thanks for any help.

AFAIK the only way to partition on multiple columns is to use a computed column.
Post #848007
« Prev Topic | Next Topic »

Add to briefcase
38 posts, Page 2 of 4
««1234»»»

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.