Is this an effective and efficient SQL Query or is there a better way?

Question

We have a passwords table which references a user table. Records never get deleted from the password table so if a user changes their password, a new entry with a more recent Created date gets inserted.

The hash of the password is salted with various things, most importantly the created date of the actual record.

In a stored procedure, I'm retrieving variables so I can do a hash for comparison. I really just want to store the most recent password hash for a user along with the record's created date:

DECLARE @ExistingPassword as varchar(200)
DECLARE @LastChanged as DateTime

SELECT Top 1
    @ExistingPassword = p.PasswordHash,
    @LastChanged = p.Created, 
FROM Password as p
WHERE p.UserId = @UserId
ORDER BY p.Created DESC

Is this a reasonably efficient way of getting the most recent password hash and its created date? Is there a better way to do it?

Answer 1

By my personal experience, you should work with a period, like StartDate and EndDate. So, you can to use a WHERE getdate() BETWEEN StartDate and EndDate to search for your current password.

Every time you add a record just with a created date for subsequent most update retrieval, baby jesus cries.

Answer 2

I think that is the fastest way to do it. I ran the below code to test your way and the only other way I could think of and your way was a little faster. I would also like to mention in a table with 1,000,000 rows I was getting response times fast enough that SSMS was not even giving me a run time, just 00:00:00. I did see one thing that might help you. If you can, put a non-clustered index on the Created column and make it sort in Descending order. Putting it in Descending order will make big difference because it will stop you from doing a table scan for the newest Created DateTime. That really could make a HUGE difference.

--Put Rows in TestTable, 3 Rows ID, User_ID, CreatedTime
DECLARE @Count INT
SET @Count = 1

WHILE @Count <= 1000000
BEGIN

INSERT INTO TestTable
VALUES (@Count%3+1,DATEADD(HH,@Count,getdate()))

SET @Count = @Count + 1

END

--Select Your way
SELECT TOP 1
 User_ID
FROM TestTable
WHERE User_ID = 3
ORDER BY CreatedTime DESC

--Select my way
SELECT
 User_ID
FROM TestTable
WHERE User_ID = 3
 and CreatedTime = (SELECT MAX(CreatedTime) FROM TestTable WHERE User_ID = 3)

Answer 3

This is efficient, but I would make sure there is an index on:

userId and created 

for performance reasons