| Author |
 Topic  |
|
|
thendraljazz
Starting Member
India
26 Posts |
 Posted - 05/26/2012 : 00:12:41
|
Hi,
My table creating columns dynamically and now the table exceeds the row size that is 1024 bytes.i got the following error,
CREATE TABLE failed because column 'Merchant147' in table 'merchantlists' exceeds the maximum of 1024 columns.
so how can i redesign my database?
can any one know the idea?
can any one help me to solve this? |
Edited by - thendraljazz on 05/26/2012 00:16:32
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47023 Posts |
Posted - 05/26/2012 : 00:21:51
|
why should a table have more than 1024 columns? what exactly are you storing in those columns? seems like design issue to me.
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
 |
|
|
thendraljazz
Starting Member
India
26 Posts |
Posted - 05/26/2012 : 00:28:46
|
| i am storing the details like price,merchant,shipping,tax who is having highest merchant based on the product_id. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47023 Posts |
Posted - 05/26/2012 : 00:31:01
|
ok..so are you telling you've more than 1024 attributes for every product?
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
Edited by - visakh16 on 05/26/2012 00:31:20 |
|
|
|
thendraljazz
Starting Member
India
26 Posts |
Posted - 05/26/2012 : 00:34:54
|
| not every product..based on the input or client the row size is differing |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47023 Posts |
Posted - 05/26/2012 : 00:48:18
|
then why not store them as rows in another table. there will two fields against each ProductID Attribute which denotes what attribute it is and Value which stores actual attribute value. This is scalable also as you can add as many attributes as you want for each product in this method as its just addition of another record in this table.
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
thendraljazz
Starting Member
India
26 Posts |
Posted - 05/26/2012 : 01:15:23
|
| ok..thanks |
|
|
|
thendraljazz
Starting Member
India
26 Posts |
Posted - 05/26/2012 : 01:16:03
|
| is there any other idea? |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 05/26/2012 : 03:45:15
|
You can try SPARSE columns. Then you can have 32000+ columns.
N 56°04'39.26"
E 12°55'05.63" |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47023 Posts |
|
|
prett
Posting Yak Master
USA
161 Posts |
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 05/28/2012 : 06:42:11
|
Please don't reference Community Technology Previews (CTPs) in the future.
They are not feature-completed. Always reference the latest build (including SPs).
The number of sparse columns in a table (wide table) has been 30,000 since SQL Server 2008.
http://msdn.microsoft.com/en-us/library/ms143432(v=sql.100).aspx
N 56°04'39.26"
E 12°55'05.63" |
|
|
|
Transact Charlie
Flowing Fount of Yak Knowledge
United Kingdom
3425 Posts |
Posted - 05/28/2012 : 12:32:38
|
even with sparse columns, sqlserver isn't really designed to support many, many, columns. Especially dynamic ones. It goes against the relational database model.
thendraljazz -- you said that you would be willing to redesign your architecture. I think you should.
why not post some more information on what you are doing and what you actually want to do? We could advise on a more normalised approach.
Or, maybe consider a column store type db like cassandra instead. All depends on what you are trying to do.
Transact Charlie
Msg 3903, Level 16, State 1, Line 1736:
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION
http://nosqlsolution.blogspot.co.uk/ |
|
|
|
Sachin.Nand
Flowing Fount of Yak Knowledge
2937 Posts |
Posted - 05/29/2012 : 03:02:06
|
32000 columns in a table..
I hope CELKO does not bumps into this thread.
After Monday and Tuesday even the calendar says W T F .... |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 05/29/2012 : 03:15:25
|
It's quite useful.
I use it in one table where we store questionnaires. Some 10% of the questions are the same, the rest are different.
That's why SPARSE is so effective, because if the column is not needed, it takes no storage space.
N 56°04'39.26"
E 12°55'05.63" |
|
|
| |
Topic |
|
the table exceeds the row size.how can i solve thi
