[RESOLVED] Not really important but...Where does an INSERT STATEMENT put the data?

[joshAU]

Hi
More out of curiosity than anything else.

If I use vb6 with ADO to INSERT data into a MS SQL dbase, where does the data go...at the start, at the end, or at some cryptic spot in the middle?

I can successfully add data to the database, but if I do a SELECT * query, the most recent data is not at the start OR at the end of the RSet.

I know the data is there as I can find it using WHERE statements, and the total number of records grows correctly, as well.

I have not set, via VB or SQL statement or MSSQL itself, any sort of sorting method.
It is just a flat file db at present.
I have not implemented and keys or index....yet.

Does it just add the data anywhere it likes ?
Or does the recordset return data in no particular order in relation to the db, unless its sorted?

I was a bit confused by this.

Thanks.
Josh

[techgnome]

Hi
Does it just add the data anywhere it likes ?
Or does the recordset return data in no particular order in relation to the db, unless its sorted?

I was a bit confused by this.

Thanks.
Josh

Yep, pretty much. There are a number of things that go into determining physically where the the data goes. It depends on if there is a clustered index on the table. That will physically re-arrange data so that like items in the index are grouped together. It also depends on the page size and the amount of data in the record being inserted. Most DBMS won't breakup data if it can help it... it likes to keep things together. It may also depend on any keys that might be set on the table.

Bottom line, is that depending on the complexity of the data and how it's indexed, there's no guarantee what order things will be in on simple Select * from the table.

-tg

[joshAU]

Thanks techgnome.

Glad to clear that up.

One thing I can say is its almost guaranteed to NOT put it at the top or the bottom. At least thats what I've found.

Thanks again.

josh

[si_the_geek]

As TG pointed out, there are several factors that determine which 'position' the data is added to - a notable one he missed is the database system you are using; for example Jet (for Access, etc) will usually add it to the 'end', but not always.

Unless you are working at an advanced level (eg: on a high level of optimisation), the best way to think of it is that the data is not actually stored in any kind of order, and that it can be returned in any way the database system decides - unless you explicitly tell it to sort in a particular way (using an ORDER BY clause).

Unless you have a clustered index that is quite complex, there will always be a bit of randomness to the ordering that the database system chooses - but depending on circumstances, you may not notice it. To avoid any issues like that, specify the ordering you want.

[szlamany]

And one thing I've learned from experience - the hard way...

If you leave off the ORDER BY clause (by accident) and things appear to come back in some order - you feel happy - go on your way thinking it you did things right. This typically happened on tables that had a clustered index - but not always - as a recordset built from an INDEX will usually come back sorted anyway...

Then one day the order changes. All because some internal memory or workspace size was reached in the database engine - or a different execution plan was used...

Within the "storage" of a Set-of-Data there is no implied order. This is most evident when you create a VIEW that uses ORDER BY. Typically you do this to get a "part" of a SET - like:

Create View XYZ as Select Top (50) Percent From... Order by LastName

You think you have a VIEW that is ORDER'd - because you put the ORDER BY. But in reality that ORDER BY is only used for "determining" the TOP (50) PERCENT - the view will actually return that first alphabetical half of the table in random order.

[joshAU]

Thanks si_the_geek and szlamany for your info.

I actually replied to this post last week, but it didnt work, and I didnt check it, until now.

Thanks once again.

joshAU

[Lord Orwell]

there's another possibility i didn't see listed. If data is deleted from the database and the database hasn't been compacted since then, the new data can be placed in the same position as the old data.

[leinad31]

For enterprise databases, tables are typically defaulted to heap type. In a heap table the data is placed in the first empty block/slot available or at the end of the table's data file if no slots were found. When a record is deleted then a "slot" becomes available for succeeding inserts.

Reorganizing data is a resource intensive process and will introduce performance bottlenecks.... you can compare it to defragmenting and compacting the file system, another resource intensive process. For an enterprise database, performance in serving the data (or making database available to users) takes precedence over internal organization of the data in the binary file (where table data is stored) as it often has no bearing (you can't view the contents of file directly and most operations are single record retrievals that can be done via index rather than organizing internal storage).

The exception is when insert occurs once and updates rarely occur (so no record movement and no large record block chaining or fragmentation of large record among several storage units), and selection is numerous and intensive (such as aggregations and OLTP reporting requirements)... in such cases imposing an organization scheme on the data, e.g. index organized tables, may be worth the performance degradation of inserts.

Storage is relatively cheap (you can live with the gaps or unused bit positions between records), lost time, productivity, and revenue are not. Up to you to weigh the pros and cons whether you'll organize the internal storage or not.