Re: Insert a record in a particular place

Ralph Ganszky wrote:

How do you know that the OP asked exactly what you expect he have asked? Are
you sure that he asked what you think/want he has asked? What I meant is
exactly what he asked from my point of view. He asked if it is possible to
insert in between two other records. A clustered index does exactly this on
row level. It has on the other side nothing to do with the order in the
result set as you mentioned. But the answer is true any way for the physical
representation on the disk.

Only partly right. A clustered index will give rows a positional order
that can be reconstructed internally in the clustered index. However,
that order is normally inaccessible to the user. Your statement that
this is a physical order on disk is misleading. There need not be any
such physical ordering and even if it exists initially there is no
guarantee it will be maintained by a clustered index.

To refer to this in the way you did (an "ordered table") without any
qualification or explanation is to invite confusion. A table is a
logical model concept and definitively does not have a logical ordering
- not even given the faults and features of SQL and T-SQL. It's
difficult to see how a clustered index answers the original question
(if the OP wasn't aware of clustered indexes then how could he be
asking a question about their internal ordering?) but even if it did, I
wanted to clear up any confusion about the phrase "ordered table".

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

.

Relevant Pages

  • Re: Checkpoint causes need for better IO subsystem?
    ... maybe it's not the IO and that maybe it's an indexing performance issue. ... Microsoft SQL Server MVP ... is this a monotonically increasing indexing scheme that I've created? ... the Clustered index is on multiple cols... ...
    (microsoft.public.sqlserver.tools)
  • Re: Indexes
    ... because we have a clustered index but I was expecting ... Only one row fit per page, so SQL Server ... You have 100000 rows in the table and 1000 rows fit per index page. ... >> Tibor Karaszi, SQL Server MVP ...
    (microsoft.public.sqlserver.server)
  • Re: Checkpoint causes need for better IO subsystem?
    ... excellent articles on clustered index selection and its impact on ... clustered index sql kimberly tripp ... Microsoft SQL Server MVP ... the random write capability of the drives comes into play, ...
    (microsoft.public.sqlserver.tools)
  • Re: Checkpoint causes need for better IO subsystem?
    ... Microsoft SQL Server MVP ... is this a monotonically increasing indexing scheme that I've created? ... Not sure about how to setup a monotonically increasing key. ... the Clustered index is on multiple cols... ...
    (microsoft.public.sqlserver.tools)
  • Re: Indexes Confuses me!!
    ... > The minimum I/O unit used by SQL Server is a page. ... and performs a bookmark lookup. ... If the table doesn't have a clustered index (aka ... reading the actual data row costs a single logical ...
    (microsoft.public.sqlserver.programming)