Triggers

Triggers are a special type of stored procedure that automatically execute when a DML or DDL statement associated with the trigger is executed. You can’t execute a trigger directly.
DML triggers are created against a table or a view and are defined for a specific event: INSERT, UPDATE, or DELETE. Regardless of the number of rows that are affected, a trigger fires only once for an action.

When you execute the event for which a trigger is defined, SQL Server automatically executes the code within the trigger.

When a trigger is defined as AFTER, the trigger fires after the modification has passed all constraints. If a modification fails a constraint, such primary key constraint, the trigger is not executed.
Note:
AFTER triggers are defined only for tables, and multiple AFTER triggers can be defined for the same action. If you have multiple triggers created for the same action, you can specify the first and last triggers to fire by using the sp_settriggerorder system stored procedure.

A trigger defined with the INSTEAD OF clause causes the trigger code to be executed as a replacement for INSERT, UPDATE, or DELETE. You can define a single INSTEAD OF trigger for a given action. Although INSTEAD OF triggers can be created against both tables and views.

Let's see this example of a DDL trigger:


Now if you try to delete any table or alter the schema of tables you will get this error:



Note:
You can fire a DDL trigger when you grant, revoke, or deny permissions at either a server or a database level.

While DML triggers have access to the virtual tables called inserted and deleted tables, DDL triggers and logon triggers have access to the EVENTDATA function. For example when inserting a recode into a table, SQL Server creates a virtual table call INSERTED and loads data into the inserted table then executes the trigger statements and writes the related data pages.

Derived Table

When you use a result set of SELECT statement as table for FROM clause this called Derived Table or Virtual Table.
View or Temp table can give you the same result of Derived table but not simple as Derived Table because With the temporary tables you need to have multiple steps in your process, first to create the temporary table, then to populate the temporary table, then to select data from the temporary table and lastly cleanup of the temporary table. With the view approach you need to create the view and then use the view in your query.
Let's see an example:



The main benefit of a derived table is the fact that the result set resides entirely in memory, which allows faster data access than if the result set were on a storage device.

How to encrypt or Decrypt stored procedures?

Sql server gives ability to encrypt triggers, functions, stored procedures, and views.
But note when you encrypted you cannot decrypted so you have to store a copy of your stored procedure in safe place to return to it or you have to use third party to decrypted or write a complex code.

Let's see an example

Logon Triggers

You can use this approach if you want to audit any logon by user to Sql Server instance but note that the Logon triggers are fired after authentication succeeds but before the user session is actually established. And You also limit the number of connections that a user is allowed to make to the instance.
Let's see this example:
1. Create a test user with public access



2. Create a logon trigger which do the following:



3. Now If you try to login by "TestUser" , you will get an error message as below:



4. To disable this trigger you can run this command:

Let the photos speak about me and my family

Let me introduce to you my vice-president (Dr.Ahmad):

and here my photo with Dr.Ahmad and my current car (notice that my current car because i want to change it to 4X4 car soon)

A faith moment , May allah accept our OMRA (minor hajj)

Now let's show you some of skills of Dr.Ahmad specially the climbing skill. Now he climbs to his bed , and how ? i don't know

and here he tries to climb by his teeth (till this date 23.1.2010 two teeth and half tooth)

Oh ,I don't wonder ! if Dr.Ahmad do the above things because see his uncles , some whites try to be negro!!

Oh, I forget my self ...

In Lebanon and exactly at the roof of my ucnle house

In Cairo ,with Abo Alhool

and behind me Salah Aldeen mosque

In Jazan !! but far away from the war with the mutinous.

Common Table Expressions (CTEs)

CTE introduced in Microsoft SQL Server 2005 that offer a more readable form of the derived table that can be declared once and referenced multiple times in a query and we can used for two things:
1. Efficiently executing a recursive queries
2. Return a temporary result set when nested inside another SQL statement
Return a Temporary result set: Let's start by an example to understand the syntax:



So from the above example, CTE allow us to define temporary result set within execution statement with readable syntax. The CTE syntax contains two parts:
1. CTE name and its columns alias(optional)
2. query within parentheses that produce the temporary result set

