March 25, 2019

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

By Emanuele Meazzo

2 Min read

4 comments

25March

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...

Read onRead later

March 22, 2019

How to Show Better Execution Plans in Azure Data Studio

By Emanuele Meazzo

1 Min read

10 comments

22March

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 Queryplan.show extension for Azure Data Studio, which...

Read onRead later

March 20, 2019

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

By Emanuele Meazzo

3 Min read

14 comments

20March

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...

Read onRead later

January 28, 2019

A SQL Server Permission Report in PowerBI

By Emanuele Meazzo

6 Min read

1 comment

28January

What it is and how to get it When working with SQL Instances that have lots of databases, sensitive data and equally lots of users/applications that accesses those, it can get pretty troublesome to keep track of every user, for every database, down to the single column/action.That’s why I developed this report, in orde to be able to quickly determine the security status of each object or...

Read onRead later

December 28, 2018

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

By Emanuele Meazzo

3 Min read

Add comment

28December

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...

Read onRead later

December 16, 2018

Row Level Security Performance Implications

By Emanuele Meazzo

6 Min read

Add comment

16December

A conversation had me wondering about performance implications in SQL Server RLS feature, specifically on how to mitigate the performance impact;TL; DR; ? Since security functions are actually Inline Table Valued Functions (ITVF), if you write them in a decent way the queries will run in the most efficent way possibile, avoiding RBAR processing. Let’s set the case scene; I’m starting...

Read onRead later

November 4, 2018

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

By Emanuele Meazzo

2 Min read

Add comment

04November

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...

Read onRead later

September 23, 2018

A PowerBI Report for SQL Server Agent Jobs

By Emanuele Meazzo

4 Min read

6 comments

23September

As my “community service” this month, I’ve worked on this neat (I hope!) PowerBI report (and companion stored procedure) to easily analyze all what you wanted to know about your Agent Jobs execution, but were too afraid to ask to the standard Reports in SSMS. This is part of the FirstRespondersKit toolkit, which I encourage you to check out. Installation: Install the...

Read onRead later

September 16, 2018

Job Performance Analysis PowerBi Report coming soon

By Emanuele Meazzo

1 Min read

Add comment

16September

It will be part of the FirstResponderKit tool, free for all

Read onRead later

August 10, 2018

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

By Emanuele Meazzo

3 Min read

Add comment

10August

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...

Read onRead later