Programming Examples

Are you a Programmer or Application Developer or a DBA? Take a cup of coffee, sit back and spend few minutes here :)

SQL Server

Articles on SQL, TSQL and Management Studio.

SQL Triggers Explained With Examples

Trigger Execution order when both AFTER & INSTEAD OF Exists

An ‘SQL Trigger’ is a compiled unit of SQL Server procedure, which can run automatically when an event occurs on the database objects. For example, you can write a piece of SQL Script (the trigger), which can be called whenever an insert (the event) takes place on a specific table. There are various types of triggers possible in SQL Server. The two crucial types are:

DDL Triggers: These triggers act on ‘Data Definition Language’; say, for example, a trigger procedure runs whenever a table is created. We can say DDL trigger as an ‘Database level trigger’ hence it acts on the DB. SQL Server invokes the Trigger procedure whenever the manipulation like Create, Alter and Drop takes place on its DB objects.

DML Triggers: These triggers act on ‘Data Manipulation Language’. In DB world, the tables maintain the Data. So, we can do manipulating data through the tables by making use of Insert, Update and Delete statements on the table. We can call DML trigger as an ‘Table Level Trigger’ since it operates at table level. Simply,

Continue Reading →

Updating Table Data Via SQL Views

Joins and Calculated Columns

An SQL View is nothing but a select query with a name given to it or we can say a view is a ‘Named Query’. Why we need a view? There will be a lot of answers for this. Some important answers are below:

1) A View can bring data from many tables by using suitable joins and while bringing so, it may use complex filters and calculated data to form the needed result set. In user point of view, all these complications are hidden, and they feel that they are pulling data from a single table.
2) Some time for security reasons, access to the table and its private details like table schema and relations are not given to the DB users. All they have is access to a view without the knowledge of what tables are actually sitting in the DB.
3) Using the view, the DB admin can restrict an user to update only some portions of the records.

Continue Reading →

Ranking Functions Examples – RANK, DENSE_RANK & Percentile

SQL Dense Rank Example

Ranking Functions are useful to provide ranking on the table columns based on the value stored in it. Say, for example, let us say a table is having students id and their Total Marks as separate columns. Now, it is meaningful if we apply the these function on the Total Marks column rather than Student ID column. With these Functions, we can easily find 2nd maximum, or 4th maximum marks. The Ranking Functions are available in SQL 2005 and later versions.

Continue Reading →