SQL Server Performance

Select first 50 columns from a table

Discussion in 'General Developer Questions' started by dineshasanka, Jun 7, 2005.

  1. dineshasanka Moderator

    Is there any easy way to select first 50 columns of a table<br />like Sele 1-50 from TableA<br />[<img src='/community/emoticons/emotion-3.gif' alt=':eek:' />)]
    dineshasanka, Jun 7, 2005
    #2

  2. Adriaan New Member

    It would be a nice extension to T-SQL to have a "column function" that you could use as an expression - you can probably create one for yourself in SQL 2005 ...

    Until then, you'll have to use scripts like this:

    DECLARE @TblName VARCHAR(100), @ColList VARCHAR(8000)

    SET @TblName = 'MYTABLENAME'
    SET @ColList = ''

    SELECT TOP 50 @ColList = @ColList + CASE WHEN LEN(@ColList) = 0 THEN '' ELSE ', ' END
    + c.[name]
    FROM dbo.syscolumns c
    WHERE c.[id] = OBJECT_ID(@TblName)
    ORDER BY c.colorder

    EXEC ('SELECT ' + @ColList + ' FROM dbo.' + @TblName)
    Adriaan, Jun 7, 2005
    #2

  3. FrankKalis Moderator

    Dinesh, you should know better [<img src='/community/emoticons/emotion-5.gif' alt=';)' />]<br /><br />--<br />Frank Kalis<br />SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />
    FrankKalis, Jun 7, 2005
    #2

  4. dineshasanka Moderator

    Well, I am not always 100% correct<br /><br />Actually this was asked by one of my friends. My first reaction was No. So I posted it here in case you have got some flash queries [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br />Tahnkx adriann
    dineshasanka, Jun 7, 2005
    #2

  5. Madhivanan Moderator

    How about this one?

    declare @s varchar(2000)
    set @s=''
    select @s=@s+','+name from syscolumns where colid in
    (select top 50 colid from syscolumns where id=object_id('tableA'))
    and id=object_id('tableA')
    select @s=right(@s,len(@s)-1)
    select @s

    Exec('select '+@s + ' from tableA')

    Madhivanan

    Failing to plan is Planning to fail
    Madhivanan, Jun 7, 2005
    #2

  6. FrankKalis Moderator

    50 columns in one table sounds ilke the schema might need some normalisation, if we talk here about OLTP. Have you tried already the INFORMATION_SCHEMA views?

    --
    Frank Kalis
    SQL Server MVP
    http://www.insidesql.de
    FrankKalis, Jun 7, 2005
    #2

  7. jastone New Member

    something else that would work if you needed just to get the names of columns, or if you have a long column list in one table.

    an "and" clause could help you find columns with a certain name

    SELECT
    top 50 name
    FROM
    syscolumns
    WHERE
    id =
    (SELECT
    id
    FROM
    sysobjects
    WHERE
    name= 'TABLE_NAME')
    ORDER BY
    colorder

    Joe Janka
    jastone, Jun 8, 2005
    #2

  8. ghemant Moderator

    Here again question comes in my mind that Y Dinesh asking such question ? he knows better ways to achieve this, select top 50 from tableA or information_schema !!!!<br />is their anything new ?[?]<br />[<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />hsGoswami<br />ghemant@gmail.com
    ghemant, Jun 8, 2005
    #2

  9. dineshasanka Moderator

    hsGoswami,
    Select top 50 from tableA gives you first 50 rows i want first 50 columns

    Joe Janka,
    thanks for the reply, actually this the ways that how it was done. But I am bit reluctant to use system tables, Anyway appreciate your response
    dineshasanka, Jun 8, 2005
    #2

  10. ghemant Moderator

    sorry, i mean to say if you need only the headings of the table's column then
    if you fire a statement : ' select top 0 * from tableA '
    it will return the column's heading .

    Regards.

    hsGoswami
    ghemant@gmail.com
    ghemant, Jun 9, 2005
    #2

  11. Madhivanan Moderator

    Dinesh, any comments on the Script I have given? [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail
    Madhivanan, Jun 9, 2005
    #2

  12. SanetteWessels New Member

    Hi Madhivanan<br /><br />I ran your script and it works cool. You only need to add <font color="blue">order by colorder</font id="blue">, or it could happen that it does not return the column names in the same order as it is specified in the table. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />Sanette<br /><br />SQL Developer<br />JustEnough Software Corporation<br /><i><font size="1">Walking on water and coding of a spec is easy, as long as both are frozen - A friend</font id="size1"></i>
    SanetteWessels, Jun 9, 2005
    #2

  13. FrankKalis Moderator

    FrankKalis, Jun 9, 2005
    #2

  14. Madhivanan Moderator

    quote:
    You only need to add order by colorder, or it could happen that it does not return the column names in the same order as it is specified in the table.

    No It selects top 50 in correct order (1 to 50)

    quote:
    Just be aware that such aggregate concatenation queries can be tricky at times. Especially when there is an ORDER BY involved.
    http://support.microsoft.com/default.aspx?scid=kb;EN-US;287515
    Frank, that one is important to note when using Order by in Dynamic Clause



    Madhivanan

    Failing to plan is Planning to fail
    Madhivanan, Jun 9, 2005
    #2

  15. FrankKalis Moderator

    The article doesn't mention dynamic SQL. Anyway, all I wanted to say is, that you need to check carefully for a correct result.

    --
    Frank Kalis
    SQL Server MVP
    http://www.insidesql.de
    FrankKalis, Jun 9, 2005
    #2

  16. SanetteWessels New Member

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"> quote:<br />--------------------------------------------------------------------------------<br /><br />You only need to add order by colorder, or it could happen that it does not return the column names in the same order as it is specified in the table. <br /><br />--------------------------------------------------------------------------------<br /><br /><br /><br />No It selects top 50 in correct order (1 to 50)<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br />Sorry, I beg to differ. [<img src='/community/emoticons/emotion-5.gif' alt=';)' />] I checked it in a table that we have and changed the top 50 to top 10 as we do not have tables wider than +/- 20 fields... the result without the order by clause missed fields that got added later into the table. See the difference between the 2 result sets in bold....<br /><br /><u>Without Order by:</u><br />InventoryID,ItemMasterID,FreightMethodID,CreationDate,FirstStockedDate,LastStockCountDate,LastIssueDate,LastReceiptDate,<b>Seasonality,ActiveFlag</b><br /><br /><u>With Order by:</u><br />InventoryID,<b>SiteID</b>,ItemMasterID,FreightMethodID,CreationDate,FirstStockedDate,LastStockCountDate,LastIssueDate,LastReceiptDate,<b>UnitOfIssue</b><br /><br />Table layout scripted:<br /><br />if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tbl_Inventory]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)<br /> BEGIN<br />CREATE TABLE [tbl_Inventory] (<br />[InventoryID] [int] IDENTITY (1, 1) NOT NULL ,<br /><b>[SiteID]</b> [int] NOT NULL , <font color="red"> Field not showing when <b>order by</b> clause is <b>not</b> used </font id="red"><br />[ItemMasterID] [int] NOT NULL ,<br />[FreightMethodID] [int] NOT NULL ,<br />[CreationDate] [datetime] NOT NULL ,<br />[FirstStockedDate] [datetime] NULL ,<br />[LastStockCountDate] [datetime] NULL ,<br />[LastIssueDate] [datetime] NULL ,<br />[LastReceiptDate] [datetime] NULL ,<br /><b>[UnitOfIssue]</b> [varchar] (50) COLLATE Latin1_General_CI_AS NULL <font color="red"> Field not showing when <b>order by</b> clause is <b>not</b> used </font id="red"><br /><br /><b>[Seasonality]</b> [int] NOT NULL CONSTRAINT [DF_tbl_Inventory_Seasonality] ,<font color="green"> Field displays incorrectly when NO order by is used </font id="green"><br /><b>[ActiveFlag]</b> [tinyint] NOT NULL CONSTRAINT [DF_tbl_Inventory_ActiveFlag] ,,<font color="green"> Field displays incorrectly when NO order by is used </font id="green"><br />[UnitOfMeasure] [varchar] (50) COLLATE Latin1_General_CI_AS NULL,<br />etc........<br /><br />Kind regards<br />Sanette<br /><br /><br /><br />SQL Developer<br />JustEnough Software Corporation<br /><i><font size="1">Walking on water and coding of a spec is easy, as long as both are frozen - A friend</font id="size1"></i>
    SanetteWessels, Jun 10, 2005
    #2

  17. Madhivanan Moderator

    Agreed.
    The subquery should have Order by colid


    Madhivanan

    Failing to plan is Planning to fail
    Madhivanan, Jun 10, 2005
    #2

  18. dineshasanka Moderator

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by Madhivanan</i><br /><br />Dinesh, any comments on the Script I have given? [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br />Yes function is working.
    dineshasanka, Jun 10, 2005
    #2

Share This Page