It's just Emanuele showing you what you can do in the Microsoft Data Platform


Cast any string directly to XML with auto-escaping characters in SQL Server


Recently I had to look up the definition for a bunch of SQL objects and didn’t want to manually retrieve them manually in SSMS (with Create Scripts) or Visual Studio (by searching the object name in my TFS repository). Since lazyness and automation are the basis of a well done engineering work, I wanted to create a list, where I could basically click on the object that I needed and see the...

How to Show Better Execution Plans in Azure Data Studio


Azure Data Studio is starting to mature and some neat features are coming to the vscode-based (and now multidatabase and multiplatform) tool. We got query plans in the app since some time, but to be frank they’re not very pretty and have some issues with dark themes: Phantom Lines in plans with Dark Theme The new way Phil Scott built the extension for Azure Data Studio, which...

Load data directly in SQL Server from a xlsx file, without OLEDB or Excel, using Powershell


I don’t know you, but people from other BU are old fashioned guys that do everything by hand, and then send you an Excel file to “put this data in the database” or “find info related to this codes”. This is an hassle, especially if you don’t have the Microsoft.ACE.OLEDB provider installed in your machine (or for some reason SSMS doesn’t recognize it or...

Why identity values can jump by the 1000’s ? Is it normal?


It must have happened you at least once to find identity columns with values that from one row to the next one jump by 1000’s rows, of course without having anyone deleting the rows in between, how this occurs? Identity columns don’t actually recalculate every time the next number to be inserted, SQL Server caches a number of values in order to obtain faster inserts and polls from...

A quick function to remove (or keep only) string patterns from SQL Server Strings


Working with strings in SQL Server isn’t as easy or powerful as using any programming language, but, I’m sure that more than once you had to to some data manipulation directly in the database, this may help you out, and it doesn’t use Loops, CLR or XML The idea is to exploit set logic, as opposed to the classic program logic of when you’re usually playing with strings in...

How to populate a PowerBI Dataset from a REST API, using PowerBI’s REST API, without any code (via Microsoft Flow)


I was wondering if PowerBI could be used for reporting on Live data, not coming from my Database using DirectQuery (or the new super neat composite mode) with with a Streaming Dataset, which I never used before, so, I got to work. The Proof Of Concept is to take live data from Cryptocurrency value using REST API and pushing them into a PowerBI report.I’m using the API from...

SQL Server: How to Find a safe restore point (and who messed up) by reading from the Transaction Log


Panic: Someone has messed up and deleted a super important piece of information in a table! Who did it? Nobody knows. When did it happen? “The data was there this morning” Super useful info, thanks brah. So you activate your DBA Magic Powers© and try to deal with this stuff yourself: you need to identify which is the exact point where the data has been deleted (in order to minimize...

A script to automatically align columnstore indexes to enhance segment elimination (and hence performances)


Columstore indexes are a “new” neat data structure that I like, even if technically they’ve been around for years, only recently they’ve become usable by most customers. Let me recap a little bit what we’re talking about, so the point of this will be clearer: The table is divided in rowgroups of about one million rows max, then each column is stored by itself in a...

Tracking Dynamic SQL performance automatically with a wrapper on sp_executesql (Dynamic SQL Inception)


in some projects I have a lot of Dynamic SQL going on, some queries are fast, some queries are slow, it depends on how the dynamic statement is actually put together ¯\_(ツ)_/¯ Tracking the performance of such statements is usually difficult because you don’t really know “why it’s slow” and you have to interpolate data from the app and the plan cache (if the plan is still...

A clarification on the waitstat: SOS_SCHEDULER_YIELD


Are you one of the people that says “we have a CPU related issue” whenever it sees SOS_SCHEDULER_YIELD popping up? Let me explain why you’re wrong. In the books online, SOS_SCHEDULER_YIELD is defined simply as: Occurs when a task voluntarily yields the scheduler for other tasks to execute. During this wait the task is waiting for its quantum to be renewed. Well, since the SQL...

It's just Emanuele showing you what you can do in the Microsoft Data Platform

Emanuele Meazzo

My Social Profiles

If you're interested in following me around in the social world, here are some URLs for you