Uploaded image for project: 'ActiveObjects'
  1. AO-3429

SQLServer unlimited length string column should use NVARCHAR(max)

    Details

    • Type: Bug
    • Status: Resolved
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 1.4.0
    • Component/s: None
    • Labels:
    • Sprint:
    • Testing Notes:
      Hide

      Add notes...

      Show
      Add notes...

      Description

      AO still uses NTEXT for unlimited length string columns on SQLServer databases.

      But Ii's been deprecated since SQLServer 2005, see https://docs.microsoft.com/en-us/sql/database-engine/deprecated-database-engine-features-in-sql-server-2016#features-not-supported-in-a-future-version-of-sql-server

      As o SQLServer 2016 it still exists, but no guarantees for next version.

      Suggest removing it now in favour of NVARCHAR(max).

      Some considerations to test to ensure that no breaking change, so possible to include in minor releases of products, such as JIRA 7.4.0 instead of having to wait until 8.0.0

      • New unlimited length string columns added as NVARCHAR(max) not NTEXT on SQLServer database.
      • Existing unlimited length string columns that are NTEXT, are not changed. They stay NTEXT on SQLServer database.
      • AO operations function without changes, and without data loss. NB: The jdbc data type remains as CLOB (2005) for both NTEXT and NVARCHAR(max) with JTDS driver but is reported as NVARCHAR wuth Microsoft jdbc driver, so ensure both handled.
      • QueryDSL operations work without changes, as well
      • Data export from Jira Cloud and import into a SQLServer Server instance, works. The tables are all created using NVARCHAR(max) and no data loss
      • Ensure that unlimited length column can not be used as a foreign key constraint, and therefore can not have any problems with NVARCHAR(max) <-> NTEXT. This should be true, but verify.

      Note, that this change should not affect any other databases (SQLServer only) and does not affect any limited string length columns.

      Update path

      If anyone wants to convert all existing tables to NVARCHAR(max), as we don't want AO to do this implicitly, the path will be similar to AO-386 Resolved where suggest to manually run the ALTER COLUMN statements, or perform a data export to XML and then reimport it.

        Attachments

          Issue links

            Activity

              People

              • Assignee:
                mmcmahon Matthew McMahon (Inactive)
                Reporter:
                mmcmahon Matthew McMahon (Inactive)
              • Votes:
                0 Vote for this issue
                Watchers:
                1 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved:

                  Who's Looking?