// Undelete Object From Database

SQL Server t-sql 1 min.

featured image Undelete Object From Database 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. [...]

// Use of Hierarchyid in SQL Server

SQL Server T-SQL 5 min.

featured image Use of Hierarchyid in SQL Server 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. [...]

// Update SCD Type 2 Dimension in One Single Transaction Using Only T SQL

SQL Server T-SQL 3 min.

featured image Update SCD Type 2 Dimension in One Single Transaction Using Only T SQL Recently I got a request inside my organization to make sure that a dimension would keep track of the changes due to requrementes from the business. This needed to be done in a single transaction in pure T-SQL code. So – what to do and how to do it. Here’s one way. The sourcetable looks like this: The request was to keep track of changes in the ManagerId according to CaseId. [...]

// Enlarge AdventureWorksDW2012

SQL Server t-sql 0 min.

featured image Enlarge AdventureWorksDW2012 I could use the AdventureWorks2012 database, but I needed the clean datawarehouse tables in order to have minimum data maintennance when testing the BIML scripts. I could not find it, and figures out it was faster to make my own. So heavily inspired by this post from Jonathan Kehayias (blog), I’ve made a script that can be used to enlarge the dbo.FactInternetSales table. The script creates a new table called dbo. [...]

// Split delimited string into rows

SQL Server t-sql 1 min.

featured image Split delimited string into rows On several occasions I’ve been assigned the task to split a delimited string into rows. I’ve done this in different ways, but never thought about the performance or stability of the different approaches for doing this. So here’s my 25 cents and findings. My first solution to this was to code a function to traverse through the string and insert a new value to a temp table for every delimiter found in the string: [...]

// Will not get stuck in date formatting again

SQL Server t-sql 1 min.

featured image Will not get stuck in date formatting again You’ve all been there, you’ve all banged your head against the monitor just because you could not remember that code for the specific date formatting you needed in SQL. I know I have. I SQL 2012 this is no longer such a headache to remember all those codes – JAY! Ex. from SQL 2008 R2 and older: SELECT CONVERT(VARCHAR(10), GETDATE(), 105) AS [DD-MM-YYYY] --Italian SELECT CONVERT(VARCHAR(10), GETDATE(), 104) AS [DD. [...]