SQL Server Performance

row_id in sql server

Discussion in 'General Developer Questions' started by cutiepie, Dec 2, 2005.

  1. cutiepie New Member

    Hi all,
    i have a task that whenever i will write a select query then when the result comes then i should be able to write a column that should write the ROW-ID of these rows. I mean for example i write a select query that gives me the following tablethe query can be like select bill_id,name from myTable order by name)
    Bill_id name
    25 abc
    18 def
    26 ghi
    23 adfd

    but my result should look like:
    Row_Id Bill_id name
    1 25 abc
    2 18 def
    3 26 ghi
    4 23 adfd

    n this Row_Id should be generated automatically....even if when i will write order by name in reverse order then also it should write the row_id as 1,2,3,4 same increasing order...n this automatic function i have to make...i cant understand..if there exist some procedure or function that already does this...or how can i just find the simple ROW_ID in general?????
    any help will be greatly appreciated.
    Regards.
  2. Madhivanan Moderator

    Where do you want to show these data?
    If you use Front End Application, use For or While loop to generate the Serial No

    Madhivanan

    Failing to plan is Planning to fail
  3. Adriaan New Member

    This is a feature that has been added to SQL 2005.

    In SQL 2000, there are two ways to fake it ...


    (1) Insert your data into a temporary table with the IDENTITY(INT) function, like this:

    SELECT IDENTITY(INT) AS RowId, MyTable.*
    INTO #T
    FROM MyTable

    SELECT * FROM #T

    DROP TABLE #T



    (2) Use a TABLE variable -

    DECLARE @WorkingTable TABLE (RowID INT IDENTITY(1,1), BillId INT, Name VARCHAR(10))

    INSERT INTO @WorkingTable (BillId, Name)
    SELECT BillId, Name
    FROM MyTable
    ORDER BY .....................

    SELECT * FROM @WorkingTable



    Nr 2 is ideal for a smaller number of rows.
    Nr 1 allows you to add and use indexes if you need to JOIN the results, or filter.
  4. cutiepie New Member

    oh THANK YOU SOOOOOOOO MUCH...!!!it works...<img src='/community/emoticons/emotion-1.gif' alt=':)' /><br />regards.
  5. FrankKalis Moderator

  6. cutiepie New Member

    hi,
    now regarding the same question..i have another problem...now i have to make a function in which the input parameter is a select statement i.e. any string and in that select statement i have to put a ROWID....that means i have to make a function that takes the select statement that has some table mentioned in it..n then i have to output the result as a table with rowid in it...how can i do that?????
    regards.
  7. druer New Member

    Assuming you have the parameter @Command come in to your function/stored procedure. That would look like so: "select whatever from whatever where whatever order by whatever"

    You could do the following:
    select @Command = Replace(@Command, 'select', 'select identity(INT) RowId,')
    select @Command = Replace(@Command, 'from', 'into #T from')
    select @Command = @Command + ' select * from #T drop table #T'

    The idea being that you simply replace the word select, with the phrase that you've already seen will end up yielding the row id, then you replace the word from with the part that will stick the results into your temp table, then you append the text that will return your results from the temp table which would be all of your requested results plus the row id.

    Hope it helps

    PS - If you have any problems with the "Select * from #t" or "drop table #t" you might need to add a carriage return line and line feed chr(13)+chr(10)
  8. Madhivanan Moderator

    Generating Row number using the query will become time consuming one if there are millions of data. You didnt tell us where you want to show data. If you use Reports make use of Record Number feature. Otherwise have identity column in the table and do query accordingly

    Madhivanan

    Failing to plan is Planning to fail
  9. cutiepie New Member

    Hi,
    Thanks a lot druer..i will try on monday if it works...n Madhivanan thanks for the suggestion n there r millions of data there...how to use record number...just curious to know...although i will try everythin on monday..
    thanks once again to all,
    Regards.
  10. Madhivanan Moderator

    If you dont have Identity column, better to have it

    Madhivanan

    Failing to plan is Planning to fail
  11. cutiepie New Member

    hi,i m not able to c the table data....i mean my procedure looks like this:<br />-- =============================================<br />ALTER PROCEDURE [dbo].[Get_Identity_Column]<br />( @p_select_command varchar(2)<br /> --@p_return_query TABLE output <br />)<br /> <br />AS<br />BEGIN <br />select @p_select_command = Replace(@p_select_command, 'select', 'select identity(INT) RowId,')<br />select @p_select_command = Replace(@p_select_command, 'from', 'into #TempTable from')<br /> --select @p_return_query = @p_select_command<br />select @p_select_command = @p_select_command + ' select * from #TempTable drop table #TempTable'<br /> exec @p_select_command<br />END<br />GO<br />-----------------------------------------------------<br />now how can i see the table...as TempTable does not exist...n i want that when i execute it then it should show me the result automatically...but it doesnt work like that <img src='/community/emoticons/emotion-6.gif' alt=':(' /> it just says that <br />---------------------<br />Msg 2812, Level 16, State 62, Procedure Get_Identity_Column, Line 22<br />Could not find stored procedure 'se'.<br /><br />(1 row(s) affected)<br />-----------------------------<br />how can i deal with this problem now????
  12. FrankKalis Moderator

    What do you expect to see, when your variable @p_select_command is a varchar<b>(2)</b>? [<img src='/community/emoticons/emotion-5.gif' alt=';)' />]<br /><br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a><br />Ich unterstütze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>) <br />
  13. cutiepie New Member

    oh thanks for pointin this mistake....i was just wondering y is it showing SE only again n again...but still it didnt solve my problem <img src='/community/emoticons/emotion-6.gif' alt=':(' /> ...now with the code:<br />--------------------<br />ALTER PROCEDURE [dbo].[Get_Identity_Column]<br />( @p_select_command varchar(2000)<br /> --@p_return_query TABLE output <br />)<br /> <br />AS<br />BEGIN <br />select @p_select_command = Replace(@p_select_command, 'select', 'select identity(INT) RowId,')<br />select @p_select_command = Replace(@p_select_command, 'from', ' into #TempTable from ')<br /> exec @p_select_command<br />select @p_select_command = @p_select_command + ' select * from #TempTable drop table #TempTable'<br /> --exec @p_select_command<br />END<br />GO<br />-----------------------------------<br />it gives me error as:<br />Msg 2812, Level 16, State 62, Procedure Get_Identity_Column, Line 22<br />Could not find stored procedure 'select identity(INT) RowId, bill.* into #TempTable from bill select * from #TempTable drop table #TempTable'.<br /><br />(1 row(s) affected)<br />------------------------------<br />now wat to do with this?????<br />while executing i m just writting <br />select bill.* from bill....<br />waitin for your reply...<br />regards.
  14. Madhivanan Moderator

    Still you didnt tell us Where you want to show these data

    Madhivanan

    Failing to plan is Planning to fail
  15. cutiepie New Member

    i just want to show the data only on the executed screen....i mean the QUERY WINDOW...just when i will execute:
    ----------------------
    USE [Telbill]
    GO

    DECLARE@return_value int

    EXEC@return_value = [dbo].[Get_Identity_Column]
    @p_select_command = N'select bill.* from bill'

    SELECT'Return Value' = @return_value

    GO
    ------------------------
    then just after pressing EXECUTE...it should show me the tables...
    regards.
  16. Madhivanan Moderator

    >>i just want to show the data only on the executed screen....i mean the QUERY WINDOW...just when i will execute:

    Well.
    Do you want to export the result?
    What is the purpose of showing this in QA?

    Madhivanan

    Failing to plan is Planning to fail
  17. cutiepie New Member

    i dont want to export result...the purpose is just to check the functionality of my procedure...if it works fine or not...if its showing me the correct selected table or not...n its not a front end application...its just a normal procedure that takes as the input a select command and shows the result with an identity column...thats all...
  18. Madhivanan Moderator

    If it is only for the testing purpose try this


    Alter PROCEDURE [dbo].[Get_Identity_Column]
    ( @p_select_command varchar(2000)
    )

    AS
    BEGIN
    Exec('select identity(int,1,1) as table_id,* into #t from ('+@p_select_command+')T
    Select * from #t')
    END
    GO

    Madhivanan

    Failing to plan is Planning to fail
  19. Adriaan New Member

    Keep a straight line on what stuff you're passing to-and-from any procedure, and how you're doing it. To return a value from an SP, you're better off using the OUTPUT keyword. You need to add it to the parameter definition and on the line where you call the procedure.

    Last point: you still had an active call to exec @p_select_command in your SP body, that's what was throwing the error.

    CREATE PROCEDURE [dbo].[Get_Identity_Column]
    ( @p_select_command varchar(2000) OUTPUT)

    AS
    BEGIN
    select @p_select_command = Replace(@p_select_command, 'select', 'select identity(INT) RowId,')
    select @p_select_command = Replace(@p_select_command, 'from', ' into #TempTable from ')
    select @p_select_command = @p_select_command + ' select * from #TempTable drop table #TempTable'
    END
    GO

    DECLARE @return_value varchar(2000)

    SET @return_value = N'select bill.* from bill'

    EXEC [dbo].[Get_Identity_Column] @return_value OUTPUT

    SELECT @return_value

    GO

    DROP PROCEDURE [dbo].[Get_Identity_Column]
    GO
  20. cutiepie New Member

    Thank you very much Madhivanan and Adriaan....that was a lot of help...thanks a lot...it worked finally [<img src='/community/emoticons/emotion-2.gif' alt=':D' />]<br />Regards.
  21. Ongura New Member

    The easiest solution to all your problem is to customise the query below and your problem is solved
    select rank=count(*), a1.au_lname, a1.au_fname
    from authors a1, authors a2
    where a1.au_lname + a1.au_fname >= a2.au_lname + a2.au_fname
    group by a1.au_lname, a1.au_fname
    order by rank
  22. Madhivanan Moderator

    It would become ineffecient if there are millions of rows in a table

Share This Page