InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 18 May 2013 09:48:39 GMT20http://www.sqlservercentral.com/Forums/Topic1425302-146-1.aspx[quote][b]sqldba_newbie (3/4/2013)[/b][hr]I think i found the root cause here. The query which we were using brings top 15 records in asc order based on a column which has NULL or 0 0r 1.[/quote]Right, so ordering by a non-unique column and limiting the results. That's the case I showed to opc. Because that column's not unique, when you order by just that you have no guarantee of what order the rows will be within the 'null, 0 or 1' groupings, hence the index changes the assumed order (because it was never guaranteed) and appears to change the results.The solution here would be to order by a second column that does define the 'correct' order of rows as you want to see them. Removing the column from the index is hiding the symptom, nothing more. There are lots of other things that can and will cause the order of rows to differ from what you assume they should be.Mon, 04 Mar 2013 09:24:26 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1425302-146-1.aspxI think i found the root cause here. The query which we were using brings top 15 records in asc order based on a column which has NULL or 0 0r 1. This was also one of the main columns in the index which did allow nulls, however if i change the index and not include this column then we the null column data would not be in the top 15.Bottom line is that it was data issue, however i would still like to know from other folks that should i include my column which allows NULL values in my index or it just depends on business reason?Thanks for your help.Mon, 04 Mar 2013 08:59:59 GMTcurious_sqldbahttp://www.sqlservercentral.com/Forums/Topic1425302-146-1.aspx[quote][b]GilaMonster (3/1/2013)[/b][hr]Or a TOP 1 (without specifying WITH TIES) where the column you're ordering by has duplicate values[/quote]Nice. That is a tricky example that might slip by even a good static code analysis rule because it depends on the uniqueness of the column.Fri, 01 Mar 2013 11:54:06 GMTopc.threehttp://www.sqlservercentral.com/Forums/Topic1425302-146-1.aspx[quote][b]opc.three (3/1/2013)[/b][hr]@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.[/quote]Or a TOP 1 (without specifying WITH TIES) where the column you're ordering by has duplicate values[code="sql"]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_descFROM dbo.testWHERE type_desc = 'user'ORDER BY type_desc;GO-- add nc index to change support for filter and orderCREATE NONCLUSTERED INDEX [ix] ON dbo.test (type_desc, name desc);-- same query as aboveSELECT TOP 1 name, type_descFROM dbo.testWHERE type_desc = 'user'ORDER BY type_desc;DROP TABLE dbo.test;[/code]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)Fri, 01 Mar 2013 11:48:00 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1425302-146-1.aspx@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.[code="sql"]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_descFROM dbo.testWHERE type_desc = 'user';GO-- add nc index to change support for ROW_NUMBERCREATE NONCLUSTERED INDEX [ix] ON dbo.test (type_desc, name desc);-- same query as aboveSELECT TOP 1 name, type_descFROM dbo.testWHERE type_desc = 'user';DROP TABLE dbo.test;[/code]Fri, 01 Mar 2013 11:31:19 GMTopc.threehttp://www.sqlservercentral.com/Forums/Topic1425302-146-1.aspxFound 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:[code="sql"]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);[/code]Query 1:[code="sql"]SELECT name, row_numFROM ( SELECT ROW_NUMBER() OVER ( ORDER BY type_desc ), name, type_desc FROM dbo.test ) tbls ( row_num, name, type_desc )WHERE row_num = 1;[/code][img]http://www.sqlservercentral.com/Forums/Attachment13277.aspx[/img]Add another index:[code="sql"]-- add nc index to change support for ROW_NUMBERCREATE NONCLUSTERED INDEX [ix] ON dbo.test (type_desc DESC);[/code]Query again, same as above but different result:[code="sql"]SELECT name, row_numFROM ( SELECT ROW_NUMBER() OVER ( ORDER BY type_desc ), name, type_desc FROM dbo.test ) tbls ( row_num, name, type_desc )WHERE row_num = 1;[/code][img]http://www.sqlservercentral.com/Forums/Attachment13279.aspx[/img][img]http://www.sqlservercentral.com/Forums/Attachment13278.aspx[/img]Unsetup:[code="sql"]DROP TABLE dbo.test;[/code]Fri, 01 Mar 2013 11:18:30 GMTopc.threehttp://www.sqlservercentral.com/Forums/Topic1425302-146-1.aspxI realized that nuance after I posted.Looking to mock-up an example...Fri, 01 Mar 2013 10:47:30 GMTopc.threehttp://www.sqlservercentral.com/Forums/Topic1425302-146-1.aspx[quote][b]opc.three (3/1/2013)[/b][hr][quote][b]GilaMonster (3/1/2013)[/b][hr][quote][b]opc.three (3/1/2013)[/b][hr]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.[/quote]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)[/quote]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:[code="sql"]SELECT name, row_numFROM ( SELECT ROW_NUMBER() OVER ( ORDER BY name ), name FROM sys.tables ) tbls ( row_num, name )WHERE row_num = 1;[/code][/quote]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.Fri, 01 Mar 2013 10:44:56 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1425302-146-1.aspx[quote][b]GilaMonster (3/1/2013)[/b][hr][quote][b]opc.three (3/1/2013)[/b][hr]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.[/quote]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)[/quote]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:[code="sql"]SELECT name, row_numFROM ( SELECT ROW_NUMBER() OVER ( ORDER BY name ), name FROM sys.tables ) tbls ( row_num, name )WHERE row_num = 1;[/code]Fri, 01 Mar 2013 10:40:42 GMTopc.threehttp://www.sqlservercentral.com/Forums/Topic1425302-146-1.aspx[quote][b]opc.three (3/1/2013)[/b][hr]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.[/quote]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)Fri, 01 Mar 2013 10:34:58 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1425302-146-1.aspx[quote][b]sqldba_newbie (3/1/2013)[/b][hr][quote][b]Jeff Moden (3/1/2013)[/b][hr][quote][b]opc.three (3/1/2013)[/b][hr][quote][b]Jeff Moden (3/1/2013)[/b][hr]Actually, the change of an index CAN very much change the result especially where UPDATEs are concerned.[/quote]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?[/quote]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.[/quote]Does have a order by something like this:[b](SELECT DISTINCT Row_number() OVER ( ORDER BY AdDate ASC ) RowThen at the end of the query result set has:ORDER BY ROW ASC[/b]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.[/quote]What is with the DISTINCT? Using the ROW_NUMBER function will make each row unique.Fri, 01 Mar 2013 10:33:05 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1425302-146-1.aspx[quote][b]sqldba_newbie (3/1/2013)[/b][hr][quote][b]Jeff Moden (3/1/2013)[/b][hr][quote][b]opc.three (3/1/2013)[/b][hr][quote][b]Jeff Moden (3/1/2013)[/b][hr]Actually, the change of an index CAN very much change the result especially where UPDATEs are concerned.[/quote]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?[/quote]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.[/quote]Does have a order by something like this:[b](SELECT DISTINCT Row_number() OVER ( ORDER BY AdDate ASC ) RowThen at the end of the query result set has:ORDER BY ROW ASC[/b]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.[/quote]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.Fri, 01 Mar 2013 10:20:50 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1425302-146-1.aspx[quote][b]sqldba_newbie (3/1/2013)[/b][hr][quote][b]Jeff Moden (3/1/2013)[/b][hr][quote][b]opc.three (3/1/2013)[/b][hr][quote][b]Jeff Moden (3/1/2013)[/b][hr]Actually, the change of an index CAN very much change the result especially where UPDATEs are concerned.[/quote]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?[/quote]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.[/quote]Does have a order by something like this:[b](SELECT DISTINCT Row_number() OVER ( ORDER BY AdDate ASC ) RowThen at the end of the query result set has:ORDER BY ROW ASC[/b]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.[/quote]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.Fri, 01 Mar 2013 10:03:21 GMTopc.threehttp://www.sqlservercentral.com/Forums/Topic1425302-146-1.aspx[quote][b]Jeff Moden (3/1/2013)[/b][hr][quote][b]opc.three (3/1/2013)[/b][hr][quote][b]Jeff Moden (3/1/2013)[/b][hr]Actually, the change of an index CAN very much change the result especially where UPDATEs are concerned.[/quote]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?[/quote]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.[/quote]TOP is an interesting example and granted, it could change the [i]presented[/i] result, but without an ORDER BY, TOP makes little sense.I do not feel that the UPDATE...FROM/JOIN and the Quirky Update apply here however. The UPDATE...FROM/JOIN is not the case that came to mind initially but is why I have begun using MERGE to replace UPDATE...FROM/JOIN queries, to gain protection from ambiguous update scenarios. iirc UPDATE...FROM/JOIN is a proprietary T-SQL implementation.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. Diving into the UPDATE scenarios I think you have to parse out the modification of the data from the selection of the data to evaluate what the "resultset" would be in a SELECT scenario.Just my take.Fri, 01 Mar 2013 09:47:53 GMTopc.threehttp://www.sqlservercentral.com/Forums/Topic1425302-146-1.aspx[quote][b]Jeff Moden (3/1/2013)[/b][hr][quote][b]opc.three (3/1/2013)[/b][hr][quote][b]Jeff Moden (3/1/2013)[/b][hr]Actually, the change of an index CAN very much change the result especially where UPDATEs are concerned.[/quote]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?[/quote]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.[/quote]Does have a order by something like this:[b](SELECT DISTINCT Row_number() OVER ( ORDER BY AdDate ASC ) RowThen at the end of the query result set has:ORDER BY ROW ASC[/b]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.Fri, 01 Mar 2013 09:35:19 GMTcurious_sqldbahttp://www.sqlservercentral.com/Forums/Topic1425302-146-1.aspx[quote][b]opc.three (3/1/2013)[/b][hr][quote][b]Jeff Moden (3/1/2013)[/b][hr]Actually, the change of an index CAN very much change the result especially where UPDATEs are concerned.[/quote]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?[/quote]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.Fri, 01 Mar 2013 09:13:22 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1425302-146-1.aspx[quote][b]opc.three (3/1/2013)[/b][hr][quote][b]Jeff Moden (3/1/2013)[/b][hr]Actually, the change of an index CAN very much change the result especially where UPDATEs are concerned.[/quote]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?[/quote]I would guess it has to do with the UPDATE ... FROM where there's not a 1-1 relationship between the table and the results of the FROM. In that case, what value is used for the update depends on the order that the rows in the FROM are returned and that can be changed by an index (or parallelism or a few other things)The quirky update also depends on a particular order for the rows.Fri, 01 Mar 2013 06:59:51 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1425302-146-1.aspx[quote][b]Jeff Moden (3/1/2013)[/b][hr]Actually, the change of an index CAN very much change the result especially where UPDATEs are concerned.[/quote]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?Fri, 01 Mar 2013 06:47:15 GMTopc.threehttp://www.sqlservercentral.com/Forums/Topic1425302-146-1.aspx[quote][b]sqldba_newbie (2/28/2013)[/b][hr]I was in an assumption that index would only change the execution plan and not the actual output of the data. I have a query which gives completely different results on two different indices?[/quote]Only if you have something in that query that assumes an order without having an order by specified. Essentially when you're assuming an order exists where there is none, an index (and a bunch of other things) can change that order resulting in different output (or different update results). Not the fault of the index, the result of assuming there's an order when there's no order by specified.Fri, 01 Mar 2013 03:02:28 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1425302-146-1.aspxActually, the change of an index CAN very much change the result especially where UPDATEs are concerned.Fri, 01 Mar 2013 00:07:44 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1425302-146-1.aspxNope. The decision by the engine to use one index or another to satisfy a query will not change the [i]contents[/i] of the resultset. The order of the resultset is a separate matter from [i]contents[/i] and may appear to be dictated by the index and may appear reliable but can actually vary from one execution to another even when using the same index depending on other server activity. You'll need to provide an ORDER BY if reliable ordering of your resultset is important to you.Thu, 28 Feb 2013 22:23:12 GMTopc.threehttp://www.sqlservercentral.com/Forums/Topic1425302-146-1.aspxWhen you say you are getting two different results, are you saying that the result sets are different or just ordered differently? If you are talking about order, there is no guarantee of order unless you have an ORDER BY on the outer most query returning data.Thu, 28 Feb 2013 18:24:04 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1425302-146-1.aspxI was in an assumption that index would only change the execution plan and not the actual output of the data. I have a query which gives completely different results on two different indices?Thu, 28 Feb 2013 15:23:05 GMTcurious_sqldba