One of the common scenario’s you run into when you are using SharePoint for some time is that lists and doc. libr. are quickly filling up over the magic number, being 5000 records.
At least this limit is set by default and can be changed using the Central Admin. But that’s a different story.
Well let’s give you an example how we can overcome the 5000 limit without having to change the default limits on a farm level.
In our SharePoint we have a list for users to record some Time Sheets per Project.
As you can see 2 of them are over the limit.
In the list settings it will throw up an alert saying you are reaching or over the top.
In normal circumstances a standard user would NOT be able to access any of the data in the list until it is again below the limit.
– One of the solutions is to create a VIEW and a FILTER the list by user.
So that when he want to look at his dataset you queries that list and get’s back only his records. which is less then 5000.
As you can see there is another thing that strikes you (or maybe not )
You see that I have created an Index Field –Created By (Indexed)
The difference for SharePoint is that if you don’t create indexes, it will have to scan the full database table. So there is a performance impact as well as as it has to read all records in the table before returning the results.
Best is to created indexes for large list, being the best practices
How to do this is easy, go to List settings –> Indexed Columns
And create one or more indexes.
Set the Filtered VIEW as default view, and test if a user (not administrator) can access the list, even if this contains more than 5000 records.
As you can see there is no problem at all add more records to the oversized list.
PS: Important remark is that if you make a FILTER setting to limit the records to be returned.
Make sure that you DO NOT set a SORT settings.
Because the SORT will have to read the full list again before it returns the records.
So this will destroy the initial solution again