Jeff Moden (5/15/2015)

Jason A. Long (5/15/2015)

Out of curiosity, why do you say this?

I could see it it you were talking about...

Lynn Pettis (5/15/2015)

Some...

Never use a function on a column if it can be avoided.

Never use between for date/datetimes, use >= and < instead.

SELECT @sql = 'SELECT *

FROM #Temp

WHERE DocDate >=...

Never use BETWEEN on dates or datetimes; instead, use >= and < the next date/time value.

Also, for literal dates, always use format 'YYYYMMDD', which is 100% unambiguous. ...

SELECT REPLACE(CONVERT(varchar(11), DATEADD(MONTH, month_adjustment, current_month), 106), ' ', '-') AS column_name

FROM (

SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0) AS current_month

) AS get_current_month

CROSS JOIN (

...

That's a good point about the minimal logging.

But from at least SQL 2008 on, you can get minimal logging on the insert into the existing empty clustered index as well,...

That's not surprising once you think through the process. Loading the table and then clustering it requires all the rows to be inserted twice.

It's faster to create the clustered index first, because if necessary you can always explicitly sort the data being into cluster key order for the insert. Typically SQL will...

Or much better, when entering literal dates and making date/datetime comparisons:

1) always use format YYYYMMDD, which is 100% under any/all SQL settings

2) use >= and < [the next day] rather...

Before that you may want to try:

EXEC sp_refreshview

on that view.

DOH, just saw this:

So obviously clustering would not apply to it, but to the underlying tables.

It's still fundamentally true that identity is usually NOT the best clustering...

herladygeekedness (5/13/2015)

Jeff Moden (5/9/2015)

Heh.......

1) The LEFT OUTER JOIN is functioning like an INNER JOIN because of the WHERE condition on a column from that table. For better performance, move the WHERE condition...

If you mean cast it to a time / datetime, you can't safely do that, because the run_duration in sysjobhistory can meet or exceed 24 hours.

The names should not be in that table, only the IDs.

Also, you need some type of amount code so that amounts for that visit can be distinguished from one another.