Like others have said time and time again, you should test it out yourself to know for sure, but here are some theoretical predictions:
- Will query performance improve at all in case (N)TEXT columns are
removed from tables in genereal (let's assume the column is not
explicitely part of the query output and there is a nonclustered index
covering the query)?
I wouldn't expect a difference here. Covering indexes typically don't need to use the table data at all.
- Will query performance especially for "SELECT *" - queries be improved by removing them?
Yes, but how much may depend on the
where clause, and how many rows actually have data in these columns. If you are selecting the
ntext column(s), or they are included in the
where clause, you can probably expect performance improvements, but mainly when they have data.
ntext columns are basically a 2-byte pointer to other pages of data. Cutting out the overhead of reading those pages will be the most significant.
Another aspect to consider for performance improvements (even without data in these columns), is the total number of 8KB data pages for a table. Reducing this cuts out the space overhead per page, and performance overhead for reading each page.
As a somewhat extreme example: if a table is so wide that one data row exceeds the 8KB page limit by only 2 bytes, then you would cut out a lot of pages reducing space and improving performance.
- Does removing such a column have any effect on used space in case all rows have a NULL value stored in the (N)TEXT column?
Yes. You will cut out 2 bytes per row for the pointer data, and 1 bit for the null flag. I believe the number of bytes per row that you save per table will be approximately*:
2 + ([#ColsRemoved] / 8) + ([#ColsRemoved] % 8) / ([#ColumnsTotal] % 8)
You also should check how many pages you reduce the table size by. There is a 96-byte header per page.
The table metadata would also be removed, but that is probably negligible.
- Is any of those improvement depending on [whether] the content is NULL or NOT NULL?
Yes, certainly. As mentioned in 3, null data is stored efficiently which helps sparse tables.
*using integer division, i.e.
3 / 2 = 1