Add option for NOLOCK against MS SQL Server

[mkirzon]

When using dplyr directly against databases, it'd be helpful to have a NOLOCK options when configuring table connections.

This is a common practice in MSSQL to ensure complex queries don't lock the database for other read/writes. The tradeoff is that the read may be uncommited so this should be a toggleable option.

[edgararuiz-zz]

@mkirzon - Good point
@batpigandme - Would you mind adding the feature tag as well? Thanks!

[hadley]

Can someone please provide a link to a good NOLOCK description, and discuss where it fit most naturally in dbplyr? As an attribute of the tbl()?

[mkirzon]

There are 2 ways NOLOCK can be set - on individual tables (most common) or on a global level for the connection. Since the table-level usage is common, its support in dbplyr could be a good place to start and agree it fits most naturally in tbl().

I like this writeup on SQL Server NOLOCKs: https://www.mssqltips.com/sqlservertip/2470/understanding-the-sql-server-nolock-hint/
Here's the full reference from Microsoft: https://docs.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-table?view=sql-server-2017

[ghost]

This issue was moved by krlmlr to tidyverse/dbplyr/issues/113.

[lock]

This old issue has been automatically locked. If you believe you have found a related problem, please file a new issue (with reprex) and link to this issue. https://reprex.tidyverse.org/