Generating Data From Script
SSMS -> Database Name -> Task -> Generate Script Select object -> Next Click on “Advanced” in the “Types of Data to Script” select “Data only” Generated Script
.NETADO.NETAgileAngularArchitectureASP.NETAuthenticationAzureAzure FunctionBIBig DataBlazorBusinessC#Cloud ServiceCosmosDBCSSDebuggingDesignEncryptionEntity FrameworkFYIGoogleHardwareInternetInterviewsJavascriptjQueryLeadershipLINQManagementMicrosoftMobileMS OfficeMS SQLNetworkingNoSQLOOPProject ManagementSoftware DevelopmentSource ControlSSISSSRSSyncfusionTeamTestingTSQLUncategorizedVendor SoftwareVisual StudioWebWeb ServicesWindowsWordPress
Grant User Role Execute Permission To All Stored Procedure Script
All SP Exec All SP one SP at a time Following script will try to add permissions (execute permissions) on all procedures on all databases on the server for the role defined.
Alter Table with Large Amount of Data Takes a Long Time
The schema change it taking so long because you are assigning a default value to the column during the change and enforcing that with a non-nullable column, and it has to populate the column for millions of rows, which is an incredibly expensive operation. Alternative to making it faster would be to add it in... » read more
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.