53

Does the order of columns in a PK index matter? Yes it does. By default, the primary key constraint is enforced in SQL Server by a unique clustered index. The clustered index defines the logical order of rows in the table. There may be a number of extra index pages added to represent the upper levels of the b-tree index, but the lowest (leaf) level of a ...


30

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 – Expecting Order without ORDER BY


27

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 language). To make it deterministic use CONVERT and the style parameter must be a constant. Additionally, styles less than or equal to 100 are nondeterministic,...


22

The following explanation is given in this Microsoft Technical Article: Why does the first index on a view have to be CLUSTERED and UNIQUE? It must be UNIQUE to allow easy lookup of records in the view by key value during indexed view maintenance, and to prevent creation of views with duplicates, which would require special logic to maintain. It must be ...


21

The primary problems with GUIDs, especially non-sequential ones, are: Size of the key (16 bytes vs. 4 bytes for an INT): This means you're storing 4 times the amount of data in your key along with that additional space for any indexes if this is your clustered index. Index fragmentation: It is virtually impossible to keep a non-sequential GUID column ...


21

Does Clustered index exist in Oracle database? since I read in some blogs Yes there is. It is called "index organized table" (IOT) - which in my opinion is the better name as it makes it absolutely clear that the index and the table are the same physical thing (which is the reason why we can have only one clustered index in SQL Server) If yes, ...


19

                          Please note that the following info is not intended to be a comprehensive description of how data pages are laid out, such that one can calculate the number of bytes used per any set of rows, as that is very complicated. Data is not the only thing taking up space on ...


19

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 customers and the dev team didn't bother to implement it. Note that the article was written nearly 10 years ago in the context of the now unsupported SQL Server ...


18

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


16

When a table has a clustered index, the index is the table data (otherwise you have a heap type table). A rebuild of the clustered index (any index in fact, but the space wouldn't be counted as "data" for a non-clustered index) will result in partially used pages being merged into a more full form. As you insert data into an index (clustered or otherwise) ...


16

From Microsoft SQL Server 2008 Internals: In fact, improving scalability is the primary reason for the limit to an index key of 900 bytes, or 16 columns, whichever comes first. At 900 bytes per key, you can have a maximum of 8 index entries per page. As you decrease the maximum number of index entries per page, you vastly increase the number of levels ...


16

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 up having to read both the index and the parts of the table that the index points too... Not necessarily — Having a btree index that is 'covering' will be the ...


15

FILLFACTOR only applies when you build or rebuild an index, not during normal operation. Normal operations always try to fill the pages to 100%. If you insert a row that has a variable width, then update the row to be longer, that row will no longer fit on the page if there isn't enough extra space to store the after-image on the same page. If there isn't ...


15

Yes, the order is critical. I highly doubt you ever query by RowNumber (eg WHERE RowNumber=1). Overwhelmingly time series are queried by date (WHERE DataDate BEWEEN @start AND @end) and such queries would require a clustered organization by DataDate. Fragmentation in general is a red-herring. Reducing fragmentation should not be your goal here, but having a ...


15

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 to present data to you in any order it finds most convenient at the time, which can mean a different order for the same query you ran earlier. If there is a ...


14

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 say this to distinguish from a possible reading of your statement that each column is sorted in the best way for that column, which isn't true. Each column is ...


13

There is nothing wrong with GUID as keys and clusters in an OLTP system (unless you have a LOT of indexes on the table that suffer from the increased size of the cluster). As a matter of fact, they are much more scalable than IDENTITY columns. There is a widespread belief that GUID are a great problem in SQL Server - largely, this is quite simply wrong. As ...


13

I know that when VARCHAR(MAX)/NVARCHAR(MAX) columns are used the data is stored out of the row... Actually, that depends on the setting of the large value types out of row option, which can be set using sp_tableoption. From the documentation: The default is for MAX values to be stored in-row, up to 8000 bytes, if they fit. Unless you have used ...


13

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 there, there is also a lot of misinformation (and sadly, this is not confined to technical info). And people copy and paste / repost / share both. So, it is good ...


13

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. It's the same table, though, so triggers, constraints, etc don't have to change. The reverse is not true, however. When you drop a clustered index on a table, ...


12

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 nothing to do with efficiency / performance. A Primary Key requires that the key column(s) be unique (in combination) and NOT NULL (individually). A PK is ...


12

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 topic now accurately reflects that inline index syntax is only valid starting with SQL Server 2014 (and some variations in 2016): The other instances, ...


10

why isn't it possible to access the data directly from the table discarding the B-tree? (most likely by scanning the table row by row) wouldn't that be more appropriate than inaccessible data at all? To answer your question, Indexing basics comes more handy -- An index is made up of a set of pages (index nodes) that are organized in a B-tree structure. This ...


10

To check what CLUSTER does, I took a table fo mine from an earlier experiment which basically contained the first 10 million positive integers. I already deleted some rows and there is an other column as well but these only affect the actual table size, so it is not that interesting. First, having run VACUUM FULL on the table fka, I took its size: \dt+ ...


10

You can create a UNIQUE CLUSTERED INDEX only on a column (or combination of columns) that contains no duplicate data. The fact that you create the index with the UNIQUE keyword means that you are now constraining users from adding non unique data. (in the key column(s) of the clustered index) A CLUSTERED INDEX can be created on a column (or combination of ...


10

An INSTEAD OF trigger stores a copy of the rows that would be affected in a hidden work table*. This is the Clustered Index Insert you see. The trigger body reads from this work table* and any data changes in the trigger use the 'normal' operator (Clustered Index Update in your example). *The query processor internally renames the work table when ...


10

The clustered index IS the table. In the classic phone book analogy where everything is ordered by Lastname, Firstname there's not some other book that the phone book refers to. The Phone book IS the book, and it's in that order. When you have a clustered index, it contains all the data for all the columns in the table, ordered by the key you pick. In the ...


10

I don't think you can perform this operation online, since you can't just move the clustered index from one column to another, and you can't use DROP_EXISTING to reduce the amount of work that has to be done. However, you can avoid an outage, if you're willing to perform the work. Create a new table with the new structure Rename the original table, and ...


10

I have found the following about Clustered Index: Data is stored in the order of the clustered index. Only one clustered index per table. When a primary key is created a cluster index is automatically created as well. You dug a couple of hours. You should have noticed that all the above facts are for SQL Server and not Oracle. Tables (at least ordinary ...


10

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 fully scan the narrow index. It seems that you were expecting a nested loops with seeks on the clustered index? The execution plan shows that the table #ToPurge ...


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