Insert Query

Question

Insert Query

gugan_ta

SSCommitted

Points: 1649
More actions
July 2, 2009 at 4:20 am

#95863

Hi ,
inserting a record in table which contains identity column...but record adds at the top of the table instead @ the bottom of the table. pls help why the record inserts at the top of the table..
Thanks...

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 1

Hi , you have a fundamental misunderstanding , there is no 'top' and there is no 'bottom', if you need something to happen in a specific order you have to specify ORDER BY.

Clear Sky SQL
My Blog[/url]

gugan_ta SSCommitted Points: 1649 More actions · Answer 2

hi,

thanx... when i try to insert a new row in a table it inserts in the 1st row.

have given the sample table & syntax..help if anything to changed. idn is an identity column.

sn_no idn

1 0

2 1

3 2

insert into (sn_no)

select '4'

record '4' inserts @ 1st row.........i want record '4' to be in the last row.

sn_no idn

4 3

1 0

2 1

3 2

Thanks........

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 3

Exactly, a fundamental misunderstanding. Do not view a sqlTable as a nice ordered list , think of it more of a haystack of data. When you select data from it it will be returned in a undefinable order. If you need a order of data returned to you then you have to , there is no other way , specify ORDER BY.

In you case :

Select * from order by sn_no

Clear Sky SQL
My Blog[/url]

Lowell SSC Guru Points: 323518 More actions · Answer 4

Just as Dave said,

that's what is a main difference between any database system and a flat file/excel spreadsheet.

SQL Server, or any other DataBase Management System, does not guarantee the order of the data unless you specifically have an ORDER BY clause.

add your new item to your table, and the system decides where and how to store the item based on the primary key of the table, and other storage requirements.

without an ORDER BY, it also grabs the data in the fastest way it can, which(most likely) is NOT the order you expect.

to get it in a specific order, you need an ORDER BY...

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

WayneS SSC Guru Points: 95460 More actions · Answer 5

The only time a new row will be placed in any kind of order is if you have a clustered index on the table. Then it will place the new row physically in order of that index.

However, this does not mean that you will see it in this order. In order to see the results of any query in anything other than a random order, you must specify an order by clause on your select statement.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 6

WayneS (7/2/2009)
The only time a new row will be placed in any kind of order is if you have a clustered index on the table. Then it will place the new row physically in order of that index.
However, this does not mean that you will see it in this order. In order to see the results of any query in anything other than a random order, you must specify an order by clause on your select statement.

Just to clarify physical order here - that means physically ordered on the appropriate page within the extent defined for that clustered index. Where that page may or may not be physically ordered - or the insert could even cause a page split and force a new page to be created, which may or may not be at the end (where is the end, exactly?). And, that insert may or may not be on the original page - or it could be on the new page.

So, what is the physical order of the table/data? Can't really say - but, if I want the data returned in a certain order I add an ORDER BY and I am guaranteed to get the results in the ORDER I specify.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

WayneS (7/2/2009)
The only time a new row will be placed in any kind of order is if you have a clustered index on the table. Then it will place the new row physically in order of that index.

Clustered index defines the logical order, not the physical. The order that rows are listed on the page doesn't have to be that of the clustering key. The physical order of pages in the file does not have to follow the order of the clustering key of rows on those pages.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass