Tagged Questions

The tag has no wiki summary.

learn more… | top users | synonyms

3
votes
1answer
54 views

Overlapping indexes

I have two non-clustered indexes on one of my dbs and their definition are: CREATE NONCLUSTERED INDEX [NC_Index1] ON [dbo].[anyTable] ( [X] ASC, [Y] ASC, [Z] ASC ) INCLUDE (A , ...
5
votes
1answer
111 views

Query optimizer recommends adding index instead of using existing index

I am trying to determine why the query optimizer in SQL Server is recommending creating a new index instead of using an existing index that appears to be sufficient for the query. First the table. ...
5
votes
1answer
169 views

What is a non-clustered index scan

I know what table scan, clustered index scan and index seek is but my google skills let me down to find a precise explanation into non clustered index scans. Why and when a query uses a non clustered ...
1
vote
1answer
147 views

Why isn't SQL Server using my non-clustered index and doing a clustered index scan?

Here are my full tables: CREATE TABLE [dbo].[tblCrawlUrls]( [cl_IdUrl] [int] IDENTITY(1,1) NOT NULL, [cl_CrawlNormalizedUrl] [nvarchar](200) NOT NULL, [cl_RooSiteId] [smallint] NOT ...
1
vote
2answers
91 views

Index optimization for table that is truncated every day

I have a bunch of tables in SQL Server 2012. These tables are fed by other tables. Every day there are routines that truncate (fully delete) them and run queries whose result are inserted into them. ...
2
votes
2answers
133 views

Filtered index not chosen, and rejected with hint

I'm trying to understand why a non-clustered index is not available for a given query when filtered. The relevant part of my (large) query is this: ) results JOIN BE_Insurance ins ON results.PayorId ...
4
votes
2answers
123 views

Clustered Table Scan Because of “SELECT *”

I have a Records table with over 100 columns and very many rows, and a nonclustered index on 5 fields based on my access paths: CREATE NONCLUSTERED INDEX [IX_Records_CustomerID] ON [dbo].[Records] ( ...
2
votes
2answers
66 views

best possible index on this situation [closed]

create table #testing(id int, name varchar(50)) go Insert Into #testing Values(231, 'fasd') Insert Into #testing Values(232, 'dsffd') Insert Into #testing Values(233, 'xas') Insert Into #testing ...
2
votes
1answer
97 views

Skipping some data from Indexing in SQL Server

I have a situation where SQL Server is giving error that index size exceeding 900 bytes. Its happening because one of the column in index definition is varchar(2000), and whenever its length goes over ...
3
votes
1answer
85 views

Using unique non clustered index with unique clustered index

Would there be any scenario where it would make sense to create a unique non clustered index that includes a column that is contained in a unique clustered index? In other words, if I have a Products ...
7
votes
1answer
231 views

Necessary to include clustered index columns in non-clustered indexes?

Considering that a non-clustered index is based on the clustered index, would it be necessary for the non-clustered index to list any of the columns contained in the clustered index? In other words ...
0
votes
0answers
25 views

Indexing and Deadlock in SQL Server 2008? [duplicate]

I am running into a deadlock in SQL Server 2008. Using profiler I was able to determine that the deadlock happens as I am trying to select and update row(s) in table in 2 different sessions. The ...
2
votes
1answer
88 views

Clustered Index / Index Oriented Table performance in join, worse than nonclustered?

Can a clustered index (or IOT in Oracle) be detrimental, when to be used on a very "broad" table, but only few columns are used? In this case, the "Product" table is used only like a junction table ...
1
vote
0answers
135 views

Why does removing a RID by creating a clustered index lose parallelism and slow down execution by 2X

If I remove a RID lookup by replacing 2 non-clustered indices (non unique) with 2 identical clustered indicies (non-unique) where there weren't any before, why does this query slow down by a factor ...
0
votes
0answers
49 views

Is there a quantitative method to follow to determine FILLFACTOR for NONCLUSTERED INDEXes?

I've read different ideas to set FILLFACTOR and then monitor fragmentation over time (#1: 5 Things About Fillfactor, #2: Database Maintenance Best Practices Part II – Setting FILLFACTOR). Instead of ...
1
vote
1answer
143 views

Non-clustered index with all columns for “select * from”?

I have a simple SELECT * FROM MyTable WHERE DataDate = '18-AUG-2013' query on a table that contains 340 columns and 3.4M rows. Running estimated Execution plan in SSMS (Ctrl-L) suggests I create a ...
2
votes
3answers
772 views

Meaning of “nonclustered located on primary”

When I run sp_help (using ALT+F1) for a table, on the indexes, there is a column index_description. and the rows have for example: nonclustered located on PRIMARY (on foreign keys) nonclustered, ...
4
votes
2answers
228 views

If I update a column record in a table, will indexes that do NOT have this column in it be affected?

In terms of performance if I have a table like so: CREATE TABLE [TESTDATA].[TableA]( [Col1] [nchar](5) NOT NULL, [Col2] [nchar](2) NULL, [Col3] [float] NULL CONSTRAINT [TableA_PK] PRIMARY ...
1
vote
2answers
292 views

Clustered vs Nonclustered Index

My database currently has a primary Key/Clustered index on the ID column for each table. However, the application that connects to the database is always looking at views defined as: SELECT * FROM ...
4
votes
1answer
79 views

Index Evaluation

I have been doing some index evalaution on a table and this is the first time I have ever looked at how well indexes are performing so and would like some advice on a certain index. The non ...
1
vote
3answers
292 views

When to create Indexes?

I have a stored procedure that takes around 2 minutes to execute. The execution plan suggested me to create a non clustered index on a table (which is a high traffic table with millions of records in ...
2
votes
1answer
128 views

Unique Non Clustered Column in Partitioned table

We have partitioned one of our SQL Server tables on a Clustered Index datetime column, we have a Primary key Non-clustered index column with Identity data type and this column is setup as a ...
1
vote
1answer
215 views

Covering Index - Include columns

I have a table : [sch].[Parent] ( ID Identity [INT] (PK), Name VARCHAR(100), Email VARCHAR(250), Active TINYINT, InsertedBy VARCHAR(100), InsertedDTM DATETIME, ...
2
votes
1answer
411 views

Non-clustered Index Update not displayed in execution plan [duplicate]

Possible Duplicate: Nonclustered Index Insert I'm trying to work out the cost of having a nonclustered index on a particular table, but I don't see any maintenance cost in the execution ...
0
votes
1answer
4k views

fundamental difference between Primary/Clustered and Secondary/Non-Clustered index

Are Primary Index and Clustered Index similar in concept Secondary Index and Non-Clustered Index similar in concept
5
votes
2answers
899 views

Large Fact table and partitioning key dilemma

I have fairly large fact table (2 billion records, approx 120 GB). This table is not partitioned and the queries are very slow to respond. I am planning to partition the table and indexes. The table ...
2
votes
1answer
162 views

Aren't two writes required to update a clustered index record

I was reading the article on indexes at simple-talk, where it is written that If a heap has a non-clustered index on it (as the primary key), and data is inserted into the table, two writes have ...
5
votes
2answers
3k views

Should I create a nonclustered index on all searchable columns or just each column separately?

I am not sure as to properly use non-clustered indexes. The SSMS plan estimator said to add another index when the following index was on the table already. CREATE NONCLUSTERED INDEX ...
4
votes
1answer
512 views

How are indexes represented in an ERD?

What do indexes look like in an Entity Relationship Diagram? I've googled it and I'm unsure what the standard look of an index is in an ERD. And I'm talking about the crowsfoot diagram that includes ...
4
votes
2answers
200 views

Need for reaching data through clustered index with a non-clustered index

I have found that when a table has both clustered and non-clustered indexes (on different columns), the leaf level non-clustered pages, instead of pointing to the data row, point to the node of the ...