SQL This!

Every once in a while you need a quick Table w/ a range of dates, that's what this is for... Declare @iLoop AS INT SET @iLoop = 0 DECLARE @rptMonths TABLE(rptMonth DateTime) WHILE @iLoop BEGIN INSERT INTO @rptMonths(rptMonth) VALUES(DATEADD(mm, @iLoop, @StartDate)) SET @iLoop = @iLoop + 1 END

This has come up before in my line of work, to get a total workday count but with out the weekends. (DATEDIFF(dd, StartDate, EndDate) + 1) -(DATEDIFF(wk, StartDate, EndDate) * 2) -(CASE WHEN DATENAME(dw, StartDate) = 'Sunday' THEN 1 ELSE 0 END) -(CASE WHEN DATENAME(dw, EndDate) = 'Saturday' THEN 1 ELSE 0 END) AS TotalWorkDays

T his article was written back before I was looking into Sql Server 2005. The underlying idea is the same, in order to keep your database running healthy you will need to maintain and administer the underlying architecture. In this short but subtle refresh I've separated the 2000 concepts and implementations from those used today in Sql Server 2005. If you have comments or corrections do feel free to email me or leave a comment. SQL Server 2000 ===================================================== It is imperative you maintenance your Database. One way to check up on the indexes per table is to run the DBCC SHOWCONTIG command as DBCC SHOWCONTIG ('tbl_YourTableName') with fast,ALL_INDEXES You'll end up with a very similar display like the following... DBCC SHOWCONTIG scanning 'tbl_YourTableName' table... Table: 'tbl_YourTableName' (1113627606); index ID: 1, database ID: 8 TABLE level scan performed. - Pages Scanned................................: 1...