Hot answers tagged

16

In SQL Server the clustered index key column(s) are always added in to the non clustered index to act as a row locator (Ref: More About Nonclustered Index Keys). For an NCI declared as unique they are added as an included column otherwise they are added to the end of the key. You might want to add the columns in explicitly if the default placement is not ...


13

Your index is seemingly fine and good (i.e. covering) for the query and it should be used. The real problem is the query itself and specifically this condition which hides an implicit conversion: WHERE [serialNumber] = 137802 According to SQL Server's datatype precedence, when two values of different datatypes are compared, the value with the datatype of ...


13

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 ...


12

Because dropping of a NCI is already as much online as it gets. Is a metadata only operation. There is not even data deletion, a dropped index rowset is simply deallocated, ie. the same operation as truncate does. Dropping a clustered index, on the other hand, implies a rebuild and is a size-of-data operation, so it does make sense to have an online ...


12

As an alternative to @AaronBertrand's solution (if you can't or don't want to create an indexed view), I would recommend you to create an index on (Enroll_Date, UserID). If this type of question is very common on your table, this should probably even be your clustered index. I would not generally recommend high-selectivity indexes as a general "best ...


11

Sounds like an ideal scenario for an indexed view, which allows you to pay for calculations and aggregates at write time instead of query time. CREATE VIEW dbo.MyIndexedView WITH SCHEMABINDING AS SELECT Enroll_Date, UserID, RawCount = COUNT_BIG(*) FROM dbo.UserTable GROUP BY Enroll_Date, UserID; GO CREATE UNIQUE CLUSTERED INDEX CIX_miv ON dbo....


11

Aarons answer is a great solution. I'll answer the question assuming you don't want to take that approach. The query that you posted will usually be executed by first grouping on (Enroll_Date, UserID), then again on (Enroll_Date). This optimization is new to SQL Server 2012. It takes effect in case of a single COUNT DISTINCT. An index on those two columns ...


10

I wrote a post about this kind of situation here, which goes into more depth than this answer. is there any way to say in index definition to include only first 800 characters from the column? This can be done by creating a computed column as LEFT(MyColumn, 800), and then indexing that column. Note that the column doesn't have to be materialized in the ...


8

The reason for this is that the "fixed" physical location of your row - the RID (or row identifier) might (and will!) change over time - think page splits that occur when a row needs to be inserted into a table on a page that's already full. Updating those RIDs in all the nonclustered indices that exist on a given table is quickly becoming both a hassle, ...


8

