SQL Server Clustering – Does It Enhance Performance?

How exactly would you 'release' the memory back?

As a general rule SQL Server will steal all the memory from the OS and then use it for it's own purpose. See Dynamic Memory Management:

The default memory management behavior of the Microsoft SQL Server Database Engine is to acquire as much memory as it needs... The instance then continues to acquire memory as needed to support the workload. As more users connect and run queries, SQL Server acquires the additional physical memory on demand. A SQL Server instance continues to acquire physical memory until it either reaches its max server memory allocation target or Windows indicates there is no longer an excess of free memory;

Depending on the amount of RAM your server has, it might just read the whole table into memory and keep it there - so your first query will load it, and any subsequent queries will just operate on those pages loaded in memory.

To get any meaningful data, you need to flush the cache before every run of your query! (run DBCC FREEPROCCACHE and DBCC DROPCLEANBUFFERS before any query execution!)

I tried your scenario with 100'000 dummy file names and almost the same table structure (I changed FileName to be VARCHAR(260)).

Without index:

Table 'TestFiles'. Scan count 1, logical reads 2137, physical reads 5, read-ahead reads 2136, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Query Subtree Cost 1.68884

With non-clustered index on filename:

Table 'TestFiles'. Scan count 1, logical reads 6, physical reads 4, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Query Subtree Cost 0.0065704

This was done with this statement:

DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS 

SET STATISTICS IO ON

SELECT * 
FROM testfiles
WHERE FileName = 'File-D8584B44-518F-428A-86A1-7836E0B60502'