Thinking outside the box

Patron Saint of Lost Yaks
posts - 203, comments - 734, trackbacks - 4

My Links

Advertisement

News

Archives

Post Categories

February 2009 Blog Posts

Competition

Adam Machanic has a nice competition going on here http://sqlblog.com/blogs/adam_machanic/archive/2009/02/27/t-sql-challenge-grouped-string-concatenation.aspx until the 16th of March 2009. Please join in! The prize, for the best submission, is a full MSDN subscription, valued at around $10,000. How's that for inspiration! My first attempt runs for 1.9 seconds and uses 418k reads. My second attempt runs for 2.1 seconds and uses 334k reads. Table 'Product'. Scan count 0, logical reads 158866, physical reads 0. Table 'SalesOrderDetail'. Scan count 31466, logical reads 97140, physical reads 0. Table 'SalesOrderHeader'. Scan count 38239, logical reads 77321, physical reads 0. Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0. Table 'Contact'. Scan count 1, logical reads 569,...

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

posted @ Wednesday, February 25, 2009 3:11 PM | Feedback (21) | Filed Under [ Optimization SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

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,...

posted @ Friday, February 13, 2009 10:50 AM | Feedback (5) | Filed Under [ Optimization SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

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 ]

Powered by:
Powered By Subtext Powered By ASP.NET