Database

Undelete object from database

Posted on

Have you ever tried to delete an object from the database by mistake or other error? Then you should read on in this short post. I recently came across a good co-worker of mine who lost one of the views on the developer database. He called me for help. Fortunately the database was in FULL RECOVERY mode – so I could extract the object from the database log and send the script to him for his further work that day. I think I saved him a whole day of work… Here is the script I used: select convert(varchar(max),substring([RowLog Contents 0], […]

Database

Ready, SET, go – how does SQL server handle recursive CTE’s

Posted on

First of all, a quick recap on what a recursive query is. Recursive queries are useful when building hierarchies, traverse datasets and generate arbitrary rowsets etc. The recursive part (simply) means joining a rowset with itself an arbitrary number of times. A recursive query is defined by an anchor set (the base rowset of the recursion) and a recursive part (the operation that should be done over the previous rowset). This blogpost will cover some of the basics in recursive CTE’s and explain the approach done by the SQL Server engine.

Database

Use of hierarchyid in SQL Server

Posted on

I attended a TDWI conference in May 2016 in Chicago. Here I got a hint about the datatype hierarchyid in SQL Server which could optimize and eliminate the good old parent/child hierarchy. Until then I (and several other in the class) hadn’t heard about the hierarchyid datatype in SQL Server. So here’s an article covering some of the aspects of the datatype hierarchyid – including: Introduction How to use it How to optimize data in the table How to work with data in the hierarchy-structure Goodies Introduction The datatype hierarchyid was introduced in SQL Server 2008. It is a variable […]