T-SQL - Coding - check table column exists in the database

Option-01: using SYS.COLUMNS 


IF EXISTS(SELECT * FROM SYS.COLUMNS WHERE NAME = N'account_id'  
            AND OBJECT_ID = OBJECT_ID(N'account'))
BEGIN
PRINT 'EXISTS';
END            
;

Option-02: using INFORMATION_SCHEMA.COLUMNS

IF EXISTS( SELECT * FROM INFORMATION_SCHEMA.COLUMNS 
            WHERE TABLE_NAME = 'account' 
           AND  COLUMN_NAME = 'account_id')
BEGIN
PRINT 'EXISTS';
END            
; 

LDAP - Error - Unable to open log file

Description:
It's a good idea to log the progress of a LDAP import/export to see what entities have successfully imported/exported and what entities are not. To enable logging, we need to use '-j' option in the command with a valid location where log files will be created.

Command:


ldifde -i -f export.ldif -s localhost -t 10389 -a "uid=admin,ou=system" secret -k -j c:\logs

Explanation:
-i = enables import (default is export)
-f = indicate import/export file. After -f user has to provide a valid file to import data from 
-s = server info. Value after -s is server info
-t = port info after the option
-a = execute the command using supplied distinguish name and password
-k = to ignore 'Constraint Violation' and 'Object Already Exists' errors. This is import specific       
       option.
-j = enable logging. Value after this option has to be a valid location

Log Location:
If log location is valid, the command will create two log files ldif.err and ldif.log. First one includes all the errors, second one is for more verbose information


Errors:
1. Log file location must follow -j
2. Unable to open log file

Solution of errors:
1. -j must follow with valid log location and need to make sure no file name
2. Need to make sure file name not provided as log location

Spring - Coding - Intercept a method call using AOP concept

Environment:
1. JDK 1.6
2. Spring 3.1
3. Log4j 1.2.16

Steps:
1. Create a main class which will implement a interface
2. Create a method interceptor/advice class which will intercept the method of class defined in step-01 
3. Setup 'Application Context' configuration with main bean and the bean which will intercept the method call

Application Context:



xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:p="http://www.springframework.org/schema/p"
xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.1.xsd">

 
 
  RongBorno
 

 
 
  
 
      
 
      class="org.springframework.aop.framework.ProxyFactoryBean">
   
     
        com.spring.interceptcall.MainClassInterface
     
   
   
     
        advice
     
   
   
 


Interface and class of callee class:

package com.spring.interceptcall;
public interface MainClassInterface {
public void aMethod();
}


package com.spring.interceptcall;

public class MainClass implements MainClassInterface {
private String name;

public MainClass(String name){
this.setName(name);
}

public void aMethod(){
//Do something
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
}

Method Interceptor/advice class:

package com.spring.interceptcall;

import java.lang.reflect.Method;
import org.apache.log4j.Logger;
import org.springframework.aop.MethodBeforeAdvice;

public class AdviceClass implements MethodBeforeAdvice {

@Override
public void before(Method method, Object[] args, Object target)
throws Throwable {
MainClass mc = (MainClass) target;

Logger log = Logger.getLogger(target.getClass());
log.debug("Class Message= "+ mc.getName() + ", method= "+ method.getName());
}
}

Class to test the sample code:

package com.spring.interceptcall;

import org.apache.log4j.Logger;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

public class ExecuteClass {

/**
* @param args
*/
public static void main(String[] args) {
ApplicationContext context = new ClassPathXmlApplicationContext("MainClass.xml");

Logger.getLogger(ExecuteClass.class).debug("Before calling method");
((MainClassInterface)context.getBean("mainClass")).aMethod();
Logger.getLogger(ExecuteClass.class).debug("After calling method");
}

}

Log4j configuration:

### direct messages to file ###
log4j.appender.file=org.apache.log4j.DailyRollingFileAppender
log4j.appender.file.layout=org.apache.log4j.PatternLayout
log4j.appender.file.layout.ConversionPattern=%d{ISO8601} %5p %c{3}:%-4L - %m%n
log4j.appender.file.File=test.log
log4j.appender.file.DatePattern='.'yyyy-MM-dd

### set log levels - for more verbose logging change 'info' to 'debug' ###

log4j.rootLogger=warn, file
log4j.logger.com.spring.interceptcall=debug

Visual Studio - Error - Visual Studio Setup cannot be run in Program Compatibility Mode

Error Description: 
I have captured screenshot of the error instead putting description (seeing is believing)

Solution-01: Check Program Compatibility Assistant registry settings

Steps:
1. Run regedit. 'Start > Run > regedit'
2. Browse to each of the following keys and delete any value that refers to setup.exe in the Visual Studio install path:
    - HKEY_CURRENT_USER\SOFTWARE\Microsoft\Windows NT\CurrentVersion\AppCompatFlags \Compatibility Assistant\Persisted

