SQL Server Environments

 SQL Server supports 2 types of environments

1. Stand Alone Environment
• For small scale applications
• Only ONE production server
2. Cluster based Environment

• For medium to large scale applications
• Min two production servers
• Banking, telecom, online application need cluster based environment

What is Control Flow?

    Control Flow is the default work space or SSIS Designer part when we create a package in SSIS (.dtsx), which will control the flow of Containers, and Tasks, optionally we can control the flow by using Precedence constraint for these Containers / Tasks.

For Complete Information Go Here

What are the components in SSIS?

1. Control Flow

2. Data Flow

3. Parameters

4. Event Handlers

5. Package Explorer 

What is SSIS?

     SSIS is know as SQL Server Integration Services which is a Microsoft Tool, which will use for Extract, Transform and Load the data from the various sources.

Parallel Execution in SSIS

Parallel Execution: is nothing but number of tasks to be executed simultaneously

     This simultaneously would be happen when there is no relation between two task with precedence constraint then only the tasks will start altogether.

    But here is limit of task executions to start altogether in SSIS, the default parallel execution limit is 

The number of processors of our using machine + 2

when you execute the package, by default number of processors of our using machine + 2 tasks will start.

     see the below image when I started directly my package it will start with 6 tasks at a time because my machine has 4 processors.

     I created one package with 20 Execute SQL Tasks without precedence constraint means no dependence between them.

Picture 1: below is the package with 20 Execute SQL Tasks

Picture 2: Started with 6 packages out of 20

Picture 3: Started with next 6 packages after completing the first 6 package out of 20

Picture 4: now again picked next 6 packages after completing previous started 6 packages.

Picture 5: finishing the rest of packages.

here is the way to increase the limit of default parallel execution tasks, Please go here

Please give feedback by your comment bellow.

MaxConcurrentExecutables Property in SSIS Package

MaxConcurrentExecutables: This is a package level property, using this this property we can increase the number of tasks parallel execution process.

    So we can increase the size of the exection taks parallely usig this MaxConcurrentExecutables, for default process please check here how it executes.

 Let see by increaseing the size of this MaxConcurrentExecutables propery how it works

  When i execute without changing any MaxConcurrentExecutables propery, here is the process which I already posted.

    I creaed once package with 20 taks without precedence constraint which means no relation between each task.

Picture 1: A package with 20 tasks without any relation (precedence constraint)

Process to change the property value.

Step 1: Go to Properties window on SSIS package by pressing F4 / directly click on properties window which is right side in SSIS package.

Step 2: Go to MaxConcurrentExecutables property on properties window.

By default SSIS package keeps MaxConcurrentExecutables propery value as -1, which means default number of taks is Total number of processors of machine + 2 task can start exectuion at once.

Step 3: Change default values -1 to 10 / as you required.

     After changing MaxConcurrentExecutables propery from -1 to 10

Picture 2: started package and picked first 10 tasks at a time out of 20 tasks.

Picture 3: started package with next 10 tasks at a time after finishes the previous 10 tasks.

so finally it finishes all the packages with picking the 10 at a time for the process.

and these 10 tasks starting order by name if we observe closely.

here is my observation,by increasing this property value our process will finish very quickly, it is very useful to when we need to save the execution time. 

 but keep in mind when you are increasing the MaxConcurrentExecutables propery value, is there any other application using same processors and do we have enough size these kind of things take into priority. 

Please provide you value feedback by giving your comment below

Ctrl R not working in SQL Server?

Ctrl + R: it's general thing which SQL Developer uses often in day.

     Some times this Result Pane won't work, so we can again create this short cut, please go through below steps.

1. go to Tool > ..Options > Keyboard > Keyboard

2. Select Window.ShowResultsPane under Show Commands containing

3. SQL Query Editor under Use new short cut in:

4. Click empty space of Press Shortcut Keys:then give your Shortcut Key (pres your           
     required shortcut key)

What is Primary Key in SQL Server?

                                                                     PRIMARY KEY                                                                                         

1. It's a CONSTRAINT, a collection of columns uniquely identifies each row in a table

2. Only one PRIMARY KEY constraint allows on Table.

3. PRIMARY KEY column will not allow NULL Values & duplicate values

What is the difference between DELETE and TRUNCATE statements?

                                                           TRUNCATE vs DELETE

TRUNCATE	DELETE
Truncate is used to delete the all records in table	Delete is used to delete the row level and table level data
We can’t rollback the data	We can rollback data if we maintain the transaction
It’s auto committed	It’s explicit committed
It’s a DDL (Data Definition Language) Command	It’s a DML (Data Manipulation Language) Command
It’s faster than the delete	It’s very slow when we compare with TRUNCATE

Synchronous and Asynchronous Transformations In SSIS

     

          I am going to explain in simple manner about Synchronous and Asynchronous Transformations in SSIS, as we know in SSIS everything based on transformations only, so these divided into few types.

Synchronous and

Asynchronous

         Mostly we should prefer for Synchronous only when we are not able to meet our requirements using Synchronous then only we should go for Asynchronous Transformation.

Synchronous (non-blocking): 

                            Processing the row by row transformation into next task, in detail, one record will not wait /depend on buffer for other record, example Data Conversion Transformation, DCT will not wait / depend on other record to transform to the next task simply it will convert the record and send to the next task, so Output is Synchronous with Input.

Asynchronous(Fully Blocking):

                                  Processing rows will depend on other / next record based on those next record it will take the decision and transform to the next task, Example, Sort Transformation, in this transformation holds the all records on buffer then it will make the decision based on the data then only records will pass to the next task.

