Table Partitions

Introduced in SQL Server 2005, before that partitioned views are available. A table partition means “divide a large table physically into one or more file groups in the same database, but logically shown as a single table”.

Advantages: 
You can Insert or access the data from a partitioned table quickly and efficiently.
E.g. if you trigger a query, it will perform the actions on respective partitions instead of doing on whole table.

You can perform maintenance operations on one or more partitions more quickly.
E.g. you can perform backup operations on individual file groups or compress the data in one or more partitions.

The query performance will improve.
E.g. when a complex query triggered on a table, instead of involving entire table involves only respective partitions. ..etc

Implementing partition mechanism:
Three components will be involved in a table partition.
1. Partition function
2. Partition scheme
3. Partitioning column

1. Partition Function:
It defines how the rows of a table are divided into multiple file groups based on the values in partitioning column. Here the rows Range will be either LEFT or RIGHT. For example, your table has 3 years of data (1981, 1982, and 1983) and we are dividing the table in to 3 partitions based on the Partitioning column values (HireDate in Employee table). Now your requirement is Partition1 contains the data before 1982, Partition2 contains only 1982, Partition3 contains 1983 and above.

LEFT RANGE: Count the Partitioning column values from LEFT side.

RIGHT RANGE: Count the Partitioning column values from RIGHT side.

2. Partition scheme:

It will group all the file groups and maps Partition Function to all file groups.

3. Partitioning column:

It is a column in the table and it will divide the rows to partitions based on some criteria. For example, Employee table have a column named as “HireDate”, it will divides the rows to partitions based on years. Means Partition1 contains only Employee who joined in 1981, Partition2 contains 1982 and so on.

Example:

 I have created a database Test_DB with 3 data files on 3 file groups. And each file group on different drives

PRIMARY on D drive,

FileGroup2 on E drive,

FileGroup3 on F drive.

CREATE DATABASE [Test_DB]

ON  PRIMARY                  --Default fileGroup ‘Primary’ on D drive

          ( NAME = N'Test_DB',

          FILENAME = N'D:\Radha\Test_DB.mdf' ,

          SIZE = 3072KB ,FILEGROWTH = 1024KB ),

         

 FILEGROUP [FileGroup2]      --Second fileGroup ‘FileGroup2’ on E drive

          (NAME = N'Test_DB1',

          FILENAME = N'E:\Radha\Test_DB1.ndf' ,

          SIZE = 3072KB ,FILEGROWTH = 1024KB ),

         

 FILEGROUP [FileGroup3]           --Third fileGroup ‘FileGroup3’ on F drive

          ( NAME = N'Test_DB2',

          FILENAME = N'F:\Radha\Test_DB2.ndf' ,

          SIZE = 3072KB ,FILEGROWTH = 1024KB )

         

 LOG ON ( NAME = N'Test_DB_log', --Log file on T drive

          FILENAME = N'T:\Radha\Test_DB_log.ldf' ,

          SIZE = 1024KB , FILEGROWTH = 10%)

GO

 I have verified files and file groups information by the below script,

SELECT FG.name as [FileGroupName],DF.name as [DBfileName],DF.physical_name as [DBfilePath]

FROM sys.filegroups FG inner join sys.database_files DF

on FG.data_space_id = DF.data_space_id

WHERE FG.type = 'FG' or DF.type_desc = 'ROWS'

GO

Step1: Create a Partition Function with RIGHT RANGE. So that, the function can divide the rows to respective partitions for each complete year,

Use [Test_DB]

GO

CREATE PARTITION FUNCTION HDFunction (datetime)

AS RANGE RIGHT FOR VALUES ( '1982-01-01', '1983-01-01')

GO

Step2: Create a partition Scheme, it will group all file groups and map the HDFunction to all file groups,

Use [Test_DB]

GO

CREATE PARTITION SCHEME Test_PartitionScheme

AS PARTITION HDFunction

TO ([PRIMARY], [FileGroup2], [FileGroup2])

GO

Step3: Create a partitioned Table, here am going to partition Employee table to 3 files groups on Test_DB as,

