Datatypes text and ntext in SQL Server will be depracated

[Frank]

In a fututre release from SQL Server the datatypes text, ntext and image will be deprecated. It is already a bad datatype because you cannot use it as a "normal" character string. No equal to sometinh else in T-SQL on a text datatype.

As far as I know Alteryx defaults to text (my source is a PostgreSQL database) when creating the table in SQL Server. The datatype in Alteryx is Vstring. Instead of text or ntext it would be zo much better to use varchar(MAX) or nvarchar(MAX) when creating the table. Not only for compatiblity and later use in T-SQL (if any), but it is faster as well. Data from a varchar(MAX) column is stored in the same page as the record, as log as it fits.

[Frank]

Above results in this kind of SQL query's:

SELECT  *
FROM     [concept_work_servicedesk].[PostgreSQL].[_http_requests] AS T
WHERE  CONVERT(varchar(MAX), T.vizql_session) = 'AAAAA621343E418B8E186984D3025962-0:1'
 AND       CONVERT(varchar(MAX), completed_at_chr) = '2016-01-25 11:57:46.9910000'

Notice the conversion.

[blyons]

I strongly encourage this ASAP. TEXT, NTEXT and IMAGE data types were deprecated since SQL Server 2005 (https://msdn.microsoft.com/en-us/library/ms143729(v=SQL.90).aspx).

[stephen81]

This is causing me a lot of extra work. Can this be changed please?

[SeanAdams]

Fully agree with this suggestion - we ran into this just yesterday.

Please can we eliminate the use of Text and nText from Alteryx as SQL output types.

[gregh]

agreed -we too have this problem, and have had to set up specific checks on the database to remedy it.

[ARich]

Hi All,

Thanks for the feedback. We're looking into this.

Best,

Alex

@RichardC

[anotherusername]

In the interim, how do you force it to remain an NVARCHAR?  Lengths up to 1023 are fine, but 1024 onwards converts to TEXT or NTEXT.

Tried searching on the forums but haven't found a solution.

(I'm using Netezza, which doesn't have TEXT/NTEXT, so the workflow fails to even write.  If I have an existing DB table defined then it's fine, but if I'm creating a new table, or streaming data into an in-DB tool, then it tries to use TEXT or NTEXT and errors out with "ERROR: Invalid datatype - TEXT")

[blyons]

@anotherusername, It isn't easy. You would need to build your own macro that reads the field types, creates dynamic SQL code using your desired data types instead of Alteryx's, and creates the table.

And, FWIW, this Idea is specific to SQL Server. If you want it to apply to Netezza, you might want to post another.

[MaudyP]

@SeanAdams, Can you please tell us what version of SQL Server and what version of Alteryx you are using?

As of Alteryx 11.0, we support the defaulting to VARCHAR for SQL Server 2016 and above.

https://docs.microsoft.com/en-us/sql/database-engine/deprecated-database-engine-features-in-sql-serv...

[blyons]

I'm sorry, @MaudyP, but I beg to differ. I am running Alteryx Designer 11.7.4.37815, and SQL Server 2016 (13.0.4466.4). I just this simple workflow to test this:

It selects records from a table with this structure:

[RecordId] [bigint] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[AccountNumber] [bigint] NOT NULL,
[Date] [datetime] NOT NULL,
[CommunicationType] [varchar](10) NULL,
[ShortComment] [varchar](70) NULL,
[LongComment] [varchar](max) NULL,
[ResponseId] [bigint] NULL,
[InboundOrOutbound] [char](1) NULL,
[ExternalDocumentAddress] [varchar](900) NULL,
[SourceCode] [varchar](10) NULL,
[AssignedToAccessorId] [bigint] NULL,
[Status] [char](1) NULL,

and it created a table with this structure:

[RecordId] [bigint] NULL,
[AccountNumber] [bigint] NULL,
[Date] [datetime] NULL,
[CommunicationType] [varchar](10) NULL,
[ShortComment] [varchar](70) NULL,
[LongComment] [text] NULL,
[ResponseId] [bigint] NULL,
[InboundOrOutbound] [char](1) NULL,
[ExternalDocumentAddress] [varchar](900) NULL,
[SourceCode] [varchar](10) NULL,
[AssignedToAccessorId] [bigint] NULL,
[Status] [char](1) NULL

Note the red [text] field.

bl