Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

does index also change the output? Expand / Collapse
Author
Message
Posted Friday, March 1, 2013 10:03 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, September 19, 2014 7:27 PM
Points: 7,107, Visits: 12,657
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
Post #1425612
Posted Friday, March 1, 2013 10:20 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:32 AM
Points: 39,905, Visits: 36,245
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 2008, MVP
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

Post #1425620
Posted Friday, March 1, 2013 10:33 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 10:34 AM
Points: 20,682, Visits: 32,282
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.



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)
Post #1425623
Posted Friday, March 1, 2013 10:34 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:32 AM
Points: 39,905, Visits: 36,245
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 2008, MVP
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

Post #1425624
Posted Friday, March 1, 2013 10:40 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, September 19, 2014 7:27 PM
Points: 7,107, Visits: 12,657
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
Post #1425634
Posted Friday, March 1, 2013 10:44 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:32 AM
Points: 39,905, Visits: 36,245
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 2008, MVP
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

Post #1425636
Posted Friday, March 1, 2013 10:47 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, September 19, 2014 7:27 PM
Points: 7,107, Visits: 12,657
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
Post #1425641
Posted Friday, March 1, 2013 11:18 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, September 19, 2014 7:27 PM
Points: 7,107, Visits: 12,657
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


  Post Attachments 
q1.jpg (50 views, 31.75 KB)
q2.jpg (50 views, 27.28 KB)
res.jpg (50 views, 9.66 KB)
Post #1425653
Posted Friday, March 1, 2013 11:31 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, September 19, 2014 7:27 PM
Points: 7,107, Visits: 12,657
@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
Post #1425660
Posted Friday, March 1, 2013 11:48 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:32 AM
Points: 39,905, Visits: 36,245
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 2008, MVP
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

Post #1425674
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse