SQL 2005 patition table

Hi, I have 20 million (5 type record ids -- 2-3gb)records need to be loaded 
into a table daily.
Is it a good idea to have a a patiotion table seperated by reciord type id.
1. It will be faster for retrieval in performance?
2. What is the drawbacks ot have a partitioned table instead of regular 
table?

Thanks, 


0
Mecn
6/9/2010 10:08:07 PM
sqlserver.server 1327 articles. 0 followers. Follow

1 Replies
905 Views

Similar Articles

[PageSpeed] 32

Mecn
My opinion is
1) It will be fatser if the user searches on  particular typeid  , if he/she 
searches on typedid 1 and 5 then it will 'scan' two partitions
2) You have to have some experience to maintain partitons

"Mecn" <mecn@yahoo.com> wrote in message 
news:eM8t%23ACCLHA.3880@TK2MSFTNGP04.phx.gbl...
> Hi, I have 20 million (5 type record ids -- 2-3gb)records need to be 
> loaded into a table daily.
> Is it a good idea to have a a patiotion table seperated by reciord type 
> id.
> 1. It will be faster for retrieval in performance?
> 2. What is the drawbacks ot have a partitioned table instead of regular 
> table?
>
> Thanks,
> 


0
Uri
6/10/2010 7:44:45 AM
Reply:

Similar Artilces:

Calling Dex Procedures from SQL
Is there any way to call dex procs from SQL or VB code? Jed There is an undocumented and unsupported method of calling Dex code from VBA. However, I know of no method of calling Dexterity from SQL. Please email me if you want an example, just remove the online. from the address below. David Musgrave [MSFT] Senior Development Consultant MBS Services - Asia Pacific Microsoft Business Solutions http://www.microsoft.com/BusinessSolutions mailto:dmusgrav@online.microsoft.com Any views contained within are my personal views and not necessarily Microsoft Business Solutions policy. This p...

is SQL 2000 architecture different SQL Server 2005 / 2008 architecture?
Hi I have a book "inside SQL Server 2000" by Kalen Delaney, which I have been told is a good book. I am going backwards... reading about the sql server architecture in chapter 3 of the book. As I'm reading... I was wondering is it different SQL Server 2000 vs SQL Server 2005/2008? Also where can I find a book/or link that can give me more details about the internals on architecture? Thank you norm (normanchan@gmail.com) writes: > Hi I have a book "inside SQL Server 2000" by Kalen Delaney, which I > have been told is a good book. I am g...

Forms - Call Data from Another Table
Hi I'm used to using lookups and forms etc but need to do something that I think links queries, VBA (completely new to) and forms: I have a form for creating new applicants I want to build a form that allows the user to search a contacts table on FirstName, MiddleName, Surname; receive a list of matches and then either 1. Select one of the matches which would then populate the applicants table or 2. Reject the matches and create a new record as normal Can you anyone point me in the right direction? Thanks James ...

Pivot Table Field Choices
Is there a global way to uncheck ALL of the boxes in a pivot table field button pull down? I see the way to do this if you have 2 levels but all of mine are single level, and I have a hundred plus. Thanks in advance if anyone has a trick! You can do this with a macro. The first macro hides all items except the last one, and the second one shows all items. Replace "Rep" with the name of your field. Sub PivotHideItemsField() 'For version 2000 -- hide all items in specific field 'sort is set to Manual to prevent errors, e.g. 'unable to set Visible Property of Pivot...

Table off window in Design
I copied a query. In the new query's design window, I scroll all the way to the bottom and see the join lines going out of sight before I see the next table. How do I resolve this problem? Ted Turner 2010 wrote: > I copied a query. In the new query's design window, I scroll all the way to > the bottom and see the join lines going out of sight before I see the next > table. > How do I resolve this problem? That's a first for me. But I suppose if I were you, I'd switch to SQL view, copy the SQL, create a new blank query, switch to SQL view and pas...

Moving customizations from SQL 2k to SQL 2k5 CRM
This may be a non-issue but I wanted to check to see if anyone has done this and ran into issues. I have the XML customization files from our previous install of CRM 3.0 with SQL 2k database. I now want to import and publish these same customizations to CRM 3.0 on SQL 2k5. Has anyone done this or can point me to documentation that tells about the success or issues for doing this? Thanks. On 7 Feb., 06:35, Chris Treanor <ChrisTrea...@discussions.microsoft.com> wrote: > This may be a non-issue but I wanted to check to see if anyone has done this > and ran into issues. > &...