Use [Test_DB]

GO

CREATE TABLE [dbo].[Employee]

 (EMPNO INT PRIMARY KEY,

 ENAME VARCHAR(20),

 JOB VARCHAR(20),

 MGR INT,

 HIREDATE DATETIME,

 SAL MONEY,

 COMM MONEY,

 DEPTNO int);

 GO

ON Test_PartitionScheme (HIREDATE);

GO

Step4: Insert values to Employee table with different HIREDATES as,

INSERT INTO [dbo].[Employee] VALUES

 (7369, 'SMITH', 'CLERK', 7902, '17-DEC-1980', 800, NULL, 20);

 INSERT INTO [dbo].[Employee] VALUES

 (7499, 'ALLEN', 'SALESMAN', 7698, '20-FEB-1981', 1600, 300, 30);

 INSERT INTO [dbo].[Employee] VALUES

 (7521, 'WARD', 'SALESMAN', 7698, '22-FEB-1981', 1250, 500, 30);

 INSERT INTO [dbo].[Employee] VALUES

 (7566, 'JONES', 'MANAGER', 7839, '2-APR-1981', 2975, NULL, 20);

 INSERT INTO [dbo].[Employee] VALUES

 (7654, 'MARTIN', 'SALESMAN', 7698, '28-SEP-1981', 1250, 1400, 30);

 INSERT INTO [dbo].[Employee] VALUES

 (7698, 'BLAKE', 'MANAGER', 7839, '1-MAY-1981', 2850, NULL, 30);

 INSERT INTO [dbo].[Employee] VALUES

 (7782, 'CLARK', 'MANAGER', 7839, '9-JUN-1981', 2450, NULL, 10);

 INSERT INTO [dbo].[Employee] VALUES

 (7788, 'SCOTT', 'ANALYST', 7566, '09-DEC-1982', 3000, NULL, 20);

 INSERT INTO [dbo].[Employee] VALUES

 (7839, 'KING', 'PRESIDENT', NULL, '17-NOV-1981', 5000, NULL, 10);

 INSERT INTO [dbo].[Employee] VALUES

 (7844, 'TURNER', 'SALESMAN', 7698, '8-SEP-1981', 1500, 0, 30);

 INSERT INTO [dbo].[Employee] VALUES

 (7876, 'ADAMS', 'CLERK', 7788, '12-JAN-1983', 1100, NULL, 20);

 INSERT INTO [dbo].[Employee] VALUES

 (7900, 'JAMES', 'CLERK', 7698, '3-DEC-1981', 950, NULL, 30);

 INSERT INTO [dbo].[Employee] VALUES

 (7902, 'FORD', 'ANALYST', 7566, '3-DEC-1981', 3000, NULL, 20);

 INSERT INTO [dbo].[Employee] VALUES

 (7934, 'MILLER', 'CLERK', 7782, '23-JAN-1982', 1300, NULL, 10);

Step5:  we can verify the rows in the different partitions as,

Use [Test_DB]

GO

select partition_id, index_id, partition_number, Rows

FROM sys.partitions

WHERE OBJECT_NAME(OBJECT_ID)='Employee'

GO

For more info click on Partitions...

Triggers

Triggers are sql queries, which will be sat on a table and executes (fires) immediately after DML commands execution is called “After Triggers”. The triggers that are execute before the DML commands execution on a table called “Instead Of Triggers”.

After Triggers

The basic aim of “triggers” is to audit (Record) the data of a table, when it has inserted or updated. (When, What, Who)

For example I want to know the information like …. Who has inserted the data, when it has inserted and which row has inserted. For this I can create an INSERT trigger. As same I can create triggers for UPDATE and DELETE operations.

An INSERT trigger fires immediately after when an INSERT operation happened on the table,

An UPDATE trigger fires immediately after when an UPDATE operation happened on the table,

A DELETE trigger fires immediately after when a DELETE operation happened on the table,

Let’s assume that we have a table Employee, and create another table Tr_Employee. Here the main table Employee audit information will be maintained in Tr_Employee table.

      CREATE TABLE [dbo].[Tr_Employee](

            [EMPNO] [int],

            [Action] [varchar](10),

            [Updated On] [datetime],

            [Updated By] [varchar](20) )

