An extensive collection of SQL Server tips and tricks that I have collected over almost 3 decades as a DBA

If this is your first time visiting the site - welcome

If you are a returning visitor, there have been some changes to the site:

• Visually, all has changed. This site was originally constructed using Google's free 'Classic Sites' builder. The site has now been upgraded to Google's 'New Sites' builder. It was hard work unlearning all the slightly clunky interface, but has resulted in a leaner interface. The auto-upgrade feature did need some tweaking as some of the classic embedded HTML features are no longer supported, but hopefully I've been successful.

• The content has remained broadly the same, although the multitude of syntax errors hidden in the HTML text have now been fixed.

All code is supplied as-is, Some has been developed from scratch, but where I have altered/extended previously published code, I have given credit when the original author was known.

• Backup and Restore

• Central Management Server

• Clustering/Availability Groups

• Connectivity

• Data Manipulation Activities

  • Data Conversion

  • Data Deduplication

  • Data Encryption

  • Data Formatting

  • Data Partitioning

  • Data Removal, Archiving and Structure

  • Data Searching

  • Data Type

  • Data Validation

  • Data/Database Compression

  • Data/Database Size Determination

  • Import/Export

  • List Manipulation

  • String Manipulation

• Database Setup

  • MSDB, SQLAgent and Maintenance Plans

  • Database Mirroring

• Date and Time

• File System

• Interview Questions

• Performance Measurement, Analysis and Optimization

• Scripting SQL Server

• SQL Server Security

• SQL Server Metadata

  • Dynamic Management Views (DMVs)

  • Declarative Referential Integrity (DRI)

  • Indexes

  • Database Code Objects (Stored Procedures, Functions and Views)

  • Tables and Triggers

  • Other Metadata Queries

• SQL Server Migration Toolkit

• Reports and SQL Server Reporting Services (SSRS)

Design Notes:

• Initially there was a severe amount of banging my head against the wall while fighting with the 'Classic' Google Sites HTML to try and make the code samples readable and easy on the eye. Thanks must go to Red-Gate for their simple (and free) code prettifier. Nothing else came close to producing sensible HTML.

• 7 Jun 2020 - after 6 years with no updates to the site, I returned and was disappointed to find that the Code Prettifier had been retired. Fortunately, Notepad ++, in conjunction with the NPPExport plugin, allows TSQL to be displayed with syntax highlighting and exported to an HTML file. This file can then be opened in Chrome and cut/pasted into any page with the formatting intact. Messy, but free :-)

• 26 Apr 2021 - after upgrade to 'New' Google Sites, most of the code formatting has been retained, but occasionally it has been less than successful. In all cases, a properly formatted version can be found in the attached .sql file(s). Eventually, I will fix this. Hopefully I can find (or Google will release) a code formatter.