Stop Using PRINT to Track Query Progress
Stop using PRINT. Start using RAISERROR. PRINT PRINT with GO RAISEERROR with NOWAIT Source:
SQL Server: Lead vs Lag
The LAG function has the ability to fetch data from a previous row, while LEAD fetches data from a subsequent row. Sources:
Database Size
Fix: Shrink database file to recover unused space. Add more disk space. Purge data from database. Move data to another database or data warehouse. Sources:
Database AlwaysOn Checklist For Automatic Failover
Failover can be done manually or automatically. For automatic failover, make sure the following has been set for a successful automatic failover. SQL Server Database AlwaysOn Checklist for Automatic Failover Availability Group Listeners are setup and all processes point to the listeners. Failover jobs have been setup. All jobs in the Always On category. XXXXX_AvailabilityGroup... » read more
Drop Index
Drop Index Re-create Index Dropping index is fast. Recreating index will take a longer time.
Sparse Column
Sparse columns are ordinary columns that have an optimized storage for null values. Sparse columns reduce the space requirements for null values at the cost of more overhead to retrieve nonnull values. Consider using sparse columns when the space saved is at least 20 percent to 40 percent. Sources: https://docs.microsoft.com/en-us/sql/relational-databases/tables/use-sparse-columns?view=sql-server-ver15
Batch Update or Delete for Database
First always create backup of table to be updated or deleted. Batch update records in a database table. Batch delete records in a database table. Result Should be able to see result as the query is still running.
Compress Database Backup Files with 7-Zip
7-Zip is a free and open-source file archiver, a utility used to place groups of files within compressed containers known as “archives”. It is developed by Igor Pavlov and was first released in 1999. 7-Zip uses its own 7z archive format, but can read and write several other archive formats. ZipFiles.cmd The following script look... » read more
Database Backup Log
Sources: https://www.mssqltips.com/sqlservertip/1601/script-to-retrieve-sql-server-database-backup-history-and-no-backups/
Database Dictionary with Extended Property
You can use tools like SQL Doc (Red Gate) to generate database dictionary. Add extended property to table and column when you create them. Adding extended property to table. Update existing extended property to table. You can also update the extended property of table and column by clicking on the table or column -> Properties... » read more