NOLOCK

I recently discovered and tried using the “NOLOCK” query hint and found in some cases it reduced my query times up to 50%, though at times it seemed to have no effect or minimal effect. I got the same number of results with each query as I had gotten in the past.

To use “NOLOCK” in a query, you put it after the table name you are querying.

Examples:

INNER JOIN Table1 (nolock)

FROM Table1 (nolock)

Basically it is a dirty read of the data – it overrides all locks currently being held and gives you a result set of what the data currently is in the database. This sounds equivalent to the “SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED” statement, except it seems to actually speed up the queries whereas I don’t see increased speed of queries with the “set transaction isolation level read uncommitted” statement.

This is new to me and I’m still experimenting with it, but if there are issues with records being locked by queries, putting (nolock) beside each joined table may leap over those locks and get the data back more quickly.

NOTE: I work in a database that is supposed to have static data outside of a nightly update. The NOLOCK query hint may not be a good choice if you are running reports inside a live system. The database I have access to is one in which I do not have the right to update, delete, or create tables, but still it appears there are locking issues based on how much the NOLOCK query hint sped up my queries.

Below is a sample query:

SELECT DISTINCT
        Meds.Med_ID as Med_ID,
        Meds.Desc as Med,
        Meds.Generic as Generic,
        Generic_Name.Desc AS Generic,
        Therapeutic.Desc AS Thera,
        Pharma.Desc AS Pharma,
        Meds.Strength As Med_Strength,
        Meds.Form as Med_Form,
        Meds.[Route] as Med_Route
INTO ##BPatchList
FROM Meds (nolock)
        LEFT JOIN Pharma (nolock)
        ON Meds.PHARM_ID = Pharma.PHARM_ID
        LEFT JOIN Generic_Name (nolock)
        ON Meds.GENERIC_ID = Generic_Name.GENERIC_ID
        LEFT JOIN Therapeutic (nolock)
        ON Meds.Thera_ID = Therapeutic.Thera_ID
        AND Meds.Med_id in (/*Buprenorphine patch*/ 1234, 3456, 2345, 5678, 4682, 1236, 5656, 6666, 1654, 5464)

SQL Server: Finding Dependent Objects

To find objects dependent on a given table, you can use the following:

execute sp_depends tablename

In the database I work with, the table I used brings up many views and a few stored procedures.

I ran into this on kudvenkat’s youtube video, “Dynamic SQL vs Stored Procedure.”

He was using it to see what objects were dependent on the table before deleting it.

I work with a database whose tables I don’t want to delete, but where it may be at times useful to know what views are dependent on a table just in order to know that a certain view exists in the first place. Thankfully with RedGate’s SQL Search app (which is a free tool, and which I use daily), I can see how a view is constructed, including which tables are used.

Getting to your ##GlobalTempTables from the Remote Server

If my understanding is correct, everybody who has access to a remote server has the ability to make temp tables on that remote server. However, this does not necessarily endow a person with the rights necessary to export that data.

This has been the case where I work for a while. Most of the time, the queries I run are small enough in results to allow me to simply copy and paste the data where needed. For larger files, I have struggled a bit more. My first work-around was to link the remote server to my local server, and run my queries from the local server, depositing the files on the local server rather than the remote server’s temp folder. This did produce correct results, but running the queries this way appeared to take quite a bit of time.

I wanted a way to transfer global temp tables from the remote server to the local one, but when I attempted to do this using a Select Into query, SQL Server always interpreted the “##” as pointing to the local temp tables rather than the remote ones. Such queries would result in the following message:

“Database ‘TempDB’ ignored, referencing object in tempdb.”

Well, today I found the solution over at StackOverflow. User Ravi provided the answer in 2014, and the answer was edited by scsimon on June 2, 2016:

The query this person provided was:

SELECT * FROM OPENQUERY(linkedServerName, ‘SELECT * FROM ##temp’)

So – I tried it out. In my case, I wanted to take the ##OpioidList file off the remote server, and I wanted to make it into a table in my local database (not the tempdb.)

First, my remote server was already linked through my localDB- we’ll call it “secretservername” here. If you don’t know whether your remote server is linked to your local one, you can check in Server Objects –> Linked Servers.

Next, I made a query on my local server:

USE LocalServer
GO

SELECT * INTO OpioidList
FROM OPENQUERY(secretservername, ‘SELECT * FROM ##OpioidList’)

This worked like a dream – creating the table in my “LocalServer” database as a non-temporary file. From there I was able to export it to a flat file to provide to someone in a different department.