February 2009 Blog Posts
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,...
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
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,...
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
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)) + '.'
...