Interview Questions 3

pivot,rownumber,group by,views,diff b/w temp table and table variable,
CTE(common type expression) ,cursor,sys.objects,funtions,scope identity,identity,
current identity,replace and stuff, Inbuild fucntion,cross join,INDEXS,COLLEATION
DIFF B/W PROC AND FUNCTION,ACID PROPERTIES AND IMPLEMENTATION,
DIFF B/W VARCHAR AND NAVARHAR UNIQUECODE,LOCKS,NORMALIZAIION,
HOW MANY CLUSTER INDEX IN 2005,AND 2008,jobs,triggers,batches,Scalar-valued function,Inline function ,Table-valued function


http://a4academics.com/interview-questions/53-database-and-sql/397-top-100-database-sql-interview-questions-and-answers-examples-queries

http://www.indiabix.com/technical/sql-server-common-questions/6

http://www.mindfiresolutions.com/Using-Pivot-Operator-in-SQL-Server-1181.php

http://dba.stackexchange.com/questions/16385/whats-the-difference-between-a-temp-table-and-table-variable-in-sql-server

http://www.dotnet-tricks.com/Tutorial/sqlserver/KY3T010412-Different-Types-of-SQL-Server-Functions.html

Table-Valued Functions in SQL Server

A table-valued function is a user define function can be used where views or table expression are allowed in T-SQL.User define function contain additional feature while views limited with single select statement. Table- valued function return the output as a table data type. The table data type is a special data type is used to store a set of rows. A user defined function works in the same way as a system function. Kinds of Table-valued functions: ·         Inline table-valued function ·         Multistatement table-valued function ·         Scalar Function Inline Table-Valued Function An Inline table-valued function return a variable of data type table whose value is derived from single Select statement .An inline function does not contain function body within Begin and End statement. For example: We want to see that records of those students which course duration is 3 .An Inline function that accepts duration as a parameter and returns all the records that have duration greater than or equal the parameter value as shown below: Query Create Function Fun (@Duration int) Returns Table      As Return ( Select S.id,S.Name,S.Age,C.Course,C.Duration from StudentDetail S join CourseDetail C on S.Id=C.Id whereC.Duration>=@Duration ) Executing “Fun” function with parameter Query Select * from Fun(3) Output Multistatement Table-Valued Function The Multistatement function is slightly complicated from other two types of function. A multistatement function uses multiple statements to build the table that is returned to the calling statement. The function body contains a Begin and End block. For example: We want to see that records of those students which  is greater than or equal to 21 .An Multistatement function that accepts age as a parameter and returns all the records that have age greater than or equal the parameter value as shown below: Query Create Function Multistate (@Age varchar(5)) Returns @table table (       StudentId varchar(5),       StudentName varchar(20),       StudentAge int,       StudentCity varchar(20),       StudentState varchar(20) )       As       Begin       Insert @table       Select * from StudentDetail where Age>=@Age       Return       End       Select * from Multistate(21) Executing “Multistate” function with parameter Query Scalar Function A Scalar function return single value of the data type referenced in return clause of the create function statement. For example: In scalar function executing while loop when Num1 value  will reach on 100 than  loop will be terminate and Num2 value add with Num1 and return total of Num1 and Num2 as result as shown below: Query Create Function SumTwoValues ( @Num1 int, @Num2 int )  Returns int  As Begin  While @Num1 <100   Begin    Set @Num1 =@Num1 +1   End   Return (@Num1+@Num2) End Executing “SumTwoValues” function with two parameter. Query

Comment Ena

SCOPE_IDENTITY Function

This article explains about SCOPE_IDENTITY function in sql server with examples.

SCOPE_IDENTITY is used to get last identity value inserted into an identity column in the same scope. Scope can be stored procedure, trigger, function or batch.

SCOPE_IDENTITY() will return the IDENTITY value inserted, which was the last INSERT that occurred in the same scope. 

The SCOPE_IDENTITY() function will return the NULL value if the function is invoked before any insert statements into an identity column occur in the scope.

Syntax of SCOPE_IDENTITY() Function :

SCOPE_IDENTITY()

Return type of above function is a sql_variant.

Examples of SCOPE_IDENTITY() Function :

Example 1 : Use of SCOPE_IDENTITY() function in select clause 

