SQL Server Performance Forum – Threads Archive
Select Big Table Again
Hello. Thanks for people reading this This is a table which stores 2 million records for all the customers. Columns of tblTransactions:+++++++++++++++++++++++++++++++++++++++++++++++++
TranscID, CustomerID, TranscDetail, WhenCreated
+++++++++++++++++++++++++++++++++++++++++++++++++
*TranscID is PK,
*CustomerID is FK
*WhenCreated is noncluster index in DESC order I’d like to Select the same Number of transactions for all the customers where WhenCreated is after a given "DateTime". Transactions are ordered by DateTime Asc for EACH of the customer. (Performance is important) Can some body give me some hints? Thanks in advance!
In his heart, a man plans his course, but the LORD determines his steps
select *
from bigtable
where WhenCreated >= @givendatetime
order by CustomerID, TranscID, WhenCreated KH
Khtanm Thanks for you reply.
But does it ensure the same number of transactions of each customer
will be returned?
quote:Originally posted by khtan
select *
from bigtable
where WhenCreated >= @givendatetime
order by CustomerID, TranscID, WhenCreated KH
In his heart, a man plans his course, but the LORD determines his steps
from bigtable
where WhenCreated >= @givendatetime
order by CustomerID, TranscID, WhenCreated KH
To get the number of transactions per customer, you need to group on this clolumn.
consider this: select CustomerID,count(*) from bigtable
where WhenCreated >= @givendatetime
GROUP by CustomerID
order by CustomerID
]]>