4 ways to get identity IDs of inserted rows in SQL Server

In this post I’ll explain the 4 methods available to get the IDs of newly inserted rows when  the table has an identity Id column.

1) @@IDENTITY

This variable contains the last identity value generated by the current connection, is not limited to the scope of the code being executed. In case the current connection didn’t insert any row with an identity the property will have a NULL value.

INSERT INTO TableA (...) VALUES (...)
SET @LASTID = @@IDENTITY

This code will give you unexpected result if there is a trigger, running for inserts in TableA, that is inserting row in other tables with an identity. In this case the @@IDENTITY variable will give you the ID inserted by the trigger, not the one inserted in the current scope.

2) SCOPE_IDENTITY()

This function will return the value of the last identity inserted in the current executing batch.

The following code

INSERT INTO TableA (...) VALUES (...)
SET @LASTID = SCOPE_IDENTITY()

will return the last value inserted in TableA by our insert, even if other connections, or trigger fired by the current connection are inserting values with IDs.

This will be the best method to use in most of the cases.

3) IDENT_CURRENT(‘table’)

This function returns the last identity value inserted in the specified table, regardless of the scope and connection.

SET @LASTID = IDENT_CURRENT('dbo.TableA')

This function is available in SQL Server 2005 and newer versions.

4) OUTPUT

The output clause can be used to get IDs when inserting multiple rows.

DECLARE @NewIds TABLE(ID INT, ...)

INSERT INTO TableA (...)
OUTPUT Inserted.ID, ... INTO @NewIds
SELECT ...

This obviously will insert into @NewIds the Ids generated by the current statement. Other connection or code executing in different scopes will not affect the result of the operation.

Like IDENT_CURRENT, also this function is available in SQL Server 2005 and newer versions.

How to add your own code snippets in Visual Studio

Visual Studio (starting from version 2005) gives you the possibility to use the code snippets to insert frequently used code parts quickly using an alias. Try for example to digit “prop” and press TAB to create a full property. The type and the name of the property are parameters of the snippets, they have a default value that can be changed.

Visual studio has a long list of pre-made code snippets available, but obviously not every option that we might need is available. In this post I want to show you how to create your own code snippets.

One task that I perform very often is to check method arguments against null values, in every public method I repeat this piece of code for each parameter.

if( arg == null )
   throw new ArgumentNullException("arg");

Let’s create a code snippet, that I will call “checknullargs”, to automate the creation of this code.

First of all we have to create a new file called “checknullargs.snippet” (you don’t have to call the file in with the same name as the alias, but I recommend to do so, it will be quicker to identify which file contains a specific snippet if you will need to edit or delete them) in the folder %Documents%\Visual Studio %Version%\Code Snippets\%Language%\My Code Snippets, where %Documents% is your Documents folder, %Version% is the visual studio version that you have installed (eg. 2005, 2008, 2010) and %Language% is the programming language that you want to use to write the snippet (and the snippet will be available only for that language).

The code snippet file is a simple XML file with the following structure.

<?xml version="1.0" encoding="utf-8"?>
<CodeSnippets xmlns="http://schemas.microsoft.com/VisualStudio/2005/CodeSnippet">
	<CodeSnippet Format="1.0.0">
		<Header>
			<!-- Descriptive information here -->
		</Header>
		<Snippet>
			<!-- Code information here -->
		</Snippet>
	</CodeSnippet>
</CodeSnippets>

The file can be opened in visual studio to get full intellisense support.

Inside the Header element let’s add information about our snippet

<Title>checknullarg</Title>
<Shortcut>checknullarg</Shortcut>
<Description>Checks for null arguments</Description>
<Author>Riccardo Munisso</Author>
<SnippetTypes>
	<SnippetType>Expansion</SnippetType>
</SnippetTypes>

Title: is the name of the snippet
Shortcut: is the shortcut that we use inside of visual studio to activate the snippet
Description: description of your snippet
Author: your name!
SnippetTypes / SnippetType: is the type of snippet, can be Expansion or SurroundsWith. An Expansion snippet just add code to the editor, a SurroundsWith snippet surrounds the selected code with the content of the snippet.

