>>>>>>Hi,
>>>>>>
>>>>>>I was creating a report based on a table in SQL Server and noticed that it was very slow. I realized that the problem was that the field/column used in selecting the records didn't have an index. I added an index on this field/column and report performs much better.
>>>>>>
>>>>>>But I also noticed that this table has an identity field but it is not set as Primary Key. How important is it to set an identity column as a Primary Key (which of course will create the index)?
>>>>>
>>>>>PK should be the key used to estalish relations in joins and/or in seek-equivalent select... from... where pk=other.fk
>>>>>Benefit is optimized index scan vs. table scan, also often better than "normal index" scan
>>>>>
>>>>>Of course automatic check for being candidate is a plus as well, no trigger needed.
>>>>>
>>>>>PK should be either GUID (+:disconnected clients can easier generate local keys, -:field len) or identity (+:short field len results in faster operations like joins, -:inserting heavy grandparent/parent/children/grandchildren client data always needs key fixup to server generated identity)
>>>>>
>>>>>You could use belt and suspender approach with GUID as PK for easy insert, triggering indexed identity column (HK as Helper Key) used in joins if set as FK value in related tables: benefit shorter key length.
>>>>
>>>>First, thank you. But in this particular case, for this table, the identity field is not used at all. That is, this table is not related to other tables. And this identity column is of integer type.
>>>
>>>That once was MsSQL new speciality: tthink of it as an autoinc field, but not with a fixed increment, but an increment based on nanoseconds plus a unique constraint, making the values candidate - should be a long long integer type, but I have not checked if it is a continuosly measured/distributed value or if date / nanosecond parts are mapped into different parts of the bitsize.
>>>
>>>>I suppose there is no harm in making it a PK, which will create an index and probably will update (when new rows are added) faster.
>>>
>>>If the identity field is used as a filter (Where Clause in R,U & D) yes, if table size is big enough. On tiny tables fitting totally into the pages SQL server caches based / specced by disk block size, the index might not offer any benefits in speed, as both structures have to be "loaded" from probably different (already mem-cached) memory locations.
>>>
>>>Keeping a single index "hot" is IMO the largest part of the reason why common lookup tables mingling all kinds of lookup into a single table with structure
>>>
>>>[PK,?] nCategory, nCat_PK, Value with index and unique constraint BinToC(nCategory)+BinToC(nCat_PK)
>>>
>>>perform in real live sometimes better than a huge number of tiny lookup tables all kept open and guzzling memory scattering access wildly across memory (whcih might be paged out)
>>>
>>>Create operations (Insert..) probably not happening at all on that table from your description - which is good, as Insert on indexed field is slower due to writes in index as well.
>>
>>The identity field (PK candidate) has NO use in any part of the program (no RU, U, & D). I could have created this table without an Identity column and it would work without a problem; I just thought it would be a good practice to have such a field. And (insert) does indeed happen on this table. How else would the table get new records? So, from what you are saying, making the Identity a PK and therefore having SQL create an index will slow down every inserts, since SQL will have to update the Identity/PK index too.
>
>Usually, a table row should an identifier that distinguishes it from the other rows in the table.
>If you already have a unique identifier for the row that is not the identity column eg. invoice number in an invoice table, there is no technical benefit from having an identity column.

if you use invoice id for key field you have to use invoice id for invoice lines and some other tables for reference. When you want to change invoice id you have to update all tables and this will a bit risky (programming mistakes, possible deadlock because of a long one transaction) and slow. but if you use identity column for this you just update your invoice no at just one table, use identity as reference with other tables.