Here is a quick guide to get the new value of NEWSEQUENTIALID() when inserting a row into a table in ms sql. If you have attempted to use NEWSEQUENTIALID() before you will know that it is only possible to use as a generated value in the table when the row is being inserted. This is a guide to get this value from c# when inserting the row. For this method we are going to be using the OUTPUT clause in the INSERT statement.

We will start with a really basic table in ms sql. Runt he following to create
it.

CREATE TABLE ATable (
      ATableID uniqueidentifier DEFAULT NEWSEQUENTIALID(),
      Data varchar(64)
)
GO

To insert a row we need some simple sql. Except in this case while we are inserting the row we can also output the value that is created during the insert from ms sql. Some sample sql todo this is below.

INSERT INTO ATable (Data) OUTPUT inserted.ATableID
       VALUES('Test Data')

So if you run that you will be returned some results. For my example I got 'DCAEFDBD-793F-E011-822D-003067017B97' back in the results pane.

Todo this in c# it will work much the same. Though to get the value back you use the ExecuteScalar command on the SqlCommand. An example is below.

class Program
{   
    static void Main(string[] args)
    {   
        DBConn Conn = new DBConn("Data Source=SQL;Initial Catalog=Testing;Integrated Security=True;Connect Timeout=30;Timeout=120");
        SqlCommand sql = new SqlCommand(@"
            INSERT INTO ATable (Data) OUTPUT INSERTED.ATableID
                VALUES(@Value)
        ");

        for (int i = 0; i < 10; i++)
        {   
            sql.Parameters.Clear();
            sql.Parameters.AddWithValue("@value", i.ToString());
            string tmp = Conn.ExecuteScalar(sql).ToString();
            Console.WriteLine(tmp);
        }

        Console.ReadLine();
    }
}

This nice thing about this when being used is that it will help prevent index fragmentation and page splits occurring on the index while is using the unique identifier. So the sample output of the Table will be as follows.



Did You find this page useful?

Yes No



Last Modified: 12 December 2016

Releated Posts


2012-08-07 - MSSQL - Finding the database restore history
2011-10-27 - CSharp - Number Of Cores and Processors
2011-10-04 - CSharp - Thread Queue Example
2011-09-29 - CSharp - Background Thread / Task
2011-09-24 - MSSQL - Checking Uptime
2011-09-17 - CSharp - Lookup Hostname
2011-08-29 - C Sharp - StopWatch, The high resolution timer
2011-08-29 - CSharp - IsGuid
2011-08-26 - CSharp - Convert Between Meters and Feet
2011-08-23 - CSharp - ThreadPool and Windows Forms
2011-08-11 - MSSQL - Saving memory by using stored procedures
2011-08-10 - C Sharp - MD5Sum / SHASum
2011-08-09 - MSSQL - Last Backup time and size
2011-07-22 - CSharp - Fibonacci sum
2011-07-13 - CSharp - Palindrome
2011-07-11 - CSharp - The string Reverse
2011-07-10 - CSharp - Interviews FizzBuzz
2011-06-27 - CSharp - HowTo Parse a URL
2011-06-24 - MSSQL - Extracting part of an email address
2011-06-22 - CSharp - Extending any class
2011-06-15 - MSSQL - Bulk Deleting rows with backup
2011-06-13 - ASPNET - Login by username or email
2011-06-07 - MSSQL - DBCC Check All Databases
2011-06-03 - MSSQL - Who locks what
2011-05-26 - MSSQL - RandomString Function
2011-05-22 - MSSQL - Removing the aspnet membership database
2011-05-19 - MSSQL - ShrinkLog
2011-05-16 - MSSQL - Enum
2011-05-12 - CSharp - ASPNET Logoff
2011-05-05 - MSSQL - Log Sizes
2011-03-07 - C Sharp - Windows Logoff
2011-03-02 - ASPNET - Dynamic Controls
2011-03-01 - C Sharp - Resize an image by size of height
2011-03-01 - MSSQL - Kill connections by host
2011-02-23 - C Sharp / MSSQL Get inserted value of NEWSEQUENTIALID()
2011-02-22 - MSSQL - Kill connections by username
2011-02-20 - MSSQL - All temp tables are global
2011-02-20 - MSSQL - Unsigned int
2011-02-17 - MSSQL - Convert IP To big int
2011-02-10 - CSharp - Shutdown or reboot windows
2011-02-10 - CSharp - Wake on Lan (WOL) Packet
2011-02-06 - C Sharp - Gridview Bound Yes No
2011-02-05 - MSSQL - Levenshtein
2011-02-02 - MSSQL - Kill connections to a database
2011-02-01 - MSSQL - Convert unix timestamp to date time
2011-01-30 - MSSQL - RegEx Support
2011-01-30 - MSSQL - Adding SHASum support
2011-01-30 - MSSQL - Enabling CLR Functions
2011-01-30 - MSSQL - Adding MD5 checksum support
2011-01-29 - MSSQL - TRIM Function