Let’s fill now the Snippet section. The snippet section is divided in two parts, a Declarations element that allows us to declare the parameters (the replaceable parts) of our snippets, and the Code element containing the actual code.

<Declarations>
	<Literal>
		<ID>arg</ID>
		<ToolTip>Argument to be checked</ToolTip>
		<Default>arg</Default>
	</Literal>
</Declarations>
<Code Language="csharp"><![CDATA[if ($arg$ == null) 
throw new ArgumentNullException("$arg$");
$end$]]>
</Code>

The Declarations element contains a list of Literals, one for each parameters of the snippet. For each parameter we have to specify the ID (that needs to be unique), a tooltip and the default value.

The Code element contains the actual code to be inserted. You can insert your parameter using sourrounding them with dollar symbols (in this example $arg$).

Note that in the above sample there is a parameter called $end$ that we didn’t declare. This is a predefined value that represents the position of the cursor after the snippet is inserted in the editor. In this case the cursor will be placed after the snippet.

There is also another predefined parameter, called $selected$ that represents the code selected in Visual studio when adding the snippet. This is useful for the SurroundsWith type of snippets.

Once you have created your snippet you will need to restart visual studio for see the changes in the editor.

The full code for the sample snippet can be downloaded here

Happy coding!

.Net basics: String comparisons

Let’s start this blog with something simple, but often performed in the wrong way even by experienced .Net developers. I’m talking about string comparisons.

The basic comparison is performed using the == operator (or it’s negation !=), that executes a comparison that is culture insensitive and case sensitive. We should use this operator only when we expect strings to exactly match.
An example is while iterating through an XML document using a XmlReader, we can use the == operator to compare the current node name with the name that we expect. In this case we want the node name to exactly match the string, so using == is the right choice.

XmlTextReader reader = new XmlTextReader(input);
while( reader.Read() )
{
	if( reader.NodeType == XmlNodeType.Element && reader.LocalName == "server" )
	{
		// Do something
	}
}

Using the == operator is the same as using the String.Equals(string, string) static method or the instance string.Equals(string) method.

Now let’s have a look at another type of comparison: case insensitive comparison.
Too often I see code like

string a = "something";
string b = "Something else";

if( a.ToUpper() == b.ToUpper() )
{
    // Do something
}

This type of comparison has a flaw: with ToUpper you are creating an uppercase copy of the string. If you are comparing large strings or a lot of strings inside loops the performance penalty can be significant.

An alternative in this case is string.Compare(string, string, bool). This method returns 0 if the two strings are equals, a non zero value if the strings are different. Passing true to the boolean parameter allows us to specify that we want to perform a case insensitive comparison.
This method however will give you different comparison results than the == operator, the reason for this is that this overload of the Compare method uses the current culture to do the comparison, while the == operator uses the raw binary value of the string.

A more appropriate overload of the same method to perform the comparison in the same way as the == operator is the string.Compare(string, string, StringComparison) method.
Passing the value StringComparison.OrdinalIgnoreCase to the last parameter we will obtain the same comparison done by the == operator, but in a case insensitive way.

string a = "something";
string b = "Something else";

if( string.Compare(a, b, StringComparison.OrdinalIgnoreCase) == 0)
{
    // Do something
}

We can use the same overload of String.Compare to perform comparisons based on a Culture (also called locale). Passing as the last parameter StringComparison.CurrentCulture or StringComparison.CurrentCultureIgnoreCase we will do comparison using the current culture of the application, passing StringComparison.InvariantCulture or StringComparison.InvariantCultureIgnoreCase will do the comparison using the InvariantCulture (a culture based on the English language but country independent).

Please bear in mind that the methods using some Culture information are slower than the ones that do the comparison based on the raw value of the string.

An equivalent approach to use the string.Compare method is to use one of the default StringComparer (there is one matching each StringCompare value). StringComparer is a class implementing the IEqualityComparer generic interface for the type String. We can pass instances of this object to other objects to control how they perform string comparisons.
For example we can easily create a dictionary where the key is a string and we want the key comparison to be case insensitive.

var dictionary = new Dictionary<string, object>(StringComparer.OrdinalIgnoreCase);