TABLOCK hint in an Update SQL Query

TABLOCK is one of several table hints in SQL Server which includes the following:

   FORCESCAN 
   FORCESEEK 
   HOLDLOCK  
   NOLOCK  
   NOWAIT 
   PAGLOCK  
   READCOMMITTED  
   READCOMMITTEDLOCK  
   READPAST  
   READUNCOMMITTED  
   REPEATABLEREAD  
   ROWLOCK  
   SERIALIZABLE  
   SNAPSHOT  
   SPATIAL_WINDOW_MAX_CELLS = integer 
   TABLOCK  
   TABLOCKX  
   UPDLOCK  
   XLOCK 

Table hints can be used with any of the following operations:
Applies to:
DELETE
INSERT
SELECT
UPDATE
MERGE

Here is a query that updates a value in the Northwind Shippers table:
----------
Use Northwind
GO
UPDATE  Shippers
WITH (TABLOCK)
SET CompanyName='Speedy Gonsalez'
WHERE ShipperID=1
--
SELECT * from Shippers
------------
The query updates the name of the shipping company whose ID=1 to a new name. When TABLOCK is used in the above manner it should be within the parenthesis.

Read this recommendation from Microsoft:

"An UPDATE statement always acquires an exclusive (X) lock on the table it modifies, and holds that lock until the transaction completes. With an exclusive lock, no other transactions can modify data. You can specify table hints to override this default behavior for the duration of the UPDATE statement by specifying another locking method, however, we recommend that hints be used only as a last resort by experienced developers and database administrators"

Creating a simple DML trigger

An action is taken when a trigger is fired. DML stands for Data Manipulation Language and DML Triggers are initiated automatically whenever a DML event occurs.

DML events include events that takes place in a Table or View and they are:

INSERT
DELETE
UPDATE statements.

Why do you need a DML Trigger?

In order to enforce business rules in the following cases/situations:

Enforce Data Integrity
Querying other tables
Auditing data changes

These can include complex Transact SQL statements.

As triggers are created for (on) a table or view, a 'Triggers' node exist for all tables in a database.

Presently there are no triggers set up for the Person.PersonPhone table in the AdventureWorks 2014 database as its Triggers node is empty as shown.

DML_00

In order to create a trigger you need to provide a name, and the SQL Statement that starts the trigger; and the table on which the trigger will fire. Execute the following statement which creates a trigger called PlNotify that triggers whenever a Insert or an Update statement is run:

CREATE TRIGGER PlNotify 
ON Person.PersonPhone
AFTER INSERT, UPDATE  
AS RAISERROR ('Notify Customer Relations', 16, 10); 
GO

When the trigger is created it gets into the Triggers node as shown.

DML_01

Let us see what happens when we try to update a phone number. Here are some of the phone numbers (only 5 from top is shown):

DML_02

Now let us update a phone number in the above table using the following:

UPDATE dbo.Person.PersonPhone
SET PhoneNumer='808-722-6655'
WHERE BusinessEntityID=1
--Print 'After update trigger fired'

The PhoneNumber of Business Entity with BusinessEntityID was 697-555-0142 before the trigger fired.

And after the above update statement is run it becomes 808-722-6655 and the trigger is completed.
With the following response:

"Msg 50000, Level 16, State 10, Procedure PlNotify,
Line 4 [Batch Start Line 8]
Notify Customer Relations(1 row(s) affected)
After update trigger fired"

The trigger fires after the update and the changed table is as shown:

DML_03

While this is a very simple example of creating a data manipulation trigger there is a lot things happening in the database. Two temporary, memory resident tables- inserted and deleted are created to take stock of the changes being made.

Also only the After Insert ,Update trigger was considered and there are other types of triggers as well.

Update your SQL Server Management Studio 2016 to work with SQL Server 2016

If you followed my recent post (http://hodentek.blogspot.com/2016/05/get-ssms-2016-and-install-to-work-with.html)you would have installed the SQL Server Management Studo 2016 Release Candidate which used the software released on April 2016.

SSMS2016_April2016

SQL Server 2016 Developer became available on June 1, and when the SSMS 2016 RC was launched, you would get a message that there is an update to SSMS 2016.

SSMSUpdate

Clicking on the Update button takes you to Microsoft web site to download the latest version whose details are shown below.

SSMS2016_June2016

Updating easy

Double click the downloaded   June 2016 version to display the

Click Install and takes a little while downloading packages and installing.

Click Close.

Here is the SQL Server 2016 Instance after making a connection to it:

SSMS_SQL2k16_00

Updating a table using Common Table Expression

It is possible to update a table but only the rows returned by the common table expression are modified.

In the Employees table we consider two employees, 'Andrew Fuller' who lives in the city 'Tacoma' and Steven Buchanan who lives in 'London'. Note that the CTE definition has a filter for City='London'

This update query tries to change the LastName to 'Bardick' of an employee whose  FirstName='Andrew'
------------------
USE Northwind
Go
---define the common table expression
WITH EMp_CTE (FirstName,LastName,CITY)  /*Name: EMp_CTE, Column List:FirstName, --LastName,City*/
AS
---define the CTE query

(SELECT FirstName,LastName,City

from Employees

where City='London'
)
---Updating the table
Update EMp_CTE
Set LastName='Bardick'
Where FirstName='Andrew' 
Go
-----
--Andrew Fuller is from Seattle
--expect Andrew Fuller to become Andrew Bardick if the query were to succeed
--Since Andrew Fuller is not from London the table does not get updated
The above query returns '0' rows Table is not modified
-------------------
Now consider this next update query:

USE Northwind
Go
---define the common table expression
WITH EMp_CTE (FirstName,LastName,CITY)  /*Name: EMp_CTE, Column List:FirstName, --LastName,City*/
AS

---define the CTE query

(SELECT FirstName,LastName,City

from Employees

where City='London'
)
---Updating the table

Update EMp_CTE
Set LastName='Bardick'
Where FirstName='Steven' 
Go
--------------------------------------
--The response is 1 row gets updated.
--Steven Buchanan is from London                         
--expect Steven Buchanan to become Steven Buchanan if the query succeeds
--Since Steven Buchanan is from 'London' update will take place and it becomes Steven Bardick as --shown.

The syntax for the CTE can be found here:
Syntax here:
https://msdn.microsoft.com/en-us/library/ms177523.aspx