Here's a site that many of you are probably familiar with if you've ever had to quickly generate some placeholder text for prototyping, testing, etc.
Monday, October 29, 2007
Maximum Capacity Specifications for SQL Server 2005
The following link contains the maximum sizes and numbers of various objects defined in Microsoft SQL Server 2005 components.
Tuesday, October 16, 2007
SQL Server GetStartOfWeek Scalar-Valued Function
I found this function on the SQL Team website. It is helpful if you need to find the first day of the week for a specified date. Keeping in mind that the start of the week might be defined differently for various clients or applications this function has a parameter that allows you to specify which day of the week should be used as the starting point.
CREATE FUNCTION [dbo].[GetStartOfWeek]
(
@MyDate DATETIME,
@WeekStarts INT = 1 -- Sun = 1, Mon = 2, Tue = 3, Wed = 4, Thu = 5, Fri = 6, Sat = 7 (Default to Sunday)
)
RETURNS DATETIME
AS
BEGIN
-- declare variables
DECLARE @FirstDayOfTheWeekForMyWeek DATETIME
DECLARE @FirstDayOfTheWeekForFirstWeek DATETIME
-- Check for valid day of week
IF @WeekStarts BETWEEN 1 AND 7
BEGIN
-- find first day on or after 1753/1/1 (-53690) matching day of week of @WeekStarts
-- 1753/1/1 is earliest possible SQL Server date.
SELECT @FirstDayOfTheWeekForFirstWeek = CONVERT(DATETIME, -53690 + ((@WeekStarts + 5) % 7))
-- verify beginning of week not before 1753/1/1
IF @MyDate >= @FirstDayOfTheWeekForFirstWeek
BEGIN
SELECT @FirstDayOfTheWeekForMyWeek = DateAdd(dd, (DateDiff(dd, @FirstDayOfTheWeekForFirstWeek, @MyDate) / 7) * 7, @FirstDayOfTheWeekForFirstWeek)
END
END
-- return value
RETURN @FirstDayOfTheWeekForMyWeek
END
(
@MyDate DATETIME,
@WeekStarts INT = 1 -- Sun = 1, Mon = 2, Tue = 3, Wed = 4, Thu = 5, Fri = 6, Sat = 7 (Default to Sunday)
)
RETURNS DATETIME
AS
BEGIN
-- declare variables
DECLARE @FirstDayOfTheWeekForMyWeek DATETIME
DECLARE @FirstDayOfTheWeekForFirstWeek DATETIME
-- Check for valid day of week
IF @WeekStarts BETWEEN 1 AND 7
BEGIN
-- find first day on or after 1753/1/1 (-53690) matching day of week of @WeekStarts
-- 1753/1/1 is earliest possible SQL Server date.
SELECT @FirstDayOfTheWeekForFirstWeek = CONVERT(DATETIME, -53690 + ((@WeekStarts + 5) % 7))
-- verify beginning of week not before 1753/1/1
IF @MyDate >= @FirstDayOfTheWeekForFirstWeek
BEGIN
SELECT @FirstDayOfTheWeekForMyWeek = DateAdd(dd, (DateDiff(dd, @FirstDayOfTheWeekForFirstWeek, @MyDate) / 7) * 7, @FirstDayOfTheWeekForFirstWeek)
END
END
-- return value
RETURN @FirstDayOfTheWeekForMyWeek
END
A couple of example calls to this function are as follows:
SELECT dbo.GetStartOfWeek('1/1/07', 1) -- Result: 2006-12-31
SELECT dbo.GetStartOfWeek('1/1/07', 2) -- Result: 2007-01-01
SELECT dbo.GetStartOfWeek('1/1/07', 2) -- Result: 2007-01-01
Notice how the results will vary based on which day you use for the start of the week.
Here's another version that a colleague of mine wrote that also provides the same functionality as the example above. His version is shorter and easier to read and testing against 750,000+ records yields the same results on every record and the performance difference between the two appears to be neglible.
CREATE FUNCTION [dbo].[fn_GetDate]
(
@InputDate DateTime,
@Day INT -- (1=Sunday, 2=Monday, etc)
)
RETURNS DateTime
AS
BEGIN
DECLARE @Offset INT
SELECT @Offset = (@Day - DATEPART(dw, @InputDate))
IF (@Offset > 0)
SELECT @Offset = -7 + @Offset
(
@InputDate DateTime,
@Day INT -- (1=Sunday, 2=Monday, etc)
)
RETURNS DateTime
AS
BEGIN
DECLARE @Offset INT
SELECT @Offset = (@Day - DATEPART(dw, @InputDate))
IF (@Offset > 0)
SELECT @Offset = -7 + @Offset
DECLARE @Date DateTime
SELECT @Date= DATEADD(dd, @Offset, CONVERT(VARCHAR, @InputDate, 101))
RETURN @Date
END
Enjoy.
Tuesday, October 9, 2007
Calculate Percentiles with SQL Server 2005
"When you need to analyze numerical data, percentiles are handy for understanding the distribution. Oracle has been providing analytical functions such as NTILE, PERCENTILE_CONT, and RANK since version 8, while SQL Server has been playing catch up. With SQL Server 2005, Microsoft finally added NTILE, RANK, DENSE_RANK, and ROW_NUMBER to SQL Server's T-SQL functions. Unfortunately, the SQL Server 2005 still does not provide PERCENTILE_CONT, and neither will SQL Server 2008. They do, however, feature the new SQL Server Common Table Expression (CTE), which you can use to calculate percentiles."
"This article presents a solution for calculating percentiles in SQL Server 2005 using SQL Server CTE. Based on a solution described in Joe Celko's SQL For Smarties, the idea is to calculate the percentile (percent rank) of each row; if one of the rows has the percentile value that you are looking for, you return it. Otherwise, you perform an interpolation between a value that is slightly more than the desired percentile and one that is slightly less."
I recently used this approach for some new functionality in one of my projects and it really saved alot of intensive and tedious queries to come up with the right value.