Can we create partitioned indexing on null columns-performance impact?

Question

Hi,

I have very big tables in my datawarehouse.doing horizontal table partitioning to increase performance of our ETL.

Now I have to select columns (key columns & include columns) for non clustered indexing .

Some columns have crores of null values but we are using them in our conditions like in where, join,gruopby/orderby,select clause.

What will be the impact on performance on including those null values columns in indexing
( as key columns & include columns).

Please advise me on this.

Kali charan tripathi

[e-mail]

INDIA

---

Kali Charan Tripathi New Delhi INDIA

Answer 1

No impacts of including these columns in your index , but oppositely it can add a rich power here for the index if you used that new 2008 techniques "Index Filter" to filter out Null value and thus you can reduce index size to be small subset of data

 instead of the entire of data entity ..

This will help significantly in 2 regards :

1-      Reducing much IO overload on your DB production server

2-      Enhancing significantly your Performance reads

I do recommend you to have a look at my blog for index optimizations exists at : http://www.sqlserver-performance-tuning.com/apps/blog/show/12927042-towards-t-sql-queries-of-0-sec-6-6-

Kindly let me know if any further help is needed

   

---

Shehap (DB Consultant/DB Architect) Think More deeply of DB Stress Stabilities

Answer 2

Hi Kali charan tripathi,

I suggest you can create filtered indexes to filter out all the NULL values, the resulting index will be smaller and cost less to maintain than a full-table nonclustered index defined on the same key columns. Details step about how to create filtered indexes, please see: http://technet.microsoft.com/en-us/library/cc280372

Thanks,
Eileen

 

Answer 3

Hi Shehap/Eileen,

Thank you very much for your quick & perfect response.

Thanks
Kali Charan Tripathi

---

Kali Charan Tripathi New Delhi INDIA