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


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

Cluster Index in a table

Posted By:      Posted Date: October 16, 2010    Points: 0   Category :Sql Server
 

Hi Team,

 

Can you correct me.

 

I have one table i have create primary key on that. automatically clustered index also created right.

 

here what is the purpose of primaykey and clustered index also

 

in case i have drop the clusted index what happend..

 

Tx


subu


View Complete Post


More Related Resource Links

Cluster Index in a table

  

Hi Team,

 I have one small dought,..

Can you any body correct me.

when ever we create a primary key in a table automatically clustered index is created right 

what is the task can do in Primarykey here

and what is the task can do in Clustered index here..

can you please guide me..

 

tx


subu

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


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

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

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


Cluster index update

  

Hi, I'm having a deadlock issue. I noticed that I've 2 queries one doing an update and the other doing an insert,

My table has a cluster index on the ID which is an identity field. The update statement is updating other columns which are not included on the cluster index, however on the execution plan it shows that it will need to do a cluster index update.

What's the reason it could be that the cluster index needs an update if the column itself is not being updated?

Thanks in advance.

FR


How do i iterate through a table that does not have an index column?

  

hi friends,

I have the following table variable that i store rows extracted from an xml document;

declare @tbl_ans table ( AnsText nvarchar(max), IsCorr bit)

this cannothave an index column.

assume it has 25 rows, i need insert each row to a table  that has three columns

That Answers table has following structure:

a_idx int,

a_text nvarchar(max),

is_active int,

upt_date datetime

The script should iterate through the @tbl_ans_table table and insert each to the answers table then i should fetch each a_idx in each iteration and insert it to another. But the problem is how itereate through @tbl_ans_table without an index?

thanks

 


Replicating a table to it's duplicate with different physical /index structure

  
Hello,  We're planning to replicate a very large table (about 1TB) -- Yes Table, not database.  Can afford only very little (10-20 min)  downtime.    Is it possible to have clean replication with the source table in the source database (non partitioned) to destination table in the destination database (partitioned and indexed differently than the source) ?   Although data is the same.     We're primarily doing this replication for paritioning sake , as we can't afford downtime.  So the plan is to replicate the entire source database to the destination.   There are 2-3 large tables that hold 80% of the database data.   So when both DBs are in sync, we'll repoint the production.

   Thanks.

Policy Management ensure a table name is in the index name

  
I am trying to write some policies to enforce our coding standards.  One of the standards we have is to prefix indexes with IX_<tablename>; the IX_ is easy to do with the index facet.  How do you access the tables name in the condition?
Thanx, Adam

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


get a correct row index after refresh the gridview table

  

hi all,

i am facing a problem to get a correct row index after i filter the data in table, it always keep the history row index in the table  before i filter. i had a search button to filter the data in gridview,

below is my coding:

 

<ItemTemplate>
 <asp:Button ID="btn_reissue" runat="server" Text="Reissue" CommandName="reissue"  CommandArgument='<%# CType(Container, GridViewRow).RowIndex %>' OnClientClick="return window.confirm('Are you sure you want to reissue this record?');" Width="65px" />
                                  </ItemTemplate>


 

    Protected Sub grdview_rowcommand(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewCommandEventArgs) Handles gridView1.RowCommand
        Dim cmd As SqlCommand
        If e.CommandName = "reissue" Then
            If Not pUser Is Nothing Then
                pDbUserCon = New SqlConnection(pDbESS)
                pDbUserCon.Open()
                pDbUserTrx = pDbUserCon.BeginTransaction()
                Try
                    Dim index As Integer = Convert.ToInt32(e.CommandArgument)
                    Dim row As GridViewRow

Get the type of an index in a table

  

Hello,

Can you please take a look at the question at the following link and try to help me?

http://social.msdn.microsoft.com/Forums/en-US/adodotnetdataproviders/thread/51af6d24-ce02-4721-b126-b8a0a7fb23c7

Thanks.


What's the Realistic Maximum Number of Index per Table

  

What is a good real world maximum(so i am not looking for this answer:http://blog.sqlauthority.com/2009/06/29/sql-server-maximum-number-of-index-per-table/) for the number of indexes on a table (table has around 50 columns and at the moment 3 indexes)

. I would like to add 3 more indexes on fields that are not used for joining but are searched upon lots of the time. Is this a good idea?

create index in subscribe table

  
Can I create a new index in the subscribe table? Thanks.

Cluster index and primary key

  

Hi Team,

 

To day i went one DBA interview 

 

There that guy is asking what is clustered index and 

a : it is pointing to leaf node.

what is primary key ?

a: it is dont allow null values and duplicate values 

when ever we have create clustered index  in a table automatically primary key is created right  ? -- i am bit confussion here 

 

all redy atable have primary key  again we have to created clusted index on that id col is it possible or not i am trying here but here it is geeting error.

 

can you please explaine me step by step the above points plz..

 

Tx

 

 

 

 

 

 

 

 

 

 


subu
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