Skip to main content
29 votes
Accepted

Does a non-clustered index make any guarantees about row order?

If you need order in your query results, put in an ORDER BY. It’s that simple. Check out this article from SQL Server architect Conor Cunningham which pretty much sums this topic up: No Seatbelt – ...
Scott Hodgin - Retired's user avatar
27 votes
Accepted

Why is casting a DATE field to VARCHAR datatype non-deterministic and is there a way to make it deterministic?

CAST is not deterministic because date format may change based on the server settings (i.e. under compatibility before 110 default date format is 0 => "Dec 12 2019 2:11PM" and output dependeds on ...
Piotr Palka's user avatar
  • 1,591
19 votes
Accepted

What index to use with lots of duplicate values?

BTree My issue here is that the BTree index will be huge since afaict it will store duplicate values (it has too, since it can't assume the table is physically sorted). If the BTree is huge I end ...
Jack Douglas's user avatar
19 votes

In SQL Server, why can a backward scan of clustered index cannot not use parallelism?

The referenced article specifically states the reason backward ordered scans were not parallelized in SQL Server 2008 (as of CU6) isn't technical but because the feature hadn't been requested by ...
Dan Guzman's user avatar
  • 28.3k
18 votes
Accepted

Is it a good idea to Partition a table (in MS SQL) based on date, when there's a clustered index present on ID (INT)

Short answer: no, you cannot do this. Per the docs... When partitioning a clustered index, the clustering key must contain the partitioning column. This means that in order to partition on [...
Peter Vandivier's user avatar
17 votes

Does a non-clustered index make any guarantees about row order?

Absolutely not - without ORDER BY row order is NEVER guaranteed. The execution plan chosen MAY result in the order wanted, but if so it is by chance. Storage order vs Result Order DBA.SE thread
George.Palacios's user avatar
17 votes
Accepted

Does it make sense to add indexes to a clustered columnstore index table?

My understanding of a clustered columnstore table (and please correct me if I am wrong) is that each column is stored in some physically ordered manner... All columns are sorted in the same order. I ...
Paul White's user avatar
  • 86.4k
15 votes
Accepted

Ordering of table is based on Clustered index or non clustered primary key?

Unless you explicitly state a desired order using an ORDER BY clause you can not guarantee the order that data will be presented in response to a query. Without an ORDER BY clause the engine is free ...
David Spillett's user avatar
14 votes
Accepted

Unexpected update results on heap using SET @Variable = Field= @Variable + 1, fixed with clustered index

The documentation for the UPDATE statement says (emphasis added): Variable names can be used in UPDATE statements to show the old and new values affected, but this should be used only when the UPDATE ...
Paul White's user avatar
  • 86.4k
13 votes
Accepted

Is heap a table without indexes at all or without a clustered index?

You need to be very careful what you read on the interwebs ;-) (of course, that also goes for this answer or pretty much anything anywhere, but still). Just as there is a lot of good information out ...
Solomon Rutzky's user avatar
13 votes
Accepted

Is it okay to have a fill factor of 100 on the clustered index that is on an ID column?

In most cases you do want a FILLFACTOR of 100. Only in special cases would you want to lower that value. Updates only cause page-splits if they increase the size of the row such that it won't still ...
Solomon Rutzky's user avatar
13 votes

What happens when you change a heap into a clustered table in SQL Server?

Yes, when you create a clustered index on a heap the rows are all sorted moved to the new clustered index. Any non-clustered indexes are rebuilt with the new clustered index key as the row locator. ...
David Browne - Microsoft's user avatar
12 votes

Should I add an auto-increment / IDENTITY field to a cross-reference table just for PK purposes?

One thing to consider is that a Primary Key and a Clustered Index are not the same thing. A Primary Key is a constraint and deals with the rules by which the data lives (i.e. data integrity); it has ...
Solomon Rutzky's user avatar
12 votes
Accepted

Clustered index creation fail on table creation

The syntax for inline index declaration was added in SQL Server 2014, though that is was absolutely unclear in the official CREATE TABLE documentation. After speaking to the documentation owners, that ...
Aaron Bertrand's user avatar
12 votes

Rebuild Very Large Primary Key Index

Even though it's a bit late, I'm going to field a response with hope that it helps or at least spurns some additional ideas/commentary on this issue because I think it's a good question. First, and I ...
John Eisbrener's user avatar
10 votes
Accepted

Clustered index not used in a delete statement

I'd like to know why the optimizer does not use the clustered index, but is using the non-clustered one? This will be a decision of the cost based optimizer. It estimates that it is cheaper to ...
Martin Smith's user avatar
  • 84.9k
10 votes
Accepted

How do I intentionally fragment a SQL Server Index?

One quick way I can imagine is creating a table with UNIQUEIDENTIFIER as a primary key and inserting lots of random values. This could be achieved using this script: CREATE TABLE dbo.Tests (Id ...
Evaldas Buinauskas's user avatar
9 votes
Accepted

Does Clustered Index on GUID create more fragmentation than Non Clustered Index?

Since you are indicating insert performance is the primary concern, I'd take the recommendation of the DBA and make the clustering key the identity column since it is a unique, monotonically ascending ...
Hannah Vernon's user avatar
  • 70.2k
9 votes
Accepted

Benefits of not having a clustered index on tables (heaps)

I'm only addressing the question in the title. The question body has too many different questions to be answered in a stack exchange answer. Heaps have the following advantages over tables with ...
Joe Obbish's user avatar
  • 32.5k
9 votes

Is it a good idea to Partition a table (in MS SQL) based on date, when there's a clustered index present on ID (INT)

If you decide to do this in one go it's likely to be limited by the speed of the disk. A comparable operation would be taking backups. Backups read a whole bunch of bytes from here and write a whole ...
Michael Green's user avatar
8 votes
Accepted

What happens when you delete a Clustered index and Non Clustered Index once created

From MSDN: When a clustered index is dropped, the data rows that were stored in the leaf level of the clustered index are stored in an unordered table (heap). Dropping a clustered index can ...
Kris Gruttemeyer's user avatar
8 votes
Accepted

Is pre-sorting valuable when using table value constructor to insert multiple values into a table with clustered index?

I've constructed a test-bed to see what happens: USE tempdb; CREATE TABLE dbo.TestSort ( Sorted INT NOT NULL CONSTRAINT PK_TestSort PRIMARY KEY CLUSTERED , SomeData VARCHAR(...
Hannah Vernon's user avatar
  • 70.2k
8 votes
Accepted

Insert much slower when target table has clustered index

Let's take a step back and forget all of the troubleshooting around the clustered index. You have an INSERT query which used to finish in a reasonable amount of time but now won't finish after hours. ...
Joe Obbish's user avatar
  • 32.5k
8 votes
Accepted

Performance differences between RID Lookup vs Key Lookup?

Leaving aside the fragmentation bogeyeman (it doesn't really matter when doing singleton lookups), the main difference is that an RID specifies the exact page a row is on, while with a Key Lookup you ...
Erik Darling's user avatar
  • 41.9k
8 votes
Accepted

How is the clustered index different from the table itself?

Don't get caught up the the physical/logical aspect. It is just terminology and what you mean by "physical" is just a matter of abstraction level in the end. IMO, the term "physical&...
Tibor Karaszi's user avatar
8 votes

Dropping a unique clustered index on identity colum

leave it alone In most OLTP workloads, the primary key is a surrogate key. This isn't a bad thing, especially because someone came along and decided that GUIDs are the best way to uniquely identify ...
Erik Darling's user avatar
  • 41.9k
7 votes
Accepted

Why SQL Server uses Non-Clustered but not Clustered Index?

The optimizer has a choice between two main strategies: Scan the table (the clustered index) checking every row to see if LoanNum = 2712. Scan & Lookup Scan the nonclustered index to find rows ...
Paul White's user avatar
  • 86.4k
7 votes
Accepted

Should I add an auto-increment / IDENTITY field to a cross-reference table just for PK purposes?

For a non-unique clustered index on comany_id alone, SQL Server will automatically add a 4 byte integer uniqueifier to all duplicate (i.e. second and subsequent for a key value) clustered index keys ...
Martin Smith's user avatar
  • 84.9k
7 votes
Accepted

What does having the primary key as the last column in a composite secondary index in an InnoDB table do?

No. If your table has the InnoDB engine and the PRIMARY KEY is (pet_id), then defining a secondary index as (person_id) or (person_id, pet_id) makes no difference. The index includes the pet_id ...
ypercubeᵀᴹ's user avatar

Only top scored, non community-wiki answers of a minimum length are eligible