Aside from what was mentioned in the comments / chat discussion regarding index tuning and query tuning based on what your execution plan says, alternatively you may be able to leverage the https://docs.microsoft.com/en-us/sql/relational-databases/performance/monitoring-performance-by-using-the-query-store?view=sql-server-ver15#Regressed to force the execution plan you prefer that uses your nonclustered index. The one caveat is the plan you want to force has to have been cached by the Query Store already: To force a plan, select a query and plan, then select Force Plan. You can only force plans that were saved by the query plan feature and are still retained in the query plan cache. This would definitely be a workaround solution, but the Query Store is a very helpful feature for monitoring and troubleshooting performance issues with queries.

nope Even with perfect indexes, SQL Server https://www.erikdarlingdata.com/sql-server/sargable-isnt-just-for-your-where-clause/ to avoid physically sorting data, the way your where clause is written. The issue is apparent in your query plan, because you're not sorting by columns, you're sorting by expressions. The only way to make expressions like this index-able is to: Make computed columns, and index them Create an indexed view, and index the resulting columns Otherwise, SQL Server will have to calculate those case expressions at runtime and sort the results. aside Since I see you falling for the cost meme, I'm https://dba.stackexchange.com/a/305722/32281 . I see you talking a lot about costs, and reducing them. Please don't pay attention to cost. It's a unit-less, estimated metric that has nothing to do with which parts of your query are slow and inefficient. Note that when you get an actual execution plan, there are no "actual" costs. They remain estimates, even after SQL Server measures and reports on many other runtime (actual) metrics. https://www.erikdarlingdata.com/sql-server/why-you-should-stop-looking-at-query-cost/ https://www.erikdarlingdata.com/sql-server/is-it-time-to-remove-costs-from-query-plans/ https://www.erikdarlingdata.com/plan-cache-liars/signs-your-execution-plan-is-lying-to-you-operator-costs/ The optimizer uses costs to choose a good enough plan quickly. Reducing them won't necessarily get you a faster query. You can have expensive queries that run quickly, and cheap queries that run slowly. I would suggest looking at https://www.erikdarlingdata.com/sql-server/how-long-did-that-operator-run-for/ to figure out which parts are most inefficient.

E_ID is not part of the WHERE clause while index IX_EMAIL_3 is sorted by E_ID so SQL can't seek to any of value from where and need to scan the index to find it. That is why it is taking IX_EMAIL_2. Try this: CREATE NONCLUSTERED INDEX IX_EMAIL_4 ON EMAIL (E_CUS_ID ASC, E_TYPE ASC, E_ID ASC) INCLUDE (E_PER_ID) WITH (FILLFACTOR = 95) I think it will be used.

Reverse the 'subscription' index. As it is, MySQL will probably use the PRIMARY KEY for lookups by a single subscription_id. Once MySQL hits a RANGE query, which an IN clause is, it doesn't use the rest of the index. CREATE TABLE `table` ( `by` varchar(20) NOT NULL, `subscription_id` int(11) NOT NULL, `subscription_type` int(11) NOT NULL, `feed_created_time` datetime NOT NULL, `content` text NOT NULL, PRIMARY KEY (`subscription_id`,`subscription_type`,`feed_created_time`), KEY `subscription` (`subscription_type`,`subscription_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

