Databricks External Data SQL Server Connection Dirty Reads

[wallco26]

I've connected a SQL Server database as an external connection in Unity Catalog. It looks like when I write SELECT queries to that connection I end up locking my tables on the SQL Server. Is there a way to query these tables using a "with (nolock)" command like I would inside SQL Server? I've done this in the past in Databricks by using a JDBC connection to the same server and adding "with (nolock)" to the right of my table name string.

[Kaniz_Fatma]

Hi @wallco26, To avoid locking tables when querying SQL Server databases using Unity Catalog, you can pass the WITH (NOLOCK) Hint as an option when creating the foreign catalogue in Unity Catalog. 

[wallco26]

Thanks Kaniz. How would I go about doing this? I don't see a hint option in the Connection UI or the Catalog UI.

[Slash]

Hi @wallco26 , 

If you can't find options on the UI, you can always use SQL syntax to create foreign catalog: 

CREATE FOREIGN CATALOG [IF NOT EXISTS] <catalog-name> USING CONNECTION <connection-name>

OPTIONS (database '<database-name>');

[wallco26]

Thanks Slash - where would the "with (nolock)" command fall into the SQL Syntax...within the OPTIONS section? What would the specific command look like?