SQLITE limitation - number of fields

[mbarone]

Hi folks,

Anyone know what the max number of fields you can have in a SQLITE database?  Google provideds mixed messages.  Whatever it is, anyway to work around it in Alteryx?  We want to put the final data for a predicive model into one table.  But it was requested for it to be de-normalized, resulting in a large number of fields, so I was curious.  

Thanks. 

[mbarone]

Just tested it and it looks like it's 999 for the max fields.  From what I saw on Google, the default limit is 2000.  Why is Alteryx only 999?

I know everyone's dying to know - why the heck would you need more than 999 fields????

Short answer --- it's significanlty faster to run some algorithms on one "huge-number-of-fields" table, than to join hundreds of smaller tables, and then run the algorithms.

[PeterS · Accepted solution]

Hello Michael,

After checking with our developers, the inability to export more than 999 fields to an SQLite database is due to a limit that is hardcoded into the SQLite driver.  Unfortuantely there are no work arounds.

[_richardr]

I just want to clarify this is a issue with the driver not SQLite as it supports by default 2000 columns.

"The default setting for SQLITE_MAX_COLUMN is 2000. You can change it at compile time to values as large as 32767. On the other hand, many experienced database designers will argue that a well-normalized database will never need more than 100 columns in a table." - http://sqlite.org/limits.html