Recursive Query:
A recursive CTE expands the definition of the table expression and consists of two parts:
An anchor query, which is the source of the recursion, along with a UNION ALL statement and a second query, which recurses across the anchor query.
let’s see an example. If I have Employees table which has these columns (EmpId,Name ,ReportsTo) and ReportsTo column is a foreign key field that refers to the primary key field EmpId and I want to know the manager of each employee and at which level



The result set of the above query:

Ranking functions - part 2

In part 1 we have seen the ROW_NUMBER and RANK functions so let's start see the other functions.
DENSE_RANK: it similar to RANK but If you do not want any gaps in a sequence, you can use the DENSE_RANK function. DENSE_RANK assigns the same value to each duplicate but does not produce gaps in the sequence.
Let's see an example:



NTILE: is used to divide a result set into approximately equal groups. Let's see this example to understand this function:



The result set divided into 3 groups so NTILE function allows you to divide a result set into approximately equal-sized groups.

Ranking functions –part1

Ranking functions are used to provide simple analytics such as statistical ordering or segmentation.
T-SQL has four functions that can be used for ranking data: ROW_NUMBER, RANK,
DENSE_RANK, and NTILE.

ROW_NUMBER: This function returns a sequential number starting at 1 for each row or grouping within your result set.
Let's see examples:



Also you can group and order each group as a separate block result set, let's see this example:



RANK: If you need to number a result set but also deal with values that have the same order by, you can use the RANK function. If the result set does not need to have the same order by, RANK produces the same results as ROW_NUMBER.
RANK assigns the same value to each row that is has the save order by and then skips to the next value, leaving a gap in the sequence corresponding to the number of rows that were tied.
Let's see an example:



And likewise ROW_NUMBER if you want to use PARTITION BY with Grouping.

sp_spaceused sys stored procedure

sp_spaceused is system stored procedure used to measure the row number of table ,reserved disk space and used disk space.
Let's see an example for a table:



Let's see an example for a whole database:



Notice When you pass 'true' parameter, SQL Server will automatically runs DBCC UPDATEUSAGE to correct any inaccuracies in catalog views. So if you need absolute size you must pass it and especially after inserting large amounts of data like this:

MERGE statement in SQL Server

This statement was introduced with SQL SERVER 2008 The MERGE statement gives you the ability to compare rows in a source and target table. You can then define the appropriate INSERT, UPDATE, or DELETE command to be performed based on the results of the comparison.
A common merge scenario is moving data from one table to another. So all moving activity can be done by one statement and this is good for optimizing the performance .

Let's see this Example:

OUTPUT Clause in SQL SERVER

The OUTPUT clause gives you the ability to access the inserted and deleted tables that in versions previous to SQL Server 2005 were accessible only through triggers.
Some of Benefits of this clause:
• For auditing actions (Insert statement)
• For data archiving (delete statement)
• To see the old and new modification in the row (update statement)
Let's see this example:



We can also use OUTPUT clause with DELETE and UPDATE statement also.

Implicit Transaction action in SQL SERVER

Implicit transactions actions are always enabled on Oracle servers. If you would like to have the same functionality in SQL Server, enable the SET_IMPLICIT_TRANSACTIONS connection property for each connection created.

Let's see an example:



Note that if you disconnected from SQL Server without rollback or commit ,your changes will not affect it to table.

Data Compression in SQL Server 2008

Data Compression in SQL Server has been made available in SQL Server 2005 with Service Pack 2 where a new storage format for storing decimal and numeric data was introduced. The vardecimal storage format allows decimal and numeric data types to be stored as a variable-length column. This concept has been extended in SQL Server 2008 to all fixed-legth data type.
You can compressing data in the tables and indexes into two levels:
1. Row level
2. Page level
In row level converts all data types to variable-length data types. It also uses no storage space to store NULL values. The more fixed-length data types (such as datetime2,int, decimal, and nchar) that you use in a table, the more likely you are to benefit from row-level compression.

In page level compression includes row-level compression and adds page-level compression using page dictionary and column prefixing. Page dictionary simply introduces pointers between rows in the same page to avoid storing redundant data.
Consider the following:

Row 01: FADI AHMAD
Row 02: SHADI AHAMD
Row 03: FADI AHMAD

If this page used page dictionary, it would look like this:

