Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««1234»»»

Partitioning in SQL Server 2008 Expand / Collapse
Author
Message
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: Thursday, October 24, 2013 5:30 PM
Points: 7, Visits: 157
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
Posted Wednesday, April 29, 2009 10:06 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 6:59 AM
Points: 226, Visits: 766
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
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: Thursday, October 24, 2013 5:30 PM
Points: 7, Visits: 157
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
Posted Wednesday, April 29, 2009 10:18 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 6:59 AM
Points: 226, Visits: 766
Yes I meant a nonclustered index.
Post #707381
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: Thursday, October 24, 2013 5:30 PM
Points: 7, Visits: 157
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
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
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: Friday, March 28, 2014 2:25 PM
Points: 9,902, Visits: 9,479
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
Posted Thursday, September 17, 2009 12:41 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, March 12, 2014 8:01 AM
Points: 16, Visits: 541
Do you plan to update your article with the correct t-sql?
Post #789915
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
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 ««1234»»»

Permissions Expand / Collapse