Can I optimize a query containing a WHERE value. A value <> 2 with index

Question

Here is my request:

SELECT TOP 10 * FROM BigTable WHERE Value <> 2;

BigTable is little specific, since the column Valuecontains the same value for each row: 2. In real cases, there may be several lines that have a different meaning, but not so much. I really need to find these rogue lines. However, I do not know what value (2 is just an example) during development (but I know at the time of the request).

The request is slow (about 5 minutes); BigTable contains 10 million rows.

So, I added an index to the column Value, which is of type smallint. After 10 minutes, the index was built, and I again requested a query. It is still very slow.

: http://sqlfiddle.com/#!6/6ce0f/1

, SQL Server <>, , ? , 2 : SELECT TOP 10 Value FROM BigTable GROUP BY Value ( 2, ).

: , - -, SELECT TOP 10 * FROM BigTable WHERE Value = x .. ( , 2), ?

EDIT:

, , , . , . , , ( ). , , . , , , .

( Entity Framework Core, ):

CREATE NONCLUSTERED INDEX [IX_Value] ON [dbo].[BigTable]
(
    [Value] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

2:

SSMS ( , , , , , )

Value <> 2

, IX_Establishments_UpdateTag ( ). : 5 18 ( , Value/UpdateTag 2)

Value < 2

IX_Establishments_UpdateTag. 1 (SSMS- 0s).

Answer 1

SQL <>. "Can" , , , .

SQL OR - (, ?) . Value IN (1,2,3) "" "Value = 1 OR Value = 2 OR Value = 3`.

Value < 2 OR Value > 2 , , , , , Value <> 2... OR, chugs .

, Value <> 2 , itd . :

• (10 , , ?... .)
• , < > 2 ( )
• , ,
• . , , -2, , 2, - .

( ,

dbcc show_statistics (<TableName>, <IndexName>)

, , . , .)

"". , , , , . , , < OR > AND?

where not (Value >= 2 and Value <= 2)

, , .

Answer 2

, :

SELECT TOP 10 * FROM BigTable WHERE Value < 2
SELECT TOP 10 * FROM BigTable WHERE Value > 2

, , :

SELECT TOP 10 * FROM BigTable WHERE Value < 2 OR Value > 2

. , .

Answer 3

SQL Server <> .

,

select top 100 empid,custid 
 from orderstest where orderid<>230

create index nci on orderstest
(orderid)
include(custid,empid)

sql-

[1] [1]: : [PerformanceV3]. [dbo]. [orderstest]. orderid < ((230)),
 [2] [1]: : [PerformanceV3]. [Dbo]. [Orderstest]. orderid > ((230))

,

RunTimeCountersPerThread Thread = "0" ActualRows = "100" ActualRowsRead = "100"

, ,

• , ,

2. , ,

,

below is a query that returns all rows and views in SSMS

select empid,custid 
 from orderstest where orderid<>230  

took 4 seconds, but when executed with discard results set after executionit instantly returns

some other reasons may be locks, locks as well .. you can enable the actual execution plan and see the wait statistics in show plan xml..for my request below the wait statistics

 <WaitStats>
              <Wait WaitType="SOS_SCHEDULER_YIELD" WaitTimeMs="1" WaitCount="3" />
              <Wait WaitType="ASYNC_NETWORK_IO" WaitTimeMs="4942" WaitCount="4389" />
            </WaitStats>