GUID in SQL Server

There are many benefits of using GUIDs as the primary key. They are unique across system borders which remove problems with duplicated identities that might occur when working with data from different systems. For example, if the identity of an entity is an integer, two different systems that create the entities will probably have several shared numbers as unique identifiers. This becomes a problem when you, for example, want to merge or replicate data between the systems. The unique constraint will be violated and you have to find a way to work around the problem. During these circumstances, GUIDs are very nice to have because they are unique across system borders.

However, there is a number of drawback also. A GUID requires more resources. It is four times larger than an integer. Which affects the amount of space required to work with GUIDs and also put extra load on I/O and network operations. GUIDs are generated randomly and not sorted. When you generate the next GUID, you don’t know if it is greater or less than previously GUID. Since the GUIDs aren’t generated in an ordered sequence, the index for GUIDs will be fragmented with a high performance penalty. This is not a problem for small databases but a very big problem for larger databases with a lot of inserts.

For system that handles large amount of data, I recommend to avoid GUID and use a sequential id. If it’s really required to have a global unique identity, look at the COMB. COMB is a algorithm to create sequential GUIDs by Jimmy Nilsson.

Transform XML in SQL Server 2005

In my current project one of the systems is used to manage files. The system is able to handle several different file types. Each file type has its own set of meta data. The meta data describes particular characteristics of each file and is stored as XML in the database. Example of meta data is File Type, Tool, Version and Release Information.

The customer wants to generate a description about the file from the meta data. Each file type has it own set of rules on how to generate the description. For example, the description for files of type Misc shall include File Type, Tool and Version. It shall be easy to change the content of a file description and not require a new release of the system.

An appealing solution is to use XSLT to transform the XML based meta data to a description. By storing the XSLT for each file type in the database, it would be easy to change file description during runtime. Since most information aggregation and querying happens in the database, it would be beneficially if we were able to generate the description in the database and use it different views. So how do we transform XML with XSLT in SQL Server 2005?

To solve this problem, I had to create utilities functions in C#, register the assembly in SQL server and to create a function that map one of the utility functions to SQL.

I created two utility functions in C#. One general purpose function used to transform XML with XSLT and one function that specifically is used to generate a file description.

The transform function takes a XML document and a XSLT style sheet and returns the resulting XML data from applying the style sheet on the document.

public static SqlXml Transform(SqlXml xmlData, SqlXml xsltData)

{

//Initialize

MemoryStream memoryXml = new System.IO.MemoryStream();

XslCompiledTransform xslt = new XslCompiledTransform();

// Load XSL transformation

xslt.Load(xsltData.CreateReader());

//Transform

System.IO.MemoryStream ms = new System.IO.MemoryStream();

xslt.Transform(xmlData.CreateReader(), null, ms);

ms.Seek(0, System.IO.SeekOrigin.Begin);

//Return the transformed value

SqlXml retSqlXml = new SqlXml(ms);

string tmp = retSqlXml.Value;

return (retSqlXml);

}

Since I wanted a string, I also created a specific function returning the description from a XML document. This function assumes that the file description is stored in tag called Description. It requires the XML document and XSLT style sheet, and returns the generated file description.

public static string GenerateDescription(string xmlData, string xsltData)

{

string description = “”;

//If missing input, return an empty string

if(string.IsNullOrEmpty(xmlData) | string.IsNullOrEmpty(xsltData))

{

return “”;

}

try

{

//Initialize

StringReader stringXmlReader = new StringReader(xmlData);

XmlReader xmlReader = new XmlTextReader(stringXmlReader);

SqlXml sqlXmlData = new SqlXml(xmlReader);

StringReader stringXsltReader = new StringReader(xsltData);

XmlReader xsltReader = new XmlTextReader(stringXsltReader);

SqlXml sqlXsltData = new SqlXml(xsltReader);

//Transform

SqlXml result = Transform(sqlXmlData, sqlXsltData);

//Get description

XmlDocument xmlDocument = new XmlDocument();

xmlDocument.LoadXml(result.Value);

description =xmlDocument.SelectSingleNode(“Description”).InnerText;

}

catch (Exception e)

{

//TODO: Determine error handling

description = “Error when generating description!”;

}

return description;

}

The assembly can be registered from SQL Server Management Studio Express or by a script. I used the following script to register my assembly.

CREATE ASSEMBLY [Company.CSSS.SqlServer]

AUTHORIZATION [dbo]

FROM ‘C:\Program Files\Company IT\Central System Supporting System\Company.CSSS.SqlServer.dll’

WITH PERMISSION_SET = SAFE

GO

Thereafter you need to enable the CLR. This can also be made from a script.

EXEC sp_CONFIGURE ‘show advanced options’ , ‘1’;

GO

RECONFIGURE;

GO

EXEC sp_CONFIGURE ‘clr enabled’ , ‘1’

GO

RECONFIGURE;

GO

The script that creates the function that maps the utility function to SQL looks like:

CREATE FUNCTION [dbo].[GetFileDescription](@xmlData [nvarchar](4000), @xsltData [nvarchar](4000))

RETURNS [nvarchar](4000) WITH EXECUTE AS CALLER

AS

EXTERNAL NAME [Company.CSSS.SqlServer].[Company.CSSS.SqlServer.XsltTransformer].[GenerateDescription]

GO