Jeffrey Yao

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
MSSQLTips award

MSSQLTips award

2017 Awards
MSSQLTips award

MSSQLTips award

MSSQLTips award

2016 Awards
MSSQLTips award

MSSQLTips award

MSSQLTips award

2015 Awards
MSSQLTips award

MSSQLTips award

2014 Awards
MSSQLTips award

MSSQLTips award

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

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)

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)

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)

More SQL Server Solutions

download

get free sql tips