vk-kirov (10/19/2010)
Hugo Kornelis (10/19/2010)
The same page in Books Online also contains this warning: "Partitioned tables and indexes are not fully supported in this view; use the sys.indexes catalog view instead." ... Since method 2 does not work for partitioned tables, it's not reliable.The 2nd query works just fine for partitioned tables 🙂
That BOL page states what columns are not supported in case of partitioned tables: first, root, groupid, dpages, reserved, used, rowcnt, rowmodctr, and FirstAIM. The column 'rows' is not in this list, and it's supported.
I missed that - I stopped reading after seeing that quote. Thanks, vk-kirov!
With this additional information, I'd argue that options 2 and 4 are both correct. The unreliability of option 2 stopped after SQL 2000 (and the question specifically mentions SQL 2005 and later), and being deprecated was nog mentioned as a deciding factor in the question.