SQL Server 2012 Coolness

In my work porting a large Oracle-based product to SQL Server 2012, I will discover many cool tricks and tips. Benefit from my discoveries here.

[sql2012cool-blog]

Replacement for ORA_ROWSCN to find changes since last refresh

One of the capabilities of Oracle our application uses is querying the database for all changes to a table since the last time we looked.  Certainly, someone could implement a portable LAST_UPDATE_TIME column on the table and have the application update that column each time a row is touched.  In fact, that would probably be the best solution since you can index that column for a quick fetch.

Well, for whatever reason, our application instead uses the ORA_ROWSCN function to return the System Change Number of the last transaction to touch each row.  To make this work at a row level, one must create the table with the ROWDEPENDENCIES option.

In searching for a comparable feature on SQL Server 2012, I found the datatype ROWVERSION. A column of ROWVERSION datatype will get a new, sequentially increasing value when inserted and on any update.  The value will be unique to that row, so multiple rows affected by one INSERT or UPDATE statement will get unique values.  While somewhat different than ORA_ROWSCN, keeping the MAX(ROWVERSION_COLUMN) from one query and using it on the next refresh to find rows with ROWVERSION_COLUMN > that value provides an effective replacement.  Additionally, columns of ROWVERSION can be indexed, making the query fast.

To make the column's purpose obvious to my developers, I've chosen to name the column ORA_ROWSCN in any table where this capability is needed.  No, a SQL Server developer won't get it, but my team of Oracle folks will.

Here's an example:

CREATE TABLE foo (   foo_key int identity(1,1),   foo_value varchar(128),   ora_rowscn rowversion ) GO

INSERT INTO foo (foo_value) SELECT o.name FROM sys.objects o GO SELECT * FROM foo GO

Notice that each row has a unique value for ORA_ROWSCN.  Now let's update a few rows.

UPDATE foo SET foo_value = foo_value WHERE foo_key <= 3 GO SELECT * FROM foo ORDER BY foo_key GO

Notice that the first 3 rows have new values for ORA_ROWSCN.  Let's make it more interesting by exercising the use case described at the start.

DECLARE @max rowversion

SELECT @max = MAX(ora_rowscn) FROM foo

UPDATE foo SET foo_value = foo_value WHERE foo_key BETWEEN 10 and 20

SELECT * FROM foo WHERE ora_rowscn > @max ORDER BY foo_key GO

Man, this stuff is great!

[sql2012cool-blog]

Convert Base 64 value to a GUID

That code looks like this:

FUNCTION Base64ToGuidString(pBase64 in Varchar2) return Varchar2 as     vRaw  VARCHAR2(32);     vGuid VARCHAR2(36); BEGIN    vGuid := '';    if (pBase64 is not null and (length(pBase64) > 23)) then     vRaw  := UTL_ENCODE.base64_decode(utl_raw.cast_to_raw(pBase64));

      vGuid := substr(vRaw, 7, 2) || substr(vRaw, 5, 2) ||                substr(vRaw, 3, 2) || substr(vRaw, 1, 2) || '-' ||                substr(vRaw, 11, 2) || substr(vRaw, 9, 2) || '-' ||                substr(vRaw, 15, 2) || substr(vRaw, 13, 2) || '-' ||                substr(vRaw, 17, 4) || '-' ||                substr(vRaw, 21, length(vRaw) - 20);

    end if;

    return lower(vGuid);   END;

Well, that's nice enough if you have a UTL_ENCODE package to lean on.  In order to do this on SQL Server 2012, I decided to leverage the excellent XML extensions to T-SQL.  Using the XML datatype, one gains access to XML Schemas, wherein we find the ability to convert a base 64 string to a binary.  From there, its a simple CONVERT to uniqueidentifier, and then CONVERT to nvarchar.

The SQL Server version looks like this:

CREATE FUNCTION Base64ToGuidString (@pBase64 varchar(max)) RETURNS varchar(max) AS  BEGIN

  DECLARE @vGuid varchar(36);   SELECT @vGuid =            convert(nvarchar(36),            convert(uniqueidentifier,            CAST(N'' AS              XML).value('xs:base64Binary(sql:variable("@pBase64"))' ,'VARBINARY(MAX)')   ));

  RETURN lower(@vGuid);

END

Man, I love this stuff!

[sql2012cool-blog]

Return rows affected by INSERT/UPDATE/DELETE

For example:

UPDATE foo SET col3 = sysdatetime() WHERE col1 = 1;

SELECT * FROM foo;

SQL Server 2012 offers the OUTPUT clause which returns the rows affected by a DML operation.  Optionally, the INTO clause can direct those results into a table or table variable.

Here's a complete example showing the OUTPUT clause used on and INSERT and UPDATE clause.

create table foo (col1 int, col2 varchar(10), col3 datetime2(0));

insert into foo output inserted.col1, inserted.col2, inserted.col3 values (1,'chris',sysdatetime());

update foo set col3 = sysdatetime() output inserted.col1, inserted.col2, inserted.col3;

insert into foo  output inserted.col1, inserted.col2, inserted.col3 values (2,'franc',sysdatetime());

Looking at the execution plan, we can verify that only one statement is performed.

So, is this example earth-shattering or game changing?  No.  But every statement saved is a good thing for applications where milliseconds matter.