Insert Triggers:  Will create on Employee table and fired after an INSERT statement on the table;

      CREATE TRIGGER trg_INSEmployee

      ON [dbo].[Employee]

      FOR INSERT

      AS

            declare @Empno int;

            declare @Action varchar(100);       -- What

            declare @UpdatedOn datetime;        -- When

            declare @UpdatedBy varchar(100);    -- Who

            select @Empno = inserted.EMPNO from inserted;  

            set @Action='INSERTED';

            select @UpdatedBy = SYSTEM_USER

           

            insert into Tr_Employee       -- Inserts respective information to Tr_Employee table

            values(@Empno,@Action,getdate(),@UpdatedBy);

      GO

Insert Trigger is created on Employee table, now insert a row to Employee, automatically insert trigger fired after the INSERT command and records the respective information to Tr_Employee table.

INSERT INTO Employee VALUES (7952,'REDDY','MANAGER',7666,'1982-01-23',6500.00,NULL,10)

Check the main table,

SELECT * FROM Employee

Check the Audit table,

SELECT * FROM Tr_Employee

Update Triggers:  Will create on Employee table and fired after an UPDATE statements on the table;

CREATE TRIGGER trg_UPDEmployee

      ON [dbo].[Employee]

      FOR UPDATE

      AS

            declare @Empno int;

            declare @Action varchar(100);       -- What

            declare @UpdatedOn datetime;        -- When

            declare @UpdatedBy varchar(100);    -- Who

            select @Empno = inserted.EMPNO from inserted;  

           

if update(ENAME) OR update(JOB)OR update(HIREDATE)OR update(SAL)OR update(DEPTNO)

            set @Action='UPDATED';

            select @UpdatedBy = SYSTEM_USER

           

            insert into Tr_Employee       -- Inserts respective information to Tr_Employee table

            values(@Empno,@Action,getdate(),@UpdatedBy);

      GO

Update Trigger is created on Employee table, now update a value to Employee, automatically update trigger fired after the UPDATE command and records the respective information to Tr_Employee table.

UPDATE Employee

SET SAL= SAL+600

WHERE EMPNO = 7369

Check the main table,

SELECT * FROM Employee

Check the Audit table,

SELECT * FROM Tr_Employee

Delete Triggers:  Will create on Employee table and fired after the DELETE statements on the table;

      CREATE TRIGGER trg_DELEmployee

      ON [dbo].[Employee]

      FOR DELETE

      AS

            declare @Empno int,@ENAME varchar(10),@JOB varchar(10),

  @HIREDATE date, @SAL money, @DEPTNO int

            declare @Action varchar(100);       -- What

            declare @UpdatedOn datetime;        -- When

            declare @UpdatedBy varchar(100);    -- Who

            select @Empno = deleted.EMPNO from deleted;    

            select @ENAME = deleted.ENAME from deleted;

            select @JOB = deleted.JOB from deleted;

            select @HIREDATE = deleted.HIREDATE from deleted;

            select @SAL = deleted.SAL from deleted;

            select @DEPTNO = deleted.DEPTNO from deleted;

           

            SET @Action='DELETED';

            SELECT @UpdatedBy = SYSTEM_USER

           

            insert into Tr_Employee       -- Inserts respective information to Tr_Employee table

            values(@Empno,@Action,getdate(),@UpdatedBy);

      GO

Delete Trigger is created on Employee table, now Delete a value to Employee, automatically Delete trigger fired after the DELETE command and records the respective information to Tr_Employee table.

DELETE FROM Employee

WHERE EMPNO = 7369

Check the main table,

SELECT * FROM Employee

Check the Audit table,

SELECT * FROM Tr_Employee

Stored Procedure

The name itself defining the behavior of “Stored Procedures” (Stored-Saved; Procedure-Query)

For example I want to get the Employee table information, for that I can fire a SELECT query as

      SELECT * FROM Employee

