Just a simple JOIN Scenario in SQL Server...

Create Table tblLeft
(
Col1 int,
Col2 int
)

Insert into tblLeft Values(1,1)
Insert into tblLeft Values(1,1)

Create Table tblRight
(
Col3 int,
Col4 int
)

Insert into tblRight Values(1,1)
Insert into tblRight Values(1,1)

Select * from tblLeft
Select * from tblRight

Select * from tblLeft inner join tblRight  on tblLeft.Col1 = tblRight.Col3

Select * from tblLeft left join tblRight on tblLeft.Col1 = tblRight.Col3

Select * from tblLeft right join tblRight on tblLeft.Col1 = tblRight.Col3

Truncate Vs Delete...Can Truncate be Rolled back in SQL Server?

Lets begin with some straight QUESTIONS???

We have with us...A TestTable with TestID Column.

Use this code to prepare this set up:

--Create a TestTable with TestID Column
CREATE TABLE TestTable (TestID INT)

--Insert some records in this Table
INSERT INTO TestTable Values(101),(102),(103),(104),(105)

--Run select to crosscheck 
SELECT * FROM TestTable
GO

We have TestTable with following records now:

TestID
101
102
103
104
105

Now...What will be the output of these three T-Sql Statements when run independently ?

Scenario 1:

-- Begin The Transaction
BEGIN TRAN
--Delete statement
DELETE from TestTable
-- Rollback Transaction
ROLLBACK TRAN
GO
-- Check output after Delete
SELECT * FROM TestTable
GO

Scenario 2: 

BEGIN TRAN
--Truncate statement
TRUNCATE Table TestTable
-- Rollback Transaction
ROLLBACK TRAN
GO
-- Check output after Truncate
SELECT * FROM TestTable
GO

Scenario 3:

BEGIN TRAN
--DROP statement
DROP Table TestTable
-- Rollback Transaction
ROLLBACK TRAN
GO
-- Check output after DROP
SELECT * FROM TestTable
GO

Now let me put some doubts in your mind before you answer...

1. Truncate and Drop are DDL statement. Are they logged - Yes or No? What is their logging behavior. Is this possible to Rollback in case of Truncate?

Now Run your queries in all these 3 scenarios to clear your doubts...

ANSWER:(All 3 scenarios when run independently i.e. not one after the other) gives same output i.e. In all 3 scenarios Transactions have been rolled back giving the following output:

 TestID
101
102
103
104
105

Scenario 4: What output will you get in this case?

BEGIN TRAN
--Truncate statement
Truncate Table TestTable
--DROP statement
DROP Table TestTable
-- Rollback Transaction
ROLLBACK TRAN
GO
-- Check output after Truncate and Drop
SELECT * FROM TestTable
GO

Answer : In this case also when both Truncate and Drop statements are there inside the BEGIN TRAN and ROLLBACK TRAN we get the same output i.e. Rollback has been done and output which we get is :

 TestID
101
102
103
104
105

Scenario 5: What output will you get in this case. When we specify drop before Truncate within BEGIN TRAN and ROLLBACK TRAN ?

-- Begin The Transaction
BEGIN TRAN
--DROP statement
DROP Table TestTable
--Truncate statement
Truncate Table TestTable
-- Rollback Transaction
ROLLBACK TRAN
GO
-- Check output after Truncate and Drop
SELECT * FROM TestTable
GO

Answer: In this case you will get This Message in the Messages Tab along with This output in Results Tab since the drop is used before Truncate within Transaction so finally whole Transaction is rolled back and the select statement outside the Transaction still comes up with 5 records.

Messages Tab:

Msg 4701, Level 16, State 1, Line 6
Cannot find the object "TestTable" because it does not exist or you do not have permissions.

Results Tab:

TestID
101
102
103
104
105

Having run all these queries Lets Draw out some conclusions and get our basics correct:

1. Within a Transaction TRUNCATE can be rolled back. As mentioned in Point No 4 below; Truncate also does logging but the nature of logging as compared to Delete is different. This is why in our examples above we were able to Roll back in case of Truncate.

2. Truncate can't be rolled back if no transaction is used. Truncate operation when you run outside a Transaction is auto committed so You will not be able to rollback if you use Truncate outside a Transaction. i.e. Once committed it is not possible to rollback Truncate and DROP.

