Jeffrey Yao

Jeffrey Yao is a senior SQL Server consultant with 15 years of database administration, his current interests are on database administration automation and visualization. He enjoys reading the concise and smart sql tips and loves R&D work in DBA Engineering topics. His blog is at: http://www.sqlservercentral.com/blogs/jeffrey_yao/.
2018 Awards
2017 Awards
2016 Awards
2015 Awards
2014 Awards
2018 Awards

2017 Awards


2016 Awards


2015 Awards

2014 Awards

Backup
- Exploring SQL Server Table Backup Methods (last update: 4/21/2017)
Comparison Data and Objects
- Finding SQL Server Table and Data Differences using PowerShell (last update: 11/15/2016)
- SQL Server Database Schema Synchronization via SQLPackage.exe and PowerShell (last update: 4/4/2017)
Constraints
- Script to Delete Data from SQL Server Tables with Foreign Key Constraints (last update: 10/15/2015)
Database Administration
- Automate SQL Server Administration Tasks Using the Windows Command Prompt (last update: 1/22/2019)
- Automatically Build a SQL Server Script Database Library from Your Script Files (last update: 11/24/2015)
- Build an Automated SQL Server Database Inventory System (last update: 7/6/2018)
- Copy a SQL Server database with just the objects and no data (last update: 2/1/2017)
- Create a Self-Service Framework for SQL Server DBA Tasks (last update: 9/17/2020)
- Generate INSERT scripts from SQL Server queries and stored procedure output (last update: 5/13/2016)
- Introduction to C# Scripting for SQL Server DBAs (last update: 11/18/2016)
- Learn SQL Server from Microsoft’s Own Code (last update: 12/19/2017)
- MSSQL-Scripter Tool and Examples to Generate Scripts for SQL Server Objects (last update: 2/12/2019)
- SQL Server HotFix History Analysis (last update: 5/22/2018)
Database Design
- CRUD Operations in SQL Server (last update: 7/19/2018)
Database Mail
- Generate HTML Formatted Emails from SQL Server (last update: 9/23/2015)
Dates
- SQL Server Date Time Calculation Examples (last update: 2/13/2015)
- SQL Server FORMAT Function for Dates, Numbers and SQL Server Agent Dates (last update: 8/6/2019)
Error Logs
- Cycle SQL Server Error Logs based on size using PowerShell (last update: 5/13/2014)
High Availability
- Find SQL Server Downtime Window After Reboot (last update: 6/25/2014)
Import and Export
- Adding more functionality to SQL Server BCP with PowerShell (last update: 6/29/2016)
- Copy data to another SQL Server instance without a Linked Server (last update: 3/1/2017)
- Dynamically Generate SQL Server BCP Format Files (last update: 9/30/2016)
- Excel File Data Validation for SQL Server Imports (last update: 9/3/2015)
- Transfer data from SQL Server to MySQL (last update: 8/14/2020)
Indexing
- Build a Robust Incremental SQL Server Update Statistics Procedure (last update: 11/2/2017)
Integration Services Administration
- Automated Inventory Collection of Scheduled SQL Server Integration Services Packages (last update: 12/5/2018)
Locking and Blocking
- Detect and Automatically Kill Low Priority Blocking Sessions in SQL Server (last update: 7/16/2014)
Monitoring
- SP_WhoIsActive Data Collection and Analysis (last update: 3/11/2020)
Performance Tuning
- Automatically Generate Performance Counter Graphs with SQL Server 2016 and R (last update: 4/12/2017)
PowerShell
- Bulk Copy Data from Oracle to SQL Server (last update: 10/26/2017)
- Complete Common SQL Server Database Administration Tasks In Parallel with PowerShell V3 WorkFlow (last update: 3/6/2015)
- Get Total Line Counts for all SQL Server Source Objects Using PowerShell (last update: 1/23/2015)
- How to find a specific text string in a SQL Server Stored Procedure, Function, View or Trigger (last update: 1/27/2015)
- One Line PowerShell Solutions To Common SQL Server DBA Tasks (last update: 11/20/2014)
- PowerShell Script to Delete All Data in a SQL Server Database (last update: 4/18/2016)
- PowerShell to automate multiple database SQL Server restores to refresh environments (last update: 2/18/2015)
Professional Development Career
- The 6 Rules to be a Happy SQL Server DBA (last update: 6/12/2017)
- Things I know now that I wish I knew when I started as a SQL Server DBA (last update: 6/13/2019)
Professional Development Interview Questions DBA
- PowerShell Interview Questions for SQL Server DBAs (last update: 6/8/2016)
- Senior SQL Server DBA non-technical interview questions (last update: 6/10/2015)
Professional Development Interview Questions Developer
- T-SQL Interview Questions for Senior Developers (last update: 11/2/2016)
Professional Development Interviewing
- Technical Interview Questions for a Senior SQL Server DBA (last update: 1/18/2017)
Professional Development Skills Development
- How to Build an Efficient Request Process for SQL Server DBAs (last update: 5/1/2019)
- How to Grow Your SQL Server DBA Career (last update: 5/7/2015)
Replication
- Auto Fix SQL Server Transactional Replication Error 20598 for DELETEs (last update: 10/23/2014)
- Auto Fix SQL Server Transactional Replication Error 20598 for UPDATEs (last update: 9/30/2014)
- Monitoring SQL Server Transactional Replication Latency via PowerShell and RMO (last update: 5/27/2015)
Security
- Backdoor to Elevate SQL Server Security Privileges (last update: 1/23/2018)
- Cloning a SQL Server Login with all permissions using PowerShell (last update: 11/22/2016)
- Different Ways to Find SQL Server Orphaned Users (last update: 12/1/2017)
- Script to drop SQL Server login and all owned objects using PowerShell (last update: 8/13/2014)
- SQL Server Functions to Mask Confidential Data using CLR or PowerShell (last update: 12/12/2016)
SQL Server 2016
- Easily Import CSV Files with R in SQL Server 2016 (last update: 2/16/2017)
- SQL Server 2016 Regular Expressions with the R Language (last update: 3/29/2017)
- Visual SQL Server Performance Data Comparison with R (last update: 5/19/2017)
SQL Server 2017
- Create Size Based Backups for SQL Server 2017 (last update: 11/27/2017)
SQL Server 2019
- Discovering New System Objects and Functions in SQL Server 2019 (last update: 10/11/2018)
SQL Server Agent
- How to Pass Data Between SQL Server Agent Job Steps (last update: 10/23/2018)
- Make SQL Server Agent Alert Notifications Smarter and More Flexible (last update: 11/15/2018)
- SQL Server Agent Job Advanced Management (last update: 8/24/2016)
SQL Server Configurations
- Inventory SQL Server Services Version and Edition (last update: 3/11/2015)
SQL Server Management Studio
- Automate Registering and Maintaining Servers in SQL Server Management Studio (SSMS) (last update: 7/1/2014)
- Using Regular Expressions to Manipulate Text in SQL Server Management Studio 2017 (last update: 6/18/2018)
T-SQL
- Advanced Use of the SQL Server ORDER BY Clause (last update: 3/15/2017)
- Delete Files with SQL Server 2016 R Logic in T-SQL Code (last update: 2/8/2017)
- Implement a Comment Based Help Framework via CLR Stored Procedure (last update: 3/21/2017)
- Sort Alphanumeric Values in SQL Server (last update: 9/11/2019)
Testing
- Generate Random Strings with High Performance with a SQL CLR function (last update: 4/17/2015)
Triggers
- How to Identify which SQL Server Columns Changed in a Update (last update: 7/21/2020)
Upgrades and Migrations
- Creating a SQL Server Build and Patch Release Table (last update: 1/15/2018)
- Extract Hot Fix Details from a SQL Server CU KB Articles (last update: 3/30/2018)
- Identify System Object Differences Between SQL Server Versions (last update: 2/12/2018)