👋 Dirceu Resende

Data Architect, Data Analyst and DBA

• 📃 I’m currently blogging at Dirceu Resende
• 🌱 I publish Power BI videos at Planilheiros
• 🎥 I publish SQL Server videos at PowerTuning
• 📄 I write SQL Server articles at iMasters
• 📖 I write SQL Server articles at códigosimples.net
• 💼 I’m currently learning Data Storytelling, Analysis Services Internals and SQL Server Internals
• 💬 Ask me about @sql, @sqlserver, @powerbi, @azuredatafactory, @ssis and @analysisservices
• 😄 Pronouns: he/him
• 🙉 Pronounce: deer-c-o
• ⚡ Fun fact: just a nerd and food lover guy
• 📃 I really enjoy studying and taking certification exams View my certifications

How to reach me:

Follow me on social media:

😄 About me

I'm currently working as Senior Data Architect at Power Tuning and I spend my free time with God, with my family, watching series, playing videogame and of course, studying and posting on my blog about Data Platform.

• Name: Dirceu Resende
• Age: 36
• Living in: Vitória, Brazil
• Stack: Azure SQL Database, Azure Analysis Services, Azure Data Factory, Power BI
• University: Faculdade Metodista Granbery - Systems Information Bacharelor Degree as Best Student (Check my title)
• Certifications: Microsoft Certified Solutions Expert (Check full list)
• Titles: Microsoft Most Valuable Professional - Data Platform (View my MVP profile)

(back to top)

💼 My Career

I have Bachelor's degree in Information Systems from Granbery Methodist School as a prominent student, recognized by the Brazilian Society of Computing as one of the best IT Students in Brazil (Check my recognization here).

I'm a database and BI specialist, working exclusively with SQL Server, Analysis Services, Integration Services and Reporting Services since 2013 and exclusively with Azure since 2018.

I worked as a DBA for a global technology company, providing DBA Outsourcing services for one of the world's largest mining companies. I also worked as a BI and DBA analyst at Espírito Santo's largest finance company, as a BI analyst at a multinational company in the Energy sector, as well as a Senior Consultant at PowerTuning – Data Consultancy.

I currently live in Brazil and work at Power Tuning as Senior Data Architect.

(back to top)

🤓 Tech Community Leader

I am very active in the Data Platform technical community and have already spoken at the biggest data events on Microsoft technologies in Brazil, such as SQL Saturday, MVPConf, Power BI Roadshow and Power Platform World Tour.

I publish videos on the Planilheiros, the largest YouTube channel related to Power BI content in Latin America and have written over 400 technical articles on my blog, the most accessed database blog in Brazil.

I also published articles on some major IT portals such as iMasters and codigosimples.net

As one of the leaders of a Data Community on my City (Vitória), called SQL Server ES we organized over 15 events in person and I am proud to be part of SQL Saturday #900 - Vitória a big data event that had 30 speakers and over 300 professionals and students in person with us. It was magic, a dream that came true!

Because my contributions and leadership in the Data Community, I have been recognized as a Microsoft Most Valuable Professional (MVP) since 2018 (Check my profile here) and I have been one of the most active Data Platform MVP's in the World.

(back to top)

💼 My Trainings (Learn with me)

• Administrando Banco de Dados no Azure
• Segurança no SQL Server

(back to top)

⚡ My best content (Contents that make me proud)

🌍 Portals

• MS SQLServer - Data Platform Portal
• SQL Server ES - Espirito Santo Data Community

🎬 YouTube

• Power BI & Analysis Services
• Power BI Report Server

(back to top)

⚡ Blog Posts - Performance Tuning

• SQL Server - Introduction to Performance Tuning Study
• Understanding Index Functioning in SQL Server
• SQL Server - When you should use ORDER BY in the query and when you should not use it at all!
• SQL Server - Using Calculated Columns (or Computed Columns) for Performance Tuning
• SQL Server - Performance Tuning Tips: What is the Difference Between Seek Predicate and Predicate?
• SQL Server - Performance Tuning Tips: Implicit Conversion? NEVER!
• SQL Server - How to Identify a Slow or “Heavy” Query in Your Database
• SQL Server - Performance Comparison between Scalar Function and CLR Scalar Function
• SQL Server - NOLOCK vs READPAST: Do you know the difference between the two?
• SQL Server 2014 - How to Estimate Progress and How Much Time Is Left to Create an Index
• SQL Server – How to identify timeout or broken connections using Extended Events (XE) or SQL Profiler (Trace)

(back to top)

🔐 Blog Posts - Security