Pivot tables
I would like to show % of a total on a subtotal field, e.g. 46/37269 = 0.7% - The calculation must be inside the Pivot table, because it get refreshed all the time with new rows of information. blue jacket 246 0.10% Jean 1952 0.82% Pants 5116 2.15% shirt 15918 6.68% Shorts 1115 0.47% skirt 7872 3.30% Tops 5050 2.12% blue Total 37269 15.64% camel Jean 821 0.34% Pants 507 0.21% shirt 993 0.42% skirt 697 0.29% camel Total 3018 1.27% You would need to add formulas to your pivot table, but why can't you just uncheck the other colours except the one you want the percentage for? I...

money 2005
Just a recommendation to all potential money 2005 purchasers...don't do it! Look around the message boards and you'll see countless examples of the program ruining peoples data; unfortunately, I'm one of those people to. Ever since i installed the program it has been updating my information on MSN Money as well. Everything was fine until I wanted to stop doing this and use Money 2005 independently of the web service. When I tried to erase the data from MSN server I couldn't. Then, when Money 2005 opens up my file it connects to the server and all hell breaks lose. My...

CRM 3.0 Install Error with SQL Reporting Services and SQL 2005
Hi, Doing a CRM 3.0 Server installation. During the last page of the install wizard (or what I assume is the last page) when I goes through and does verification I get a red X next to SQL Reporting Services. The message I receive when I look at the details is: ***** The specified path is not a metabase path. Parameter name: path ***** Now SRS is installed and working. I put in the URL: http://<servername>/reportserver and every other variation on that I can find. Thoughts? Alan try http://servername/reports also assuming you did not use ssl... =======================...

Pivot Tables + Color + Dynamic Content generation and display
I have a dynamically generated pivot table (generated from actions in another spreadsheet - number of rows and location keeps changing - cant use conditional formatting). The data I currently have displayed on the pivot table is: Resource Project Allocation Jan Allocation Feb -------- ------- -------------- -------------- Resource1 Project1 0.5 0.5 Resource1 Project1 0.25 0.5 Resource1 Total 0.75 1.0 Resource2 Project1... .. I need to (1) Highlight, in red, items where Resource totals are less than 1.00. e.g. Resource 1 Total. Just...

Formatting default value in a table
I am in the process of creating a database to used in a small office to track ongoing projects. I am trying to get the next new record to automatic populate with the next number. Currently I have row set up as a number, field properties I have set Format to 0"7-"000. However, I am not sure what iI should set the default value to. Projects listed currently are 07-002, 07-003 and I would like the database to automatically populate with the next project number 07-004. Any ideas or suggestions would be appreciated. Thank you Not exactly what you want, but the code is simi...

Strange result in pivot table
I use a pivot table to show transactions for the company. In the data list, all is OK but in the pivot table an item is shown as "Zho's, So". If I double click on it, the detail is still the correct one! So how can it be shown as something else than the detail data (which is 008W100GL1ST2Y) ? --- Message posted from http://www.ExcelForum.com/ Any chance you didn't refresh the pivottable after you changed the raw data? "Ankan <" wrote: > > I use a pivot table to show transactions for the company. In the data > list, all is OK but in the pivot tabl...

kb971117 report viewer redistributable 2005
I am having a problem getting this update installed. Have a 2003 Server Enterprise edition sp2. This guy became available a couple of weeks ago I believe. When it installs it prompts me for a location by saying this: Microsoft Report Viewer Redistributable 2005 The feature you are trying to use is on a network resource that is unavailable. Click OK or enter an alternative path to the folder containing the installation package ReportViewer.msi. So I do a search for this file and it is located in C:\Windows\microsoft.net\framework\v2.0.50727\Microsoft Report Viewer Red...

Pivot charts & tables
Hi, I'm using Excel 2002 and have a number of charts that I want to put together. I have 3 questions relating to pivot tables & charts. (1) I wish to use the same file but each month add new data to the source data. Will my pivot tables automatically incorporate new data? At the moment I have all my data in one worksheet. I assume the easiest method of doing this is to add new data below the last record? (2) I have a number of charts where I want only the top 10 series. For example I have a pie chart with the top 10 franchises (there are over 30). I have one other pie 'slice&...

SQL 'for xml' and C# example??
I'm missing some piece of the puzzle. I'm using 'for xml' type queries from SQL2K - and now trying to change the front-end from ADO to C#, I can't quite figure it out.. It seems that I could use an XmlReader.. and somehow use that instead of a DataReader.. but that didn't work.. I tried using a DataReader and get the byte array - but that wasn't right. SQL returns formatted XML, I need to load that XML into an XmlDocument somehow without ANY molestation of that dom. Any ideas? Examples? Missing links???? thanks THANKS!! I've been looking for the answer to ...

