Fal (3/3/2010)
benkoskysa (3/3/2010)
... I used a database where the sys.master_files table was empty...Well, it can't be empty. At the very least you can expect 8 rows from a brand-spankin' new install, 2 rows for each of master, tempdb, msdb and model. It's likely you don't have the right permissions to this view.
Although, this suggests 2 things: 1) that there is now another reason that people who get a QotD wrong can claim it is invalid - coz the required permissions weren't specified; and 2) that many cut-n-pasters will get the correct answer for the wrong reason.
S.
I just should have opted to use a different view to populate the table with 10 records. I chose the sys.master_files for 3 reasons:
1. It has 2 rows for each of the visible system databases (no rows are visible for resource), making it 8 in total
2. Usually we have at least one non-system database (if we have a job :-)), which adds 2 more records, making it at least 10 in total, which satisfies the test requirement to have 10 records needed for QoD
3. The total number of records is still pretty small so the cte part is not heavy.
I guess the better choice would be to opt for something not heavy but less restrictive, for example
select
top 10
row_number() over (order by table_name) record_number
from information_schema.tables
for the cte part. Thank you for pointing out the flaws in my question. I will try to be more accurate next time.
Oleg