Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

sql server output data types

gregh
7 - Meteor

 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? 

 

6 REPLIES 6
s_pichaipillai
12 - Quasar

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
7 - Meteor

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
ACE Emeritus
ACE Emeritus

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
12 - Quasar

@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
6 - Meteoroid

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
5 - Atom

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,

Labels