The Short Answer: I misread your question a little bit. The below discussion still applies and explains my answer, but I would suggest UTCDate, ID for your index. Especially because of your partitions and the fact that UTCDate is included in every query. The question of fragmentation is a good one, and I imagine you'll see some in either of your two choices, but the performance benefit would be best with this way, I feel. The Previous Discussion: This is one you'll likely get a few answers on and I've asked a couple questions in the comments. That said a few quick thoughts to get started (ignoring the multiple system question and the way the IDs are being used): 1.) Presuming you are talking about SQL Server partitioning, a partition aligned clustered index is generally best. So in my mind that is a good vote for UTCDate. 2.) You indicate that filters will always (is that really always? or figuratively always?) have UTCDate. In my mind that is another good vote for UTCDate. 3.) Because you'll be having IDs coming in at both a high range and a low range, you will experience some level of index fragmentation on ID. Assuming UTCDate is always increasing and you aren't entering data from dates all over the place, you may actually experience less fragmentation going with UTCDate. You also didn't indicate if this is a fact table or dimension. Assuming it is a fact table, that is another good argument for date in my book. If you are always querying filtering on a date or date range, then the clustered index being on that range scan value should help your queries. The fact that it would be partition aligned would also likely help. If this is a dimension and you will sometimes join on ID to pull data out of it, then there could be a stronger argument for going with ID, though I'd argue that the unaligned nature of that index to your partition could cause you more grief in the future performance wise. I would not bother adding ID to the index. Unless you include it enough as a filtering predicate and could actually see an improvement in performance of most queries. Yes, the date column alone wouldn't be unique, but if this table is a BIGINT, the 4 byte uniqueifier SQL Server adds to the clustered index to make it unique would actually cost less in terms of storage space than that ID column. These are just my thoughts and they are quick thoughts. This is one of those things you should really test, try a couple ways and see how it works out. Though partitioning sometimes forces your hand a bit. So I would probably end up with the date column only as my first choice and date plus ID as my second choice. Others will likely have alternative ideas on that one, too. And more information edited back into your question will help.

And it looks like curiosity didn't actually kill the cat :-). So I found this book Database System Concepts (Sixth Edition) online and it's freely available to be read. I've scratched it a bit and it turns out that it has the needed definition inside: Primary index: in a sequentially ordered file, the index whose search key specifies the sequential order of the file. Also called clustering index. The search key of a primary index is usually but not necessarily the primary key. Secondary index: an index whose search key specifies an order different from the sequential order of the file. Also called non-clustering index. So there you have it. Now I suppose you were reading another book (by same author(s)) which was with tests inside (Q&A) and not this one with theory. I'd say you should use them both at the same time. Now, I'm not sure that a general book would be the best way to start. Personally I'd start with any freely available kit (SQL Server Express, MySQL Lamp, Oracle XE, SQLite...whatever sounds good for your ear) and play with concepts and also their physical implementation. I'd say going also with examples will work better than just pure theory.

How do I make the DROP wait for the CREATE CLUSTERED to finish? You don't, it already does. The statements are sequential, i.e. synchronous, one after the other. What is heap storage? Paul Randal explains So what you really have is just a stack of paper thrown all over the desk, almost literally. Your operation neatly numbers the pages on the bottom right corner, collates the pages in sequential order, binds them, and then... unbinds and throws them back onto the desk again in a heap. The total achievement is near zero. Sure you will regain some usage from tidying up and removing forwarded pages, the data pages may be a tad bit more compact. Other than that, it's just as messy as you started.

I hate to state the obvious but I'd say test both scenarios and run your production queries against all 3 scenarios (scenario 1 being the current non partitioned one). The reason I state this is because I don't know what your code is querying. Do they actually have a benefit of being sorted in the base table by the identity column as opposed to the other column? For example, are your queries actually looking for the row ID often? If you're not sure, you might be surprised by getting some performance benefits. The general idea of using ID as the clustered key was for range scans, but in our case, we scan by customerID and Date, so it worked out perfectly for us, and perhaps you. Check out this article by Kim Tripp: http://www.sqlskills.com/blogs/kimberly/post/The-Clustered-Index-Debate-Continues.aspx "What is often cited as the “reason” for IDENTITY PRIMARY KEY clustered index definitions is its monotonic nature, thus minimizing page splits. However, I argue that this is the only “reason” for defining the clustered index as such, and is the poorest reason in the list. Page Splits are managed by proper FILLFACTOR not increasing INSERTS. Range Scans are the most important “reason” when evaluating clustered index key definitions and IDENTITies do not solve this problem.Moreover, although clustering the IDENTITY surrogate key will minimize page splits and logical fragmentation due to its monotonic nature, it will not reduce EXTENT FRAGMENTATION, which can cause just as problematic query performance as page splitting. In short, the argument runs shallow " Typically you want your clustered index to be as narrow as possible, unique, and non nullable as it get's carried into all other indexes. I have a table with roughly 10 billion rows and we partition off the datetime column, which has worked out great.

