SSIS - FastLoad options with Triggers

Lets say I have a table (DestTable) which has a trigger on INSERT event.
Now if I am populating the data into this table (DestTable) using SSIS FastLoad option then "will the trigger execute?".

Any guess!!!!!!!!!!!!!!!!!!

Using FastLoad option to populate the records into the table will not execute trigger.
If you want to execute the trigger using FastLoad option then right-click the destination component and click "Show Advance Editor....", go to "Component Properties" and then mention FIRE_TRIGGERS in FastLoadOptions.
Same we use while inserting the data using BULK INSERT in T-SQL.

Alternative of CTE

Few days back I was facing a performance issue due to CTE. Then I replaced the CTE with set of sql statements and got the performance boost of 96%.
Here is the sample code which I used :

CREATE TABLE #t (assetid INT, assetname varchar(25), parentassetid INT)
INSERT INTO #t (assetid,assetname,parentassetid) VALUES(1,'aa',null)
INSERT INTO #t (assetid,assetname,parentassetid) VALUES(100,'a',1)
INSERT INTO #t (assetid,assetname,parentassetid) VALUES(101,'b',100)
INSERT INTO #t (assetid,assetname,parentassetid) VALUES(102,'c',100)
INSERT INTO #t (assetid,assetname,parentassetid) VALUES(103,'d',101)
INSERT INTO #t (assetid,assetname,parentassetid) VALUES(105,'e',102)
INSERT INTO #t (assetid,assetname,parentassetid) VALUES(106,'f',108)
INSERT INTO #t (assetid,assetname,parentassetid) VALUES(107,'e',102)


CREATE TABLE #item (id INT identity(1,1), assetid INT, assetname varchar(25), lvl INT)
DECLARE @counter INT
SET @Counter = 0
INSERT INTO #item (assetid,assetname,lvl) SELECT assetid,assetname, @counter FROM #t WHERE parentassetid = 1

WHILE @@ROWCOUNT > 0
BEGIN

SET @counter = @counter + 1

INSERT INTO #item (assetid,assetname,lvl) SELECT a.assetid,a.assetname,@counter FROM #t a INNER JOIN #item b
on a.parentassetid = b.assetid and exists (SELECT 1 FROM #item c WHERE b.assetid = c.assetid and c.lvl = (@counter-1))

END
SELECT * FROM #item

I hope this will help you!!!!

Alternate way of RowCount using sys.partitions

Usually we use COUNT(1) to get the number of rows in a table which is time consuming in case of large tables.

Here is the alternate method to get the Row Count using system view in efficient manner:

SELECT OBJECT_NAME(OBJECT_ID), Rows FROM sys.partitions
WHERE INDEX_ID IN (0,1) -- 0 = Heap;1 = Clustered
AND OBJECT_ID = OBJECT_ID('TableName')

To know more about sys.partitions

Find the open transaction details in Sql Server

The below query can be used to get the details of OPEN TRANSACTION:

SELECT DB_NAME(dbid) AS DBNAME, (SELECT text FROM sys.dm_exec_sql_text(sql_handle)) AS SQLSTATEMENT FROM master..sysprocesses WHERE open_tran > 0

How to get the size of the database

Today i am posting the methods to find the size of database through the sql statement.
Method 1: Using sp_helpdb
The below statement gives the information about all the databases:
EXEC sp_helpdb

whereas the below gives the information about a specified database (in this case about master database):
EXEC sp_helpdb N'master'

Method 2: Using sys.master_files
It is a system view which contains a row per file of a database as stored in the master database:
SELECT DB_NAME(database_id), SUM(size)*8/1024 as size_in_MB FROM sys.master_files GROUP BY database_id

How to find the unused indexes in Sql Server

SELECT OBJECT_NAME(si.OBJECT_ID) as TableName, si.NAME as IndexName, si.INDEX_ID as IndexID
FROM
sys.indexes si INNER JOIN sys.objects so ON si.object_id = so.object_id
WHERE OBJECTPROPERTY(si.object_id,'IsUserTable') = 1 AND index_id NOT IN
(
SELECT index_id FROM sys.dm_db_index_usage_stats sdmv
WHERE sdmv.object_id = si.object_id AND sdmv.index_id = si.index_id
)

XQuery Language

While looking into XQuery, I found it very interesting and decided to share this with you by posting it into the blog.
XQuery language that is used for querying the xml data type. XQuery is a language that can query structured or semi-structured XML data. With the xml data type support provided in the Database Engine, documents can be stored in a database and then queried by using XQuery.

Example: