1

I am using SQL Server Standard Edition 2012.

When doing Audit using Trigger, the fields of the audited row(inserted or deleted) are being inserted into the table in XML Format. However, as observed, the space occupied is 10 times the original row (checked using sp_spaceused command, suspected due to the tags).

I am wondering, is it possible to compress XML inside the trigger (means using SQL)?

EDIT: Here's an example of the XML (there are around 50 columns in total). As observed, this really occupies A LOT of spaces.

<t>
  <MyKey>1</MyKey>
  <ClientKey>10</ClientKey>
  <DocNum>doc number</DocNum>
  <Source></Source>
  <DocType>doc</DocType>
  <GLDate>1900-01-01T00:00:00</GLDate>
  <GLDesc></GLDesc>
  <FY>2119</FY>
  <Period>4</Period>
  <CurrKey>-1</CurrKey>
  <InvoiceDate>1900-01-01T00:00:00</InvoiceDate>
  <InvNum></InvNum>
  <TaxInvNum></TaxInvNum>
  <CreatedBy>-1</CreatedBy>
  <CreatedDate>1900-01-01T00:00:00</CreatedDate>
  <UpdatedBy>23</UpdatedBy>
  <UpdatedDate>1900-01-01T00:00:00</UpdatedDate>
  <VoidBy>-1</VoidBy>
  <VoidDate>1900-01-01T00:00:00</VoidDate>
  <RejectBy>-1</RejectBy>
  <RejectDate>1900-01-01T00:00:00</RejectDate>
  ...
  ...
  ...
</t>
6
  • First question, why use XML for the audit record ? You can probably add logic to the trigger to remove white space etc but that will probably cost to much.
    – Spörri
    Oct 20 '16 at 9:32
  • XML seems to be a bit more 'universal' than creating shadow tables. Also, i am able to compare the inserted and the deleted with XML in SQL in order to store the 'difference only' during an UPDATE trigger. However, if it is an INSERT or DELETE, that seems clumsy if using XML. I think the culprit is the tags instead of whitespace. Eg: <mylongcolumnname>abc</mylongcolumnname>.
    – zeroflaw
    Oct 20 '16 at 9:44
  • What datatype are you using for the XML? The XML datatype is already internally optimized to only store element and attribute names once, and to remove whitespace. If not using the XML datatype, that is the problem. Oct 20 '16 at 10:28
  • Attribute-based XML (eg <row mylongcolumnname="abc" />) would conceivably be more compact, but XML is not famous for its brevity.
    – wBob
    Oct 20 '16 at 14:46
  • @srutzky. Yup, it is XML(.) type. I am afraid the tags, despite being optimized, still occupy a lot of spaces...
    – zeroflaw
    Oct 21 '16 at 1:32
1