• SQL Server - How to identify fragile passwords, empty or equal to username
• stpSecurity_Checklist - Best practices and security checklist for SQL Server
• SQL Server - Understanding the Risks of TRUSTWORTHY Property Enabled in a Database
• SQL Server - How to Avoid SQL Injection? Stop using Dynamic Query as EXEC (@Query). Now
• SQL Server - How to Avoid Brute Force Attacks on Your Database
• SQL Server - How to Avoid and Protect Against Ransomware Attacks Like WannaCry on Your Database Server
• SQL Server - How to use EXECUTE AS to execute commands as another user (Impersonate login and user)
• SQL Server - How to Hide Databases from Unauthorized Users

(back to top)

🔑 Blog Posts - Data Privacy

• General Personal Data Protection Act (LGPDP or LGPD) applied to SQL Server databases
• SQL Server 2016 - Data Masking with Dynamic Data Masking (DDM)
• SQL Server - Avoiding Queries on Certain Columns with Column Level Security (CLS)
• SQL Server - Encrypting passwords with the symmetric encryption functions ENCRYPTBYPASSPHRASE and DECRYPTBYPASSPHRASE
• Working with cryptographic functions (MD4, MD5, SHA1, SHA2_256, and SHA2_512) using the SQL Server HASHBYTES function
• Working with the Base64 Encryption Algorithm in SQL Server
• SQL Server 2008 - How to Encrypt Your Data Using Transparent Data Encryption (TDE)
• SQL Server 2016 - How to Encrypt Your Data Using Always Encrypted
• SQL Server 2016 - How to protect your data using Row Level Security (RLS)

(back to top)

👀 Blog Posts - Audit

• Audit on SQL Server (Server Audit)
• SQL Server - How to Monitor and Audit Data Changes in Tables Using Change Data Capture (CDC)
• SQL Server - How to use auditing to map actual required permissions on a user
• SQL Server 2016 - How to “Time Travel” Using the Temporal Tables Feature
• SQL Server - How to Create a Data Change History for Your Tables (Audit Logs)
• Using SQL Server Standard Trace to Audit Events (fn_trace_gettable)
• SQL Server - How to implement audit and control of logins (Logon Trigger)
• How to create an Audit trigger to log object manipulation in SQL Server
• How to Create an Audit to Monitor Job Creation, Modification, and Deletion in SQL Server
• SQL Server - Permissions and privileges audit trigger at database and instance level (GRANT and REVOKE)

(back to top)

📊 Blog Posts - Monitoring

• SQL Server - Query to return running queries (sp_WhoIsActive without consuming TempDB)
• SQL Server - Using Resource Governor for greater control over server resources
• SQL Server - How to Create Error and Exception Tracking in Your Database Using Extended Events (XE)
• SQL Server - How to identify locks, blocks, and blocking sessions
• How to find out the date of last access to a table or view and execution of a procedure in SQL Server
• SQL Server - How to know the date of a user's last login
• SQL Server - How to know how long a specific database has been online
• SQL Server - How to identify and collect time consuming query information using Extended Events (XE)
• Is your SQL Server up to date? Returning the latest version of Service Pack (SP) or Cumulative Update (CU) for your instance
• SQL Server - How to recover the source code of deleted objects (View, Stored Procedure, Function and Trigger)
• SQL Server - How to identify deprecated features being used in the instance (Extended Events - XE)
• SQL Server - How to identify and monitor total free disk space used by database datafiles
• SQL Server - How to identify and monitor disks, free and used total disk space
• SQL Server - How to identify, monitor and prevent overflow in columns with IDENTITY and SEQUENCE of tables with many records
• SQL Server – How to list the largest tables and indexes in the database and show the size of each

(back to top)

📁 Blog Posts - Files

