none
added ID is always on the top.

    Question

  • I added two records into two tables with relationship.

    table 1: NameId, Date, col3... (NameId has date type integer)

    table 2 NameId, Date, col3..

    in my stored procedure:

    DECLARE @NameID int

    DECLARE @date smalldatetime

    SET @NameID = (SELECT MAX(NameID) FROM dbo.table1)
    SET @date = (SELECT Date FROM dbo.table1 WHERE NameID = @NameID)

    INSERT INTO dbo.table2([NameID], [Date], [col3]) VALUES(@NameID, @date, @col3)

    procedure can be executed without problems. But the record I added into table2 is alway on the top (first row) which should be the last one in the table.

    Do I miss the datatype conversion?  How to fix it?

    Thanks.

    I am very new in this field. Your help is appreciated.

    Tuesday, July 02, 2013 3:43 PM

Answers

  • A key concept in database development is to think of a table of an unordered set of rows rather than like rows in a spreadsheet, where rows are ordered.  An RDBMS like SQL Server stores and retrieve rows as efficiently as possible.  The only way to guarantee results are returned in order is with an ORDER BY clause (in your case, ORDER BY NameID).

    You can create an index on the column(s) in SQL Server so that SQL Server maintains a logically ordered b-tree index to facilitate locating and sorting by index key.  But it is still required to specify ORDER BY to guarantee result ordering because the contract is an unordered result set unless otherwise specified.

     


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    • Proposed as answer by Kalman TothModerator Wednesday, July 03, 2013 12:45 PM
    • Marked as answer by 98108 Wednesday, July 03, 2013 8:33 PM
    Wednesday, July 03, 2013 12:26 PM

All replies

  • Hello,

    If table2 is a heap and there is no order by clause in the select query when you retrieve the data then the data will come back in an unordered and random way. You can use an order by clause or put a clustered index to force order.

    -Sean


    Sean Gallardy | Blog | Twitter

    Tuesday, July 02, 2013 3:53 PM
  • thanks. table2's NameID field is always ordered. which like: 1, 2, 3, 4, ...
    Tuesday, July 02, 2013 4:18 PM
  • Stop programming; you are doing everything wrong. You got the basic  terms wrong. Rows are not records, columns are not fields and tables are not files. You have no idea how to name a data element. Your failure to post DDL is very, very rude.

    Think about how silly “name_id” is; an attribute can be a “<something>_name” or “<something>_id” under the ISO-11179 rules, but not that list of attribute properties without an attribute. DATE is a reserved word and too vague to be a column! 

    >> I added two records [sic] into two tables with relationship.

    CREATE TABLE Alphas
    (alpha_id INTEGER NOT NULL PRIMARY KEY, 
     foobar_date DATE NOT NULL, 
     );

    We never have tables with the same structure. A table models a completed set, so there can be only one of it! Tables are not files. 

    We do not use local variables in declarative programming. That was BASIC, FORTRAN, COBOL, etc. Instead of the local variable, @alpha_id, we would use the scalar expression: 

    SET @foobar_date 
      = (SELECT foobar_date 
          FROM dbo.Alphas 
         WHERE alpha_id = (SELECT MAX(foobar_date FROM Alphas)));

    But look! We do not need @foobar_date, either! Why create all that garbage? 


    INSERT INTO Beta
    SELECT alpha_id, foobar_date, .. 
      FROM Alphas
     WHERE foobar_date = (SELECT MAX(foobar_date) FROM Alphas);

    But this is a total waste. Put this into a VIEW and it will be correct every time, not waste disk and perform much better. 

    >>  But the record [sic] I added into Beta is always on the top (first row) [sic: rows have no ordering!!] which should be the last [sic] one in the table.<<

    Tables have no ordering; this makes no sense. Again, you are missing the fundamentals. The best book for a complete noob is MANGA GUIDE TO DATABASE, then you can try a more advanced book. I have been teaching SQL for a few decades; I will guess that you are 2-3 years away from being able to write simple SQL because you have no foundations. 



    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Tuesday, July 02, 2013 4:29 PM
  • I know I am new and willing to learn something new. I knew Date is reserved but the attributes and tables were already there for years. I am not trying to create tables. the table2's attribute3 is not the same as table1. so how to insert it into the table2 using your example:

    INSERT INTO Beta
    SELECT alpha_id, foobar_date, .. 
      FROM Alphas
     WHERE foobar_date = (SELECT MAX(foobar_date) FROM Alphas);

    Thanks.


     
    Tuesday, July 02, 2013 8:27 PM
  • INSERT INTO dbo.table2([NameID], [Date], [col3]) VALUES(@NameID, @date, @col3)

    procedure can be executed without problems. But the record I added into table2 is alway on the top (first row) which should be the last one in the table.

    A table by definition is an unordered set of rows.  If you need rows returned in a particular sequence, you must specify ORDER BY when selecting from the table.

    That said, you might get lucky when the optimizer and storage engine happen to decide to return data in a logical order by a particular index that happens to be the order you want.  But that behavior should not be relied upon as it can change depending on a number of factors, such as parallelism, server activity, service packs, versions, etc.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Wednesday, July 03, 2013 1:09 AM
  • thanks. As the data inserted is always the next number (NameID is integer starts at 1, so if there are 99 records, then the next record on attribute NameID will be 100). That is why, it should always on the bottom. But when I execute the stored procedure, it shows the record on the top. If I do select, Then it goes to bottom. Do not know why. It is my second stored procedure and I am new in this field.

    Thanks.

    Wednesday, July 03, 2013 3:02 AM
  • A key concept in database development is to think of a table of an unordered set of rows rather than like rows in a spreadsheet, where rows are ordered.  An RDBMS like SQL Server stores and retrieve rows as efficiently as possible.  The only way to guarantee results are returned in order is with an ORDER BY clause (in your case, ORDER BY NameID).

    You can create an index on the column(s) in SQL Server so that SQL Server maintains a logically ordered b-tree index to facilitate locating and sorting by index key.  But it is still required to specify ORDER BY to guarantee result ordering because the contract is an unordered result set unless otherwise specified.

     


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    • Proposed as answer by Kalman TothModerator Wednesday, July 03, 2013 12:45 PM
    • Marked as answer by 98108 Wednesday, July 03, 2013 8:33 PM
    Wednesday, July 03, 2013 12:26 PM
  • Since you have taught decades and I guess you had thousands students. You would not mind teach one more student, would not you? 
    Wednesday, July 03, 2013 8:46 PM