1

I have read that Microsoft SQL Server has deprecated the text data type in favour of varchar(max).

PostGreSQL already allows varchar (without a length) as an alternative for text.

The question is, what does the ANSI SQL standard have to say about deprecating text, and the best alternative? Also, does MySQL/MariaDB also follow this?

Thanks

ypercubeᵀᴹ
  • 97,895
  • 13
  • 214
  • 305
Manngo
  • 2,819
  • 8
  • 34
  • 56
  • Yes, I know that SQL Server isn’t fully ANSI compliant. The question is what does the ANSI standard have to say about the TEXT data type? – Manngo Jun 28 '16 at 05:50
  • Does anybody know if SQL has informed the public which version of SQL Server will remove the TEXT data type? – thecoolmacdude Nov 12 '19 at 15:14

2 Answers2

9

In the ANSI SQL Standard, there is no TEXT type. There are various string types defined, like CHAR and VARCHAR and many more but no mention of a TEXT type. There is a CHARACTER LARGE OBJECT and a BINARY LARGE OBJECT.

The various TEXT types found in various DBMS (Postgres, SQL Server, MySQL) are additions and have small differences between them. Their similarity is that they can hold very large strings.

SQL Server's TEXT is being deprecated. MySQL's TEXT is not deprecated. PostgreSQL's TEXT is well supported and recommended by many to be used everywhere, in place of VARCHAR(n). From Postgres documentation, Character Types:

In addition, PostgreSQL provides the text type, which stores strings of any length. Although the type text is not in the SQL standard, several other SQL database management systems have it as well.

So, the questions:

The question is, what does the ANSI SQL standard have to say about deprecating text, and the best alternative? Also, does MySQL/MariaDB also follow this?

are simple to answer: Nothing and No. There is no deprecation of TEXT as there is no TEXT type. TEXT isn't even a reserved keyword in ANSI SQL. One DBMS (MySQL) does not have to follow another DBMS's (SQL Server) deprecation decisions for a type that isn't in the standard.

ypercubeᵀᴹ
  • 97,895
  • 13
  • 214
  • 305
  • 1
    Thanks. That’s the answer I was looking for — information about TEXT rather than information about SQL Server. I also note that PostgreSQL appears to prefer varchar (no length) to text. Of the popular databases I am aware that MySQL is furthest from ANSI in many respects. – Manngo Jun 28 '16 at 11:34
  • 1
    text is slightly more efficient than varchar in Postgres. I think there was an answer in the site, by @Erwin, explaining why. All DBMS have differences with the standard but Postgres is one that has implemented many syntax features. I'd say it's the one closest to the standard. – ypercubeᵀᴹ Jun 28 '16 at 11:38
0

The ANSI SQL standard (which I assume you refer to) will nothing to say on how Microsoft (or Oracle, etc) implement and remove data types to suit their particular flavour of DBMS, and which ones take precedence.

The Microsoft standard is to use varchar in place of text, nvarchar in place of ntext, and varbinary in place of image, and this is because of the improved capabilities/performance of the data types and how it is stored in the database.

Here's a related StackOverflow question and answer that should give you more details: https://stackoverflow.com/questions/564755/sql-server-text-type-vs-varchar-data-type

Update

This is as close as you're going to get to reading the standard without buying it. You're interested in section 4.4.3.2 Character String types

http://jtc1sc32.org/doc/N1951-2000/32N1963T-text_for_ballot-FCD_9075-1.pdf

As I said earlier - the standard has no bearing on how anyone deprecates a data type, nor anything about specific data types. Just what a type itself should conform to.

I've also found that the PDF I've linked to can be found on the SQL:2011 Wiki page (which I assume you've checked before asking this question) : https://en.wikipedia.org/wiki/SQL:2011

Stephen Falken
  • 1,195
  • 6
  • 11