Inserting data into very first and very last row

Question

Post reply

Inserting data into very first and very last row

sql_ques

SSCommitted

Points: 1546
More actions
December 22, 2009 at 2:54 am

#215048

Is it possible to insert data into a table so that it will be the very first row or the very last row of the table?

Viewing 6 posts - 1 through 5 (of 5 total)

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

Even if there were a way to do this with any certainty (and reproducibility / reliability) without changing your table structure, why would you want to? If you can provide a little information about what it is that you are trying to achieve, there's almost certainly a way of achieving it which is more "conventional".

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

LutzM SSC Guru Points: 107049 More actions · Answer 2

The physical order doesn't really matter that much...

If your table has a clustered index, new rows will be inserted in the physical order, maybe causing to physically move other rows to "make room" for the new one. Also, all nonclustered indexes would need to be updated as well if reordering takes place.

Since this will reduce performance, usually someone would avoid to physically rearrange rows when inserting a new one. Therefore, you most likely will see either an auto increment column or a datetime column with default =getdate() as columns to be used as a clustered index (= define the physical order).

@gurus: correct me if I'm wrong 😉

Btw: What are you trying to accomplish?

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 3

Sql_query (12/22/2009)

Is it possible to insert data into a table so that it will be the very first row or the very last row of the table?

According to what? What do you define as the "first" and "last row"? If you think a SELECT without an ORDER BY shows the "order", then you're in for a surprise someday.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

GSquared SSC Guru Points: 260824 More actions · Answer 4

Relational tables don't have a first row or last row. The clustered index may, if there is one, but the table itself doesn't.

Why do you need to insert a first row? What problem are you trying to solve?

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

SQLRNNR SSC Guru Points: 281324 More actions · Answer 5

Echoing what the others have said - the physical order is not that important. You have indexes to help with sorting.

To guarantee a single record will be the very first row in a table, you could insert it into an empty table - and then it would also be the last record.

If there is something more specific that you are trying to accomplish, it would be extremely helpful to post query, desired results and table structures.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events