Salesforce Certified Platform Developer II — Question 207

As part of a new integration, a developer is asked to implement a new custom search functionality that is capable of performing unrestricted queries and can account for all values within a custom picklist field, Type__c, on the Opportunity object. The search feature must also account for NULL values.

The organization-wide default for the Opportunity object is set to Public Read-Only, and a new custom index has been created for the Type__c field. There are more than 5 million Opportunity records within the environment, and a considerable amount of the existing records have NULL values for the picklist.

Which technique should the developer implement to maximize performance when querying NULL values?

Answer options

• A. Use a SOSL query to return ALL opportunities that have a value of NULL in any field.
• B. Use the OR operator to combine WHERE clauses to strictly search for each value within the picklist, including Type__c = NULL.
• C. Perform two SOQL queries; one to query Opportunities where Type__c != NULL, and another to query where Type__c = NULL, then join the result set using Apex.
• D. Create a formula field that substitutes NULL values for a string of text, create an index for the formula field, then use the formula within the WHERE clause.

Correct answer: D

Explanation

The correct answer is D because creating a formula field that substitutes NULL values allows for better indexing and querying performance. Options A and B do not optimize for NULL values effectively, while C introduces unnecessary complexity by executing multiple queries and relying on Apex for merging results.