SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


does index also change the output?


does index also change the output?

Author
Message
Orlando Colamatteo
Orlando Colamatteo
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14889 Visits: 14396
sqldba_newbie (3/1/2013)
Jeff Moden (3/1/2013)
opc.three (3/1/2013)
Jeff Moden (3/1/2013)
Actually, the change of an index CAN very much change the result especially where UPDATEs are concerned.

I think we covered the OP's question about SELECT statements so I don't feel bad taking a sidebar here.

Is this something to do with the quirky update? Can you provide some documentation that explains the behavior you're alluding too?


It certainly applies to the quirky update but I wasn't referring to such an arcane method. Gail is spot on for what I meant.

They can also affect SELECTs that use TOP with no ORDER BY. The addition of a index can instantly change what the TOP returns if the index comes into play. In most cases, of course, it is NOT something that should be relied on.


Does have a order by something like this:

(SELECT DISTINCT Row_number()
OVER (
ORDER BY AdDate ASC ) Row

Then at the end of the query result set has:

ORDER BY ROW ASC


However if i remove the "inappropriate" index, query makes use of another index and provides results as expected? I did do a update stats with full scan.

Without an ORDER BY that query will not be guaranteed to return the same result every time. The ORDER BY in the window function determines the ranking each row coming back from the FROM-clause yields. You need the ORDER BY on the query to guarantee order else the engine will just return the results in the quickest way it can find with no attention paid to ordering.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86877 Visits: 45263
sqldba_newbie (3/1/2013)
Jeff Moden (3/1/2013)
opc.three (3/1/2013)
Jeff Moden (3/1/2013)
Actually, the change of an index CAN very much change the result especially where UPDATEs are concerned.

I think we covered the OP's question about SELECT statements so I don't feel bad taking a sidebar here.

Is this something to do with the quirky update? Can you provide some documentation that explains the behavior you're alluding too?


It certainly applies to the quirky update but I wasn't referring to such an arcane method. Gail is spot on for what I meant.

They can also affect SELECTs that use TOP with no ORDER BY. The addition of a index can instantly change what the TOP returns if the index comes into play. In most cases, of course, it is NOT something that should be relied on.


Does have a order by something like this:

(SELECT DISTINCT Row_number()
OVER (
ORDER BY AdDate ASC ) Row

Then at the end of the query result set has:

ORDER BY ROW ASC


However if i remove the "inappropriate" index, query makes use of another index and provides results as expected? I did do a update stats with full scan.


Post the exact query. Somewhere in there there's an assumption about order without an order by, hence when you change the index, the order the rows are processed changes and your query results change because of the incorrect assumption.

Update stats has nothing to do with anything here.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Lynn Pettis
Lynn Pettis
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39133 Visits: 38518
sqldba_newbie (3/1/2013)
Jeff Moden (3/1/2013)
opc.three (3/1/2013)
Jeff Moden (3/1/2013)
Actually, the change of an index CAN very much change the result especially where UPDATEs are concerned.

I think we covered the OP's question about SELECT statements so I don't feel bad taking a sidebar here.

Is this something to do with the quirky update? Can you provide some documentation that explains the behavior you're alluding too?


It certainly applies to the quirky update but I wasn't referring to such an arcane method. Gail is spot on for what I meant.

They can also affect SELECTs that use TOP with no ORDER BY. The addition of a index can instantly change what the TOP returns if the index comes into play. In most cases, of course, it is NOT something that should be relied on.


Does have a order by something like this:

(SELECT DISTINCT Row_number()
OVER (
ORDER BY AdDate ASC ) Row

Then at the end of the query result set has:

ORDER BY ROW ASC


However if i remove the "inappropriate" index, query makes use of another index and provides results as expected? I did do a update stats with full scan.


What is with the DISTINCT? Using the ROW_NUMBER function will make each row unique.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86877 Visits: 45263
opc.three (3/1/2013)
My comment was that the yield from the FROM, WHERE, GROUP BY and HAVING clauses is not altered based on which index the engine picks.


I can probably produce you an example where it is (row number over a non-unique column and filter on that row number comes to mind)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Orlando Colamatteo
Orlando Colamatteo
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14889 Visits: 14396
GilaMonster (3/1/2013)
opc.three (3/1/2013)
My comment was that the yield from the FROM, WHERE, GROUP BY and HAVING clauses is not altered based on which index the engine picks.


I can probably produce you an example where it is (row number over a non-unique column and filter on that row number comes to mind)

I am not picturing it. Wouldn't that require a derived table, in which case the ORDER BY in the ranking function would guarantee the correct result?

Was thinking something like this:

SELECT  name,
row_num
FROM ( SELECT ROW_NUMBER() OVER ( ORDER BY name ),
name
FROM sys.tables
) tbls ( row_num, name )
WHERE row_num = 1;



__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86877 Visits: 45263
opc.three (3/1/2013)
GilaMonster (3/1/2013)
opc.three (3/1/2013)
My comment was that the yield from the FROM, WHERE, GROUP BY and HAVING clauses is not altered based on which index the engine picks.


I can probably produce you an example where it is (row number over a non-unique column and filter on that row number comes to mind)

I am not picturing it. Wouldn't that require a derived table, in which case the ORDER BY in the ranking function would guarantee the correct result?

Was thinking something like this:

SELECT  name,
row_num
FROM ( SELECT ROW_NUMBER() OVER ( ORDER BY name ),
name
FROM sys.tables
) tbls ( row_num, name )
WHERE row_num = 1;



name is unique.

When you do a row number over a column that is not unique, the order in which those row numbers are assigned to 'duplicate' values is not guaranteed, change the index that SQL's using and you can change the order that the row numbers are assigned.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Orlando Colamatteo
Orlando Colamatteo
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14889 Visits: 14396
I realized that nuance after I posted.

Looking to mock-up an example...

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Orlando Colamatteo
Orlando Colamatteo
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14889 Visits: 14396
Found one. I was never under the impression that adding or changing an index was completely harmless for various other reasons but not for this particular one. I am still faulting over the fact that it is something in a SELECT-column-list that is altering the outcome, although it is being incorporated into a resultset so all bets are off. This has been added to my list of things to consider when tuning, reviewing and writing code.

Setup:

USE tempdb;

CREATE TABLE dbo.test
(
name VARCHAR(100),
type_desc VARCHAR(100)
);
INSERT INTO dbo.test
( name, type_desc )
VALUES ( 'xyz', 'user' );

INSERT INTO dbo.test
( name, type_desc )
VALUES ( 'abc', 'user' );

CREATE CLUSTERED INDEX [cx] ON dbo.test (name);



Query 1:
SELECT  name,
row_num
FROM ( SELECT ROW_NUMBER() OVER ( ORDER BY type_desc ),
name,
type_desc
FROM dbo.test
) tbls ( row_num, name, type_desc )
WHERE row_num = 1;





Add another index:
-- add nc index to change support for ROW_NUMBER
CREATE NONCLUSTERED INDEX [ix] ON dbo.test (type_desc DESC);



Query again, same as above but different result:
SELECT  name,
row_num
FROM ( SELECT ROW_NUMBER() OVER ( ORDER BY type_desc ),
name,
type_desc
FROM dbo.test
) tbls ( row_num, name, type_desc )
WHERE row_num = 1;







Unsetup:

DROP TABLE dbo.test;



__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Attachments
q1.jpg (55 views, 31.00 KB)
q2.jpg (56 views, 27.00 KB)
res.jpg (55 views, 9.00 KB)
Orlando Colamatteo
Orlando Colamatteo
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14889 Visits: 14396
@Gail, In thinking about this a little more, I think the example I gave is just a roundabout way of asking for TOP 1 without an ORDER BY, which I say is nonsensical.

USE tempdb;

CREATE TABLE dbo.test
(
name VARCHAR(100),
type_desc VARCHAR(100)
);
INSERT INTO dbo.test
( name, type_desc )
VALUES ( 'xyz', 'user' );

INSERT INTO dbo.test
( name, type_desc )
VALUES ( 'abc', 'user' );

CREATE CLUSTERED INDEX [cx] ON dbo.test (name);
GO
------------------------------------------------------------------------------

SELECT TOP 1
name,
type_desc
FROM dbo.test
WHERE type_desc = 'user';
GO

-- add nc index to change support for ROW_NUMBER
CREATE NONCLUSTERED INDEX [ix] ON dbo.test (type_desc, name desc);

-- same query as above
SELECT TOP 1
name,
type_desc
FROM dbo.test
WHERE type_desc = 'user';

DROP TABLE dbo.test;



__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86877 Visits: 45263
opc.three (3/1/2013)
@Gail, In thinking about this a little more, I think the example I gave is just a roundabout way of asking for TOP 1 without an ORDER BY, which I say is nonsensical.


Or a TOP 1 (without specifying WITH TIES) where the column you're ordering by has duplicate values

USE tempdb;

CREATE TABLE dbo.test
(
name VARCHAR(100),
type_desc VARCHAR(100)
);
INSERT INTO dbo.test
( name, type_desc)
VALUES ( 'xyz', 'user');

INSERT INTO dbo.test
( name, type_desc)
VALUES ( 'abc', 'user');

CREATE CLUSTERED INDEX [cx] ON dbo.test (name);
GO
------------------------------------------------------------------------------

SELECT TOP 1
name,
type_desc
FROM dbo.test
WHERE type_desc = 'user'
ORDER BY type_desc;
GO

-- add nc index to change support for filter and order
CREATE NONCLUSTERED INDEX [ix] ON dbo.test (type_desc, name desc);

-- same query as above
SELECT TOP 1
name,
type_desc
FROM dbo.test
WHERE type_desc = 'user'
ORDER BY type_desc;

DROP TABLE dbo.test;



Moral of the story, be careful when ordering by a non-unique column and then restricting the rows based on that ordering (via row_number, rank, etc or top)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search