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…
The OFFSET… FETCH NEXT extension to SELECT statement was introduced first in SQL Server 2012. It should basically support simple pagination of query results from the application. For large datasets, it’s really important to understand how it is internally implemented, and based on these findings, I won’t recommend using it…
The WRITE (expression,@Offset,@Length) extension of the UPDATE statement can be used to perform updates that insert or append new data in minimally logged mode if the database recovery model is set to bulk-logged or simple. Minimal logging is not used when existing values are updated. This all is valid for…