    - HKEY_CURRENT_USER\SOFTWARE\Microsoft\Windows NT\CurrentVersion \AppCompatFlags\Layers

3. Rerun setup

Additional Information:
Sometimes setup.exe is set to OS compatible mode which might prevent it to run on existing OS. In that case clear out compatibility setting and rerun the setup.

Steps:
1. locate setup.exe.
2. Right-click setup.exe and then click Properties.
3. On the Compatibility tab, clear "Run this program in compatibility mode for" 

4. Then click OK

Resources:
1. http://go.microsoft.com/fwlink/?linkid=143397

LDAP - Error - A referral was returned from the server

Problem:
System throws below error-01 when try to execute command-01.

Error-01:Add error on line 2: Referral

The server side error is "A referral was returned from the server."

0 entries modified successfully.

An error has occurred in the program

Command-01:
ldifde /i /f schema.ldf /v /k /c "DC=development,DC=com" "dc=test,dc=com"


Cause:
1. The export file had different mapping where export was taken from another box but in the import command it specify 'development'


Solution:
1. Need to make sure export environment is correctly mentioned in import which is the first parameter for '-c' switch

Note:
1. DC values are comma separated
2. No space in between DCs

Resources:
1. http://support.microsoft.com/kb/276382
2. http://support.microsoft.com/kb/237677

VirtualBox - Setup - RDP to windows VM

There are two options to setup Remote Desktop in Guest VM using Oracle VirtualBox. Once the RDP is setup, windows RDP client can be used to connect to the VM.

1. Option-01: Use VirtualBox RDP feature
2. Option-02: Use guest VM's RDP directly

Environment Information:
1. Operating System: Windows 2008 R2
2. Oracle VirtualBox: 4.1.16

Option-01: Use VirtualBox RDP featureSetup Correct Network Adapter:

1. Enable 'Remote Display Server' in VM Manager
1. Go to VirtualBox Manager
2. Select the VM where RDP is needed
3. Click 'Settings'
4. From left navigation panel, click 'Display'
5. From right pane, select 'Remote Display' tab
6. Check 'Enable Server'
7. Provide a port number in 'Server Port:' (remember this port which will be needed to setup Firewall later)
8. Click 'OK'
2. Enable RDP in host:
1. Click 'Start', point to 'Control Panel', click 'System and Security', click 'System' and then click 'Remote Settings' or
   Click 'Start', right-click 'My Computer', click 'Properties' and then click 'Remote Settings'
2. On 'Remote' tab, under 'Remote Desktop' section, select 'Allow connections from computers', second option. (Based on need, third option can be selected; Allow connections only from computers running Remote Desktop with network level authentication)
3. Create a Firewall rule in host:
1. Click 'Start' - 'Administrator Tools' - Windows Firewall with Advanced Security'
2. Click 'Inbound Rules' from left navigation panel
3. Click 'New Rule...'
4. Select 'Port' - hit 'Next'
5. Select 'TCP', specify port number which is given in Step-01.07 - hit 'Next'
6. Select 'Allow the connection' - hit 'Next'
7. Select 'Domain', 'Private', 'Public' based on need - hit 'Next'
8. Provide a unique name for the rule - hit 'Finish'
   
4. Install VirtualBox Guest Additions

Option-02: Use guest VM's RDP directly:

1. Enable Remote Desktop in guest:
1. Click 'Start', point to 'Control Panel', click 'System and Security', click 'System' and then click 'Remote Settings' or
   Click 'Start', right-click 'My Computer', click 'Properties' and then click 'Remote Settings'
2. On 'Remote' tab, under 'Remote Desktop' section, select 'Allow connections from computers', second option. (Based on need, third option can be selected; Allow connections only from computers running Remote Desktop with network level authentication)
2. Setup Correct Network Adapter:
1. Go to VirtualBox Manager
2. Select the VM where RDP is needed
3. Click 'Settings'
4. From left navigation panel, click 'Network'
5. Go to appropriate adapter tab. Most of the times, a VM has only one adapter, in that case 'Adapter 1' tab has to be selected
6. Select 'Bridged Adapter' under 'Attached to:' dropdown
7. Select the appropriate network from 'Name:'. If host machine has multiple network adapters (Wireless, NIC card etc.), all will be displayed here.
8. Need to make sure 'Cable Connected' checkbox is checked
9. Press 'OK'

Note:

1. Collect host OS's IP address and the port number against that RDP/firewall is setup to connect from RDP client when using Option-01.

2. Collect guest OS's IP address to connect from RDP client when using Option-02.

LDAP - Error - Unwilling To Perform

Problem:
System throws below error-01 when try to execute command-01.

Error-01:

Unwilling To Perform
The server side error is "The modification was not permitted for security reasons."
0 entries modified successfully.
An error has occurred in the program

Command-01:
ldifde /i /f schema.ldf /v /k /c "DC=development,DC=nyc" "dc=sso,dc=test,dc=com"

Cause:
1. This problem may occur when try to import data that cannot be written to Active Directory

Solution:
1. Import data from Active Directory using filter and specify only those fields which are needed
Command-02: include fields using '/l field1,field2'
ldifde /f schema.ldf /v /l field1,field2

Command-03: exclude fields using '/o field1,field2'
ldifde /i /f schema.ldf /v /o field1,field2

2. Import data from Active Directory using parameter "/n" which will exclude all the binary fields
Command-04: 
ldifde /i /f schema.ldf /v /n

Notes:

1. To get more detail information about the error, below command can be used
   Command-05: 
   ldifde /i /f schema.ldf /v /k /c "DC= development,DC=nyc"  "dc=sso,dc=test,dc=com"  /j c:\ldiflog\
   'c:\ldiflog' folder has to exist
   
2. Few fields are required in Active Directory
1. distinguishedName or dn
2. objectclass
3. sAMAccountName
3. Few fields are optionalchangetype, accountExpires, codePage, cn, countryCode, displayName, instanceType, logonHours, distinguishedName, objectCategory, name, userAccountControl, uSNChanged, uSNCreated, whenChanged, whenCreated
4. Few fields cannot be imported
   badPasswordTime, badPwdCount, lastLogoff, lastLogon, logonCount, memberOf, objectGUID, objectSid, primaryGroupID, pwdLastSet, sAMAccountType
5. 
   

Resources:
1. http://support.microsoft.com/kb/276382
2. http://support.microsoft.com/kb/237677

JQuery - Best Practices

Best practices:

1. Save selection in a variable if the selection needs to make multiple time.
   Explanation:  jQuery doesn't do caching of selections and for every selection lot of code runs behind the scene
2. Use 'if($("element").length)' instead of  'if($("element"))' to make sure selection has records
   Explanation:  Any selection using $() always returns an object and object always evaluates to TRUE, even if selection doesn't contain any elements.
   'if($("element"))' will return '[]' which is treated as empty object 

T-SQL Programming - Useful Tips - Dynamic Query

Code-1: Dynamic query checks column name exists in the table


DECLARE 
@tableName VARCHAR(100) = 'table_name',
@targetColumnName VARCHAR(100) = 'column_name',
@columnExists SMALLINT = 0,
@sql NVARCHAR(MAX)
;

SET @sql = 'SELECT @cnt=COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '''+@tableName+''' AND COLUMN_NAME = '''+@targetColumnName+'''';
PRINT @sql;
EXECUTE sp_executesql @sql, N'@cnt SMALLINT OUTPUT',@columnExists OUTPUT;

Points to consider:

1. All text parameters for sp_executesql have to be 'N' type (Note: string to the code page that corresponds to the default collation of the database or column) (See Code-1, Code-2)
2. Parameter (in/out) definition count has to match with parameters provided (see Code-2, four parameters declared for dynamic query, two are in and two are out parameters,)
3. If there are any output type parameters defined, than corresponding parameters have to mark as OUTPUT also (See Code-2)
4. Input or output parameters assignment can have same or different name (see Code-2, all variables used inside dynamic query and declared outside match with corresponding one)

Code-2: Dynamic query takes two input parameters, does two mathematics operations and returns results in two output parameters

DECLARE 

@inParam1 INT = '2',

@inParam1 INT = '4',

@outParam1 INT,

@outParam2 INT,

;

SET @sql = 'SELECT @inParam1, @inParam2';
SET @sql = @sql + 'SET @outParam1=@inParam1 + @inParam2';
SET @sql = @sql + 'SET @outParam1=@inParam1 * @inParam2';
PRINT @sql;

EXECUTE sp_executesql @sql, N'@inParam1 INT, @inParam2 INT,@outParam1 INT OUTPUT, @outParam2 INT OUTPUT', @inParam1, @inParam2, @outParam1 OUTPUT, @outParam2 OUTPUT;

Convert data type of a column

Background:
This post will cover few options to convert a column data type without loosing actual data. All the scripts will work fine based on the assumption that value in column can actually be converted and data will not be lost. For example, INT to VARCHAR conversion is relatively straight forward, no possible data loose. Whereas, VARCHAR to INT might fail if that column has non-numeric value or has value which is too big (INT column range is -2,147,483,648 to 2,147,483,647).


Option-1 is simpler, actual data type change statement is one line. Option-2 is much complicated approach where a dummy column is used. I have added Option-2 for two reasons, 1) that's the one I created first 2) in some scenarios Option-2 might help others to consider as base approach and build deployment/database structure modification script on top of that.


Option-1: using ALTER COLUMN



DECLARE 
@tableName VARCHAR(100) = 'wk_fact_ctr_transaction',
@targetColumnName VARCHAR(100) = 'account',
@targetColumnDataType VARCHAR(100) = 'INT',
@columnExists SMALLINT = 0,
@sql NVARCHAR(MAX)
;


-- Check column exists in the table
SET @sql = 'SELECT @cnt=COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '''+@tableName+''' AND COLUMN_NAME = '''+@targetColumnName+'''';
PRINT @sql;
EXECUTE sp_executesql @sql, N'@cnt SMALLINT OUTPUT',@columnExists OUTPUT;


IF @columnExists IS NOT NULL AND @columnExists > 0
BEGIN
PRINT 'Column exists in the table';
BEGIN TRY
SET @sql = 'ALTER TABLE '+@tableName+' ALTER COLUMN '+@targetColumnName+' '+@targetColumnDataType;
EXECUTE(@sql);
END TRY
BEGIN CATCH
DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;


SELECT 
@ErrorMessage = ERROR_MESSAGE() + ' - COLUMN CONVERSION NOT POSSIBLE.',
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();

RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
END CATCH;
END
ELSE
PRINT 'Column does not exist in the table';

Option-2: ALTER COLUMN by introducing a dummy column



DECLARE 
@tableName VARCHAR(100) = 'wk_fact_ctr_transaction',
@targetColumnName VARCHAR(100) = 'account',
@dummyColumnName VARCHAR(100) = 'account_temp',
@targetColumnDataType VARCHAR(100) = 'VARCHAR(100)',
@dummyColumnDataType VARCHAR(100) = 'INT',
@columnExists SMALLINT = 0,
@sql NVARCHAR(MAX)
;


SET @sql = 'SELECT @cnt=COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '''+@tableName+''' AND COLUMN_NAME = '''+@targetColumnName+'''';
PRINT @sql;
EXECUTE sp_executesql @sql, N'@cnt SMALLINT OUTPUT',@columnExists OUTPUT;


IF @columnExists IS NOT NULL AND @columnExists > 0
BEGIN
PRINT 'Column exists in the table';
BEGIN TRY
SET @sql = 'ALTER TABLE '+@tableName+' ADD '+@dummyColumnName+' '+@dummyColumnDataType;
EXECUTE(@sql);


SET @sql = 'UPDATE '+@tableName+' SET '+@dummyColumnName+'='+@targetColumnName;
EXECUTE(@sql);


SET @sql = 'ALTER TABLE '+@tableName+' DROP COLUMN '+@targetColumnName;
EXECUTE(@sql);


SET @sql = 'ALTER TABLE '+@tableName+' ADD '+@targetColumnName+' '+@targetColumnDataType;
EXECUTE(@sql);


SET @sql = 'UPDATE '+@tableName+' SET '+@targetColumnName+'='+@dummyColumnName;
EXECUTE(@sql);


SET @sql = 'ALTER TABLE '+@tableName+' DROP COLUMN '+@dummyColumnName;
EXECUTE(@sql);
END TRY
BEGIN CATCH
DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;


SELECT 
@ErrorMessage = ERROR_MESSAGE() + ' - COLUMN CONVERSION NOT POSSIBLE.',
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();

RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
END CATCH;
END
ELSE
PRINT 'Column does not exist in the table';

T-SQL Programming - Useful Tips - Text Search

SQL Server 2008 R2

TEXT SEARCH

Case Sensitive Search
1. CHARINDEX('ExpressionToFind', 'ExpressionToSearch' COLLATE Latin1_General_CI_AS)

    Here CHARINDEX function returns the position of the string found in  the search. Returns 0 if not found.

Case Insensitive Search
1. Using CHARINDEX

CHARINDEX('ExpressionToFind', 'ExpressionToSearch' COLLATE Latin1_General_CS_AS)

 - Here CHARINDEX function returns the position of the string found in  the search. Returns 0 if not found.

2. Using Like 
IF EXISTS(SELECT * FROM (SELECT 'This is a test' a) tab WHERE a like '%test')

- 'Like' does case insensitive search in T-SQL

How to retrieve last IDENTITY using JDBC

Background:

I was working on an existing module which was using JDBC for all the database operations. There was a scenario where front-end UI has to update a set of tables, meaning data will be inserted into parent table first, than in child tables using parent's ID to establish the referential integrity. Existing code was using database MAX function to fetch the ID which I felt not a good idea and might have concurrency issues when multiple users will be using the application. My investigation came up with few alternates which I have listed below.

Right now the application is pointing to the MS SQL Server database and all the solutions are for that database only. Plan is to do similar investigation in future to incorporate other databases like Oracle.

Solutions for MS SQL Server:

Solution-01: Using JDBC built-in functionality

Assumptions:

1. Table where the insertion is happening has a auto-generated key

Steps: (see code-01)

1. Use Statement.RETURN_GENERATED_KEYS during PreparedStatement creation
2. Execute insert statement
3. Get list of generated keys as ResultSet
4. Get the first value from ResultSet

Solution-02: Using MS SQL server OUTPUT clause feature in the query

Assumptions:

1. The query should use OUTPUT clause feature of SQL Server (I have another post, which explains how to use OUTPUT clause)

Steps: (see code-02)

1. Create a query with OUTPUT clause
2. Execute the query using ' executeQuery' method of Statement
3. Get list of generated keys as ResultSet
4. Get the first value from ResultSet

Resources:

1. How to use OUTPUT clause, this is my another post on this topic - http://peerprogrammer.blogspot.com/2012/04/how-to-retrieve-identity-of-table_18.html

Sample Codes:

--------------------

--- Code-01 - use JDBC feature to get IDENTITY

--------------------

public int getLastIdentity(String sql) {  

PreparedStatement pStmt;  

int lastIdentity = 0;  

try {  

pStmt = conn.prepareStatement( sql, Statement.RETURN_GENERATED_KEYS);  

   

pStmt.executeUpdate();  

ResultSet identities =  pStmt.getGeneratedKeys();  

  

identities.next();  

lastIdentity = identities.getInt(1);  

identities.close();  

pStmt.close();  

conn.close();  -- connection is created outside of this method

} catch (Exception e) { e.printStackTrace(); }  

return  lastIdentity;  

}

--------------------

--- Code-02 - use SQL Server 2005 feature OUTPUT clause

--------------------

Input SQL should look like below and assuming the table's auto increment column name is 'id':

INSERT INTO 'tablename' OUTPUT inserted.id VALUES('First Column', 'Second Column');

public int getLastIdentity(String sql) {  

PreparedStatement pStmt;  

int lastIdentity = 0;  

try {  

pStmt = conn.prepareStatement(sql);  

ResultSet identities =   pStmt.executeQuery();  

identities.next();  

lastIdentity = identities.getInt(1);  

identities.close();  

pStmt.close();  

conn.close();  -- connection is created outside of this method

} catch (Exception e) { e.printStackTrace(); }  

return  lastIdentity;  

}

How to retrieve IDENTITY of a table

Background:
I have a Stored Procedure which need to insert data into a set of tables by maintaining the referential integrity. Meaning parent table ID value (ID is a Identity column) need to be fetched and used in child tables during insertion. There is a trigger defined in the parent table which inserts a record in another table. Logic in the SP is like 1. inserts a record in parent table 2. immediate statement retrieves last identity using @@IDENTITY global variable. As the trigger inserts record in another table, the @@IDENTITY variable returns the identity from second table. See Query-03 which represents the same scenario.


This problem drove me to investigate in detail about the approaches and their pros and cons to retrieve IDENTITY value of a table. 


Factors need to consider: (mostly for @@IDENTITY, SCOPE_IDENTITY(), IDENT_CURRENT())
Different scopes:

1. The session scope - the connection which produced the IDENTITY value
2. The statement scope - where is the statement that produced the IDENTITY value
3. The table scope -the able produced the IDENTITY value

What is scope?
A scope is a module: a stored procedure, trigger, function, or batch. Therefore, two statements are in the same scope if they are in the same stored procedure, function, or batch.


What is @@IDENTITY?

1. @@IDENTITY is a system global variable
2. Returns the last IDENTITY value produced on a connection
3. Regardless of the table that produced the value
4. Regardless of the scope of the statement that produced the value
5. Returns IDENTITY of current session, ignores the scope

Note: From the attributes one thing is clear that developer has to be certain where the IDENTITY comes from and the scope of it unless same problem might occur which I explained above. (see Query-03 for sample code)


What are the alternatives?

1. Use  SCOPE_IDENTITY()
1. Attributes:
1. Returns the last IDENTITY value produced on a connection and by a statement in the same scope
1. Regardless of the table that produced the value
2. Returns IDENTITY of current session and current scope
2. Notes:
1. IDENTITY created in Trigger or User Defined Function for other table(s) won't affect actual table's IDENTITY
2. (see Query-03 for sample code)
2. Use  IDENT_CURRENT('tablename')
1. Attributes:
1. Returns last IDENTITY produced in a table
2. Regardless of the connection that created the value
3. regardless of the scope of the statement that produced the value
2. Notes:
1. IDENTITY created in Trigger or User Defined Function for other table(s) won't affect actual table's IDENTITY
2. If IDENTITY created in current scope is needed, this might not return correct value as some other connection might have already entered record in between
3. (see Query-03 for sample code)
3. Use OUTPUT clause:
1. Attributes:
1. Returns information from, or expressions based on, each row affected by an INSERT, UPDATE, DELETE (see Query-01)
2. Information can be retrieved in a tabular form only, cannot be retrieved in a variable (see Query-02) 
3. Columns returned from OUTPUT reflect the data as it is after the INSERT, UPDATE, or DELETE statement has completed but before triggers are executed
2. Notes:
1. The Output clause is a new to SQL Server 2005
2. An UPDATE, INSERT, or DELETE statement that has an OUTPUT clause will return rows to the client even if the statement encounters errors and is rolled back. The result should not be used if any error occurs when you run the statement
4. Use SEQUENCE feature in column (similar as Oracle Sequence)
1. Notes:
1. Available in SQL Server 2011 only
2. I couldn't come up with sample code as not used SQL Server 2011 yet
5. Use custom sequence (a table to store sequence value and a Stored Procedure to modify the value)
1. Notes:
1. Custom logic
2. Concurrency need to be handled manually

Resources:

1. How to use OUTPUT clause -  http://msdn.microsoft.com/en-us/library/ms177564.aspx
2. Problem in SCOPE_IDENTITY -  (1) https://connect.microsoft.com/SQLServer/feedback/details/328811/scope-identity-sometimes-returns-incorrect-value (2)  http://support.microsoft.com/kb/2019779

Sample Queries:


-------------
-- Query-01 - OUTPUT clause 
-------------
-- Data table
CREATE TABLE test(id INT IDENTITY(1,1), value VARCHAR(100));
-- table for output
DECLARE @outputTable TABLE (id INT);


INSERT INTO test(value) 
 OUTPUT inserted.id 
 INTO @outputTable
 VALUES('One');

SELECT * FROM test;
SELECT * FROM @outputTable;


-- Test OUTPUT with UPDATE
INSERT INTO test(value) VALUES('Two');
UPDATE test
 SET value = 'One Changed'
 OUTPUT deleted.id
 INTO @outputTable
 WHERE id=1
;
SELECT * FROM test;
SELECT * FROM @outputTable;


-- Test OUTPUT with DELETE
DELETE test
 OUTPUT deleted.id
 INTO @outputTable
 WHERE id=2;
SELECT * FROM test;
SELECT * FROM @outputTable;
GO


-------------
-- Query-02 - Output of OUTPUT clause in a variable
-- Result: it fails because OUTPUT returns data in tabular format
-------------
-- Data table
CREATE TABLE test(id INT IDENTITY(1,1), value VARCHAR(100));
DECLARE @outputVariable INT;


INSERT INTO test(value) 
 OUTPUT inserted.id 
 INTO @outputVariable
 VALUES('One');

SELECT * FROM test;
SELECT 'Variable= '+CONVERT(VARCHAR(100), @outputVariable);
GO


Error:
Msg 1087, Level 16, State 1, Line 6
Must declare the table variable "@outputVariable".


-------------
-- Query-03 - Usage of @@IDENTITY, SCOPE_IDENTITY(), IDENT_CURRENT()
-------------
-- Data tables
CREATE TABLE one(id INT IDENTITY(1,1), value VARCHAR(100));
CREATE TABLE two(id INT IDENTITY(1,1), value VARCHAR(100));
GO
-- Create trigger in table 'one'
CREATE TRIGGER TROne
 ON one
 AFTER INSERT
AS
DECLARE @value VARCHAR(100), @id INT;
SELECT @value=value, @id=id FROM inserted;

INSERT INTO two(value) VALUES('ID='+CONVERT(VARCHAR(100), @id) + ' is inserted in table one');
INSERT INTO two(value) VALUES('Value='+CONVERT(VARCHAR(100), @value) + ' is inserted in table one');
GO


INSERT INTO one(value) VALUES('Record one');
SELECT '@@IDENTITY= '+CONVERT(VARCHAR(100), @@IDENTITY);
SELECT '@@SCOPE_IDENTITY= '+CONVERT(VARCHAR(100), SCOPE_IDENTITY());
SELECT '@@IDENT_CURRENT for table one= '+CONVERT(VARCHAR(100), IDENT_CURRENT('one'));
SELECT '@@IDENT_CURRENT for table two= '+CONVERT(VARCHAR(100), IDENT_CURRENT('two'));
GO