Thinking outside the box

Patron Saint of Lost Yaks

February 2009 Blog Posts

Competition

posted @ Saturday, February 28, 2009 4:29 PM | Feedback (5) | Filed Under [ Optimization ]

Fastest LeapYear checker?

CREATE FUNCTION dbo.fnIsLeapYear (             @Year SMALLINT ) RETURNS BIT AS BEGIN             RETURN      CASE DATEPART(DAY, DATEADD(YEAR, @Year - 1904, '19040229'))                                      WHEN 29 THEN 1                                      ELSE 0                          END END

Improved anniversary calculation (better datediff)

Some time ago, I wrote this article about how DATEDIFF works. http://www.sqlteam.com/article/datediff-function-demystified At the end I suggested two functions to calculate the number of months according to how human mind works. At the discussion later, a person notified me that it calculated the wrong number of months if you tried January 29th 2009 to February 28th 2009. The day is still greater, but you cannot have more days in february 2009 than 28. These are improved functions that also deals with those situations. CREATE FUNCTION [dbo].[fnMonthsApart] (     @FromDate DATETIME,     @ToDate DATETIME ) RETURNS INT AS BEGIN         RETURN  CASE                     WHEN @ToDate < DATEADD(MONTH, DATEDIFF(MONTH, @FromDate, @ToDate), @FromDate) THEN DATEDIFF(MONTH, @FromDate, @ToDate) - 1                     ELSE DATEDIFF(MONTH,...

Get all your databases and their sizes

SELECT      @@SERVERNAME AS SqlServerInstance,             db.name AS DatabaseName,             SUM(CASE WHEN af.groupid = 0 THEN 0 ELSE f.size / 128.0E END) AS DatabaseSize,             SUM(CASE WHEN af.groupid = 0 THEN af.size / 128.0E ELSE 0 END) AS LogSize,             SUM(af.size / 128.0E) AS TotalSize FROM        master..sysdatabases AS db INNER JOIN  master..sysaltfiles AS af ON af.[dbid] = db.[dbid] WHERE       db.name NOT IN ('distribution', 'Resource', 'master', 'tempdb', 'model', 'msdb') -- System databases             AND db.name NOT IN ('Northwind', 'pubs', 'AdventureWorks', 'AdventureWorksDW')   -- Sample databases GROUP BY    db.name

posted @ Thursday, February 12, 2009 11:20 AM | Feedback (3) | Filed Under [ SQL Server 2008 Administration SQL Server 2005 SQL Server 2000 ]

How to tell who did a backup when

SELECT      db.name AS DatabaseName,             bf.logical_name AS LogicalName,             CASE bs.[type]                         WHEN 'D' THEN 'Database'                         WHEN 'I' THEN 'Differential database'                         WHEN 'L' THEN 'Log'                         WHEN 'F' THEN 'File or filegroup'                         WHEN 'G' THEN 'Differential file'                         WHEN 'P' THEN 'Partial'                         WHEN 'Q' THEN 'Differential partial'                         ELSE 'Unknown'             END AS BackupType,             CASE bf.file_type                         WHEN 'D' THEN 'SQL Server data file'                         WHEN 'L' THEN 'SQL Server log file'                         WHEN 'F' THEN 'Full text catalog'                         ELSE 'Unknown'             END AS FileType,             bs.user_name AS UserName,             bs.backup_start_date AS StartDate,             bs.backup_finish_date AS FinishDate,             CAST(bs.software_major_version AS VARCHAR(11)) + '.'            ...

posted @ Thursday, February 12, 2009 10:36 AM | Feedback (2) | Filed Under [ SQL Server 2008 Administration SQL Server 2005 SQL Server 2000 ]