Is this a test that you are setting up?

You need to look at using a recursive CTE to traverse the Hierachy with a bit tweaking of the example in BoL (http://msdn.microsoft.com/en-us/library/ms186243(v=sql.105).aspx)

This should give you a good starting...

I would suggest you read this article by Gail https://www.simple-talk.com/sql/database-administration/gail-shaws-sql-server-howlers/

Also this white paper http://msdn.microsoft.com/en-us/library/dd578580(SQL.100).aspx on Partitioning and indexing strategy.

You might want to look at the GROUPING SETS functionality or ROLLUP/CUBE grouping for this.

http://msdn.microsoft.com/en-us/library/bb522495(v=sql.105).aspx

SELECT

OrderDate

AVG(CAST((ProductPrice * OrderQuantity) AS MONEY)) AS AverageSale

FROM TEST3

WHERE DATENAME(WEEKDAY, OrderDate) = 'Saturday' AND

Year(orderdate) =...

Wouldnt...

Another option may be to use a Recusive CTE's to replace the Hierarchy Joins that you are using.

Suresh Kumar Maganti

I agree the bulk insert method is the better way, especially with the One off lock on the destination table, rather than the 700 million lock...

I think you missed the point that chris was making, if you look at his example he uses an Inline CTE to act as a Tally table.

As you have a...

Sorry Gail,

If you are adding a...

I've seen this behaviour before, and tracked it down to when the outer reference was either coming from a JOIN or from a derived table.

In the end I inserted...

Partitioning table in SQL server is generally considered a maintenance process.

As for the column, most people I can think of use a Time based solution (Period, Year, Day) depending on...

http://www.microsoft.com/en-us/sqlserver/sql-server-2014.aspx

What about 7.5?

+1. Definately easier to read Chris, Formatting FTW.

This is the problem

(SELECT SUM([VALPAYPLAN])

FROM ABC

WHERE DATE <= Getdate() AND ([TYPE] = 'PAYMENT') GROUP BY ID )

You need to add an additional filter to this sub query

SELECT...