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 12»»

Table Partitioning Expand / Collapse
Author
Message
Posted Friday, July 27, 2007 11:19 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: Moderators
Last Login: 2 days ago @ 9:27 AM
Points: 6,800, Visits: 1,917
Comments posted here are about the content posted at http://www.sqlservercentral.com/columnists/awarren/3135.asp

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
Post #385917
Posted Thursday, August 9, 2007 1:39 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, August 8, 2012 4:45 AM
Points: 26, Visits: 46
Nice article. I'll be waiting for you next articles on the topic to get a glimpse into the lower level details and implementation of the concepts described esp: Horizontal Partitioning.

Best regards.
Post #389172
Posted Thursday, August 9, 2007 2:32 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 26, 2012 5:26 AM
Points: 1,367, Visits: 1,585
It is a nice article. Concerning vertical partitioning I'd recommend SQL Refactor. It has a table split refactoring that takes a table, allows you to specify which columns should stay/moved/copied to a new table, what the future relationship will be (1:1, 1:n, m:1), and creates a script for you that
- create the new table
- sets up automatically a primary key on this table (based on shared columns)
- copies unique data into this new table
- removes the necessary columns from the original table
- sets up the foreign keys as specified by the "future relationship"
- rewrites all your stored procedures, views, etc to reference a joined version of these
two new tables (if both of them are needed) or only the relevant table.

More about this at http://www.simple-talk.com/sql/sql-tools/faster-database-development-with-sql-refactor/

Regards,
Andras




Andras Belokosztolszki, MCPD, PhD
GoldenGate Software
Post #389185
Posted Thursday, August 9, 2007 5:45 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Sunday, November 16, 2014 11:31 PM
Points: 119, Visits: 162

Great article. I just wanted to say that I have had some very large performance gains to query times when employing horizontal partitioning on biggish fact tables in a datawarehouse environment using SQL2000. When I first looked into this, a number of seasoned SQL professionals at my workplace had never heard of the 'partitioned view' functionality in SQL2000, allowing the 'union all' view over the tables to essentially mimic full table functionality, including provisions for insert/update/deletes without specifiying which underlying table we need to work on. My workmates didn't believe this was achievable until I put an example together, showing differences in query plans. Once I did this, they were mighty impressed (with the functionality, not just me!).

As mentioned in the article, other benefits available including shortening the time window required for backup and re-indexing. We also got these benefits thrown in for free, even though we were only targetting query performance.

So I encourage everyone to eagerly await the rest of the articles, as I will be.

Matt.




Post #389228
Posted Thursday, August 9, 2007 6:47 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 29, 2014 10:56 AM
Points: 2, Visits: 67

Great article Andy, thanks for taking the time to go over this topic (very timely for me  ).

I'm looking forward to more depth and details soon!

-Ron

Post #389250
Posted Thursday, August 9, 2007 7:18 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Wednesday, January 11, 2012 2:47 PM
Points: 681, Visits: 10,845
Thanks Andy!  I cannot wait for the followups. 

-- Cory
Post #389263
Posted Thursday, August 9, 2007 7:35 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, June 8, 2010 5:47 AM
Points: 13, Visits: 9
Nice Article. Thanks Andy.
Post #389269
Posted Thursday, August 9, 2007 9:12 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, November 2, 2012 7:05 AM
Points: 75, Visits: 446

This is a very good article.

I just have one question or clarification. When you say

“In SQL 2005 we have true partitions, where we can define a table as residing on a partition and all the sub tables are managed by SQL, there is no view.”

Isn’t this only in SQL 2005 Enterprise Edition?




Post #389334
Posted Thursday, August 9, 2007 9:22 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: Moderators
Last Login: 2 days ago @ 9:27 AM
Points: 6,800, Visits: 1,917
Im sorry if I didnt make that clear, you're correct; can only partition in Enterprise (or Developer of course) edition.

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
Post #389340
Posted Thursday, August 9, 2007 9:01 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, November 10, 2008 9:51 AM
Points: 4, Visits: 47

this article is good

 

i want some help to improve the performance of DTS package that holding Store procedure and before connection to flate file drpping the index and after that recreating the index.

 

In which are i have to work out for improving performance

 

Post #389505
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse