Many times we would like to store hierarchal data in database. For example you can see in the image we can have manager, supervisors who work below managers, workers who work under supervisor and so on. This kind of data can be stored by using “hierarchyid” datatype in SQL Server. It’s also called “Hid” data type. By using this data type we no more need those recursive logic to get the parent and also we do not need to get involved with complex self reference parent child kind of database design.
Also have a look at the below SQL Server interview question videos
Increase SQL Server performance using SQL Server profiler: -
SQL Server interview question video: - What are page split’s in SQL Server ?