I have a table with 5000 users along with 6 years of attendance data. If I want to retrieve user data from the table it takes time to load.
Usercode, Rosterdate, Shift code, AttendanceCode
If i want to retrieve the shift information from 1-jun-2020 to 10-jun-2020 for 5000 users,
would take time to retrieve the data in SQL server.
what kind of index should i update in above table?
index on the date columns in filter will definitely help.
Welcome! Is this for reporting purposes?
I did index on following columns
But no helps.
indexing based on how you are going to query the data !!!
example if you use user = 'Sam' index on user = 'Sam' ( filtered index )
please google search !!! lots lots lots of things ideas are there
below is one such link
hope it helps i did not take a look into it !!!
You should create a unique clustered index on ( Rosterdate, Usercode ).
A non-clustered index isn't likely to help with very many queries. Unless it is a completely covering index, but that likely means you'd have to repeat every column in the table.