SQL INSERT SELECT ordered

Question

SQL INSERT SELECT ordered

Navigation

#1 by (1 votes)

0

I have created a temporary table @TablaTurno , which I insert data in the following way:

insert @TablaTurno
select ROW_NUMBER() OVER(ORDER BY tturno)+@idturno as IDTurno,mci.IDCaja as IDCaja,@idestado as IDEstado,@idlocal as IDLocal,...
...order by ROW_NUMBER() OVER(ORDER BY tturno) asc

Then

insert MC_VENTAS.dbo.MCCDC_TURNO
select * from @TablaTurno

And the data is disorganized, eg (when doing SELECT to MC_VENTAS.dbo.MCCDC_TURNO )

But when doing the SELECT to the table @TablaTurno , it appears ordered. What can I do ?, since I need that in my table MC_VENTAS.dbo.MCCDC_TURNO appear ordered.

If I do the SELECT to @TablaTurno it lists me like this:

1
2
3
.
.
.

If I do SELECT a MC_VENTAS.dbo.MCCDC_TURNO it looks like this:

And MC_VENTAS.dbo.MCCDC_TURNO is filled with @tablaturno . Why is it messed up?

sql-server sql-server-2012

asked by Paul Quispe Pachas 03.01.2017 в 17:25

source

1 answer

Orientation in Azure Perform x simulations and collect the data in a CSV file

score 1 · Answer 1

the data is disorganized, eg (when doing SELECT to MC_VENTAS.dbo.MCCDC_TURNO )

This is not a defect. As it says the official documentation of SQL Server under the theme of the clause ORDER BY :

The order in which rows are returned in a result set can not be guaranteed , unless a ORDER BY clause is specified.

This is a very simple truth, but for a reason that I still do not understand, many resist. If you want to get ordered results, the unique way to guarantee it is by adding a ORDER BY to your query.

But when doing the SELECT to the table @TablaTurno it appears ordered

It does not matter. Do not be fooled Although it seems that you achieve results ordered without the clause ORDER BY with certain queries, and although it seems that the results are still ordered by executing the same query several times, it is a deception! Do not fall for the trap!

There are a number of factors that can cause you to change the order of your results when you least anticipate it. And if that happens, it will have been your fault. Because the documentation tells you more than clearly that you can not assume any order unless you add a ORDER BY to your query.

What can I do ?, since I need that in my table MC_VENTAS.dbo.MCCDC_TURNO appear ordered

For emphasis, the only correct answer to that question is: make your query with ORDER BY .

How can I reduce the cost of using ORDER BY ?

This is a better question. For example, if you want to see the data in the table sorted by IDTurno , but with good performance , it's a good idea to have an index defined in the column IDTurno .

Moreover, if you define it as the cluster index of your table, note the effect it has according to the documentation :

Cluster indexes sort and store rows of data in the table or view according to the values of the index key.

So if you define IDTurno as the clustered index of your table, this has the effect of sorting the data in the table by the values of IDTurno , which means that cost nothing to apply a ORDER BY IdTurno to your query, because the records are already sorted.

But, a moment ... how is it that the cluster index orders the records in the table by IdTurno ? Did not we just say that the records are always disordered, and that is why it is necessary to specify the clause ORDER BY ? Is it a contradiction?

It is not a contradiction. Although it is possible to affect the storage order of the records in the table by means of the cluster index, this does not guarantee the order of the records at the time of making the query. He may do it, but the point is that there is no guarantee he will do it. Again, the only way to guarantee the order of records in a query is by using the ORDER BY clause.