Tag Info

Hot answers tagged

11

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


10

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


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

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


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

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

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


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


6

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


5

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


5

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


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

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


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


4

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


3

No. Since the update does not update data in neither col 2 nor the clustered key (col 1), the index _idx_TableA with only col 2 does not get updated. @professionalAmateur, click on the execution plan button on the top. Run you query. Execution plan tab should show as below.


3

Clustered index key performs best to be unique, narrow, static and ever-increasing by itself. So in this case, the inclusion of DeletedDate actually result the clustered key becomes non-unique non-static (presuming the DeletedDate value could be changed). The INCLUDE in the non-clustered index is useful to cover the query without having to perform a key ...


3

Your first column in the index is cl_totalcrawltimes. In the WHERE clause you're using this column to define a lower range (cl_totalcrawltimes > 0). Depending on the data distribution this most probably will lead to an index scan instead of an index seek. Furthermore, cl_crawlsource is not part of the index (the include section, preferrably), so this would ...


3

Yes, as ypercube already said. The only thing to look out is a wild discrepancy in the column actual data size. For instance if NC_Index1 is some 1Mb, and NC_Index2 is 200GB then you can introduce some potential scan perf problems. But, frankly, the chances of this being the case are, basically, 0. Or NULL, depending on your prefs.


2

The reason it is telling you to create a new index is because the one you have is not the most optimal one possible. The query optimizer is quite selective in the way that it chooses which index to use, the following link should help you understand selectivity: http://blogs.msdn.com/b/bartd/archive/2011/01/25/query_5f00_tuning_5f00_key_5f00_terms.aspx You ...


2

Based on your note it sounds like adding a non-clustered index to that table is the right way to go. Play with the order of the columns in the development system, and add any needed columns as included columns and see if the query gets any faster. The general rule of thumb is that is a query is a part of the application and the query is being used ...


2

You cannot have an unique constraints backed by an aligned index (or plain unique non-clustered indexes) unless you add the partitioning column to the the unique expression. So if you have partitioned your table on column [datetime] then your unique constraint (or the unique index) must be ([datetime], [xyz]). Since more often than not this is not ...


2

Try generating an actual plan in SQL Sentry Plan Explorer. For a narrow plan the actual costs are not included in the XML, so we can't show those, but we at least have the ability to let you know that non-clustered indexes are affected. Disclaimer: I work for SQL Sentry. A lot more information found in the duplicate question: Nonclustered Index Insert ...


2

Definitely don't do that. The missing index hints can be very useful but the recommendations can be dumb, occasionally outright ridiculous. Creating a copy of the entire table for the benefit of this query fits the later. If your most common queries use a predicate on DataDate then it may be appropriate to change your tables clustered index to this. Only ...


2

Additional info: by default a database is created with only a PRIMARY filegroup, and even if you add filegroups later, PRIMARY is the default for new objects unless you also change that setting (you can change the default, and a lot of people do) or override it explicitly when you create the object. So in a lot of cases the ON PRIMARY bit is extraneous and ...


2

In SQL Server it’s possible to store data in more than one physical file on the file system. This is typically done for larger databases where separate data files are stored on different drives or even on different physical machines. ON PRIMARY means that table is stored in main database file. Other options would be to create more file groups and ...


2

Yes having a column in multiple unique keys is sometimes perfectly reasonable. In the case that you gave above I'm not sure I would bother since the ProductId key is unique regardless. But let's say that you have a product table like this: ProductVendor PK ProductCode PK ProductDescription ..... In this particular case the ProductVendor and ...



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