• SQL Server - File Operations Using xp_cmdshell (Listing, Reading, Writing, Copying, Deleting, and Moving Files)
• SQL Server - How to list, read, write, copy, delete and move files with CLR (C #)
• File Operations Using OLE Automation in SQL Server
• SQL Server - Importing and Exporting data from Excel spreadsheets
• Importing CSV Files into the SQL Server Database
• SQL Server - How to import text files into database (OLE Automation, CLR, BCP, BULK INSERT, OPENROWSET)
• SQL Server - How to export database data to text file (CLR, OLE, BCP)
• SQL Server - How to export the contents of a column to XML files
• SQL Server - How to export and import tabular data files (Ex: CSV) using CLR (C #)
• How to export data from a SQL Server table to HTML
• SQL Server - How to integrate with FTP and list, upload, and download files using CLR (C #)
• SQL Server - How to Batch Run All .sql Scripts in a Folder or Directory by SQLCMD

(back to top)

🛠️ Blog Posts - SQLCLR, PowerShell and OLE Automation

• Introduction to SQL Common Language Runtime (CLR) in SQL Server
• Performing POST and GET requests using CLR (C #) in SQL Server
• SQL Server – How to consult the dollar (USD), euro (EUR) or any other currency in real time with API and SQLCLR
• SQL Server - How to send messages to Whatsapp contacts, groups and mailing lists via API
• SQL Server - How to integrate database with Slack and send messages using Python and sp_execute_external_script
• SQL Server - How to calculate the distance between two locations using latitude and longitude (without API)
• SQL Server - How to compress and unzip files and directories using 7-zip and xp_cmdshell or SQLCLR (C #)
• SQL Server - How to capture data from a Web page (Webscraping with HttpRequest) and insert the data into the database with SQLCLR (C #)
• SQL Server - How to query and consume WordPress RSS feeds using CLR or xp_cmdshell (cURL)
• SQL Server - How to read, list, create, change and delete Windows registry keys (Regedit) by CLR (C #)
• SQL Server - How to list and eliminate Windows processes using the CLR (C #)
• Consuming the Google Maps API using OLE Automation in SQL Server
• SQL Server - How to back up all SQL Agent jobs via command line (CLR C # or Powershell)

(back to top)

🧷 Blog Posts - Query Development

• SQL Server 2016 - Using Native JSON Support (JSON_VALUE, JSON_QUERY, OPENJSON, FOR JSON, ISJSON, JSON_MODIFY)
• SQL Server - How to read, import, and export data from XML files
• SQL Server - How to use regular expressions (RegExp) in your database
• SQL Server - How to identify and replace “strange things” (hidden characters) in strings and tables
• SQL Server - Breaking strings into sub-strings using a separator (Split string)
• SQL Server - How to concatenate rows by grouping data by a column (Grouped concatenation)
• SQL Server 2012 - Working with Sequences and IDENTITY Comparisons
• SQL Server - How to create recursive queries with CTE (Common Table Expressions)
• SQL Server 2012 - Using the FORMAT function to apply masks and formatting to numbers and dates
• SQL Server – Using TRANSLATE to replace multiple REPLACE
• SQL Server - String or binary data would be truncated: What is, how to identify root cause and how to fix
• SQL Server - How to identify data conversion errors using TRY_CAST, TRY_CONVERT, TRY_PARSE, ISNUMERIC, and ISDATE
• SQL Server - Grouping Data Using ROLLUP, CUBE, and GROUPING SETS
• How to remove accent and special characters from a string in SQL Server
• Removing HTML Tags from a String in SQL Server

(back to top)

✅ Blog Posts - General Stuff

• SQL Server - How to enable and configure Database mail to send and monitor database emails (sp_send_dbmail)
• SQLCMD - The SQL Server command line utility
• SQL Server - How to send the contents of a table or query in the body of the email as HTML
• SQL Server - How to use MERGE command to enter, update and delete data with 1 command only
• SQL Server - How to create a code version of your Stored Procedures in HTML and with comments on the change
• SQL Server - How to standardize the Collation of all columns in the database
• SQL Server - How to document the database and its objects (tables, procedures, columns) using Extended Property
• SQL Server - How to retrieve the source code of an encrypted object (WITH ENCRYPTION)
• SQL Server - How to query Active Directory (AD) information using Linked Server (ADSI)
• SQL Server - Understanding SQL Agent Permissions and Roles (SQLAgentUserRole, SQLAgentReaderRole, and SQLAgentOperatorRole)
• SQL Server - How to transfer logins between instances by backing up users, logins, and permissions
• SQL Server - How to export the source code of all Stored Procedures, Functions, Views and Triggers from a database to .sql files
• How to estimate how much time is left to finish the backup on SQL Server?

(back to top)

📈 Blog Posts - Business Intelligence

• Azure Data Factory (ADF) – How to create offline documentation (in CSV) of your project
• Using PowerShell and the API with Agent or SSIS to Automate Updating a Power BI Data Set
• SQL Server and Power BI - How to analyze and generate WhatsApp group conversation statistics
• SQL Server Reporting Services (SSRS) - Which reports are most accessed? And which are not being used?
• SQL Server Reporting Services (SSRS) - How to log the report view and identify which user is accessing
• SSRS - Reporting Services in your company without paying anything? Meet SQL Server Express with Advanced Services
• Analysis Services - Creating Your First Multidimensional Cube in the Star Model (Star Schema)
• Analysis Services - How to use XLMA or Powershell to process cubes and dimensions via command line (T-SQL) or SQL Agent Job
• Analysis Services - How to use XLMA to backup and restore cubes via the T-SQL command line
• SQL Server and Power BI: How to load Stored Procedure data into SQL Server with DirectQuery

(back to top)

😊 Nerd Stats

(back to top)