locked
Microsoft SQL Server 2005 Compact Edition - Number of Columns per Table RRS feed

  • Question

  • I know the maximum columns per table in MS SQL Server 2005 (32-bit) is 1024. What is the maximum coulmn capacity in MS SQL 2005 Compact Edition (32-bit).

    • Moved by Mark Wilson-ThomasMicrosoft employee Tuesday, July 19, 2011 4:34 PM This question relates to SQL Server Compact Edition rather than the SQL Server Tools. Moving to more appropriate forum. (From:SQL Server Developer Tools - Codename “Juneau”)
    Tuesday, July 19, 2011 12:22 PM

Answers

    • Proposed as answer by Peja Tao Thursday, July 21, 2011 7:05 AM
    • Marked as answer by Peja Tao Tuesday, July 26, 2011 6:57 AM
    Wednesday, July 20, 2011 10:03 AM
  • Unless you have a very strong reason to have such a big number of columns on your table, this is the kind of question that I assume that when they are asked it means that something is not being done properly. SQL Server Compact performs quite well on desktop machines - at least when compared with mobile devices - and IMHO, instead of adding columns to my table, like in a table where you save car characterisitcs...

    ID, Owner, Brand, Model, Year, Color, Wheels, Cilinders, Capacity, Seats, Sunroof, Type, Radio, fuel, ...

    1, Alberto Silva, Land Rover, Discovery, 1998, Red, 16", 4, 2500, 7, 4x4, mp3, diesel, ...

    2. John Doe, Toyota, Prius, 2010, White, 15", 4, 1800, 5, hatch, dvd, hybrid, ...

    I would do it with two tables, like

    Cars

    ID, Owner, Brand, Model

    1, Alberto Silva, Land Rover, Discovery

    2. John Doe, Toyota, Prius

    Characterisitcis

    Car, Characteristic, Value

    1, year, 1998

    1, color, red

    1, wheels, 16"

    ...

    1, fuel, diesel

    ...

    2, year, 2010

    2, color, white

    ...

    2, fuel, hybrid

    ...

    (underline columns are Primary Keys)

    This gives you a lot of flexibility, allows to manage extra-data without changing table structure, makes records smallerr so easier to maintain at the file level, makes it easier to display information on detail pages with repeaters.

    Hope this helps!


    Alberto Silva Microsoft MVP - Device Application Development - http://msmvps.com/AlbertoSilva moving2u - R&D Manager - http://www.moving2u.pt
    • Proposed as answer by ErikEJMVP Wednesday, July 20, 2011 10:03 AM
    • Marked as answer by Peja Tao Tuesday, July 26, 2011 6:57 AM
    Tuesday, July 19, 2011 6:18 PM

All replies

  • Unless you have a very strong reason to have such a big number of columns on your table, this is the kind of question that I assume that when they are asked it means that something is not being done properly. SQL Server Compact performs quite well on desktop machines - at least when compared with mobile devices - and IMHO, instead of adding columns to my table, like in a table where you save car characterisitcs...

    ID, Owner, Brand, Model, Year, Color, Wheels, Cilinders, Capacity, Seats, Sunroof, Type, Radio, fuel, ...

    1, Alberto Silva, Land Rover, Discovery, 1998, Red, 16", 4, 2500, 7, 4x4, mp3, diesel, ...

    2. John Doe, Toyota, Prius, 2010, White, 15", 4, 1800, 5, hatch, dvd, hybrid, ...

    I would do it with two tables, like

    Cars

    ID, Owner, Brand, Model

    1, Alberto Silva, Land Rover, Discovery

    2. John Doe, Toyota, Prius

    Characterisitcis

    Car, Characteristic, Value

    1, year, 1998

    1, color, red

    1, wheels, 16"

    ...

    1, fuel, diesel

    ...

    2, year, 2010

    2, color, white

    ...

    2, fuel, hybrid

    ...

    (underline columns are Primary Keys)

    This gives you a lot of flexibility, allows to manage extra-data without changing table structure, makes records smallerr so easier to maintain at the file level, makes it easier to display information on detail pages with repeaters.

    Hope this helps!


    Alberto Silva Microsoft MVP - Device Application Development - http://msmvps.com/AlbertoSilva moving2u - R&D Manager - http://www.moving2u.pt
    • Proposed as answer by ErikEJMVP Wednesday, July 20, 2011 10:03 AM
    • Marked as answer by Peja Tao Tuesday, July 26, 2011 6:57 AM
    Tuesday, July 19, 2011 6:18 PM
  • This question is to know the is SQL server 2005 capacity is same as SQL server compact edition. Please tell me the maximum column and rows size for SQL server Compact Eidtion.
    Wednesday, July 20, 2011 5:11 AM
  • If you have installed SQL Server Compact Books Online, there's a section where you can find such information. There is also a online version on MSDN, later I can give you a direct link if you don't find it meanwhile.

     

     


    Alberto Silva Microsoft MVP - Device Application Development - http://msmvps.com/AlbertoSilva moving2u - R&D Manager - http://www.moving2u.pt
    Wednesday, July 20, 2011 8:08 AM
    • Proposed as answer by Peja Tao Thursday, July 21, 2011 7:05 AM
    • Marked as answer by Peja Tao Tuesday, July 26, 2011 6:57 AM
    Wednesday, July 20, 2011 10:03 AM