How do you use the table hint TABLOCK in a SQL Query?

Here is a query that updates a value in the Northwind Shippers table using TABLOCK:
----------
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"

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

How do you create a data manipulation language trigger?

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.

What is a Common Table Expression (CTE) in SQL Server?

Microsoft documentation defines Common Table Expression thus,

"A common table expression (CTE) can be thought of as a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query. Unlike a derived table, a CTE can be self-referencing and can be referenced multiple times in the same query.

It is unlike a temporary table (persisted) during a connection or a view persisted in the database. CTE is only in memory.

We will consider CTE's many use case scenarios in later posts and the objective of this post is to give a simple example using the Northwind database.

A CTE should have the following:

• CTE with a CTE Expression name
• An Optional Column list
• A query defining the CTE

After a CTE is defined it can be referenced like a table or view and you can do all of the following statements:

• Select
• Insert
• Update
• Delete

It can also be used in defining a view by providing the columns.

Now let me define CTE using the Employees table in Northwind. The example is just to show the syntax as the result can be obtained from a single query without using CTE.

USE Northwind
Go
---define the common table expression
WITH NEmp_CTE (FirstName,LastName,CITY)  /*Name: Nemp_CTE, Column List:FirstName, LastName,City*/
AS
---define the CTE query
(SELECT FirstName,LastName,City
from Employees
where City in ('Tacoma','London')
)
---Use it in a Select Query
SELECT LastName,FirstName,CITY
From NEmp_CTE
where LastName <>'Buchanan'
go

The response to this query is:

Note: The response such as the above does not really need a Common Table Expression but the point was to show the construction of the Syntax used for CTE. In fact, it is to make complex queries more readable.

What is a trigger in SQL Server?

A trigger is an event or an action that precipitates other events. However a trigger in SQL Server is a special kind of stored procedure that automatically executes when an event occurs in the database server. 

If you want to know what is a stored procedure, follow this link:
http://hodentekhelp.blogspot.com/2011/06/what-is-stored-procedure.html

There are different kinds of events that can occur in a database server such as the following:

• Data Manipulation language events such as,

Insert

Update

Delete

statements on a Table or View.

In these cases if a trigger is defined, it will fire (respond to the event) regardless of any changes are made to the rows of a table.

• Data Definition Language events such as,

Create

Alter

Drop

and certain stored procedures that perform DDL-like operations

• Log-on trigger

These fire when a session is established by a user.