Understanding Hierarchyid Data Type in SQL Server

Posted May 20, 2019 By AuthorVS1

This article addresses the meaning and usage of hierarchyid data type and the method used in SQL Server.

Hierarchyid can be defined as a system data type or a variable length. As the name suggests it is used for representing different positions in a hierarchy. However, column type of hierarchyid doesn’t automatically represent a hierarchy tree. It completely depends on the application, to assign and generate hierarchyid values in a way that the values are able to reflect their relationship with rows. Any data where one item can be considered as a parent of any other item forms a Hierarchical relationship.

Hierarchyid Data Type

Hierarchyid data type value represents a position in the hierarchy tree. Here are some of the basic properties of hierarchyid values:

Extremely Compact

Hierarchyid data type compacts the data into small bits which can significantly help in storing the data effectively in the system. On an average, the bits required for representing a tree node for X nodes depends on the fanout, the average subdivision or related values of a node. So, if an organization with a hierarchy of over 100,000 people with a fanout of 6 levels will only take about 38 bits.

Depth – First Order Comparison

If two hierarchyid are given X and Z, X < Z, this means that X will come before in the depth-first traversal of the hierarchy tree. All index of hierarchy data type is stored in depth-first order. And nodes which are placed in depth-first traversal close to each other are also stored near each other.  For example, the employees of a department will be stored adjacent to their management record.

Arbitrary Deletion and Insertion

GetDescendant method allows users to generate sibling in the right/left section or in between any node or two sibling nodes. The system also ensures to maintain the comparison property, even when a node is deleted or inserted in the hierarchy. Most deletions and insertions are preserved by the compactness property. However, if a user inserts between two existing nodes, it will produce hierarchyid values with a slightly un-compact representation.

The encoding which is used in hierarchyid type is restricted to 892 bytes. So, nodes with a higher range of representation which exceeds 892 bytes won’t be represented by hierarchyid type.

Data Type Conversion

Users can convert hierarchyid data type into other data types using the following methods:

  • The user can use ToString () method for converting the hierarchyid value to a logical representation by a nvarchar(4000) data type.
  • In order to covert hierarchyid into varbinary, users can use Read () and Write () data types.
  • Users need to convert Hierarchyid data type into strings before transmitting hierarchyid parameters via SOAP.

Hierarchyid type uses logic to encode information of a single node from the hierarchy tree by encoding its path through all channels starting from its root till its node. This path creates a logically represented sequence, where all nodes follow a standard path which starts from the root. The comparison between these children is conducted based on their integer sequences which are separated by dots and are represented in a dictionary order. Hierarchyid is an easy way to create queries and store data in hierarchical order.

If you are running a commercial application on SQL Server, you need to make sure that you thoroughly address data loss scenarios arising out of a database crash. Consider getting a state of the art sql repair tool.

Author Introduction:

Victor Simon is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including access recovery and sql recovery software products. For more information visit www.datanumen.com


This article addresses the installation error ‘A Constraint Violation Occurred’ and how users can solve it.

This is among the most common problems witnessed by most people while installing SQL Server in Windows. Usually, this error occurs when the setup SQL Server Cluster creates a failover cluster manager with a network name resource which automatically leads to a fail. Programmers often find themselves caught up with an error which slows down their work speed. This violation is caused due network error. So, ensure to login or use an account to install the set-up. Users cannot install SQL Server Cluster without logging in to an account.

A Constraint Violation Occurred

The cluster consists of a network name which contacts AD (active directory), DC (Domain controller) through the name Windows Cluster Network on computer account which is also known as Cluster Network Object (CNO). So, this error is caused because of the (windows user account) domain admin account is not being used for creating a SQL computer object in the AD.

Solution to tackle ‘A Constraint Violation Occurred’ installation Error

To solve this problem, a user needs to log in to his/her domain controller machine and create a new computer account. Then the user needs to grant full control of computer name to cluster name. Now all you have to do is retry the installation and it will initiate the setup.