3. TRUNCATE removes all rows from a table without logging the individual row deletions. TRUNCATE TABLE is similar to the DELETE statement with no WHERE clause; However, TRUNCATE TABLE is faster because it uses fewer system & transaction log resources.

4. The DELETE statement removes rows one at a time and records an entry in the transaction log for each deleted row. We can say A DELETE statement is always fully logged. TRUNCATE TABLE removes the data by de-allocating the data pages used to store the table data and records only the page de-allocations in the transaction log. Hence less Transaction Log space is used in case of TRUNCATE.

5. Truncate uses Fewer locks. When the DELETE statement is executed using a row lock, each row in the table is locked for deletion. On the contrary TRUNCATE TABLE do not lock each row as Delete does but always locks the table (including a schema (SCH-M) lock) and page.

6. TRUNCATE TABLE removes all rows from a table, but the table structure and its columns, constraints, indexes, and so on remain. We have to use DROP TABLE to remove the table definitions well as its data.

7.  TRUNCATE TABLE cannot activate a trigger because the operation does not log row - by - row deletions.

8. DROP removes one or more table definitions and all data, indexes, triggers, constraints and permission specifications for those tables. Any view or stored procedure that references d dropped table must be explicitly dropped by using DROP VIEW or DROP PROCEDURE statement.

9. If the table contains an identity column, the counter for that column is reset to the seed value defined for the column. If no seed was defined, the default value 1 is used. To retain the identity counter it is advisable to use DELETE.

10. You cannot use TRUNCATE TABLE on tables that are referenced by a FOREIGN KEY constraint. Instead, use DELETE statement without a WHERE clause.

11. You can truncate a table having a foreign key that references itself.

12. You cannot use TRUNCATE TABLE on tables that participate in an indexed view. Better use Delete in such cases.

13. DROP TABLE cannot be used to drop a table that's referenced by a FOREIGN KEY constraint. The referencing FOREIGN KEY constraint or the referencing table must first be dropped. If both the referencing table and the table that holds the primary key are being dropped in the same DROP TABLE statement, the referencing table must be listed first.

14. By default, a DELETE statement always acquires an exclusive (X) lock on the table it modifies, & holds that lock until the transaction completes. With an exclusive (X) lock, no other transactions can modify data; read operations can take place only with the use of the NOLOCK hint or read uncommitted isolation level. You can specify table hints to override this default behavior for the duration of the DELETE statement by specifying another locking method, however, it is not recommended and can lead to data inconsistency.

Reference : Self study, MSDN and other internet resources. 

Note: This blog is based on self study and is subject to be modified as and when I discover something new or find anything incorrect. Suggestions  and comments are welcome.

Regards,
Paras

What is a Precedence Constraint in SSIS Project Control Flow?

Precedence Constraint: In SSIS Project Control Flow, a Precedence Constraint defines the workflow between two executables. The Precedence Constraint can be based on a combination of the execution results and the Evaluation of Expressions.

A Precedence Constraint has the following Constraint Options:

A Package Designer has the option of choosing between the following 4 Evaluation Operations:

1. Constraint : Have 3 values Success(Green), Failure(Red), Completion(Blue).
2. Expression: Execution of the next task depends on the evaluation result(True or False) of the expression provided.
3. Expression and Constraint: Next task in the hierarchy runs based on options selected as Value of Constraint(Success, Failure, Completion) AND evaluation result of the expression.
4.  Expression or Constraint : Next task in the hierarchy runs based on options selected as Value of Constraint(Success, Failure, Completion) OR evaluation result of the expression.

Multiple Constraints: If the constrained Task has multiple constraints, you can choose how the constraints interoperate to control the execution of the constrained Task.

Options in Radio Button are:

1. Logical AND: All constraints must evaluate to TRUE.
2. Logical OR: One constraint must evaluate to TRUE

Some Important Properties of a Precedence Constraint are:

1. PrecedenceExecutable: The executable to wait on.
2. ConstrainedExecutable: The executable that will execute.
3. EvalOp: Any of the 4 Evaluation Operation explained above.
4. Expression: The expression to be evaluated.
5. Value: Succes, Failure or Completion
6. LogicalAnd: True or false
7. EvalutesTrue: Test if the precedence constraint will eavalute TRUE.

Note: The above post is based on self study and subject to be edited. Comments and Suggestions are very welcome.  Thanks...

Regards,
Paras