INSERT INTO [Northwind].[dbo].[Shippers]

           ([CompanyName]

           ,[Phone])

     VALUES

           ('Load Runner',

            '(503) 555-9830')

SELECT SCOPE_IDENTITY()

Output

4

SQL Functions – STUFF() and REPLACE()

STUFF() can be used to stuff a string into another string. It inserts the string at a given position, and deletes the number of characters specified from the original string.

– © 2011 – Vishal (http://SqlAndMe.com)

DECLARE @string1 VARCHAR(20) = 'Microsoft Server'

DECLARE @string2 VARCHAR(20) = 'SQL Server 2005'

SELECT      @string1 + ' -> ' + STUFF(@string1, 11, 0, 'SQL ')

            AS 'String 1',

            @string2 + ' -> ' + STUFF(@string2, 15, 1, '8 R2')

            AS 'String 2'

Result Set:

String 1                                 String 2

—————————————- ————————————-

Microsoft Server -> Microsoft SQL Server SQL Server 2005 -> SQL Server 2008 R2

(1 row(s) affected)

In the first string it inserts 'SQL ' at specified position – 11, the third argument 0 indicated the number of characters to be deleted before inserting the new string.

For second string, we have deleted one (1) character starting from position 15, which deletes '5', and then it inserts the new string at position 15 – '8 R2'.

REPLACE():

REPLACE() replaces all the specified characters with new characters.

DECLARE @string3 VARCHAR(35) = 'sql 2005, sql 2008, sql 2008 r2'

SELECT @string3, REPLACE(@string3,'sql','SQL')

Result Set:

———————————–      ———————————–

sql 2005, sql 2008, sql 2008 r2   SQL 2005, SQL 2008, SQL 2008 r2

(1 row(s) affected)

However, it is not limited to same number of characters:

DECLARE @string3 VARCHAR(35) = '2008 R2'

SELECT @string3, REPLACE(@string3,'20','SQL Server 2')

Result Set:

————–       ————————

2008 R2              SQL Server 208 R2

(1 row(s) affected)

SQL SERVER – Collate – Case Sensitive SQL Query Search

 

If Column1 of Table1 has following values ‘CaseSearch, casesearch, CASESEARCH, CaSeSeArCh’, following statement will return you all the four records.

SELECT Column1
FROM Table1
WHERE Column1 = 'casesearch'
To make the query case sensitive and retrieve only one record (“casesearch”) from above query, the collation of the query needs to be changed as follows.

SELECT Column1
FROM Table1
WHERE Column1 COLLATE Latin1_General_CS_AS = 'casesearch'
Adding COLLATE Latin1_General_CS_AS makes the search case sensitive.

Default Collation of the SQL Server installation SQL_Latin1_General_CP1_CI_AS is not case sensitive.

To change the collation of the any column for any table permanently run following query.

ALTER TABLE Table1
ALTER COLUMN Column1 VARCHAR(20)
COLLATE Latin1_General_CS_AS

what are the different types of locks available in sql server?

There are different types of lock in SQL Server 2000 and 2005. These locks are applied in different situations. Following are the list of locks : 

SHARED - SQL Server uses shared locks for all read operations. For example a select statement. 

UPDATE - This lock applied on those resources that can be updated. This lock prevents the common form of dead lock that occurs when multiple sessions are locking the data so that they can update it later. 

EXCLUSIVE - Used for data-modification operations, such as INSERT, UPDATE, or DELETE. Ensures that multiple updates cannot be made to the same resource at the same time. 

INTENT - Used to establish a lock hierarchy. The different types of intent locks are: intent shared, intent exclusive, and shared with intent exclusive. 

SCHEMA - Used when an operation dependent on the schema of a table is executing. The different types of schema locks are: schema modification and schema stability. 

BULK UPDATE - This lock is applied when there is a bulk copying of data and the TABLOCK is applied. 

KEY RANGE - Protects the range of rows read by a query when using the serializable transaction isolation level. Ensures that other transactions cannot insert rows that would qualify for the queries of the serializable transaction if the queries were run again.

 

 

Transaction and ACID properties in SQL Server

Transaction:

Transaction are used to solve the problem of concurrency and data recovery. A transaction specifies a sequence of transact-SQL statements that build a logical unit.

Concurrency - Situation in which many user application programs read and write the same data at the same time is called concurrency.

Recovery - Problem can arise for system database is different hardware and software error which is responsible for recovery from all kinds of software and hardware error.

Transaction properties(ACID)

Transactions have four properties atomicity, consistency, isolation, and durability (known as the ACID properties).

1. A (Atomicity) 

The sequence of operations must be atomic, either all or no operations are performed. Each transaction is said to be atomic if when one part of the transaction fails, the entire transaction fails and database state is left unchanged. Atomicity requires that database modifications must follow an "all or nothing" rule.

Example:

The transaction subtracts 10 from A and adds 10 to B. If it succeeds, it would be valid because the data continues to satisfy the constraint. However, assume that after removing 10 from A, the transaction is unable to modify B. If the database retains A's new value, atomicity and the constraint would both be violated. Atomicity requires that both parts of this transaction complete or neither.

2. C (Consistency)

Consistency means that the transaction should be follow all the rule of user validation. If A send all the data to B and in between there is no any duplicity and modification of data. That means data will reach same as send by A. This is called consistency of data.

Example

Rules that can be enforced by the database system are that the primary keys values of a record uniquely identify that record, that the values stored in fields are the right type and in the right range.

3. I (Isolation)

In isolation if one transaction in progress at the same time other transaction can not perform operation. It will wait to complete the first transaction. The isolation portion of the ACID properties is needed when there are concurrent transactions. Concurrent transactions are transactions that occur at the same time, such as shared multiple users accessing shared objects. DBMS to prevent conflicts between concurrent transactions are a concept referred to as isolation.

4. D (Durability)

Durability are used to recover the committed transaction updates against any kind of system failure (hardware or software). Durability is the DBMS's guarantee that once the user has been notified of a transaction's success the transaction will not be lost, the transaction's data changes will survive system failure, and that all integrity constraints have been satisfied.

Features to consider for durability:

1. recovery to the most recent successful commit after a database software failure.
2. recovery to the most recent successful commit after an application software failure.
3. recovery to the most recent successful commit after a CPU failure.
4. recovery to the most recent successful backup after a disk failure

Sql server indexes <<Previous  Next>> 1.What is an index? Indexes of SQL Server are similar to the indexes in books. They help SQL Server retrieve the data quicker. Indexes are of two types. Clustered indexes and non-clustered indexes. Rows in the table are stored in the order of the clustered index key.................... Read answer 2.Disadvantages of the Indexes Use of intexes slow down Data modification operations (such as INSERT, UPDATE, DELETE). Every time data changes in the table, all the indexes need to be updated.................. Read answer 3.Define Clustered and Non-Clustered Index. Clustered index exists as sorted row on disk. Clustered index re-orders the table record.....................  Read answer 4.What is Unique Index? Unique index is the index that is applied to any column of unique value.  A unique index can also be applied to a group of columns...................... Read answer 5.Difference between clustered and non-clustered index. Both stored as B-tree structure. The leaf level of a clustered index is the actual data where as leaf level of a non-clustered index is pointer to data................... Read answer 6.Explain the 'Fillfactor' concept in Indexes. The fill factor option is provided for smoothening index data storage and performance.................. Read answer 7.How do you maintain a fill factor in existing indexes? Usually the data keeps getting added, deleted, or updated in the table due to which the fill factor is implemented during the index creation itself. Fill factor is not maintained after an index is created............... Read answer 8.What is it unwise to create wide clustered index keys? A clustered index is a good choice for searching over a range of values. After an indexed row is found, the remaining rows being adjacent to it can be found easily................ Read answer 9.What is full-text indexing? Full text indexes are stored in the file system and are administered through the database.................  Read answer 10.What is fill factor and pad index? A fill factor is a specification done during the creation of indexes so that a particular amount of space can be left on a leaf level page to decrease the occurrence of page splits.................. Read answer 11.Describe important index characteristics. The characteristics of the indexes are: They fasten the searching of a row. They are sorted by the Key values................. Read answer 12.What are the types of indexes? Types of indexes: Clustered: It sorts and stores the data row of the table or view in order based on the index key.  Non clustered: it can be defined on a table or view with clustered index or on a heap. Each row contains the key and row locator................ Read answer 13.Describe the purpose of indexes. Allow the server to retrieve requested data, in as few I/O operations. Improve performance.................. Read answer 14.Determine when an index is appropriate. a. When there is large amount of data. For faster search mechanism indexes are appropriate.  b. To improve performance they must be created on fields used in table joins............... Read answer 15.Syntax and an example for create, rename and delete index. Create Index: CREATE INDEX index_name ON table_name (col_1,col_2..);  Example: Create index index_sample ON employee(emp_id).........

 

 

 

@@TRANCOUNT - TRANCOUNT Function

This article explains about @@TRANCOUNT function in sql server with examples.

@@TRANCOUNT function returns the number of active transactions for the current connection.

The BEGIN TRANSACTION statement increments @@TRANCOUNT by 1. ROLLBACK TRANSACTION decrements @@TRANCOUNT to 0, except for ROLLBACK TRANSACTION savepoint_name, which does not affect @@TRANCOUNT. COMMIT TRANSACTION or COMMIT WORK decrement @@TRANCOUNT by 1.

Syntax of @@TRANCOUNT Function :

@@TRANCOUNT

Return type of @@TRANCOUNT function is an integer.

Clustered Indexes

A clustered index stores the actual data rows at the leaf level of the index. Returning to the example above, that would mean that the entire row of data associated with the primary key value of 123 would be stored in that leaf node. An important characteristic of the clustered index is that the indexed values are sorted in either ascending or descending order. As a result, there can be only one clustered index on a table or view. In addition, data in a table is sorted only if a clustered index has been defined on a table.

Note: A table that has a clustered index is referred to as a clustered table. A table that has no clustered index is referred to as a heap.

Nonclustered Indexes

Unlike a clustered indexed, the leaf nodes of a nonclustered index contain only the values from the indexed columns and row locators that point to the actual data rows, rather than contain the data rows themselves. This means that the query engine must take an additional step in order to locate the actual data.

A row locator’s structure depends on whether it points to a clustered table or to a heap. If referencing a clustered table, the row locator points to the clustered index, using the value from the clustered index to navigate to the correct data row. If referencing a heap, the row locator points to the actual data row.

Nonclustered indexes cannot be sorted like clustered indexes; however, you can create more than one nonclustered index per table or view. SQL Server 2005 supports up to 249 nonclustered indexes, and SQL Server 2008 support up to 999. This certainly doesn’t mean you should create that many indexes. Indexes can both help and hinder performance, as I explain later in the article.

In addition to being able to create multiple nonclustered indexes on a table or view, you can also add included columns to your index. This means that you can store at the leaf level not only the values from the indexed column, but also the values from non-indexed columns. This strategy allows you to get around some of the limitations on indexes. For example, you can include non-indexed columns in order to exceed the size limit of indexed columns (900 bytes in most cases).

Index Types

In addition to an index being clustered or nonclustered, it can be configured in other ways:

·         Composite index: An index that contains more than one column. In both SQL Server 2005 and 2008, you can include up to 16 columns in an index, as long as the index doesn’t exceed the 900-byte limit. Both clustered and nonclustered indexes can be composite indexes.

·         Unique Index: An index that ensures the uniqueness of each value in the indexed column. If the index is a composite, the uniqueness is enforced across the columns as a whole, not on the individual columns. For example, if you were to create an index on the FirstName and LastName columns in a table, the names together must be unique, but the individual names can be duplicated.

A unique index is automatically created when you define a primary key or unique constraint:

§  Primary key: When you define a primary key constraint on one or more columns, SQL Server automatically creates a unique, clustered index if a clustered index does not already exist on the table or view. However, you can override the default behavior and define a unique, nonclustered index on the primary key.

§  Unique: When you define a unique constraint, SQL Server automatically creates a unique, nonclustered index. You can specify that a unique clustered index be created if a clustered index does not already exist on the table.

·         Covering index: A type of index that includes all the columns that are needed to process a particular query. For example, your query might retrieve the FirstName and LastName columns from a table, based on a value in the ContactID column. You can create a co

Common Table Expression(CTE) in Sql server 2005

Introduction:

The common table expression is one of the new features in sql server 2005. It can be used instead of temp table or table variables in the stored procedures in the circumstances. Let's see CTE with some example queries.

Background:

Most of the developers while writing the stored procedures they create the temp tables or table variables. They need some table to store the temporary results in order to manipulate the data in the other tables based on this temp result.

The temp variables will be stored on the tempdb and it needs to be deleted in the tempdb database.

The table variable is best when compare with the temp tables. Because the table variable initially will be there in the memory for the certain limit of size and if the size increase then it will be moved to the temp database. However the scope of the table variable is only up to that program. When compare with table variable the CTE is best. It just store the result set like normal view.

CTE (Common Table Expression):

The CTE is one of the essential features in the sql server 2005.It just store the result as temp result set. It can be access like normal table or view. This is only up to that scope.

The syntax of the CTE is the following.

WITH name (Alias name of the retrieve result set fields)
AS
(
//Write the sql query here
)
SELECT * FROM name

Here the select statement must be very next to the CTE. The name is mandatory and the argument is an optional. This can be used to give the alias to the retrieve field of the CTE.

CTE 1: Simple CTE

WITH ProductCTE
AS
(
  SELECT ProductID AS [ID],ProductName AS [Name],CategoryID AS [CID],UnitPrice AS [Price]
  FROM Products
)
SELECT * FROM ProductCTE

Here all the product details like ID, name, category ID and Unit Price will be retrieved and stored as temporary result set in the ProductCTE.

This result set can be retrieved like table or view.

CTE2:Simple CTE with alias

WITH ProductCTE(ID,Name,Category,Price)
AS
(
  SELECT ProductID,ProductName,CategoryID,UnitPrice
  FROM Products
)
SELECT * FROM ProductCTE

Here there are four fieds retrieves from the Products and the alias name have given in the arqument to the CTE result set name.

It also accepts like the following as it is in the normal select query.

WITH ProductCTE
AS
(
  SELECT ProductID AS [ID],ProductName AS [Name],CategoryID AS [CID],UnitPrice AS [Price]
  FROM Products
)
SELECT * FROM ProductCTE

CTE 3: CTE joins with normal table

The result set of the CTE can be joined with any table and also can enforce the relationship with the CTE and other tables.

WITH OrderCustomer
AS
(
  SELECT DISTINCT CustomerID FROM Orders
)
SELECT C.CustomerID,C.CompanyName,C.ContactName,C.Address+', '+C.City AS [Address] FROMCustomers C INNER JOIN OrderCustomer OC ON OC.CustomerID = C.CustomerID

Here the Ordered Customers will be placed in the CTE result set and it will be joined with the Customers details.

CTE 4: Multiple resultsets in the CTE

WITH MyCTE1
AS
(
  SELECT ProductID,SupplierID,CategoryID,UnitPrice,ProductName FROM Products
), 

MyCTE2
AS
(
  SELECT DISTINCT ProductID FROM "Order Details"
)
SELECT C1.ProductID,C1.ProductName,C1.SupplierID,C1.CategoryID FROM MyCTE1 C1 INNER JOINMyCTE2 C2 ON C1.ProductID = C2.ProductID

Here, there are two result sets that will be filtered based on the join condition.

CTE 5: Union statements in the CTE

WITH PartProdCateSale
AS
(
SELECT ProductID FROM Products WHERE CategoryID = (SELECT CategoryID FROM CategoriesWHERE CategoryName='Condiments')
UNION ALL
SELECT ProductID FROM Products WHERE CategoryID = (SELECT CategoryID FROM CategoriesWHERE CategoryName='Seafood')
)
SELECT OD.ProductID,SUM(OD.UnitPrice*OD.Quantity) AS [Total Sale] FROM "Order Details" ODINNER JOIN PartProdCateSale PPCS ON PPCS.ProductID = OD.ProductID
GROUP BY OD.ProductID

Normally when we combine the many result sets we create table and then insert into that table. But see here, we have combined with the union all and instead of table, here CTE has used.

CTE 6: CTE with identity column

WITH MyCustomCTE
   AS
   (
      SELECT CustomerID,row_number() OVER (ORDER BY CustomerID) AS iNo FROM
         Customers
   )
SELECT * FROM MyCustomCTE

SQL SERVER – 2005 – OUTPUT Clause Example and Explanation with INSERT, UPDATE, DELETE

SQL Server 2005 has new OUTPUT clause, which is quite useful. OUTPUT clause has accesses to inserted and deleted tables (virtual tables) just like triggers. OUTPUT clause can be used to return values to client clause. OUTPUT clause can be used with INSERT, UPDATE, or DELETE to identify the actual rows affected by these statements.

OUTPUT clause can generate table variable, a permanent table, or temporary table. Even though, @@Identity will still work in SQL Server 2005, however I find OUTPUT clause very easy and powerful to use. Let us understand OUTPUT clause using example.
————————————————————————————————————————
—-Example 1 : OUTPUT clause into Table with INSERT statement
————————————————————————————————————————
USE AdventureWorks;
GO
--------Creating the table which will store permanent table
CREATE TABLE TestTable (ID INT, TEXTVal VARCHAR(100))
----Creating temp table to store ovalues of OUTPUT clause
DECLARE @TmpTable TABLE (ID INT, TEXTVal VARCHAR(100))
----Insert values in real table as well use OUTPUT clause to insert
----values in the temp table.
INSERT TestTable (ID, TEXTVal)
OUTPUT Inserted.ID, Inserted.TEXTVal INTO @TmpTable
VALUES (1,'FirstVal')
INSERT TestTable (ID, TEXTVal)
OUTPUT Inserted.ID, Inserted.TEXTVal INTO @TmpTable
VALUES (2,'SecondVal')
----Check the values in the temp table and real table
----The values in both the tables will be same
SELECT * FROM @TmpTable
SELECT * FROM TestTable
----Clean up time
DROP TABLE TestTable
GO
ResultSet 1:
ID TextVal
——————— ————————
1 FirstVal
2 SecondVal

ID TextVal
——————— ———————
1 FirstVal
2 SecondVal
————————————————————————————————————————
—-Example 2 : OUTPUT clause with INSERT statement
————————————————————————————————————————
USE AdventureWorks;
GO
----Creating the table which will store permanent table
CREATE TABLE TestTable (ID INT, TEXTVal VARCHAR(100))
----Insert values in real table as well use OUTPUT clause to insert
----values in the temp table.
INSERT TestTable (ID, TEXTVal)
OUTPUT Inserted.ID, Inserted.TEXTVal
VALUES (1,'FirstVal')
INSERT TestTable (ID, TEXTVal)
OUTPUT Inserted.ID, Inserted.TEXTVal
VALUES (2,'SecondVal')
----Clean up time
DROP TABLE TestTable
GO
ResultSet 2:
ID TextVal
——————— ———————
1 FirstVal

(1 row(s) affected)

ID TextVal
——————— ———————
2 SecondVal
————————————————————————————————————————
—-Example 3 : OUTPUT clause into Table with UPDATE statement
————————————————————————————————————————
USE AdventureWorks;
GO
----Creating the table which will store permanent table
CREATE TABLE TestTable (ID INT, TEXTVal VARCHAR(100))
----Creating temp table to store ovalues of OUTPUT clause
DECLARE @TmpTable TABLE (ID_New INT, TEXTVal_New VARCHAR(100),ID_Old INT, TEXTVal_Old VARCHAR(100))
----Insert values in real table
INSERT TestTable (ID, TEXTVal)
VALUES (1,'FirstVal')
INSERT TestTable (ID, TEXTVal)
VALUES (2,'SecondVal')
----Update the table and insert values in temp table using Output clause
UPDATE TestTable
SET TEXTVal = 'NewValue'
OUTPUT Inserted.ID, Inserted.TEXTVal, Deleted.ID, Deleted.TEXTVal INTO @TmpTable
WHERE ID IN (1,2)
----Check the values in the temp table and real table
----The values in both the tables will be same
SELECT * FROM @TmpTable
SELECT * FROM TestTable
----Clean up time
DROP TABLE TestTable
GO

Hi 

Magic tables are nothing but INSERTED, DELETED table scope level, These are not physical tables, only Internal tables. 

This Magic table are used In SQL Server 6.5, 7.0 & 2000 versions with Triggers only. 

But, In SQL Server 2005, 2008 & 2008 R2 Versions can use these Magic tables with Triggers and Non-Triggers also. 

Using with Triggers: 
If you have implemented any trigger for any Tables then, 
1.Whenever you Insert a record on that table, That record will be there on INSERTED Magic table. 
2.Whenever you Update the record on that table, That existing record will be there on DELETED Magic table and modified New data with be there in INSERTED Magic table. 
3.Whenever you Delete the record on that table, That record will be there on DELETED Magic table Only. 

These magic table are used inside the Triggers for tracking the data transaction. 

Using Non-Triggers: 
You can also use the Magic tables with Non-Trigger activities using OUTPUT Clause in SQL Server 2005, 2008 & 2008 R2 versions.