The difference is that Enterprise edition without the hint may decide not to use the indexed view but the base tables instead. My personal experience is that SQL Server is somewhat braindead in this. I almost always have to use the hint: the query is quicker with far less IO even though the plan "looks" worse with a scan on the view not index seeks on the base tables. And it runs more consistently too YMMV of course So, to answer, it will (should?) work the same based on what I've seen. Other folk may have different experiences and I'm interested in other answers To avoid using the hint everywhere you can wrap the indexed view in another view with the hint: hints propagate inwards to all outer queries will automatically have NOEXPAND.

The only valid uses are for staging tables used in import/export/ETL processes. ad-hoc, temporary and short term backup of tables using SELECT * INTO.. Staging tables are typically quite flat and truncated before/after use. Note that a clustered index is typically few small compared to the data size: the data is the lowest level of the index structure. Heap tables also have problems. At least these: can not be defragmented to reduce space on disk. This matters because used data pages will be scattered throughout the MDF for example, because data has no "order" from the clustered index non-clustered index now point to the row, not the clustered index entry. This affects performance: Need for reaching data through clustered index with a non-clustered index Also see http://www.sqlbadpractices.com/heap-tables/ (Bad practice) http://msdn.microsoft.com/en-us/library/hh213609.aspx (MS's recommendations) http://sqlskills.com/BLOGS/PAUL/post/A-SQL-Server-DBA-myth-a-day-(2930)-fixing-heap-fragmentation.aspx (Paul Randal's DBA myths)

