I cribbed the example code given in the original post and added some line breaks. It looks like the following...
Select LastName, EmpNumber, Granted, Denied, ID_Picture
where ProximityCardNumber = @id
There's a missing single quote but, if you look at the last line, it looks like typical ORM code. To the point that others have made, ORMs will frequently make the mistake of using some form of unicode datatype and, if the underlying datatype doesn't match, then no index in the world will help because the difference in datatypes (when the variable is NVARCHAR()) will almost always cause the table to be scanned as it converts the underlying column to (in this case) NVARCHAR(11) before it can do the comparison.
Since this is 3rd party code, you'll need to contact the vendor and get them to fix their code so that the datatype actually matches if it's not matched to the actual datatype of the ProximityCardNumber column in the EmployeeCards column. You might also want to advise them of the Best Practice of using the two part naming convention (SchemaName.ObjectName) to help the code avoid making trips to the master database if the service user for the app isn't actually named "dbo".
If you could post the CREATE TABLE code including the indexes, that would be a good step in helping us help you.
There's also a possible optimization if the vendor drags their feet or refuses to change the code IF there's a datatype mismatch but I need the CREATE TABLE statement with the index creation code and then I also need you to execute the following code and post the output.
--===== We don''t care about super accuracy here.
-- Make it so the following queries aren''t blocked
-- by other things. This is a SESSION setting and
-- it will NOT affect anyone else.
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
--===== Determine if the LOBs are allowed In-Row
-- or forced out of row.
SELECT OutOfRow = large_value_types_out_of_row
WHERE object_id = OBJECT_ID('dbo.EmployeeCards','U')
--===== No pun intended but this will help us get a
-- picture of what''s happening with the LOBs.
SELECT PLenMin = MIN(LEN(ID_Picture))
,PLenAvg = AVG(LEN(ID_Picture))
,PLenMax = MAX(LEN(ID_Picture))
is pronounced "ree-bar
" and is a "Modenism
" for R
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
"Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)