[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

sql server NOT operator

Posted on 2012-08-20
10
Medium Priority
?
389 Views
Last Modified: 2012-08-26
please expert, can you explain me with an example in transact sql code this
In a very large table why would do you think the NOT operator might be the least efficient one to use?
0
Comment
Question by:enrique_aeo
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 70

Assisted Solution

by:Éric Moreau
Éric Moreau earned 668 total points
ID: 38313422
because no index can efficiently use NOT
0
 

Author Comment

by:enrique_aeo
ID: 38313475
can you explainme with transact sql code
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 38313667
can you explainme with transact sql code
That cannot be explained by showing some T-SQL code.  You can however see what Eric is referring to by examining the Execution Plan.
0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 

Author Comment

by:enrique_aeo
ID: 38314228
an example please
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 38314586
If you insist, but it is not going to help you:
SELECT *
FROM YourTableName
WHERE NOT Col1 = 1

Open in new window

0
 

Author Comment

by:enrique_aeo
ID: 38314767
but i need test
because no index can efficiently use NOT
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 38314811
I give up.

Good luck.
0
 
LVL 3

Accepted Solution

by:
LuisGuerrero earned 1332 total points
ID: 38316802
Maybe this can help you understand
execution path
The way the "not" is resolved in this case (the field on the example is an int), it looks for all the values less than and greater then the value you dont want to look for (this is the seek predicate). Also, the same execution path is used for the "<>" operator, both are the least efficient searches you can use.
0
 

Author Comment

by:enrique_aeo
ID: 38317119
then as I do that search?
0
 
LVL 3

Assisted Solution

by:LuisGuerrero
LuisGuerrero earned 1332 total points
ID: 38317233
That's another question, and you would need to explain more about your table(s) and your particular need to get almost all the records on a large table. There's not a universal or simple answer for that, at least not one i know
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question