Row 01: FADI AHMAD
Row 02: SHADI AHAMD
Row 03: 01

So the row 01 pointer to row 03 and this's saving your database storage.

Here an examples for both levels:



Now how to evaluate the estimated space saving by using Data Compression ?
There are tow ways :
1. By using sp_estimate_data_compression_savings system stored proceudre

2. Data Compression Wizard
Do the following steps:
Right click on the "Sales.SalesOrderDetail" table like below:

Then choose Manage Compression .
Secondly choose compression type and click calculate to run the same result of sp_estimate_data_compression_savings or next to implement the data compression on the table.

SELECT … INTO statement

SELECT INTO statement allows you to create a new temporary or permanent table populated with the results of the defined SELECT statement. You can select all columns or defined the specific columns and you can use WHERE clause or JION clauses with your SELECT statement.
The SELECT INTO statement is most often used to create backup copies of tables.
Let's see examples:

Diff between Delete and Truncate statement

Truncate like delete statement but without WHERE clause with these differences:
1- DELETE statement logs information on each row deleted, while the TRUNCATE does not remove data, but rather deallocates whole data pages and removes pointers to indexes. The data still exists until it is overwritten or the database is shrunk.
2- Because of the minimal logging, along with how the Database Engine removes the data from the table, the TRUNCATE TABLE statement executes more quickly and requires fewer resources on the server.
3- If an identity column exists in the table, the TRUNCATE TABLE command resets the identity seed value.
4- TRUNCATE will not work on tables, which are referenced by one or more FOREIGN KEY constraints.

OUTER APPLY and CROSS APPLY in SQL SERVER

The APPLY operator uses the results from a query as input to apply a table-valued
Function to each row in the result set.
The APPLY operator has two forms, CROSS APPLY and OUTER APPLY.
CROSS APPLY returns only rows from the left output, which produces data from the table-valued function (matches row between the table and function).
OUTER APPLY returns all rows from the left, outer table. Like an OUTER JOIN statement.
Let's see this Example:

Summary:
OUTER APPLY returns all rows from the outer table along with the results returned by
the function when rows match, while CROSS APPLY returns only the rows from the
outer table where a match exists within the function results.

SET IDENTITY_INSERT setting

You cannot enter a value for an identity column when executing an INSERT or UPDATE statement. You can overcome this limitation by issuing The SET IDENTITY_INSERT ON command in the current connection.
Let's see this example:

Grouping Data or Summary Data in SQL Server statement – part 2

In part 1 article we saw some statements that help us in grouping data for summary data or reporting in easiest way.
Let's see GROUPING SET and GROUPING function (introduced in SQL Server 2008):
These are new feature in SQL Server 2008 to allow you with multiple grouping to be returned in one result set (means , here there are two time of groups) .
Let's see this example :




…Last row



So we get the same result set that generated by WITH ROLLUP.
Note: to distinguish the null value that returned by WITH ROLLUP or WITH CUBE or GROUPING SET ,SQL SERVER provides GROUPING function .
This function will return 1 if the row return by the above statements and 0 for other .
So let's see the above example with GROUPING function:

Grouping Data or Summary Data in SQL Server statement – part 1

Sql server provides various statements that help us to generate Summary Data for reporting in Sql server Database like Aggregation functions ( SUM, AVERAGE,..) ,GROUP BY , GROUPING SETS , WITH ROLLUP ,WITH CUBE and GROUPING functions :
The GROUP BY clause can be used in a SELECT statement to collect data across multiple records and group the results by one or more columns
Let's see these Examples on GROUP BY clause:



First result set demonstrated how we can generate subtotal based on ProductSubcategoryId (we can group by more than one column as same way by adding this column to SELECT clause and GROUP BY clause) and Second result set demonstrated how we can generate total result.
Now let's work With Rollup and Cube (introduced with SQL SERVER 2005):



… Last row



The above result set demonstrated how WITH ROLLUP generated to us subtotal count for each ProductSubcategoryID column.
Notice the last row also show the Total Count for all ProductSubcategoryID subtotal with NULL of ProductSubcategoryID.
Let's see another example by grouping by two columns:



….Last row



WITH CUBE (work with more than one column) show same Summary Data with different way. Let's see this example WITH CUBE:



…Last rows