While XML is certainly more bloated than native datatypes, it is not as bad as most people would assume, or even incorrectly calculate, as long as you are using the XML datatype. The XML datatype (in SQL Server) has internal optimization whereby it stores a dictionary of element and attribute names and a tree structure for the document. Meaning, element names are not doubled, and a name repeated 100 times is listed only once. Also, all structural whitespace (i.e. that's not part of a value) is removed.

Still, even with this optimization, you still run into two issues with XML:

  • column names are present in each document (i.e. each row of the audit table has to repeat the column names, whereas those column names only exist once in the meta-data that defines the Table that is being audited).

  • all data is serialized to strings (well, unless you're using "typed" XML, which means you have an associated XML Schema / XSD, but how often does that happen?). Sometimes this can actually help, such as when you have INT values that are positive and a single digit: 0 - 9. Or it can be equivalent when the values are -9 up through -1 and 10 up through 99, since those two ranges represent the same 4 bytes as the INT. If you are wondering why my maths seem so far off, keep in mind that XML data is UTF-16 (Little Endian), meaning: 2 bytes (well, for most of the characters you would regularly deal with, but 4 bytes per character for Supplementary Characters). So the -9 is 2 characters which is 4 bytes, same as 99. But then 3 characters is 6 bytes and it just goes up from there.

    The date values are DATETIMEOFFSET which is 8 - 10 bytes. The values shown in the XML have 0 fractional seconds which would be 8 bytes. The values in the XML are in the form of 1900-01-01T00:00:00. That is 19 characters, which is really 38 bytes.

As far as compression goes, you have two choices:

  1. If you are using SQL Server 2016 or newer, you could compress the XML values using the built-in COMPRESS function, and then DECOMPRESS. These two functions are the same as GZip and GUnzip, respectively.

  2. If you are using SQL Server 2005, 2008, 2008 R2, 2012, or 2014, you could get the same GZip algorithm via SQLCLR. This can be coded somewhat easily, or you can just grab the SQL# SQLCLR library (which I wrote, but these two functions are in the Free version), which contains the Util_GZip and Util_GUnzip functions.

In either case, you can save quite a bit of space by compressing the values. However, you also lose the ability to use define any XML Indexes on this data, and you can't query it directly using XML functions such as .value or .nodes. Querying the data can still be done, but you would first need to use either DECOMPRESS or Util_GUnzip (or similar SQLCLR scalar UDF) on the value to turn it back into usable XML.


Regarding attribute-based XML vs. element-based XML: in most cases you are better off with attribute-based as it does take up less space than element-based, and often performs a little better. However, the size difference between the two is much more obvious when the XML is stored as plain text, whether in VARCHAR / NVARCHAR or a text file encoded as ASCII, UTF-8, or UTF-16 / Unicode. But when stored in SQL Server in an XML datatype (variable or column), the internal optimization reduces the required space, especially if there are element and/or attribute names that are repeated quite often.

For example, take all of the rows in master.sys.all_columns.

DECLARE @ElementBased XML;
SET @ElementBased = (
                     SELECT * FROM master.sys.all_columns FOR XML PATH('Row')
                    );

DECLARE @AttributeBased XML;
SET @AttributeBased = (
                       SELECT * FROM master.sys.all_columns FOR XML RAW('Row')
                      );

SELECT @ElementBased AS [ElementBasedXML],
       @AttributeBased AS [AttributeBasedXML],

       DATALENGTH(@ElementBased) AS [ElementBasedBytes],
       DATALENGTH(@AttributeBased) AS [AttributeBasedBytes],
       ((DATALENGTH(@AttributeBased) * 1.0) / DATALENGTH(@ElementBased)) * 100
               AS [XmlAttributeSizeRelativeToElementSize],

       DATALENGTH(CONVERT(NVARCHAR(MAX), @ElementBased)) AS [ElementBasedNVarCharBytes],
       DATALENGTH(CONVERT(NVARCHAR(MAX), @AttributeBased)) AS [AttributeBasedNVarCharBytes],
       ((DATALENGTH(CONVERT(NVARCHAR(MAX), @AttributeBased)) * 1.0) /
         DATALENGTH(CONVERT(NVARCHAR(MAX), @ElementBased))) * 100
               AS [NVarCharAttributeSizeRelativeToElementSize]

       -- The following 2 fields require either SQL# ( https://SQLsharp.com/ )
       -- be installed, or using SQL Server 2016 (or newer), in which case you
       -- would use the COMPRESS function.
       ,DATALENGTH(SQL#.Util_GZip(CONVERT(VARBINARY(MAX), @ElementBased)))
               AS [ElementBasedGZippedBytes],
       DATALENGTH(SQL#.Util_GZip(CONVERT(VARBINARY(MAX), @AttributeBased)))
               AS [AttributeBasedGZippedBytes]

Returns the following (minus the two fields with the actual XML values):

ElementBasedBytes                           1717896
AttributeBasedBytes                         1544661
XmlAttributeSizeRelativeToElementSize            89.92  (rounded-up)

ElementBasedNVarCharBytes                  11778162
AttributeBasedNVarCharBytes                 6923728
NVarCharAttributeSizeRelativeToElementSize       58.79  (rounded-up)

ElementBasedGZippedBytes                     193629
AttributeBasedGZippedBytes                   146881
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.