Difference between IDENT_CURRENT,SCOPE_IDENTITY and @@IDENTITY

This is an important distinction and thus I am pointing to this post for reference.

SQL 7 only allowed @@identity, however now we have other properties we can access to get the most recent identity. This article by Kamal on DotNetSpider covers those distinctions. It is also covered by Microsoft HERE.

How to Compare Two Byte Arrays

I answered a post here with a refinement regarding comparing two byte arrays.

I am reposting here…

This post was meant to go at the end of another post named “How to Compare Two Byte Arrays” which was answered by Marcus Andrén, but it was closed. I used his code from that post to create this, and added two items discussed in the previous post:

1. different length arrays
2. buffer must be divisible by 4.

I simply check the remaining bytes using the two byte pointers.

public unsafe static bool CompareByteArrays(byte[] b1, byte[] b2)
{
if (b1.Length != b2.Length)
return false;
fixed (byte* bp1 = b1)
{
fixed (byte* bp2 = b2)
{
int* ip1 = (int*) bp1;
int* ip2 = (int*) bp2;
for (int i = 0; i < b1.Length/4; i++)
{
if (ip2[i] != ip1[i])
return false;
}
if ((b1.Length % 4) != 0)
{
for (int i = b1.Length - (b1.Length % 4); i < b1.Length; i++)
{
if (bp2[i] != bp1[i])
return false;
}
}

return true;
}
}
}

Peace,

Robb

Writing BLOBs to a table in Sql Server

I have been storing large binary objects related to a database for a long time, but always ended up using a file system and storing the reference to the file (i.e. a URI, or pathname). Recently I had to actually store the object itself, and did not know where to start looking.

Since I am dealing with multi-threading and insertion of .5 gig files, I needed something that would allow me to buffer the write and not have to put the whole thing into an array before writing it. This is the solution. I know that Sql Server 2008 has a file system solution for BLOB storage and it integrates with file IO for updates, but like it seems to be so often in this career, it is too late for me to implement a new method of storage of my BLOBs. Maybe next time.

I used all sorts of search terms: binary, buffering, sql server, blob, update, insert, etc, and came up with lots of pages, but no joy until this page. It turns out that the search terms I needed were: UPDATETEXT, SCOPE_IDENTITY, and TEXTPTR. I spent a long time looking for this article and finally found and article titled:
Conserving Resources When Writing BLOB Values to SQL Server

NOTE: This was originally where this post stopped, but I have found that the command that this article uses is not going to be supported in future versions of SS, and instead we should be using UPDATE.WRITE() (available in SS2005 and beyond). The code sample above should still be helpful in integrating this new command.

Here is a blog post about UPDATE .WRITE…

Reading BLOBs in Sql Server

This post was one of the best I found on reading large binary objects from a database.
I was a bit surprised / disappointed to find that it was still writing the entire object at once in its example for writing a BLOB. So for that there is another post…
Read / Write BLOBs from / to SQL Server using C# .NET DataReader