October 9, 2012 at 5:52 pm
What is the maximum number of columns that can be Pivoted uisng the PIVOT command in SQL 2008 R2?
Im currently pivoting around 300 columns and its all working fine. But thinking about future expansion of the system and I'm a little worried if there is a physical limit (in much the same way you can only have 1000 odd columns in a view) or not and was hoping someone here had an answer.
October 9, 2012 at 5:59 pm
As far as I know (and quickly poked around to see if I was horribly wrong) there's no hard-coded limit. There's obviously a point where the memory ends up limited but from my knowledge and experience there's no significant limit that simply trying to twist that much data in memory causes.
However, it does beg the question... why are you pivoting over 300 columns of information? At that point it starts to go out of the range of user reporting and into the range of massive flattening of data for another storage unit.
If it's for users, you might want to look into a different approach. SSAS/PowerPivot may be where you want to take this in the end for the end users, as it'll be much easier for them to manipulate the data if it's already in a tool they'd just need to learn, instead of build from scratch.
What's the target for this data? I think if we knew the end result of the intent of the process some of the folks around here could either offer alternative approaches or perhaps better tools.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
October 9, 2012 at 6:15 pm
Thanks for the reply, figured this question was coming 😉
Its for building a fact table for an SSAS cube. I can't go into the solution is too much detail . But rest assured the result for the end user is exactly what they need, we only keep the data for 5yrs and its currently processing 18 months of data in under 1min (Including the 300 column pivot).
Assuming there is no hard ceiling on the number of columns, I suspect my only concern might be a server resource issue, but I have ideas on how I can partition the data anyway. But if I dont need to do that, then even better. After all, performance is only an issue if performance is actually an issue
October 9, 2012 at 6:33 pm
LOL, true enough.
Even if you run into a soft ceiling in memory, it won't be hard to work around (though over a certain # of columns you DO have a hard limit in any schema). Pivot into temp tables and then join the 1 to 1 temp tables for your 'additional columns' into the final select to feed the cube.
There's a hard limit on a nonwide tables (I didn't even know there WERE wide tables until 5 minutes ago) of 1,024 columns, with obviously the 8k limit/page.
However, for a quick and easy cheatsheet of all hard limits in the system (assuming you're in R2):
http://msdn.microsoft.com/en-us/library/ms143432(v=sql.105).aspx
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
October 9, 2012 at 8:36 pm
Nice info in that link, wide tables are also completely new to me.
So even if I can pivot X number of columns I can only store them in upto 1024 columns (or 30000 for wide) and they must keep within the 8K limit (I hadn't actually considered the 8K limit)
Good to know I have some wiggle room. The 8K/page limit shoudln't be a problem as all the data is numerical and typically a 50/50 mix of Int and Decimal(27,9), but I can convert the decimals to reals for this stage in the reporting, leaving plenty of headroom of the 8K limit. And as you say I can split the data up and create multiple tables and join them back when required.
Thanks again for the feedback, its given me some fat to chew. I feel a lot happier knowing what the expected limits will be, as at least now I can plan for growth around them. At least I considered the limits at 300, and 1023 😉
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply