.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
MarieAdela
Imran Ghani
Post New Web Links

Creating a clustered index on a partioned table - need help understanding effect of filegroup placem

Posted By:      Posted Date: April 10, 2011    Points: 0   Category :
 

We have a large table ~40 Million rows and about 35 columns.  Things are starting to slow down and i am looking at partioning.  The table has some historical data and most of it can be ignored except for the current year, so I wanted to partion the data on the year column.  So I created a filegroup and ndf/ldf files for each year (ie: 2005,2006,2007, etc...)

The primary key is clustered on an identity column in this table.  When I went to recreate the table I got a warning message that the clustered index would be created on the primary file group as I specified, so i decided to do some research and found this article:

http://www.sqlservercentral.com/Forums/Topic522984-361-1.aspx#bm739776

This leads me to believe that creating a clustered idex on the primary file group will prevent true partionting.  Is this true?  If so where should I store my clustered index as this seems to defeat the purpose of partioning then.

Also I was reading something that the clustered index should contain the key column that the data is partioned on, so it should be something like (PriKeyCol, theYear) ? as opposed to just the (PriKeyCol)

Thanks for any help,
Matt




View Complete Post


More Related Resource Links

Creating indexed view from the table which has no index?

  

Hi,

Can we create indexed view from the base table which has no index?

This is because i don't want any indexes in my base table but for better query performance i want in view..

Please suggest..

Thanks in advance,

Senthil


SQL 2005 Inserting into Cluster vs. Non Clustered index table

  

All things being equal, when inserting data into cluster indexed table, assuming I am inserting in the order of clustered index, is there a difference how sql server will fill in the physical pages compared to if the inserted table had only non-clustered index? 

On a different subject, is the physical location of data on a page effects query performance?  If so, how?

Thanks in advance


Cutting Edge: Creating a Multi-table DataGrid in ASP.NET

  

If you bind a multi-table DataSet to a DataGrid, only the first table is recognized. Here Dino Esposito writes a custom solution the the multi-table problem.

Dino Esposito

MSDN Magazine August 2003


Creating a table-valued function on a user defined type in SQL Server CLR

  
We would like to be able to create a table-valued function on a user defined type.  We would like the syntax in SQL to look like it works for the XML nodes function:   DECLARE @myXml XML = '<a><b>1</b><b>2</b><b>3</b></a>' ; SELECT  node.query('text()') FROM    @myXml.nodes('a/b') nodes (node) ;   In other words, in the FROM clause, we can access the "nodes" method of the xml variable and it returns a rowset. Thanks!

SP2010- Creating an Index server which will host all the Service applications

  
Guys, Current Environment : 1 Sql server 2008 R2 and 1 SharePoint 2010 Server OS : windows server 2008 all 64 bit (goes without saynig) For better performance i want to seperate out Service application ( expecially Search Service application) to another windows server while keeping the existing one as a WFE hosting CA as well. From the little documentation i found i am creating an Index server(with out CA on it) 2 questions here : 1. Is this the best way to optimize my farm? 2. Is there any online documentation or any knd of link that woudl describe how to set up the index server. thanks in advance. Sameer  

Does inserting a non clustered index reduce IO?

  
I ran a report on my prod server (Performance - Top queries by total IO) and found the following query the 2nd highest.   select * from Table1 where Col1 is null order by Col2, Col3, Col4 Table has 100,000 rows. 1 pk and about 30 columns After running it against engine tuning advisor it advised, if i created a non clustered index on Col1, Col2, Col3 and Col4 it would increase the performance by 77%. But when i run the command just by it self it runs and completes instantaneously. What am i missing? Should i create the index to reduce the IO's as the Advisor suggests?    

