USE [Tools] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- select dbo.XrdDay(4,'thursday', 'november', 2018) -- Thanksgiving -- select dbo.XrdDay(4,'thursday', 'november', 2019) -- Thanksgiving -- select dbo.XrdDay(4,'thursday', 'november', 2020) -- Thanksgiving -- Must have the day of week and the name of month spelled correctly. create or alter function [dbo].[XrdDay]( @xrd int --3rd, 4th, 5th, etc. ,@dayname varchar(8) --'saturday' ,@month varchar(10) --'february' ,@year smallint --year ) returns date as begin; -- declare @xrd int = 3; declare @dayname varchar(8) = 'monday'; declare @month varchar(10) = 'september'; declare @year smallint = 2019; declare @Result date= @month + ' ' + convert(varchar(64),(@xrd*7) - 6) + ',' + convert(varchar(64), @year); -- get Sunday. --select @Result, datename(weekday, @Result); --I'm fairly sure this is inefficient, but I can't seem to recall of another way around it. --to make it better, we need to calc @sunday+int==dayname in one go, not this while loop. while datename(weekday, @Result) collate SQL_Latin1_General_CP1_CI_AS != @dayname set @Result = dateadd( day, 1, @Result ); --select @Result, datename(weekday, @Result); return @Result; end;
Tag: MSSQL
Categories
TempDB Best Practices
- TempDB should have as many data files as there are cores available to SQL Server, but only up to 8 data files. Too many TempDB data files can cause contention. Recommendation: Create 8 files and test from there on.
- TempDB should only have 1 log file. Extra log files will improve performance as they are written to sequentially.
- The data files for TempDB should all be the same size.
- TempDB data files and TempDB log files should be on separate disks.
- TempDB files should be on the fastest disks.