Naveen

Your query is joining (what looks like) two views, and from the picture you've posted, I'd say that each of those views in turn contains several joins.

No, like I said, let's get the row counts right before we look at the indexes.  Why did you change index_id IN (0,1) to index_id NOT...

Here's how to get a row count for each table:
SELECT
     object_id
,    SUM(rows) AS NoofRows
FROM sys.partitions
WHERE index_id IN (0,1) -- clustered index or heap

Don't bother with sp_MSforeachtable.  Join to sys.partitions instead - that has a column with the row count in it.  Start by returning a result set with a row count for...

Yes, of course.  Show me what you've tried, and I'll give you some pointers.

John

If it works, and you've tested it on large (say million-plus rows) data sets and it performs well, then go with it.  If you're going to be using dynamic SQL...

Sounds a little bit like a homework question.  Be that as it may, you need to join the sys.tables, sys.indexes and sys.partitions catalog views to solve this problem.  Have a...

Here you go.  Not tested.

-- This relies on there being no gaps or repititons in the ID column.
-- If there are, you'll need to number...

Time for you to provide table DDL in the form of a CREATE TABLE statement, sample data in the form of INSERT statements and expected results based on the sample...

Use a CTE to get row numbers partitioned by ID and Group and ordered by date (descending), then select from that CTE where the row number is 1.

John

Indeed.  And please make sure you read and understand about SQL injection before that goes anywhere near a live server.

John