Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2003
    Posts
    21

    Question Unanswered: Is it possible to access COLUMNS using a variable index (like in ADO Recordsets)

    Im working on a database design which is meant to sustain heavy insert loads. To reduce the number of records and joins I want to "flatten" the data into wide tables.

    can I do something like this?

    DECLARE @Index TINYINT

    Select tablename.column(@Index) FROM tablename?

    (of course in another syntax this is just an example)

    Maybe its possible with Cursors

    Fetch Next From CursorX(@index) INTO @SomeVariable

    I dont want to use dynamic SQL (and create the entire query dyamically as a string) since that will cost me the entire precomplication advantage, Or open a cursor for each set of records separately (thats even worse).

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    149

    Re: Is it possible to access COLUMNS using a variable index (like in ADO Recordsets)

    Personally, I would try to flatten the database design for OLAP and not for OLTP. So may be it would be better if you can describe in more detail, what you are trying to do. Good you are trying to avoid Dynamic SQL. In addition to re-compile every time, Dynamic SQL requires you to give permissions on the base tables Vs just the SPs/Fns.

    Merry Christmas

    - cbarus


    Originally posted by ldba
    Im working on a database design which is meant to sustain heavy insert loads. To reduce the number of records and joins I want to "flatten" the data into wide tables.

    can I do something like this?

    DECLARE @Index TINYINT

    Select tablename.column(@Index) FROM tablename?

    (of course in another syntax this is just an example)

    Maybe its possible with Cursors

    Fetch Next From CursorX(@index) INTO @SomeVariable

    I dont want to use dynamic SQL (and create the entire query dyamically as a string) since that will cost me the entire precomplication advantage, Or open a cursor for each set of records separately (thats even worse).

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i would not flatten the design for OLAP if the volume of inserts is high -- in that case i would normailze for insertions

    sbaru, it is not necessary always to quote entire posts you're replying to, because they're located in the same thread, especially if yours is the first reply

    ldba, the precompilation "disadvantage" of dynamic sql is insignificant when compared to the disadvantage of horribly performing queries due to poor design

    reducing the number of records and joins by using wider tables just makes the heavy insertion load even heavier


    rudy

Posting Permissions

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