Orlando Colamatteo (1/20/2016)

---

ScottPletcher (1/20/2016)

---

Orlando Colamatteo (1/19/2016)

---

ScottPletcher (1/19/2016)

---

Of course that code doesn't specify the date key, so it will do a full table scan. Again, vastly too much overhead when two...

Sergiy (1/19/2016)

---

SumonB (1/19/2016)

---

Hi Scott,

I executed the code today..

Declare @StartDate Datetime,

@EndDate Datetime

if DAY(@StartDate) >= 7

set @StartDate = DATEADD(day, -15, DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0))

else

set @StartDate = DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH,...

Orlando Colamatteo (1/19/2016)

---

ScottPletcher (1/19/2016)

---

Of course that code doesn't specify the date key, so it will do a full table scan. Again, vastly too much overhead when two very simple arithmetic...

Orlando Colamatteo (1/18/2016)

CREATE FUNCTION dbo.get_day_in_nth_week_in_month

(

@Year SMALLINT,

@MonthNumber TINYINT,

@WeekInMonthNumber TINYINT,

@DayName VARCHAR(9)

)

RETURNS TABLE

AS

RETURN

(

SELECT day_dt,

...

It's not nearly that complicated to get the nth given day of any month:

DECLARE @SecondTuesdayOfMonth_InThreeMonths datetime2

--set to last possible day = 14th of month, then ...

SET @SecondTuesdayOfMonth_InThreeMonths = DATEADD(DAY, 13,...

SQL Agent schedules all of it jobs from msdb, and I don't see anything that looks like a calendar table in it.

Keep in mind, too, you've also created a potential...

Orlando Colamatteo (1/16/2016)

---

There could be a trade off for CPU and coding effort. If you wrapped the calculation into an iTVF...

Orlando Colamatteo (1/16/2016)

---

ScottPletcher (1/16/2016)

---

Simple mathematical calcs could give you any of those dates, with no need for I/O against a table. But I'm also not attempting actual code without...

It simply probably didn't occur to the SSMS developers at the time to visually mark it. They probably didn't deal with disabled indexes at the time and almost certainly...

Simple mathematical calcs could give you any of those dates, with no need for I/O against a table. But I'm also not attempting actual code without existing data definitions/DDL.

I think this will do the same thing, much more simply and clearly:

Declare @StartDate DATETIME,

@EndDate DATETIME

if DAY(@StartDate) >= 7

set @StartDate = DATEADD(day, -15, DATEADD(DAY, DATEDIFF(DAY, 0,...

For a BULK INSERT, you'd typically want to take a table lock anyway, to allow minimal logging (edit: and reducing locking and logging overhead as much as possible) . ...

Hugo Kornelis (1/13/2016)

---

It actually is. If not needed, it is a waste of storage both on disk,...

No, I wouldn't worry about indexing/clustering for the other result sets if they are created as part of the main query.

Isn't this q just a duplicate of http://www.sqlservercentral.com/Forums/Topic1752199-3077-1.aspx