SQL Server Performance Forum – Threads Archive

Assigning Values to Variables

In the FAQ section, the answer to "Is there any performance difference between using SET or SELECT to assign values in Transact-SQL?" is that there is not difference between SET and SELECT from a performance standpoint, but that SELECT should be used to conform to the ANSI standard. Have you been able to determine a difference between: SELECT @UserID = (SELECT UserID FROM Users WHERE Name = @UserName) and SELECT @UserID = UserID FROM Users WHERE Name = @UserName The execution plan for the second form is consistently cleaner than the first, but I’m not sure if this is a result of my indexes or the syntax.

---

The execution plan does look cluttered but the performance difference in two is negligible. I don’t think there should be any problem in using any of these although I would recommend using latter for selecting directly from table / view / function and the former for selecting after doing some aclculations in subquery e.g. aggregate / count etc. Gaurav

---

]]>