We need to read the whole table

(This is called a clustered index scan - we'll get back to that.)

Worse yet, concurrency...

SQL Server uses two-phase read-write locks

• Before a transaction begins, locks are acquired for needed blocks of data
• When all locks are acquired, the works begins
• When work completed, all locks are released
• When reading data, a read lock is taken out
  • Multiple read locks can be on the same resource simultaneously
• When writing data, a write lock is taken out
  • Only one write lock can be issued and it must be exclusive (even for read locks)
• When there is a locking conflict, a deadlock is issued and one transaction is terminated

Solution: Order the data in a way that you don't need to read the whole table.

(The order of the table is defined by its clustered index.)

(By default, the clustered index is the primary key.)

ID        ResidentID ChangeDate    ChangeType ChangeDetails                            UserName
--------- ---------- ------------- ---------- ---------------------------------------- --------------

008...    A          2014-02-11... 10         NPO to NAS                               E-Marshal

246...    A          2015-12-21... 30         Regular to Pureed                        C-Carraway

083...    A          2016-02-13... 20         Breakfast Tray - Juice of the Day (4oz)  D-Wojtalik

0FC...    C          2015-02-25... 20         HS SNACK - FRUIT CUP (1/2 CUP)           C-Smith2

126...    D          2014-05-05... 20         2:00PM - Juice, Orange (4 oz)            A-Barron

167...    F          2015-09-23... 30         Regular to Mech. Soft/Ground Meat        C-Palacios

01C...    M          2015-04-02... 10         General to NCS/NAS                       J-Yates

248...    W          2015-02-05... 30         Regular to Mech Soft                     D-Kesvormas

CREATE CLUSTERED INDEX IX_ResidentChangeLog ON ResidentChangeLog (ResidentID, ChangeDate, ID)

A-W

A-C

A

A-C

D-W

D-F

M-W

Clustered Index Seek

Limitations

• Only one clustered index per table
  • (This should be clear because it's literally the order of the table itself)
• The RowID is the set of clustered index values
  • Plus also a long random number if the PK of the table isn't included in the clustered index

Non-clustered indexes

• Extra structure that links to the RowID
• Automatically changed on UPDATE/INSERT/DELETE
• Unlimited number
• Included columns