CODEWITHSUNDEEP
t-sqlsql-server-2008-r2table-statistics
Mukul Singh Saini
Mukul Singh Saini

Reputation: 1

record order in T-sql changing with statistics update

I am facing an issue with the record order in the given query.

SELECT 
EA.eaid, --int , PK of table1
EA.an, --varchar(max)
EA.dn, --varchar(max)
ET.etid, --int
ET.st --int
FROM dbo.table1 EA
JOIN dbo.table2 ET ON EA.etid = ET.etid
JOIN @tableAttribute TA ON EA.eaid = TA.id -- TA.id is int and is not a PK
ORDER BY ET.st

The value of ET.st column is same for all records in the given scenario.

The order of records given by the query is changing randomly on updating statistics. Sometimes it is in order of EA.eaid and sometimes in the order of TA.id.

Please provide an explanation for such a behaviour.How is the statistics affecting the ordering here?

I am using sql server 2008 R2.

Upvotes: 0

Views: 108

Answers (1)

lc.
lc.

Reputation: 116498

The order of rows returned from a database query is undefined unless specified by an ORDER BY clause. Since you are only ordering by ET.st and all values of this column are the same, the results will be returned in a non-deterministic order (based on the plan determined by the optimizer and the order of indexes used). Updating index statistics allows the query optimizer to choose the best (usually the most deterministic) indexes; it is likely that the query plan has changed as a result which is causing a different ordering to come out.

It sounds to me like you want to order by something other than ET.st.

Upvotes: 2

Related Questions