TRUNCATE Table Vs DELETE Table

TRUNCATE Table

• Fastest method for deleting all the rows from a table.
• We can say Truncate Table is similar to Delete Statement without WHERE clause (for.e.g. DELETE FROM EMP)
• Uses fewer system and transaction log resources. Only the page deallocations are recorded in the Transacton Log.
• Fewer locks are used. Truncate table always locks the table and page but not the row.
• Without exception, zero pages are left in the table
• If the column in a table is having IDENTITY colum then Truncate Table command will reset the identity to it’s seed value. If no seed is defined then value 1 is used as default value.

DELETE Table

• Deletes one row at a time and each deleted row is logged in the transaction log.
• Delete statement locks each row for deletion (when used with Row Lock)
• Once the delete process is over, table still contains the empty pages in a heap.
• If the DELETE statement doesn’t use the Table Lock then the table may contain many empty pages .
• To deallocate the empty pages, use TABLOCK hint in the DELETE statement. This will put a shared lock on the table instead of row or page lock and deallocate the pages.

Note: When empty pages are not de allocated, the space cannot be reused by other objects.

Example

TRUNCATE TABLE EMP_TEST 

DELETE FROM EMP_TEST WITH (TABLOCK)

SSIS Task Host Container

What is Task Host Container in SSIS?

1. It’s a default container where every single tasks fall into.
2. If we don’t specify a container then the task will fall into Task Host container.
3. The Task Host is not configured separately, instead, it is configured when we set the properties of the task it encapsulates.
4. SSIS extends variables and event handlers to the task through task host container

By Harsh Shah

Difference Between SET and SELECT commands in SQL Server

SET	SELECT
Can assign the value of an expression to the variable	Can assign the value of an expression to the variable
Cannot retrieve data from data source. It can only retrieve data from the expressions.	Can retrieve data from data source and also uses other SELECT clauses (WHERE, FROM etc)
Use SET only when you want to assign value of a function result or constant to a variable	Can use for both i.e. retrieve value of function result

Example

–Variable declaration
DECLARE
    @vName VARCHAR(10),
    @vobject_id VARCHAR(30),
    @vName1 VARCHAR(30);

–assigning a single value
SET @vName1=’Test Value’;

–Selecting and retrieving multiple values in a single select statement
SELECT
    @vName=name,
    @vobject_id=object_id
FROM
    sys.objects
WHERE
    name =’sysrowsetcolumns’

–printing the retrieved values
SELECT  @vName1,@vName, @vobject_id

–By Harsh Shah