I just found out using this makes a huge performance increase for a query. Are there other things that I can do to speed up my query, of course, joining it correctly? I have also started to use WITH(NOLOCK).
How did you find that out? I'm asking because the only place where it seems to matter is in SSMS and then only if you have a shedload of RBAR. As for the WITH(NOLOCK) thing, you need to be REALLY care using it because it will allow two copies of the same row(s) to be present in your result set if you happen to hit the rows in the middle of an UPDATE. It will also read uncommitted data that could be rolled back.
JeffModen, yes I discovered SET NOCOUNT ON when testing my SQL in SSMS. So you are saying that it doesn't matter if I was to run a SP without it? I am not sure what ShedLoad of RBAR is. As for the WITH(NoLock), thanks for sharing that. I will read more on it unless you have an example. If not, don't worry about it because I will look into it. Thanks Jeff.
SET NOCOUNT ON is very useful in stored procedures - especially when called from SSIS/SSRS and other external applications. It removes that extra resultset that can cause issues for those applications.
NOLOCK is the same as READ UNCOMMITTED and allows for 'dirty' reads. This means your queries could return invalid data - meaning missing data or duplicated data - or failure due to data movement. It should be avoided as much as possible.
An alternative would be to enable SNAPSHOT isolation or set the database to RCSI. You can enable SNAPSHOT isolation which only enables the feature...and then use:
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
in your queries/procedures. This allows for that code to read data from a snapshot - avoiding any blocking on other processes writing to the database.
Definitely read up on that and understand the potential issues...
I really like your suggestion Jeff. I will research it this weekend.
I totally agree with that reason for using SET NOCOUNT ON.
I'll also say that you should plan on rebuilding indexes after enabling snapshot isolation because it (IIRC) adds a 14 byte column to the clustered index and (again, IIRC) to non-clustered indexes (not to mention the extra disk, backup, and memory space it will also use).
See my reply above while you're doing such research.
Absolutely - that is one of the issues. I really like Kendra's overview here: https://www.brentozar.com/archive/2013/01/implementing-snapshot-or-read-committed-snapshot-isolation-in-sql-server-a-guide/