Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Feb 2005
    Posts
    88

    Unanswered: Help with "Create View" statement and Eorror Message

    Hi all,
    I am trying to create a view with approx. 3000 columns... and got the following error message:

    "CREATE VIEW failed because column 'HSEPRIN' in view 'MyTestView' exceeds the maximum of 1024 columns.

    Is it mean the max number of columns for each table is 1024? I thought in SQL server the table can contain as much information as possible.
    Anyone can help to answer my question?

    Thank you in advance.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    As much information vertically, not horizontally.

    Frankly, if you are trying to create a view with 3000 columns, the problem is in your design, not SQL Server's limitations!

    Why are you doing this? Maybe somebody here can find a better approach for you to take.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    No, a single row in a table can only contain a bit short of 8 kilobytes. A given row in a result set (therefore in a view) can only contain 1024 columns, and there are some limitations on the 1024th column.

    That said, how on earth would you make use of a view that wide ?!?! What would you do with it ?

    -PatP

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I definitely recommend printing it on legal-size paper set to landscape orientation, using Arial Narrow font.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    You can you go over the row limit in a physical table but you just get some warning about inserts and updates. See it often in poor designs or lack there of.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  6. #6
    Join Date
    Feb 2005
    Posts
    8

    ..

    Are you by any chance trying to crosstab that 40-year history you were talking about in that other post? That's the only thing I can think of that would give you that many columns.

  7. #7
    Join Date
    Feb 2005
    Posts
    88
    I do not have to see/create all the columns. However I would like to know the limitation. I just tried running the same query again for 950 columns which was succussful.
    Maybe I have to run 3000 columns separately to create 3000/950 views. Can I union them together as a one object/something? In addition,
    How am I going to update number of views on daily basis?

  8. #8
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    All kidding aside.

    Why don't you provide us with some more information about what exactly you are trying to do? Of course the ddl might be too much 411. But if you give us enough info one of might come up with something or at least some advice.

    Some of the folks in this forum are as smart as they think they are. Myself excluded. I am as dumb as I seem. darrrrrrrrrrrrrrrrrrrrrrr!!!!!!!!!!
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  9. #9
    Join Date
    Feb 2005
    Posts
    88
    This post is related to the one "updating daily information in a history table (was "Help-Brainstorming")"... which provides details.

    Sorry about the confusion. And thank you for the help.

  10. #10
    Join Date
    Feb 2005
    Posts
    88
    Good Morning All,
    Hope you all had a great weekend!
    I think I am probably asking a silly question but...
    I created a view by using the following statement:

    create view TestView1
    as
    select date as Date,
    XXXXX= sum(case when ID='XXXXX' then Field1 else 0 end),
    YYYYY= sum(case when ID='YYYYY' then Field1 else 0 end)
    from MyTable
    Group by Date

    The structure of MyTable is:
    Date(datetime) ID(char 10) Field1(float)
    1/1/65 XXXXX -999.999
    1/4/65 XXXXX -999.999
    ...
    2/24/05 XXXXX 500
    2/25/05 XXXXX 550
    1/1/65 YYYYY -999.999
    1/4/65 YYYYY -999.999
    ...
    2/24/05 YYYYY 600
    2/25/05 YYYYY 650

    when I run "select * from TestView order by date"
    The actual results I got:
    Date XXXXX YYYYY
    1/1/65 0.0 0.0
    1/4/65 0.0 0.0
    ...
    2/24/05 500 600
    2/25/05 550 650

    This is the results I should expect:
    Date XXXXX YYYYY
    1/1/65 -999.999 -999.999
    1/4/65 -999.999 -999.999
    ...
    2/24/05 500 600
    2/25/05 550 650


    What is wrong with my create view statement? Do I have to specify the datatype?

    Thank you for the help in advance.

  11. #11
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    What is wrong with it?

    The question is, "What good is it?"

    What is the practical use of a view with 3000 cross-tabbed columns? You can't print it. You can't display it. You can't use it practically in any other views or procedures.

    What are you planning to do with this?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  12. #12
    Join Date
    Feb 2005
    Posts
    88
    This is going to be the data source for another application (Matlab).
    And this is desired format. It does not matter if I can display them all as long as I can display partially to make sure the information is there and the expected data format.

    Should I put information into a table instead of View?

    Thank you for the help!

  13. #13
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    MatLab can't accept normalized data?

    And it can't do its own crosstabs?

    That is pretty weak.

    I'm sorry, but I just can't suggest any solution along the lines you are thinking, because I think it is going to cause you severe problems in the future.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  14. #14
    Join Date
    Feb 2005
    Posts
    88
    Matlab is a statistic package to do math calculation and generate graphs. It may take 5+ hours to run the results therefore I am thinking to use SQL to generate the expected data source format to feed into Matlab.
    If you think the only possible solution should be on Matlab side, I guess I have to work on that.
    However, do you know why my actual result from my sql statement shows data as "0.0" instead of expected "-999.999" which is stored in the table?

    Any suggestion and comments are truely appreciated!
    shiparsons

  15. #15
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I don't have that problem..you probably shouldn't be using float though...

    Code:
    USE Northwind
    GO
    
    SET NOCOUNT ON
    CREATE TABLE myTable99([ID] varchar(15), [Date] datetime, Field1 float) 
    GO
    
    INSERT INTO myTable99([Date],[ID],Field1)
    SELECT '1/1/65',  'XXXXX', -999.999 	UNION ALL
    SELECT '1/4/65',  'XXXXX', -999.999 	UNION ALL
    SELECT '2/24/05', 'XXXXX', 500 		UNION ALL
    SELECT '2/25/05', 'XXXXX', 550 		UNION ALL
    SELECT '1/1/65',  'YYYYY', -999.999 	UNION ALL
    SELECT '1/4/65',  'YYYYY', -999.999 	UNION ALL
    SELECT '2/24/05', 'YYYYY', 600 		UNION ALL
    SELECT '2/25/05', 'YYYYY', 650
    GO
    
    CREATE VIEW myView99
    AS
      SELECT   [Date]
    	 , SUM(CASE WHEN [ID]='XXXXX' THEN Field1 ELSE 0 END) AS X
    	 , SUM(CASE WHEN [ID]='YYYYY' THEN Field1 ELSE 0 END) AS Y
        FROM MyTable99
    GROUP BY [Date]
    GO
    
    SELECT * FROM myView99
    GO
    
    SET NOCOUNT OFF
    DROP VIEW myView99
    DROP TABLE myTable99
    GO
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

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