Query to find users from where?

Based on my question here, I have updated my query to take user input so it can be a generic query:

DECLARE @location AS nvarchar(max) = ##location:string##;
SELECT u.Id [User Link], u.Reputation, u.Location
FROM Users u
WHERE lower(u.Location) LIKE lower('%' + @location + '%')
ORDER BY 'Reputation' DESC


Is there something I should be doing differently, or is this good?

• As d347hm4n's answer points out, we should be careful about our column types. Users.Location is NVarChar(100). – nhgrif Apr 21 '15 at 10:39

This looks fine, the only thing I would say is @location is being declared as nvarchar(max). I can only assume that u.Location is also nvarchar(max). If it isn't then set it to be the same size. nvarchar max will have a slight performance hit to it.

There's no need to add a table alias when you are only dealing with a single table.

Also there's no need to worry about LOWER as the columns have a case insensitive collation.

Fixing the datatype issue mentioned in the comments and removing the unnecessary other stuff mentioned above you would end up with.

DECLARE @location AS NVARCHAR(100) = ##location:string##;

Reputation,
Location
FROM   Users
WHERE  Location LIKE '%' + @location + '%'
ORDER  BY Reputation DESC


One other thing to consider is if the input itself might contain any characters of special significance in the LIKE pattern syntax.

It is unlikely that any legitimate locations will contain characters such as %, _, [.

If you do want to correctly support searches for these strings you would need to either ESCAPE them all or just use CHARINDEX instead.

SELECT Id AS [User Link],
Reputation,
Location
FROM   Users
WHERE CHARINDEX(@location, Location) > 0
ORDER  BY Reputation DESC