Here SELECT query is simple; if you require same table information for multiple times, you can write and fire the same query multiple times & get the results. For suppose the query has multiple lines like

     SELECT EMPNO,ENAME,SAL,DEPTNO

FROM Employee

      WHERE DEPTNO = 10

      ORDER BY SAL DESC

It is time consuming process to write the same queries for multiple times. Instead of writing multiple times, I can store this query in database and can use it for multiple times by calling to it (Avoid re-witting). In simple words ‘Stored procedure’ is a place in database, you can store/save the query in that place and provide a name to that place. 

      CREATE PROCEDURE Proc_Employee

      AS

      SELECT * FROM Employee

Or

      CREATE PROCEDURE MaxSalEmp

      AS   

      SELECT EMPNO,ENAME,SAL,DEPTNO

FROM Employee

      WHERE DEPTNO = 10

      ORDER BY SAL DESC

Whenever you want to execute the query which has been stored, just call the name of the place by  EXEC <PlaceName>.

EXEC MaxSalEmp

If you want to modify that query; just write as

ALTER PROCEDURE MaxSalEmp

      AS   

      SELECT EMPNO,ENAME,SAL,DEPTNO

FROM Employee

      WHERE DEPTNO = 20  -- Modified

      ORDER BY SAL DESC

In the above example, if you want to know the details of another department number instead of 10 or 20, need to create another empty space (Parameter) in the Stored Procedure with a name and data type (@Parameter datatype). And pass the required values to that particular space at execution,

      CREATE PROCEDURE MaxSalEmp (@DNO INT) –- Parameter Declararion

      AS   

      SELECT EMPNO,ENAME,SAL,DEPTNO

FROM Employee

      WHERE DEPTNO = @DNO                 -- Make it as Dynamic

      ORDER BY SAL DESC

And call the name of the place with parameter by  EXEC <PlaceName>(Parameter),

EXEC MaxSalEmp 10  -- Call the Stored Procedure with value

If you want to drop the place as DROP PROCUDURE <PlaceName>

      DROP PROCEDURE MaxSalEmp

Ref: Calling a Stored Procedure with another Procedure

For more info : Sql server Stored Procedures 

MSBI_SSIS_Conditional Split Transformation

Conditional split transformation is used to split the data into multiple destinations depends on the condition.

Let's assume that there is a source table Employee and the destination requirement is to move the data to multiple tables, each table as a specific department number. (E.g : For DEPTNO 10 need to create a separate table,For DEPTNO 20 need to create a separate table.... etc. ) 

We can write a query in SQL Server as:
/* For DEPTNO 10 */
SELECT * FROM Employee
WHERE DEPTNO = 10

/* For DEPTNO 20 */
SELECT * FROM Employee
WHERE DEPTNO = 20

/* For DEPTNO 30 */
SELECT * FROM Employee
WHERE DEPTNO = 30

We can implement it in a SSIS package as below,

• Take a Data Flow task to control flow. 
• Edit the Data Flow as drag & drop OLEDB Source, Conditional Split Transformation and 3 OLEDB Destinations(name it as DEPT10, DEPT20, DEPT30) to Data Flow for design. 
• Edit the OLEDB Source, provide the connection and authentication details and select the Employee table. 
• Make a connection from OLEDB Source to Conditional Split Transformation.
• Edit the Conditional Split Transformation as below, 

In the "Conditional Split Transformation Editor" window expand the Columns folder, drag and drop the DEPTNO column to Condition box assign the values(Conditions), click on OK.

• Now make a connection from Conditional Split Transformation to DEPT10 Destination, it will displays a "Input Output Selection" window.
• Here first select the Output "DEPT 10" which we given in Conditional Split Transformation. Now make connections from Conditional Split Transformation to DEPT20,DEPT30 destinations same as earlier. 

**Note: "Conditional Split Default Output" is used to fetch the remaining data which is not participated in the conditions(For example DEPT NO 40,50...).

• Edit all the Destinations and provide the connections, authentication details and create a new table for each destination with respective Department number(E.g: EMP_DEPT10,EMP_DEPT20....).
• Check the data in the destination tables.