sql summarizing help
Hi All, Given: table1 --------- id, cdate, weeksago, amount, typeid, timeclose 496, 2010-02-11, 0, 450, 3, 1 490, 2010-02-01, 1, 200, 4, 1 491, 2010-02-01, 1, 350, 2, 2 493, 2010-02-01, 1, 500, 5, 1 489, 2010-01-21, 3, 150, 2, 1 136, 2010-01-12, 4, 500, 3, 2 137, 2010-01-12, 4, 100, 3, 1 138, 2010-01-12, 4, 500, 2, 1 1. i'm trying to create a sql that will give me a summary resultset grouped by weeksago. 2. i want to sum the columns and get counts based on typeid. 3. avg of timeclose here's the sql i have: select weeksago, , (sum(timeClose)/(count(timeclo...

table problem
I have a table with three columns of data. Column A contains a part number such as "Part XX", or "Part ZZ". Column B has a date in the 01/01/05 format. Column C has a numeric value. There are about 20,000 rows of data. The dates cover three years. For example: PartXX 1/1/05 123 Part XX 3/7/05 456 PartZZ 9/10/07 789 PartXX 1/1/06 159 PartXX 1/1/07 234 Eart part only has one entry for each date during the year. In the fourth column I'll add a column with all 365 days for the year. The date will be in the 1/1, 1/2, 1/3 format. I want to create an output which searche...

Access 2000 embedded Excel Pivot Table
I have created an Access reporting module that uses several Excel pivot tables which are embedded in the Access file. There doesn't appear to be any way to determine which table in Access the pivot tables are referenced to. If you right click on the pivot table and select wizard, the area in which the data source would normally appear in an Excel-based pivot table is totally blank. Any suggestions for how to identify the data source for a pivot table that is embedded in an Access 2000 file? Have a look at the property pivot_Table.SourceData "Jeff" wrote: > I have c...

Update multivalue field in table from form with SQL
Access 2007 on Vista I am using a form to establish the variables for a report before it is created. One field on the underlying table is a multivalue field, and the form includes a multi-select combo box control. After selections are made and the OK button on the multi-select combo drop down is clicked, focus moves to the next control. I have discovered though, that the underlying table does not update immediately, but does so correctly when I close the form. This sequence does not deliver the proper report data, so I need a way to immediately update the underlying table (m...

Access 2007 Pivot Table 04-23-10
How do I remove a pivot table? ...

Refer to SQL statements globally
I have two large SQL statements that I need to use across several forms, reports, etc. Rather than repeat them in each form, I'm trying to store and reference them globally. For example, I'd like to store strSQL1 and strSQL2 in a global module, so I can use either like this in a form: Random form: Me!lstResults.RowSource = strSQL1 Random report: Me.Report.RecordSource = strSQL2 Would I just store the SQL statements in a global module like: Public Function SQLSource() As String Dim strSQL1 As String Dim strSQL2 As String strSQL1 = "SELECT blah blah...

To SQL or not to SQL?
I have a very vertical product developed 100% in M/S Access. I developed it myself in 1993 in the initial release of Access and been with it ever since. It is currently very stable and running in Access 2003. My big question is how or even *if* we should move it to SQL. My customers are getting bigger all the time with more users. Typically our customers run one or two workstations generally never more than 5. Performance, however, is starting to slow with database sizes passing 500 meg. We also have a LOT of code behind forms so are bound tightly to the Access development environ...

Filling in blank rows in a Table of data
I have a table of data with the following layout Flag Name Service 1 mike service 1 service 2 service 3 1 dave service 1 service 5 1 jeff service 7 service 5 service 4 1 eric service 1 service 9 I am wondering if there is a fomula that will allow me to fill in the blanks (i.e., the missing names) in the name column. I would imagine t...

Payees in 2005 Business
When you change account or address information is another Payee created in Money? My gas company is listed under Payees and I have auto bill pay setup for them. However when I look up what has been paid to that payee it only goes back to March of this year! When I look up the category it goes back much further, so when I looked up the Payee for before March, the same Payee showed up but some information is different (and not greyed out like for my recurring bill). BTW... this is affecting Budgets as well when I look at my budget history. :) Graham ...

Pivot Table GETPIVOTDATA
I have a simple data table which I am using to better understand GETPIVOTDATA. PivotTable1 contains only 12 rows of data like so: Month Division Acct Amount 1/1/2005 A 100 553 1/1/2005 A 200 714 1/1/2005 A 300 6 1/1/2005 B 100 430 1/1/2005 B 200 729 1/1/2005 B 300 246 2/1/2005 A 100 670 2/1/2005 A 200 64 2/1/2005 A 300 799 2/1/2005 B 100 948 2/1/2005 B 200 563 2/1/2005 B 300 884 I have tried several variations of GETPIVOTDATA, but I am stuck (#REF!) on two in particular. The simple variations work well: =GETPIVOTDATA("Amount",$A$3) =GETPIVOTDATA($A$3,"Gran...