SQL Performance: Say no to the "NOT IN" operator
Hello friends, Paraphrasing the title of the american television show " Say Yes to the dress ", our title of today is " Say no to the NOT IN operator ". Why? Well, we will show you that the " Not in " operator can be substituited with the " Not Exists " operator and that normally the Not Exists operator is fastest ! How we write a Query ultimately determines how much it will be efficient. Yes, SQL Server is a declarative language ( we tell him what we want to achieve and he internally decides how to get it ) but if we help him by applying a series of good rules we will get improvements from the point of view of performance. But now, as usual, we will show you with an example Let's go! For the example we will use our tables ORDRIG and PRODUCTS. Each table have a clustered index on the ID integer field: CREATE CLUSTERED INDEX IDX_ORDRIG_ID ON ORDRIG(ID) CREATE CLUSTERED INDEX IDX_PRODUCTS_ID ON PRODUCTS(ID) Well...