-1

I have some questions regarding the (still deprecated) (N)TEXT datatype in Sql Server - in many tables I can find (N)TEXT columns and many (90%+) rows where those values are NULL, so their use case seems to be obsolete and removing(DROPPING!) them is an option - but the benefits have to be weighted in comparison to the effort in app code.

  1. 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)?

  2. Will query performance especially for "SELECT *" - queries be improved by removing them?

  3. 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?

  4. Is any of those improvement depending on wether the content is NULL or NOT NULL?

  • 4
    What do you mean by "removing" the NTEXT? If there is data in some rows, and if the app still has the ability to use these columns, are you going to drop the column, convert to [N]VARCHAR(MAX), or convert to [N]VARCHAR(1 - 4000)? All technicalities aside, all of your questions could be answered relatively easily by restoring your DB on a test server, making the change(s), rebuilding the clustered indexes, and then checking sp_spaceused and running some representative queries. – Solomon Rutzky Jan 30 '17 at 15:56
1

Like others have said time and time again, you should test it out yourself to know for sure, but here are some theoretical predictions:

  1. 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.

  1. 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.

  1. 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.

  1. 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.