More about these Transformation please go through below link(s).

Understanding Synchronous and Asynchronous Transformations

Please give your valuable comments below :)

Ways of column alias name in SQL Server.

As per my knowledge I am using in three ways of giving alias names to the database table column in SQL Server while selecting,

Below are the types which I am using:

1. Giving the alias name in Square Bracket [ ]

2. Giving the alias name with Underscore _

3. Giving the alias name with Double Quotes “ ”

4. Giving Direct Expected Name

1. Square Brackets

          The required column/ header name should be with in the Square Brackets only, with in this bracket we can give spaces also in expected name, so mostly we will use these kinds of brackets when we have space in our expected column name, below is the example.

Example:

SELECT                                                                                                           

  Empno                                         --Original Name of the column   

, Empno AS [Employee Number]--Alias Name of the Column   FROM tbl_Emp

O/P

2. Underscore _

          The required column/ header name should be with Underscore i.e. _, with this underscore only between the two words like Employee and Name, we have to concatenate these two with Underscore I.e. Employee_Name

Example:

SELECT  

  Empno   --Original Name of the column                

, Empno  AS Employee_Number --Alias Name of the Column   FROM tbl_Em            

O/P

3. Double Quotes:

          The required column/ header name should be within the Double Quotes only, with in this double quote we can give spaces also in expected column name, so this also mostly we will use these kinds of Quotes when we have spaces in our expected column name, below is the example.

Example:

SELECT                                                                                                           

  Empno --Original Name of the column                                           

, Empno AS "Employee Number"--Alias Name of the Column   FROM tbl_Emp                                                                                            

O/P

4. Direct Expected Name:

          This like directly whatever you what you can give, there is no such condition like above, but here we can’t write column name with spaces, it will work only for single world column name

Example:

SELECT                                                                                                            

  Empno                                         --Original Name of the column   

, Empno AS "EmployeeNumber"--Alias Name of the Column  

FROM tbl_Emp                                                                                             

O/P

Please comment below

Department (Dept) Sample Data

Please Copy the below Query and run on your SSMS.


/* Droping the Existing Dept Table: */
DROP TABLE IF EXISTS tbl_Dept;

/* Creating New Dept Table: */
CREATE TABLE tbl_Dept(
tbl_Deptno INT NOT NULL PRIMARY KEY
, DName VARCHAR(50) NOT NULL
, Location VARCHAR(50) NOT NULL);

/* Inserting Data into Dept Table: */
INSERT INTO tbl_Dept VALUES 
 (10,'Accounting','New York')
,(20,'Research','Dallas')
,(30,'Sales','Chicago')
,(40,'Operations','Boston');

/*cSeleting Inserted Data */
SELECT * FROM tbl_Dept

Click Here for : Employee Table Sample Data

Please comment below

Employee (Emp) Sample Data

Please Copy the below Query and run on your SSMS.

/*Please relook once you before Drop the Existing Table: */
DROP TABLE IF EXISTS tbl_Emp;

/*Creating New Employee Table*/
CREATE TABLE tbl_Emp(
  Empno INT NOT NULL PRIMARY KEY
, Ename VARCHAR(50) NOT NULL
, Job VARCHAR(50) NOT NULL
, Mgr INT
, Hiredate DATE
, Sal DECIMAL(10,2)
, Comm DECIMAL(10,2)
, Deptno INT NOT NULL);


/*Inserting Data Into above created Table: */
INSERT INTO tbl_Emp (Empno,Ename,Job,Mgr,Hiredate,Sal,Comm,Deptno)
VALUES
  (7369, 'SMITH', 'CLERK', 7902, '6/13/93', 800, 0.00, 20)
, (7499, 'ALLEN', 'SALESMAN', 7698, '8/15/98', 1600, 300, 30)
, (7521, 'WARD', 'SALESMAN', 7698, '3/26/96', 1250, 500, 30)
, (7566, 'JONES', 'MANAGER', 7839, '10/31/95', 2975, null, 20)
, (7698, 'BLAKE', 'MANAGER', 7839, '6/11/92', 2850, null, 30)
, (7782, 'CLARK', 'MANAGER', 7839, '5/14/93', 2450, null, 10)
, (7788, 'SCOTT', 'ANALYST', 7566, '3/5/96', 3000, null, 20)
, (7839, 'KING', 'PRESIDENT', null, '6/9/90', 5000, 0, 10)
, (7844, 'TURNER', 'SALESMAN', 7698, '6/4/95', 1500, 0, 30)
, (7876, 'ADAMS', 'CLERK', 7788, '6/4/99', 1100, null, 20)
, (7900, 'JAMES', 'CLERK', 7698, '6/23/00', 950, null, 30)
, (7934, 'MILLER', 'CLERK', 7782, '1/21/00', 1300, null, 10)
, (7902, 'FORD', 'ANALYST', 7566, '12/5/97', 3000, null, 20)
, (7654, 'MARTIN', 'SALESMAN', 7698, '12/5/98', 1250, 1400, 30);


/*Selecting Data from Table*/
select * from tbl_Emp


Please comment below 

SSISDB Backup

USE SSISDB

GO

BACKUP MASTER KEY TO FILE = 'D:\Datafortune\Master_Key_Backup'

ENCRYPTION BY PASSWORD='Password$2018%'

Can you define Equijoin?

The condition for matching the rows based on the equality of two values, one from each of the tables being joined. So that's what makes it an equijoin: the ON condition is equality. This includes inner joins and all three types of outer joins.

Please ask your Questions in comment box..