Marios Philippopoulos (2/26/2008)

---

As Jack mentioned earlier, do use sp_executesql in your dynamic SQL for efficient reuse of execution plans.
Even with the 100 possible columns involved here, I'm sure a handful of favorites will take the bulk of choices for sorting, and you will want these execution plans to be re-used as much as possible.

sergeyledenev (2/4/2012)

---

Might be out of scope, but on my last job I was issued to create the similar table structure to keep there the contact information. The demand was in order to keep the different contact schemas in the same table.

I've gone with the other approach (it was not too easy to get the boss' agreement )

Instead of the single table with contact information I've implemented the structure of 2 tables: the contact and contact_details. The contact_details has 3 columns: contact_id, value and value_type_id.

That all allowed me to avoid such problems as sorting. And has used the storage more efficient way.