Difference of create Index by using include columns or not using

I Want To Create Index In SQL Server 2008 R2 in Column1 and Column2 What is the difference of below query:

Not include

CREATE NONCLUSTERED INDEX [IX_1] ON [dbo].[MyTable]
(
    [Column1] ASC,
    [Column2] ASC
) ON [PRIMARY]
Or include:

CREATE NONCLUSTERED INDEX [IX_2] ON [dbo].[MyTable]
(
    [Column1] ASC
)
INCLUDE ([Column2]) ON [PRIMARY]



In the first one Column2 gets added to the index key. In the second one it might not (*) get added to the key in which case it will only appear in the index leaf pages. This can allow the index to be searched by Column1 but avoid the need to go back to the base table (bookmark lookup/key lookup) to retrieve the value for Column2.

i.e. it makes index2 "covering" for queries such as

SELECT Column1,Column2
FROM [dbo].[MyTable]
WHERE Column1 = 'X'
And it also covers queries such as

SELECT Column1,Column2
FROM [dbo].[MyTable]
WHERE Column1 = 'X' AND Column2 = 'Y'
But index1 may well perform better for the second query as it can seek on the two columns directly (as opposed to only being able to search on Column1 then needing to evaluate all matching rows at the index leaf level to see if they meet the Column2 predicate). If Column2 is never used as a search predicate against that index and your queries against that index wouldn't benefit from having Column2 ordered then it should be added as an INCLUDE-d column to keep the size of the key down and reduce the number of pages in the index.

(*) The reason I say "might not" above is because if Column2 is (part of) the clustered index key it will still be added there anyway for a non clustered index not created with the UNIQUE option.

Difference between @@IDENTITY, SCOPE_IDENTITY, IDENT_CURRENT

Compare

@@IDENTITY

It returns the last identity value generated for any table in the current session, across all scopes.

Let me explain this... suppose we create an insert trigger on table which inserts a row in another table with generate an identity column, then @@IDENTITY returns that identity record which is created by trigger.

SCOPE_IDENTITY

It returns the last identity value generated for any table in the current session and the current scope.

Let me explain this... suppose we create an insert trigger on table which inserts a row in another table with generate an identity column, then SCOPE_IDENTITY result is not affected but if a trigger or a user defined function is affected on the same table that produced the value returns that identity record thenSCOPE_IDENTITY returns that identity record which is created by trigger or a user defined function.

IDENT_CURRENT

It returns the last identity value generated for a specific table in any session and any scope.

In other words, we can say it is not affected by scope and session, it only depends on a particular table and returns that table related identity value which is generated in any session or scope.

SQL Query

I am explaining the above process with the help of some sample query, hope it helps:

CREATE TABLE Parent(id int IDENTITY);

CREATE TABLE Child(id int IDENTITY(100,1));

GO

CREATE TRIGGER Parentins ON Parent FOR INSERT

AS

BEGIN

   INSERT Child DEFAULT VALUES

END;

GO

--End of trigger definition

SELECT id FROM Parent;
--id is empty.

SELECT id FROM Child;
--ID is empty. 

--Do the following in Session 1
INSERT Parent DEFAULT VALUES;
SELECT @@IDENTITY;
/*Returns the value 100. This was inserted by the trigger.*/

SELECT SCOPE_IDENTITY();
/* Returns the value 1. This was inserted by the
INSERT statement two statements before this query.*/ 

SELECT IDENT_CURRENT('Child');

/* Returns value inserted into Child, that is in the trigger.*/

SELECT IDENT_CURRENT('Parent');

/* Returns value inserted into Parent. 
This was the INSERT statement four statements before this query.*/ 

-- Do the following in Session 2.

SELECT @@IDENTITY;

/* Returns NULL because there has been no INSERT action
up to this point in this session.*/ 

SELECT SCOPE_IDENTITY();

/* Returns NULL because there has been no INSERT action
up to this point in this scope in this session.*/

SELECT IDENT_CURRENT('Child');

/* Returns the last value inserted into Child.*/