Understood.  I knew what your perspective was.  I was a bit concerned that you might not know what others are doing with their queries.  I also don't know what...

Hmm, maybe I misunderstand, I thought the OP said that it was just that one query for the max date, ~ every hour, in order to provide that value...

• This reply was modified 4 years, 3 months ago by  Craig Smith.

I'd probably just add a trigger to the table to capture the max value(s) in a separate table so that it was always available.  All you have to check...

• This reply was modified 4 years, 3 months ago by  Craig Smith.

A question to be answered is this: How wide is each row of data?  This will impact the number of rows of data in each data page.  The fewer...

Thanks everyone for all the responses and good information.  I'm sorry I haven't been able to keep up with them.  Let me try clarify a few things quickly.

There are 24...

Thanks everyone.  I have added a clustered primary key in our test environment but can't compare the performance as the configuration is totally different.  When I do the 300 million...

• This reply was modified 4 years, 3 months ago by  Craig Smith.

Execution plan is attached.

Sorry, I am curious as to how it can do this and what factors influence it; I would like to know when indexes are needed for these types of queries.

The slight extra space is to store the upper level(s) (root level and intermediate level(s)) of the index, which are the b-tree structure that allows quick searches to specific...

Also, in SQL Server, when you're looking at a clustered index, remember, that's the table. The data is stored with the clustered index. There isn't any kind of separation...

Thank you.

Thanks everyone. I can post more details later, but here are some quick comments/questions:

Good to know about compute scalars, I was wondering what that meant.

What do you...

Thanks, yes, I updated statistics. In the interest of being able to show it conveniently here I reduced the query down to a very simple example. The original...

Here's what I do:

ALTER PARTITION FUNCTION <function_name>()

SPLIT RANGE (@NewDate)

ALTER PARTITION SCHEME <schema_name>

NEXT USED [PRIMARY]

Prior to that I verify that the partition doesn't already exist, but you may not need to...

Jeff Moden (6/24/2015)You can make INSERT/SELECT run nearly as fast as SELECT/INTO and you don't need to be in the BULK LOGGED or SIMPLE recovery model. Create your target...