TABLE ResidentChangeLog
- ID uniqueidentifier
- ResidentID uniqueidentifier
- ChangeDate datetime
- ChangeType tinyint
- ChangeDetails nvarchar(MAX)
- UserName nvarchar(50)
ID ResidentID ChangeDate ChangeType ChangeDetails UserName
--------- ---------- ------------- ---------- ---------------------------------------- --------------
001... X 2016-02-13... 20 Lunch Tray - Cranberry Juice (4 oz) S-Hampton
008... A 2014-02-11... 10 NPO to NAS E-Marshal
01C... M 2015-04-02... 10 General to NCS/NAS J-Yates
054... X 2016-02-29... 20 TID - HOUSE SUP 2.0 - 120CC (120CC) J-DeMouchette
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
246... A 2015-12-21... 30 Regular to Pureed C-Carraway
248... W 2015-02-05... 30 Regular to Mech Soft D-Kesvormas
...
ID ResidentID ChangeDate ChangeType ChangeDetails UserName
--------- ---------- ------------- ---------- ---------------------------------------- --------------
001... X 2016-02-13... 20 Lunch Tray - Cranberry Juice (4 oz) S-Hampton
008... A 2014-02-11... 10 NPO to NAS E-Marshal
01C... M 2015-04-02... 10 General to NCS/NAS J-Yates
054... X 2016-02-29... 20 TID - HOUSE SUP 2.0 - 120CC (120CC) J-DeMouchette
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
246... A 2015-12-21... 30 Regular to Pureed C-Carraway
248... W 2015-02-05... 30 Regular to Mech Soft D-Kesvormas
Find all changes to Resident A in the past year
SELECT * FROM ResidentChangeLog
WHERE ResidentID='A' AND ChangeDate > '2015-06-16'
+300,000 more records...
(This is called a clustered index scan - we'll get back to that.)
(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
Remaining 300,000 rows...
Clustered Index Seek