The tag has no wiki summary.

learn more… | top users | synonyms

7
votes
3answers
1k views

Index performance on ON versus WHERE

I have two tables @T1 TABLE ( Id INT, Date DATETIME ) @T2 TABLE ( Id INT, Date DATETIME ) These tables have a non-clustered index on (Id, Date) And I join these tables SELECT * ...
0
votes
0answers
3 views

Table with composite unique cluserted key and non-clustered surrogate primary key

We are running a legacy application which is an OLTP SQL 2008 R2 database having all tables defined as heaps with non-clustered index on surrogate primary key. I am considering introducing ...
2
votes
1answer
20 views

How to copy non clustered indexes in transactional replication

I create transactional replication ,but forget to include non clustered indexes and now my subscriber have only clustered indexes (primary key) and I want to transfer all nonclustered indexes to ...
2
votes
1answer
29 views

Non clustered index for non-evenly distributed data in large SQL table

I have the following table containing 2.000.000.000 rows: CREATE TABLE [VeryLargeTable] ( [dataCol1] [NVARCHAR(MAX)] , [dataCol2] [NVARCHAR(MAX)] , [Manufacturer] [NVARCHAR(10)] , ...
0
votes
0answers
15 views

Azure Database Sync with warning on row update

To create a real time backup DB in Azure, I have used the Azure sync group. What I have noticed is that if I edited one of the rows in any table (let's say, the customer's name) , I receive the ...
0
votes
0answers
36 views

What happens to the database structure when i add a nonclustered index

Our team is working for a while now with Azure SQL. After a lot of trying , we managed to add a sync group between the main DB and the backup one. Of course, this means that for every change in the ...
2
votes
1answer
65 views

Multiple indices on a table

I have run SQL Profiler and I can see that I'm "missing" some indexes (non-clustered) on a table. How is it best to handle indexes that can be a "subset" of another index? Index 1 UserId, ...
4
votes
1answer
58 views

MSSQL suggestion to create nonclustered index - on 2 columns, reversed

I have a query, where MSSQL 2012 suggested a create a nonclustered index as follows: CREATE NONCLUSTERED INDEX [<name>] ON [dbo].[tblUserCourses] ([courseID]) INCLUDE ([userID]) I created the ...
3
votes
1answer
95 views

CREATE NONCLUSTERED INDEX ONLINE, DROP EXISTING

I want to add three included columns to an existing non-clustered index. With that I'm solving performance issues in the database. Here is some information about the index: Rows: 112 Millions Disk ...
2
votes
1answer
258 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 ...
0
votes
1answer
43 views

One Non-Clustered Index that covers every possible combination of predicates?

Say we have a table against which a number of different queries can be executed and each query has its own combination of predicates. So, for example, if we have a table with columns A, B, C, etc we ...
2
votes
0answers
43 views

SQL Server 2005 Datawarehouse DB - Integrity failure caused by NonClustered indexes

Last year I've been working with a data warehouse database (SQL Server 2005) loaded on a daily basis during the nights. Overall database performance is acceptable. However, there is a "known error" ...
2
votes
3answers
357 views

Why is the SQL Server query plan not using indexes in a query almost similar to one that uses indexes?

I have two UPDATE queries that are similar in structure, yet the SQL Server query plan for one shows indexes being used, and for the other it shows only a regular table scan. The following are the ...
0
votes
2answers
405 views

Storing IP addresses - varchar(45) vs varbinary(16)

I am going to create a table with two fields - ID as BIGINT and IPAddress as either varchar(45) or varbinary(16). The idea is to store all unique IP addresses and use a reference ID instead the actual ...
0
votes
2answers
91 views

What to do with a table with a lot ( 50+ ) indexes? SQL Server 2008

We have a table here, used by our software. Since i'm new in this company ( the new dba here ), what i see is that some tables of some databases have a LOT of indexes ( like 50...60 ). Is this good? ...
0
votes
1answer
62 views

Order of Columns in Index

I have following query which has ON clause on different column, Where clause on different column and Selection on different columns so my question is what should be Order of Columns in Index as ...
1
vote
2answers
134 views

Are nonclustered indexes used when joining tables?

I know that joins of type Sort-Merge don't use nonclustered indexes, because they work by sorting both tables and then joining them. But I've read recommendations to noncluster index all FKs, I ...
6
votes
1answer
596 views

Why can a non-clustered index not be dropped using online=on option

When attempting to drop a non-clustered index using the online option such as DROP INDEX [IX_MYINDEX] ON [dbo].[myTable] WITH ( ONLINE = ON ), I receive the following error message. Msg 3745, Level ...
2
votes
0answers
97 views

Covering Index - Insert Performance

We are running some performance tuning on our database in SQL Server. The database in itself is pretty small with around 13 tables. But there are multi-level relationships between tables. When I try ...
1
vote
2answers
327 views

Will a non-clustered index on a column with covering index on all the columns of table affect the Delete statement Records)?

I am trying to delete thousands of records say 35K from a table which consists like 10 million records. Delete * from Table where ScenarioID=859 I have also tried bulk deletion DECLARE ...
3
votes
2answers
411 views

Using sp_BlitzIndex - Diagnosed as “Index Hoarder: Addicted to nulls”

Context: MS SQL Server 2012 In using Kendra Little's awesome tool, sp_BlitzIndex, a number of tables were diagnosed as "Index Hoarder: Addicted to nulls". I'm not looking for a debate over NULLs and ...
1
vote
1answer
55 views

Which Indexes in which situations is more efficient in SQL Server 2012

I use SQL Server 2012. I want know when I want to know in what situation I have to use CREATE NONCLUSTERED COLUMNSTORE INDEX .... and in what situation I have to use Create NonClustered Index... ...
0
votes
1answer
67 views

non clustered index on same table but the order is different?

I have one table it has 5 columns like col1...col5 . i need to create non clustered index on col2,col3,col4. I did .. then my boss is asking me create one more non clustered index on col4,col3,col2 ...
1
vote
1answer
52 views

What happens if an index build is cut half way through?

Today I ran a create NC index but stopped the execution after running for 1 hour 46 minutes. The main reason being I started to get worried about the disk space getting lower. It consumed roughly 10 ...
5
votes
3answers
409 views

SQL Server clustered index, index balancing and insert performance using NewID

I have a large (6db) trace table. It has a clustered key (DateTime) which is created through GETDATE(). The connection pool for connections to this database/table rises as high as 50 on average ...
3
votes
1answer
161 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 , ...
7
votes
1answer
222 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
611 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
676 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
263 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
73 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 ...
3
votes
2answers
2k 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 ...
5
votes
2answers
162 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
1answer
107 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
107 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 ...
8
votes
1answer
1k 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 ...
2
votes
1answer
131 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
152 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
55 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 ...
2
votes
3answers
1k 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, ...
1
vote
1answer
339 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 ...
4
votes
2answers
460 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
407 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
83 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
439 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 ...
1
vote
1answer
315 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
527 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 ...
5
votes
2answers
1k 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 ...
0
votes
1answer
5k 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
2
votes
1answer
185 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 ...