June 2025 Discount: Comprehensive Database Performance Health Check | Testimonials

Home

SQL SERVER – Script/Function to Find Last Day of Month

Pinal Dave
SQL, SQL Server, SQL Tips and Tricks
SQL DateTime, SQL Function, SQL Scripts, SQL Utility
Following query will find the last day of the month. Query also take care of Leap Year. Script: DECLARE @date DATETIME SET @date='2008-02-03' SELECT DATEADD(dd, -DAY(DATEADD(m,1,@date)), DATEADD(m,1,@date)) AS LastDayOfMonth GO DECLARE @date DATETIME SET @date='2007-02-03' SELECT DATEADD(dd, -DAY(DATEADD(m,1,@date)), DATEADD(m,1,@date)) AS LastDayOfMonth GO ResultSet: LastDayOfMonth ----------------------- 2008-02-29 00:00:00.000 (1 row(s) affected)…
Read More

SQL SERVER – 2005 Understanding Trigger Recursion and Nesting with examples

Pinal Dave
SQL, SQL Performance, SQL Server, SQL Tips and Tricks
SQL Scripts, SQL Server Security, SQL Stored Procedure, SQL Trigger
Trigger events can be fired within another trigger action. One Trigger execution can trigger even on another table or same table. This trigger is called NESTED TRIGGER or RECURSIVE TRIGGER. Nested triggers SQL Server supports the nesting of triggers up to a maximum of 32 levels. Nesting means that when…
Read More

SQL SERVER – Disable Index – Enable Index – ALTER Index

Pinal Dave
SQL, SQL Performance, SQL Server, SQL Tips and Tricks
SQL Index, SQL Scripts, SQL Server DBCC
There are few requirements in real world when Index on table needs to be disabled and re-enabled afterwards. e.g. DTS, BCP, BULK INSERT etc. Index can be dropped and recreated. I prefer to disable the Index if I am going to re-enable it again. USE AdventureWorks GO ----Diable Index ALTER INDEX [IX_StoreContact_ContactTypeID]…
Read More