A non-clustered index scan may be chosen in this scenario: the optimizer determines that it is cheaper to scan all rows rather than perform seeks/range scans the non-clustered index is "skinnier" than the clustered index the non-clustered still covers the columns needed by the query (or it covers enough of them and a lookup for the remainder is still ...


8

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 where LoanNum = 2712 Look up the column data for the matched rows not covered by the nonclustered index. The key point is that the nonclustered index is ...


7

This is the name of the filegroup or partition scheme that the index is created on. This can be specified when creating an index with a second ON clause. The sp_help procedure calls sp_helpindex which retrieves the name from sys.data_spaces The primary filegroup contains the primary data file and any other files not specifically assigned to another ...


7

If you need columns in the output that aren't covered by the index, the optimizer has to make a choice: Perform a table / clustered index scan (therefore all columns are there) Perform a seek, then perform lookups to retrieve the columns not covered Which way it will choose depends on a variety of things, including how narrow the index is, how many rows ...


7

You haven't really given enough information about the original execution plan, what it changed to or what columns you put in the new NCI. It is possible that the query performance can be improved even further. The cost of the new index is the initial build, the additional processing of entries on INSERT and UPDATE and potentially the fragmentation ...


7

No, _idx_TableA will not be affected for this operation. I have modified your example and added another index (NCI) that actually includes the key column Col3. Here's my example code: use testdb; go CREATE TABLE [DBO].[TableA]( [Col1] [nchar](5) NOT NULL, [Col2] [nchar](2) NULL, [Col3] [int] NULL CONSTRAINT [TableA_PK] PRIMARY KEY CLUSTERED (...


7

In simple terms, it involves less processing and movement of NC index entries when data in the clustered index physically moves (row forwarding, page splits, INSERTs etc). Mostly the clustered index entries only need changed: not the NC index pointers. By using RIDs, you'd need to do a lot more work on the NC indexes. To minimize this lookup in a query, ...


7

They don't tend to be shown in ERDs. An ERD focuses on the Entities and their Relationships, but an index is a copy of the data from one (or potentially more) of the entities, created to assist in the execution of queries. While it's possible that an index could be unique and therefore contribute to the database design, they are not typically shown on ERDs. ...


7

I assumed it would follow my hint, and maybe error out at execution time if I wound up with some bad data and the index was missing some needed values. The query optimizer will only use a filtered index in a query plan if it can guarantee (within its reasoning framework) that all possible matches can be served from the index. This is by design, to avoid ...


7

SQL Server does not "rebalance the tree" as a periodic event. I have last heard this term in the context of Oracle. All that SQL Server does it increase the tree height when necessary. This is an event that happens only a few times in the entire existence of a B-tree. In a DML heavy workload there can be many small tree adjustments called page splits. These ...


7

Are there any reasons for periodic/cyclic slow-down of insert performance? Yes. check point events. With a write intensive workload, big RAM server, as you describe, a large number of 'dirty' pages accumulate in memory. At the predetermined checkpoint interval all these dirty pages get written to disk, causing a spike of IO requests. This in turn slows ...


6

SQL Server will show you an Index it would like to use, since it estimates that index would make life easier. There is certainly no need to create an index on every searchable field, in fact doing so will make write performance substantially worse. If you have less than say 5 to 10 indexes on the given table, and you run that query all the time, you ...


6

An index is an on-disk structure associated with a table or view that speeds retrieval of rows from the table or view. An index contains keys built from one or more columns in the table or view. These keys are stored in a structure (B-tree) that enables SQL Server to find the row or rows associated with the key values quickly and efficiently. I want to ...


6

Yes, given the constraints in the question, particularly that the primary key column is the leading column in the indexes. Also assuming the primary key never changes. Not necessarily. The optimizer can indeed infer uniqueness without marking the nonclustered index unique. Marking the index unique may introduce a Split-Sort-Collapse combination in ...


5

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 ...


5

I think the problem here is a difference in terminology. The "number of writes" that's usually referred to is the number of object accesses, rather than the number of pages that get touched by the physical operation. The reason why that's usually used as a metric in discussion is because it's a more "stable" and meaningful number to talk about. As we're ...


5

Since we are talking about the clustered index, just because you defined the CI key column as ID, you still have the DeletedDate data in the leaf data pages of the index. That's the nature of the clustered index: It is the table data. Because you are typically having queries that look like: select * from YourTable where DeletedDate is null; You will ...


5

Go ahead and drop the clustering key while you're importing data. When you've finished your INSERTs, create the clustering key first, then the PK if it's non-clustered, then any remaining indices. I'm running such scripts at this very moment, and it takes about half as long as inserting into a table which is fully indexed. There's no problem in going ...


5

how to stored the actual IP address - in text or bytes format. Which is going to be better? Since "text" here refers to VARCHAR(45) and "bytes" refers to VARBINARY(16), I would say: neither. Given the following information (from Wikipedia article on IPv6): Address representation The 128 bits of an IPv6 address are represented in 8 groups of 16 bits ...


5

Without further info, this is more of speculation but judging on what we have: a table that is quite wide (1.3 to 4.0 rows per page on average) the query that is slow is using: only PWFID on the join condition, two columns Title, SITime on the select list and no other column anywhere (WHERE, HAVING etc.) Then a covering non-clustered index on (PWFID)...


4

(I'm assuming SQL Server in this answer, please clarify which RDBMS you're using if it's not SQL Server) The index you want is: CREATE INDEX [IX_Parent_Id] ON [sch].[Parent] (Id) INCLUDE (Name) You use INCLUDE when a column isn't being filtered on (isn't in the WHERE clause) but is being selected. INCLUDE means that the column will only be included at ...



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