BI Developer: December 2018

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.