0

As the title says, I am facing a peculiar problem with SQL Server 2008 R2. I am currently working with huge dataset in Analytics project, almost 1 million rows in each table. Now the problem is, if I run a query with normal select statement like Select * from Table... Gives me the whole table results, even a where condition gives the perfect results.

But as soon as I use any aggregate function in a query, then I am getting only 65536 rows , after which the query is exiting. Now I am cent percent sure that the result set is much huge than the one returned. Steps I tried before raising the question. 1) Tried increasing the result to grid size in SQL Server Management Studio, Options.It is maxed out at 65536 2) Exporting to CSV using the query. The total no of rows is 65536 3) Exporting to Excel.. Same problem.

Kindly guide me as to what am I doing wrong.

For reference, I am posting the SQL Query for which I am getting this problem.

select a.Store,a.Seq_Item, MIN(a.Date) StartDate,MAX(a.Date) EndDate,SUM(a.Transaction_Items)tot_sales, COUNT(a.Transaction_Items)tot_sales_day,(DATEDIFF(DAY, MIN(a.Date), MAX(a.Date))+1)actual_days, ROUND(SUM(a.Transaction_Items)/CAST(COUNT(a.Transaction_Items) as float),2)avg_sales_per_sales_day, ROUND(SUM(a.Transaction_Items)/CAST((DATEDIFF(DAY, MIN(a.Date), MAX(a.Date))+1)as float),2)avg_sales_per_actual_day, round((ROUND(SUM(a.Transaction_Items)/CAST(COUNT(a.Transaction_Items) as float),2)/ ROUND(SUM(a.Transaction_Items)/CAST((DATEDIFF(DAY, MIN(a.Date), MAX(a.Date))+1)as float),2)),2)sales_day_velocity, ROUND(round((ROUND(SUM(a.Transaction_Items)/CAST(COUNT(a.Transaction_Items) as float),2)/ ROUND(SUM(a.Transaction_Items)/CAST((DATEDIFF(DAY, MIN(a.Date), MAX(a.Date))+1)as float),2)),2),0) category from Store129 a group by a.Seq_Item,a.Store order by a.Seq_Item

Now, I am working in Amazon Server hosting environment, and have the option of Upgrading my SQL server version to Standard Edition. Currently it is Express edition. Now, if I upgrade, would that solve my issue.

Regards, Arindom Ray Chaudhuri

Arindom Ray Chaudhuri
  • 1
  • 1
    usually queries that give you that many results won't perform anyway because it will take quite some time and memory to actually load the results. If you use these results in some kind of view you might want to build the paging parameters in your query itself – Batavia Sep 01 '14 at 10:59
  • Hi Batavia, Thanks for responding. Can you please elaborate on this point, like what are the steps that I need to take, if I start working with a view. – Arindom Ray Chaudhuri Sep 01 '14 at 11:17
  • I tried creating a view, and I got an error like The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified. – Arindom Ray Chaudhuri Sep 01 '14 at 11:19
  • OK, removed the order by clause, and still same issue. Stuck at 65536 rows – Arindom Ray Chaudhuri Sep 01 '14 at 11:22
  • my point is 65536 rows are most likely way to much to actually use in many scenarios. you can look here http://stackoverflow.com/questions/13220743/implement-paging-skip-take-functionality-with-this-query too see how you can implement paging – Batavia Sep 01 '14 at 13:11

1 Answers1

1

http://msdn.microsoft.com/en-us/library/ms143432.aspx

That's a batch size so maybe try loading the results into a temporary table and then query from it. Might not be the perfect solution.

Pardon me for the 'answer' i cant comment due to the lack of points.

user3300750
  • 36
  • 4
  • I tried getting the results to another table, same issue, that new table has only 65536 rows. If this is some SQL Server licensing issue, I can definitely look for higher edition, but first I need to know whether it is licensing issue. – Arindom Ray Chaudhuri Sep 02 '14 at 10:25
  • can you please try this. This will ensure that the rows are inserted in batches. /* `declare @Count int set @Count = 1 while @Count > 0 begin insert into NewTable set @Count = @@ROWCOUNT end */` hope this helps – user3300750 Sep 04 '14 at 14:34
  • I actually, tried a different approach. I understood, this is a limitation of the software that you are using. SQL Server Enterprise edition have more space for recordset, which is not available with Express edition. – Arindom Ray Chaudhuri Nov 03 '14 at 08:03