sql server output data types

[gregh]

 What's the logic behind conversion from Alteryx field types to sql server types?
- I have found v_string usually goes to nvarchar (would have expected varchar)

and above some size (>1000, <6000) it changes to text (sometimes ntext) -> odd as sql server supports up to 8000

this has significant speed implications (~6x slower to text than varchar)

any ideas? 

[s_pichaipillai · Accepted solution]

Greg,

you are right V-String == Varchar . not sure why it considers as Nvarchar

are you creating the table On the Fly in Alteryx,

can you Design your table with Varchar if you feel that its VString? and then Map the coulumns in Alteryx

FYI : if you have your data size more than 8000 Byts, you can use Varchar (Max)

by the way, which version of SQL server are you using ?

[gregh]

Thanks - I think that is what we'll have to do... looks like [text] & [varchar](max) are similar speeds, while [varchar](xx) is much faster

(sql server 2012)

[jdunkerley79]

Alteryx assumes strings are Unicode (http://help.alteryx.com/9.5/CodePages.htm).

Hence nvarchar is the appropriate SQL Server type if it is creating the table.

[s_pichaipillai]

@gregh

If Varchar(max) is necessary, can enable Row or Page compression on your table and test the performance?

just a thought , how we can tune outside of Alteryx

and Another option, did you test with Bulk Load option for loading data into SQL Server?

[Frank]

If you make a varchar(8000) or smaller it is stored in the table dataspace. It will thus be compressed with datacompression. A varchar(MAX) is stored outside the datapage. So getting this value takes a little bit longer.

Look at this page: https://technet.microsoft.com/en-us/library/ms189087(v=sql.105).aspx

In short: varchar(MAX) is, without additional settings, sored in the same page as the record if it fits. If it doesn't fit the content will move ans only a reference remains. This will cost performance.

By the way: get rid of text/ntext. They will not be available soon and are already giving problems in T-SQL.

[Eddy2018]

Hi,

Is there a way to have the output Append option work for a pre sql statement that already created the columns as a varchar(255)?

thanks,