Help creating HTML table from dataTable when every nth dataTable row = 1 html table row... C#

  
Hello, Im trying to create an html table from data that is in a dataTable but Im having trouble coming up with the right logic. Every 3 rows in the dataTable needs to be 1 row in the html table.  Ive tried using foreach through the datatable with a modulus to determin every third dataTable row but i still run into snags because i am building the table using objects rather than using concatenated strings..(example TableRow tr = new TableRow  etc...). I cant add the cells to the rows when the row is only created in an if statement etc. Can someone post a good example in c# of how they would achieve this. The amount of data in the dataTable could change from time to time so the code cant rely on a pre determined size, unless you just need the total rows and columns, that can be dtermined when the dataTable is filled from the sqlDataReader. <table><tr><td>dataTable row 1 values</td><td>ataTable row 2 values</td><td>dataTable row 3 values</td></tr> <tr><td>dataTable row 4 values</td><td>dataTable row 5 values</td><td>dataTable row 6 values</td></tr>   Any help and example would be great. Ive been able to do similar tables in other apps but thats only when I use concatenated text strings to build the html table. Im trying to stay away from that this time

Clustered vs NC index storage differences?

  
Does clustered index takes more disk space than NC for the same filed(s) for large tables? Also is CI takes longer (time) to build than NC if you have large tables? Any microsoft articles supporting these aruguments.

how can i force to use index on table

  
I have go through execution plan,it shows me a table scan(10%) on a table who has the index column.then also it show table scan so how can i force it to use the indexe column from the table???   Thank's Digambar

Creating table with database data, ASP.net and C#

  
Hi!I just started with my first ASP.net page with C# and I want to create a dynamic table with information from my database. I have connected to the database to the page with datareader, so that is solved. The problem is making the table and import the data into the different rows and columns. I also want the possibility to edit and remove this information from the table. Its supposed to be a table with information about customers.I have no idea whats the best solution for this and I hope you guys got information or links that show how to do this?Regards,fh01

Changing table name and Index relation

  
Hi experts, Does changing the table name in sql require anything with the indexes as well? Do I need to drop and create indices in this case? Regards

Oracle clustered table to MS SQL Server 05 SSIS

  
Hi Guys, Can the SSIS deal with clustered Oracle tables? Moreoever, what are clustered tables compared to partitioned tables? I cant find much about it. (only sytax how to create them). Thanks, metalray

How index will effect in update query

  
Hi All, I have one update query it has lot of joins with other table ,If we add index in the secondary table with this join condition will it improve the performance? eg: UPDATE FunctionalAcknHeaders  SET cStatus = 'E'  FROM FunctionalAcknHeaders fh   LEFT JOIN OrderHeaders oh ON cast(oh.nOrderHeaders_id as varchar(10)) = 'abc'  WHERE oh.nOrderHeaders_id is null   AND fh.cStatus = 'R'   if i create index on OrderHeaders  table nOrderHeaders_id, will it improve the performance ? Thanks, Roshan

Partitoning Table in SQL 2008 - Index

  
1. How is the index mainitained for a partitioned table?  2. Whether SQL Server has an overhead in maintaining INDEX in a partitioned table compared to a non-partitioned one? 3. If i insert few rows, does it affect the INDEX statistics in all partitions or only the affected partition. I would like to find out more details on how SQL Server manages INDEX on a partitioned table.

Table scan going on apart from having an Index

  

Hi,

I have a stored procedure where the performance is bad. The Execution plan shows 5 table scans each of cost 12%. The data is being pulled from the table using the following where clause.

where (datestamp>getdate() - 'sep28 1902 12:00 AM')

I already have an index on Datestamp column but still Table scan is happening. There is no restriction on avoiding index is specified in the query. 

If the index is place inappropriately there would be index scan but here I have table scan.

Could anyone help me in finding out why this is happening.

 

-Thank you,

Jayasree


Table scan going on apart from having an Index

  

Hi,

I have a stored procedure where the performance is bad. The Execution plan shows 5 table scans each of cost 12%. The data is being pulled from the table using the following where clause.

where (datestamp>getdate() - 'sep28 1902 12:00 AM')

I already have an index on Datestamp column but still Table scan is happening. There is no restriction on avoiding index is specified in the query. 

If the index is place inappropriately there would be index scan but here I have table scan.

Could anyone help me in finding out why this is happening.

 

-Thank you,

Jayasree


Creating a Form say grid sort of a interface where in data values will be extracted from the table 4

  

how dis can be done help please with simple button click and changing the values it can be done but how to move columns with data in graceful manner the way we have in calendar control... 


Categories: 
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend