CODEWITHSUNDEEP
sqlsql-server-2008
Nitin Singh
Nitin Singh

Reputation: 159

How to replace function with a view in sql?

i have been in a strange situation i m using a function is to retrieve the top 1 record from a particular table. But using function is killing the performance of my query as the table have more than 50,000 record. i want to know how can i replace that function with a view and then join it into a stored procedure so i can get better performance. here is the code that i have written in the function 

Create function getlatestusernote(@warrantid varchar(20))      
Returns varchar(300)    
As       
Begin       
Declare @latestnote  VARCHAR(300)    
SELECT TOP 1 @latestnote= Remarks FROM Warrant_notes WHERE WarrantID = @warrantid AND NotesCat <>0 ORDER BY NoteDate  desc    
return @latestnote      
END

Not i want to have a view for the same query kindly help me to figure out this problem. thanks in advance.

Upvotes: 1

Views: 274

Answers (1)

Matthew Haugen
Matthew Haugen

Reputation: 13286

I took this from this top rated (albeit unaccepted) answer (which you should look into to better understand what's going on) to another question and adapted it. Of course, I have no way to test this, but I believe this looks right. Let me know if it doesn't work and I can help troubleshoot.

You should be able to create your view based on this, then query against that.

SELECT WarrantID, Remarks
FROM
(
    SELECT 
        RowNumber = ROW_NUMBER() OVER (PARTITION BY t.WarrantID ORDER BY t.NoteDate DESC),
        t.Remarks,
        t.WarrantID
    FROM
        Warrant_notes t
    WHERE NotesCat <> 0
) i
WHERE i.RowNumber = 1;

Upvotes: 1

Related Questions