# Thinking outside the box

Patron Saint of Lost Yaks

## October 2010 Blog Posts

##### Sweet and simple "first and last" weekday calculation

DECLARE @Year SMALLINT = 2011,         @NumberOfYears TINYINT = 3   ;WITH cteCalendar(FirstOfMonth, LastOfMonth) AS (         SELECT  DATEADD(MONTH, 12 * @Year + Number - 22801, 6) AS FirstOfMonth,                 DATEADD(MONTH, 12 * @Year + Number - 22800, -1) AS LastOfMonth         FROM    master..spt_values         WHERE   TYPE = 'P'                 AND number BETWEEN 1 AND 12 * @NumberOfYears ) SELECT  DATEADD(DAY, DATEDIFF(DAY, 6, FirstOfMonth) / 7 * 7, 6) AS FirstSunday,         DATEADD(DAY, DATEDIFF(DAY, 5, LastOfMonth) / 7 * 7, 5) AS LastSaturday FROM    cteCalendar

posted @ Thursday, October 28, 2010 9:25 AM | Feedback (0) | Filed Under [ Optimization SQL Server 2008 Algorithms SQL Server 2005 ]

##### How to determine if you should use full or differential backup?

Or ask yourself, "How much of the database has changed since last backup?". Here is a simple script that will tell you how much (in percent) have changed in the database since last backup. -- Prepare staging table for all DBCC outputs DECLARE @Sample TABLE         (             Col1 VARCHAR(MAX) NOT NULL,             Col2 VARCHAR(MAX) NOT NULL,             Col3 VARCHAR(MAX) NOT NULL,             Col4 VARCHAR(MAX) NOT NULL,             Col5 VARCHAR(MAX)         )   -- Some intermediate variables for controlling loop DECLARE @FileNum BIGINT = 1,         @PageNum BIGINT = 6,         @SQL VARCHAR(100),         @Error INT,         @DatabaseName SYSNAME = 'Yoda'   -- Loop all files to the very end WHILE 1 = 1     BEGIN         BEGIN TRY             -- Build the...

posted @ Thursday, October 21, 2010 4:34 PM | Feedback (5) | Filed Under [ SQL Server 2008 Algorithms Administration ]