As seen in the execution plan, the optimizer has decided to use a non-clustered index scan operator, and it reads data pages from the data cache. Now, we will apply the missing index suggestion. Without a doubt, the created index is used by the database engine and it boosts the performance of the query because the logical reads number is lower than the previous one. The index seek operator details show us there is not any difference between the Actual Number of Rows and the Estimated Number of Rows.
This situation shows that the query optimizer finds accurate information about how many NULL rows are stored in the MiddleName column. Most likely, the following question will pop up in your mind. Basically, the statistics store the data distribution about the tables, and the query optimizer makes use of this information during the creation of a query plan. When we create an index this data distribution information is also stored for the NULL valued columns. So that, optimizer estimates how much memory and other resources are required for a query.
As we can see there is an number of NULL values are stored in the MiddleName column and this information is used by the query optimizer. In some cases, the ISNULL function is used with the where condition but this usage method may lead to use indexes inefficiently.
However, this query cannot use the created non-clustered index so it will read all index pages and then return the appropriate rows. Specifying a unique index makes sense only when uniqueness is a characteristic of the data itself. With multicolumn unique indexes, the index guarantees that each combination of values in the index is unique.
Both clustered and nonclustered index can be unique. We can create a unique clustered index and multiple nonclustered indexes on the same table. From the above explanation it is clearly understood that unique constraint does not allow duplicate values and the same is true with NULL values as well.
As mentioned above if we insert multiple NULL values to maintain data integrity SQL Server will throw an error, so the work-around would be as follows:. By doing a small change we can achieve the uniqueness.
No error raised after executing the above query, now check if the uniqueness is retained by inserting the existing value into TITLE column. Login to reply. Post reply. June 6, at pm I need a SQL query to find all the indexed columns which is having null values.
Please do me needful. Help us help you. Hi Lange, This a one time analysis process only. Thank you. June 7, at am Thanks for sharing Lowell. This is a great script. June 7, at pm June 16, at am
0コメント