I'm going to go on the assumption that you've identified a problem that needs to be fixed... though I'm not entirely convinced of that yet. Similar to my answer here, there are two cases when it comes to vendor applications. The application is currently under license and/or the vendor/owner doesn't want you meddling. In this case, definitely do not change the schema in any way, including adding indexes of any kind. Making schema changes may in fact void your license contract, but this can also cause problems for the vendor if they decide to fix the issues in a future schema update. Speaking from personal experience from the point of view of the vendor, we sometimes have to very actively tell clients to stop creating objects in our databases, because it impacts our ability to successfully apply schema changes. (I'm not just saying that -- we had a case a couple months ago where a schema update failed for one client because of an unexpected dependency from a client-created object. Slightly different scenario than adding an index, but still, it does come up.) There are rare cases where database-level changes are okay, but you need to make absolutely certain the vendor is okay with what you're planning to do. The best option for this scenario is... actually, not to make any changes yourself. Put together a compelling case to prove why it's important these changes be made, and present it to the vendor for them to implement in a future release. From a vendor's point of view, ideally suggestions are specific, simple to implement and test, and have a high positive impact for all clients. In a large application, suggesting that all tables that are heaps be converted to clustered indexes may be (and probably is) a good idea, but it's a complete non-starter. Instead, figure out the top 5-10 most important tables or 2-3 application areas that would benefit from this type of change in your environment. The application is out of license or the vendor/owner doesn't care what you do with it. In this case, you can add any index you want, but objects should remain in place with the same names (note I didn't say the same objects). There are many options here, depending on how the application and database works internally. If you still plan to apply future vendor-supplied schema changes, tread very carefully and make sure you create undo scripts for all your changes. The key to successfully making changes is to maintain application functionality... easier said than done, of course, if you plan to make significant changes. That said, simply adding clustered indexes is pretty safe. The challenge will be selecting an appropriate key for each index, and this should be done by hand, not automated. The only way to do this part well is by familiarizing yourself with a table's access patterns and purpose. Come to think of it, you could go through the same process as in the other case: identify the 5-10 tables that would have the highest overall impact, and fix those first. You may find that's all that needs to be done. Edit: this answer, in a more general sense, is now available in video form.

"My question is how and when does SQL Server create multiple leaf levels (index_level 0) for the same index." The 2008 R2 documentation for sys.dm_db_index_physical_stats includes a link to Table and Index Organization, which shows the following diagram: It describes the data that may be stored in each of the three possible allocation unit types: Your clustered index does contain three leaf levels, one per allocation unit type. For example: CREATE TABLE dbo.Example ( example_id integer PRIMARY KEY, lob_data nvarchar(max) NULL, padding varchar(8000) NULL, overflow varchar(8000) NULL ); INSERT dbo.Example ( example_id, lob_data, padding, overflow ) VALUES ( 1, REPLICATE(CONVERT(nvarchar(max), N'X'), 8001), REPLICATE('Y', 4000), REPLICATE('Z', 6000) ); SELECT ddips.index_id, ddips.index_type_desc, ddips.alloc_unit_type_desc, ddips.index_level, ddips.avg_page_space_used_in_percent FROM sys.dm_db_index_physical_stats ( DB_ID(), OBJECT_ID('dbo.Example'), 1, 1, 'DETAILED' ) AS ddips; Output: ╔══════════╦═════════════════╦══════════════════════╦═════════════╦════════════════════════════════╗ ║ index_id ║ index_type_desc ║ alloc_unit_type_desc ║ index_level ║ avg_page_space_used_in_percent ║ ╠══════════╬═════════════════╬══════════════════════╬═════════════╬════════════════════════════════╣ ║ 1 ║ CLUSTERED INDEX ║ IN_ROW_DATA ║ 0 ║ 50.3953545836422 ║ ║ 1 ║ CLUSTERED INDEX ║ ROW_OVERFLOW_DATA ║ 0 ║ 74.3019520632567 ║ ║ 1 ║ CLUSTERED INDEX ║ LOB_DATA ║ 0 ║ 99.0239683716333 ║ ╚══════════╩═════════════════╩══════════════════════╩═════════════╩════════════════════════════════╝ Your table contains large object (LOB) columns (MAX or old-style text, ntext or image types) and variable-length column definitions which allow individual rows to exceed the 8060 byte INROW limit. For rows that exceed 8060 bytes, ROW_OVERFLOW_DATA allocation units will be created. This is often problematic for performance, since row data access requires following an off-page pointer to retrieve the overflowed data. I would certainly look at the design of the table before worrying too much about how full the pages are on average. Whether you should be concerned about page fullness depends on which allocation unit it refers to.

Matching indexed views is a relatively expensive operation*, so the optimizer tries other quick and easy transformations first. If those happen to produce a cheap plan (0.05 units in your case) optimization ends early. The bet is that continued optimization would consume more time than it saved. Remember the optimizer's primary goal is a 'good enough' plan quickly. Using the clustered index on the view isn't expensive in itself, but the process of matching a logical query tree to potential indexed views can be. As I mentioned in a comment on the other question, the view reference in the query is expanded before optimization, so the optimizer doesn't know you wrote the query against the view in the first place - it sees only the expanded tree (as if the view had been in-lined). "Good Enough Plan" means the optimizer found a decent plan and stopped early in an exploration phase. "TimeOut" means it exceeded the number of optimization steps it set itself as a 'budget' at the start of the current phase. The budget is set based on the cost of the best plan found in a previous phase. With such a low-cost query (0.05) the number of budgeted moves will be quite small, and quickly exhausted by regular transformation given the number of joins involved in your sample query (there are lots of ways to rearrange inner joins, for example). If you are interested to know more about why indexed view matching is expensive, and therefore left for later stages of optimization and/or only considered for more costly queries, there are two Microsoft Research Papers on the topic here (pdf) and here (citeseer). Another relevant factor is that indexed view matching is not available in optimization phase 0 (transaction processing). Further reading: Indexed Views and Statistics * and only available in Enterprise Edition (or equivalent)

I would say that I agree with @MartinSmith. Determining the clustered index key is something that requires some thought and planning. But if you were looking to get a head start and generate some T-SQL code that, say, creates the CREATE INDEX commands on heaps that have an IDENTITY column (a generally accepted clustered index key), then you could do something like this: declare @create_indexes nvarchar(max); set @create_indexes = N''; select @create_indexes = @create_indexes + 'create clustered index ' + quotename('IX_' + object_name(i.object_id)) + char(13) + char(10) + 'on ' + quotename(object_name(i.object_id)) + '(' + quotename(c.name) + ');' + char(13) + char(10) + 'go' + char(13) + char(10) from sys.indexes i inner join sys.columns c on i.object_id = c.object_id where i.type = 0 and c.is_identity = 1; print @create_indexes; Again, I will restate that like @MartinSmith said, I wouldn't just blindly execute any DDL that will impact performance and design like this. But the above is a start, and will give you the T-SQL for heaps that have an IDENTITY column.

Your table will work fine for this purpose, but you probably want to add an index. If the primary reason for using this table is to take an outside_ticket_id and get the corresponding ticket_id's I would add the following clustered index: CREATE CLUSTERED INDEX [CL_Lookup_OD_ID] on [lookup](outside_data_id) GO If the primary lookup will be the other way around (trying to find the outside_data_id from a ticket_id) place the clustered index on the other column. ---- Oh, sorry, just noticed this is Postgres. The above syntax is SQL Server. For Postgres, create an index on the column, then issue the cluster command, like so: CREATE INDEX IX_outside_data_id on lookup(outside_data_id); CLUSTER lookup using IX_outside_data_id; You may also want to look at the "fillfactor" on the index depending on how heavy the insert load is here. But that is a large topic worth exploring on it's own...

The specifics of how SQL Server does it is not public. But I recommend you read the vast research material on the subject which is public. Modern B-Tree Techniques is a good start. Many more papers from Goetz Graefe talk about this area, he is well known in the industry. Interpolation search is a well known technique in B-Trees and there is plenty of material detailing possible approaches (bear in mind that none of the papers describe explicitly how SQL Server does it and there are many ways to skin a cat).

You should tag which database system and version you're referring to, as this could affect the answer that is appropriate. But assuming you mean one of the main modern relational database systems, there's a couple of different objects you're asking about, so here's an answer for each of them: Primary Keys: They do not affect performance in a negative way at all, and in fact can slightly help performance in certain database systems because they guarantee uniqueness on a set of fields in the table. It is bad relational design in most cases to not define a primary key on a table. Primary keys are not the same thing as clustered indexes, though some database systems will create the clustered index on the primary key fields by default, out of assumption the primary key fields will be used in predicates (JOIN, WHERE and HAVING clauses). Foreign Keys: Similar to primary keys these also have no measurable negative performance impact, and can also potentially help performance by guiding the database system to generate a more efficient execution plan in specific cases. (I find this to be a rare occurrence though and most times there's no performance difference one way or the other.) Clustered Indexes: The clustered index on a table can definitely improve performance when the fields of that index are commonly queried on in a predicate, and in most cases it's recommended to have one on a table. The clustered index is the logical ordering of the data in the table itself and therefore doesn't take up any additional space. Without a clustered index the table is logically stored in a heap (or similar data structure, depending on database system) which can be inefficient to read data from. A clustered index on a table can result in INSERT operations taking slightly longer, but the difference of generally milliseconds for INSERT operations is usually worth the tradeoff for the major performance improvements of SELECT, UPDATE, and DELETE queries. The edge cases where there might be a net loss in performance from using a clustered index is when you use a staging table to temporarily store the data before it's moved elsewhere, and you don't plan to do any querying against that table other than INSERT operations or selecting the entire table.

The clustered index is the table. It includes all columns. It is (basically) impossible for the clustered index to be smaller than any one non-clustered index. You probably looked at only the clustered key column size or only the non-leaf level when you read that 7GB size. So if you rebuild the clustered index, then you rebuild all columns - including the compression setting. Rebuild of the clustered index in the end copies the data to a new location and after its done, it removes the data from the old location. If you were on 2017, you could do resumable index rebuild, which allow you to pause the rebuild, empty the log and then resume the rebuild again.

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, the leaf-level pages of the clustered index are left in-place and they become the new heap. Nonclustered indexes are still rebuilt as the row locator switches from the CI key to the rowid (file:page:slot).

please take a look at this. How to estimate the size of a NCI, you will find all key factors playing role on the final index size. https://docs.microsoft.com/en-us/sql/relational-databases/databases/estimate-the-size-of-a-nonclustered-index?view=sql-server-ver15