| Author |
Topic |
|
thendraljazz
Starting Member
26 Posts |
 Posted - 2012-05-26 : 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? |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-05-26 : 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 MVPhttp://visakhm.blogspot.com/ |
 |
|
|
thendraljazz
Starting Member
26 Posts |
Posted - 2012-05-26 : 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
52326 Posts |
Posted - 2012-05-26 : 00:31:01
|
| ok..so are you telling you've more than 1024 attributes for every product?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
thendraljazz
Starting Member
26 Posts |
Posted - 2012-05-26 : 00:34:54
|
| not every product..based on the input or client the row size is differing |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-05-26 : 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 MVPhttp://visakhm.blogspot.com/ |
|
|
|
thendraljazz
Starting Member
26 Posts |
Posted - 2012-05-26 : 01:15:23
|
| ok..thanks |
|
|
|
thendraljazz
Starting Member
26 Posts |
Posted - 2012-05-26 : 01:16:03
|
| is there any other idea? |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2012-05-26 : 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
52326 Posts |
|
|
prett
Posting Yak Master
212 Posts |
Posted - 2012-05-28 : 06:07:34
|
| Yes, you can use Sparse Columns feature which don't get you over the 1024 column limit, but could mean you can squeeze more columns into the 8,060 byte row limit for SQL Server. But this feature is updated in SQL Server 2008.Ref: http://blogs.technet.com/b/andrew/archive/2008/02/28/sql-server-2008-sparse-columns.aspx |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2012-05-28 : 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
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2012-05-28 : 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 CharlieMsg 3903, Level 16, State 1, Line 1736:The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION http://nosqlsolution.blogspot.co.uk/ |
|
|
|
Sachin.Nand
2937 Posts |
Posted - 2012-05-29 : 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
30421 Posts |
Posted - 2012-05-29 : 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" |
|
|
|
|
the table exceeds the row size.how can i solve thi