Import Excel - can we change default varchar size from 4000 to 900?
Posted: Fri 29 Nov 2013 15:51
Whenever importing workbooks, the data type for text gets set to varchar(4000)
Is there a way to change the DEFAULT length value? In a normalized table it is rare to have all fields of this size - but more importantly, the maximum length that T-SQL allows for INDEXING is 900, so if I was to have a default value which is most flexible, it would be 900, not 4000. Since spreadsheets tend to be flat data with many columns that have to be analyzed in many ways - I always have to go through each column before importing to figure out which column I may want to create an index on and change its size to 900 or less. The number of fields that may need to be indexed is much higher than the number of fields that may contain 4000 characters.
Carlo
Is there a way to change the DEFAULT length value? In a normalized table it is rare to have all fields of this size - but more importantly, the maximum length that T-SQL allows for INDEXING is 900, so if I was to have a default value which is most flexible, it would be 900, not 4000. Since spreadsheets tend to be flat data with many columns that have to be analyzed in many ways - I always have to go through each column before importing to figure out which column I may want to create an index on and change its size to 900 or less. The number of fields that may need to be indexed is much higher than the number of fields that may contain 4000 characters.
Carlo