Databricks External Data SQL Server Connection Dirty Reads

wallco26
New Contributor III

‎07-10-2024 02:12 PM - edited ‎07-10-2024 02:30 PM

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.

0 Kudos

wallco26
New Contributor III

‎07-15-2024 09:43 AM

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

0 Kudos

szymon_dybczak
Esteemed Contributor III
In response to wallco26

‎07-15-2024 10:47 AM

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>');

0 Kudos

wallco26
New Contributor III

‎07-16-2024 07:56 AM

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

 

0 Kudos