Monday, October 29, 2007 @ 11:28 am,Other,Matt Pavey
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 @ 11:17 am,SQL Server,Matt Pavey
The following link contains the maximum sizes and numbers of various objects defined in Microsoft SQL Server 2005 components.
http://msdn2.microsoft.com/en-us/library/ms143432.aspx
Tuesday, October 16, 2007 @ 9:48 am,SQL Server,Matt Pavey
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
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
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
DECLARE @Date DateTime
SELECT @Date= DATEADD(dd, @Offset, CONVERT(VARCHAR, @InputDate, 101))
RETURN @Date
END
Enjoy.
Tuesday, October 9, 2007 @ 7:53 am,SQL Server,Matt Pavey
"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.
