SQL Server 2012 Coolness

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.

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

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.