Dynamics NAV How To: BLOB Fields and GUIDs as Primary Clustered Key
Security — Sensitive Data Encapsulation
Early this year the Microsoft Dynamics NAV blog published an article entitled, “Design Pattern: Security – Sensitive Data Encapsulation.” Table 1261 Service Password is used as an example in the article. However, what really caught my eye (and ultimately led to this article) was the use of GUID as the clustered primary key.
BLOB Field
Before discussing the GUID as primary clustered key, please allow a brief foray into the recommended use of a separate table for BLOB fields. Ever since Jorg Stryk published his article “BLOB Fields with NAV & SQL” discussing NAV performance with regard to BLOB fields, I have used a separate Data Entry table to store XML data.
BLOB fields can store image or textual data up to two gigabytes in size. The clustered index contains a representation of all data in the table/record, so BLOB fields containing data can slow performance. It is better to separate BLOB fields into their own table especially when there is a need to reference images or web service transmission data (such as XML) in transactional tables.
When capturing XML for multiple transaction tables, it is very easy and convenient to reference the Data Entry primary key “Entry No.” in those tables. I even include code in either the OnDrillDown or OnLookup trigger of the Entry No. field in the transactional table to display the referenced data.
GUID as Primary Clustered Key
There is much debate on the use of GUID (or UNIQUEIDENTIFIER) as the primary key. Searching on this topic will yield many opinions. However, it is generally regarded that the use of GUID as primary clustered key will:
- Consume more space on disk. 16 bytes are used for GUID (4 times the space) as opposed to 4 bytes for Integer.
- Lead to index fragmentation; Microsoft states: “The reason for this is that to insert data into the middle of a clustered index (out of sequential order) causes SQL Server to make room for the data by rearranging the cluster…non-clustered indexes don’t reorder the data as rows are inserted to the table, so they don’t have the performance impact of a clustered index on inserts of non-sequential data.”
For more information, reinforcing this view, please read the articles:
The maximum value for a signed Integer is over 2 billion (2,147,483,647), allowing plenty of room for growth in the small- to mid-sized enterprise. For an opposing view prizing scalability over disk space, core processing speed and I/O, please read Thomas Kejser’s comment in this article:
Conclusion
NAV by default sets the primary key as Clustered. It is possible to change this property and set an alternate index (a key other than the primary key) as Clustered. Using this method, it is possible to set GUID as the primary key and Entry No. as a clustered secondary key. What is not recommended is to keep a GUID primary key as Clustered. For more information on BLOB fields or other NAV technical blogs, please subscribe to our Developer Blog today.
For more step-by-step instructions on how to perform specific tasks in Microsoft Dynamics NAV, see our collection of How-To blogs.