New answers tagged

0

Why do you need a PK? Why not just go with company_id as a non-clustered index? You said most searched are on all entries or by company_id Rarely update Rarely delete org_path, this is the only table in which it exists The answer from Martin Smith may get you what you need I am not familiar with automatically add a 4 byte integer uniqueifier Maybe ...


7

New Answer (based on new details provided in updates to the Question) 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) ...


5

SQL Server will automatically add a 4 byte integer uniqueifier to all duplicate (ie second and subsequent for a key value) clustered index keys to make it unique. This is not exposed to the user though. The advantage of adding your own unique identifier as a secondary key column is that you can then still seek by companyid but also seek to individual rows ...


0

Finally I managed to talk about it with the DBA. He thought the table will have many many columns (because of a similar table) and all will be needed in the query and in that case page splits will be much common and painful. But since the table is in fact small and all the queries will be based only on the token, he agreed that making the token the ...


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


-3

The FILLFACTOR on the table is 100, thus there are no free pages in the cluster index. If you're doing lots of inserts try setting FILLFACTOR to something like 80. And read the SQL Server books online regarding FILLFACTOR. ;-)


5

This started as a comment/questions but it got to long so I moved it here: I'm really thrown by this question. 1.5mil rows isn't really all that big. And the point behind an identity is that it's ever increasing. If that's your CL you shouldn't be doing inserts into the middle of a page, certainly not often enough to cause the level of fragmentation ...


1

You might find it interesting to check Thomas Kejser's take on indexes in SQL Server. Although clustered indexes are very useful, there can be reasons to keep a heap. For example, read this post: http://kejser.org/clustered-indexes-vs-heaps/ Particularly look at the topic: Fragmentation Prone tables with lots of INSERT activity This topic seems to ...


7

Since you are indicating insert performance is the primary concern, I'd take the recommendation of the DBA and make the clustering key the identity column since it is a unique, monotonically ascending number, which is guaranteed to (almost) never cause page-splits on the table. Also, don't store the GUID in an NVARCHAR(100) column, use the data type ...


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


1

Scanning are performed by loading index page by page. The number of rows fit in one page depends on the size of the index. Scanning using non clustered index will be faster compared to the clustered one, since more records will be available to be scanned per page. HTH


0

A clustered index contains all data in the row. It is not possible to tell exactly why the index was chosen without the actual plan file. I can say that based in my experience the optimizer is looking at the statistics. Because you are only returning one row then reading the smaller index and seeking to the data is much faster. You could test this by ...



Top 50 recent answers are included