Note: Users should ensure to close their account and create a new account if they are unable to properly install the SQL Server Cluster. Also, you should consider checking the stats of your computer to check with the software whether it’s compatible with your system or not.

Sometimes this error can also occur due to interruptions in the network, so you can consider checking your system’s network.

Steps to Prevent ‘A Constraint Violation Occurred’

Here is a simple step by step process which will help you to solve this issue, without spending extra bucks on a programmer.

Step 1: Start the Set up > Select Run > dsa.msc. This will automatically bring the Computers UI and Active Directory Users. Here the user can select the user that they want to provide access to the net installed software.

Step 2: Now select the Advanced Features which is located under the View menu.

Step 3: In case the name of SQL Virtual Server is already created, you can search or go to OU and consider creating a new VCO or computer object under it.

Step 4: Now bring your cursor to the new object that you have just created and click on its properties.

Step 5: Select Add to its security tab. Now select Object Types and while doing so ensure that you have selected Computers, and then click on OK.

Step 6: Now the user needs to type in the CNO name and click on Ok. Go to CNO and select Allow in its Permissions for providing full control permissions to the users.

Step 7: Right Click on VCO and disable it.

This process is also called pre-staging of VCO.

Hopefully, this will help you to save your time and install the server on your system. To deal with data errors after installation consider a specialized tool that can deal with SQL Server corruption.

Author Introduction:

Victor Simon is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including access recovery and sql recovery software products. For more information visit www.datanumen.com


This article addresses all the improvements that have been made in SSMS’s Import Wizard and how they can be used for efficiency.

SQL Server Management Studio aka SMSS is used for multiple purposes like managing, administrating, accessing, configuring and developing all components of any SQL application like Azure SQL Database, SQL Data Warehouse, and SQL Server.

Import Wizard Improvement

Recently, the 17.3 SMSS was released, and today we are going to talk about how its Import Wizard has improved. The company has made some significant changes to its Import Flat File Wizard. Apparently, this new Import Flat File Wizard promises to make importing file to SQL Server simpler and hassle-free.

SMSS 2017 Flat File Import Setting

Import Flat File Wizard has been designed to streamline the process of importing flat file in an intelligent framework while using specialized knowledge and requiring next to minimal amount of user intervention. This new option is much more intelligent, automotive, and spontaneous which helps the user in importing files without much effort or involvement.

Steps to use a Flat File Wizard in SMSS

High Level Steps – Specify Input File > Preview Data > Modify Schema > Summary > Results.

Step 1: The user needs to launch the wizard in SSMS, for which he/she needs to click on the database file then Tasks and then click on the Import Flat File Wizard.

This Import Flat File Page also consists of a brief description of the page.

Step 2: Click Next > Now the user needs to specify the location where he/she wants to import their file, while also specifying a unique table name for the file that is being imported.

Step 3: Once the users set up the location of the Flat File, it will automatically generate a Flat Filename. If the user chooses to preview his or her data, he/she can go to the next screen and preview their data. While using this option, the data is corrected automatically where quotes are removed. This prevents the user from wasting his/her time in making configuration changes to import it.

Step 4: Click next and go to Modify Columns. The wizard has been improved to become intelligent to identify and perform, and make predictive file splits. It also scans the data to predict data type for column values.

This screen also allows users to set up column properties like Data Type, Name, and Primary Key etc. Users can access the complete list of column types using its drop down settings. Users can also set up different data type according to their requirement.

Step 5: After selecting the column types, the user can look at the task settings on the Next Screen.

Step 6: Now select next to initiate the import.

Step 7: Congratulations! Your file has been imported. Make sure to check your newly created table with all the data types.

Errors with Flat File Wizard

New files should have a unique table name if a similar file already exists then the document will return with an error saying “Provided Table Name already exists”.

A user then needs to be assigned with permissions to create new tables. However do not confuse such errors with data errors which need a specialized tool that can repair corrupt SQL Server database to address the situation.

Author Introduction:

Victor Simon is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including access recovery and sql recovery software products. For more information visit www.datanumen.com