Results 1 to 8 of 8
  1. #1
    Join Date
    Aug 2002
    Posts
    45

    Unanswered: Max Number of feilds

    hi all

    having worked with access in the past and getting stumped by the fact a table could only hold 256 feilds....... does sql server have any such limit????

    thanks

    cq
    Sent By Royal Mail

  2. #2
    Join Date
    Mar 2004
    Posts
    80
    1024 columns per base table.
    see Maximum Capacity Specifications in BOOKS ON LINE for more details

  3. #3
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    http://www.sqlteam.com/Item.ASP?ItemID=684 for information on partitioning the data in the tables, if the limit of 1024 columns are not sufficient in your project.

    HTH
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  4. #4
    Join Date
    Aug 2002
    Posts
    45
    excellent thankyou both....

    i think that should be enough!!!!!!
    Sent By Royal Mail

  5. #5
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    No offense, but if you have that many columns in a table, you have a serious design issue. Searching against that table and the tables inefficient use of memory when that big will kill you. Can you just redesign the table?
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  6. #6
    Join Date
    Dec 2002
    Location
    Antwerp, Belgium
    Posts
    227
    That depends on what's in the tables. Our company has a data-entry department that enters for example surveys for our clients. We reach the 256 limit regularly, because of the number of questions. And sure, you can split the survey into parts, but this complicates the programs that are used to enter and view the resulting information.
    Johan

  7. #7
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    There are cases where you could possibly need that many. It's still more efficient to break it into smaller pieces though. Take a large table and break it into several small pieces in a one-to-one relation. Put the driving information you will search by in one table. Now query the two designs and tell me which one is more efficient.

    There are times when it makes sense, but not very often.
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I would strongly suggest looking at making a small logical change, that leads to a largish code change, that leads to making this problem (and many others) a moot point.

    Consider storing your data in multiple tables. Instead of one table that holds everything, have a separate table for respondant, one for survey information, possibly one for questions, and definitely one for answers. This allows a practical infinity of questions for any survey, and it allows you to quickly and easily change a survey to include new questions/answers as needed!

    This design process is called normalization. It becomes VERY important as your databases grow larger and more complex.

    -PatP

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •