Updating multiple rows in sql server
SQL Server supports many different lock modes and this can be somewhat complex at times.
Learning is best to start from exclusive locks, which are the easiest to understand.
This is a requirement of the relational model and is referred to as relational closure.
A view is basically the relational model's way of turning a SELECT statement into a "table" that is accessible using SQL. They can consist of any combination of the following: Views are defined using SQL and are represented internally to SQL Server by a SELECT statement, not by stored data.
Furthermore, views are simple to create and implement.
Although this process works are you familiar with any other options to directly perform the INSERT, UPDATE or DELETE operations?
Intent locks prevent the potential situation where a newly-acquired lock might invalidate locks on a lower level in the hierarchy.
Finally, schema locks (SCH-M and SCH-S) are used to prevent changes to object structure, bulk update locks (BU) are used when updating or inserting multiple rows using a bulk update, and key-range locks (R) are used to lock ranges of keys in an index.
In addition, the OPENROWSET command can also support SELECT statements where a table is joined to the Excel spreadsheet.
Let's work through some examples with the SQL Server 2005 Adventure Works sample database for each operation with a sample Excel spreadsheet.