There are various options on how to save hierarchical data in SQL Server. This article is an overview of them demonstrating their usage on a simple dataset of employees and their managers. I will extend this article when a new option will be available or if you will advise it…
There isn’t built-in support for working with hierarchies in the SQL Server except for the hierarchyid data type. This means that we should solve most of the hierarchical data processing tasks using the old-school portfolio of standard T-SQL language. One of these tasks is a simple conversion of hierarchical paths…
Objects like tables or views can be referenced using the dot (.) notation based on this pattern: [{server}].[{database}].[{schema}].[{object}], e.g. [MyServer1].[MyDatabase1].[dbo].[MyTable1]. One will expect that this sequence should go from right to left and parts can’t be skipped. But we can have much more fun with it in the reality. When…
Generate Array of Values in FOR JSON
23. 1. 2022
There is quite sustainable support for working with JSON format in SQL Server since the 2016 version. But it’s tricky time-to-time to do more complex things, especially building more complex JSON documents from tabular data format. One of these things is how to build an array of values, e.g. we…
Practical Tally Table to WHILE Comparison (Generating Calendar) The “Tally table” is a well-known concept not only in the SQL Server world but in all set-based (relational) database engines. I don’t want to repeat some basic or go much deep into it. You can do it on your own starting…
DBCC CHECKIDENT RESEED behaves differently when TRUNCATE or DELETE was used on the table
28. 12. 2021
Let’s assume we have some script where tables are filled with data and then DELETED/TRUNCATED again to rerun the script. Some of these tables are large and they don’t have a foreign key reference so we will decide to use TRUNCATE to remove the data. For tables referenced by foreign…
FORMATMESSAGE() maximum output length
26. 12. 2021
FORMATMESSAGE() is a quite useful competitor to RAISERROR() function, mainly in the case we won’t print the message directly but prepare it for further processing. One important limitation is there we must remember: The maximum output character length is limited to 2047 characters. When the final message is longer then…
It’s very easy to get TOM (Tabular Object Model) for datasets hosted in Power BI Service and extract various metadata from it (tables, measures, etc.). We will do it just now using a simple C# console application and download TOM for one sample dataset using Power BI User credentials. Similarly,…
End of the year 2020 Microsoft extended the Power BI Service API with new admin access-related endpoints and added the option to use Service Principal instead of a dedicated administrative account for connection. You can read more details on it in this official Power BI Blog post. In this step-by-step…
PowerBIApiConnector is a simple console application that will help you to test two things: a connection to Azure Active Directory and that you have properly configured an Azure application to access the Power BI Admin Read-Only API. Application is created in .NET.Core 5.0 and you can download it as: PowerBIApiConnector_Executable…