Tag Info

Hot answers tagged

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


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


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


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


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


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


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


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

As mentioned: Yes: Add the nonclustered index. How many bytes wide will it be? You do want to evaluate if it will negatively affect write operations. This is not an easy thing to assess from a distance, as there are many factors. The most grunt way is if you have a sense of your current write performance, add the new index and see if write performance, or ...


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


1

You are correct Erik. In case a large portion of the leaf level of the clustered index needs to be read, the size of the other columns affects the amount of data that needs to be read, since the leaf level of a clustered index contains the table pages. Nonclustered indexes contain the clustered index values for the ability to perform a lookup when a column ...


1

Instead of having a non-clustered index containing all the columns of your table, you would be better off having a clustered index for the table. Find the column(s) that uniquely identifies each row and use that as your clustering key. I am assuming that you do not already have a clustered index on that table seeing that it was decided to create a ...


1

The date or date key can be an EXCELLENT choice in clustering and partitioning for a fact table in a lot of cases. If you are often scanning by a date range, including date in your criteria this can work well. By partitioning on the date and aligning your clustered index by changing it (yeah that is a bit of work for the DBA but not much and worth it.. I ...


1

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



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