Hi, I have a table I am importing from excel where are few of the cells have a length of 450. What data type should I use when I create the table? Varchar(MAX) or should I use something else. Using sql server 2017.
Thanks
If the max length is 450 characters, VARCHAR(MAX) is going to be overkill. Even VARCHAR(1000) would be overkill, but would give you wiggle room. But it depends too. If your excel file never has more than 450 characters, then VARCHAR(450) may make sense.
Also, if you have any unicode data, you will want to use NVARCHAR instead of VARCHAR.
If you are going to have over 8000 bytes of data (approximately 8000 characters), then VARCHAR(MAX) makes sense, but otherwise, I'd use a smaller data size.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
March 10, 2021 at 8:21 pm
Thanks. I don't think it has unicode but I'm not sure what exactly that is and I haven't been able to find any good samples by googling.
March 10, 2021 at 8:42 pm
Unicode is basically special characters OR non-english characters. If it is all characters you can see on your keyboard and you have a USA keyboard layout, then you likely don't need UNICODE (ie NVARCHAR/NCHAR). I add that "live in" part because keyboard layouts outside of the USA sometimes have some unicode characters on them (including Canada).
???¦?? <-- examples of a UNICODE characters
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
March 11, 2021 at 2:22 am
This was removed by the editor as SPAM
March 11, 2021 at 8:58 am
This was removed by the editor as SPAM
March 11, 2021 at 2:46 pm
I just noticed my "UNICODE" examples got screwed up.
As for SheilaMajors's response about "LONG TEXT" and "MEMO", those are not valid SQL Server data types that I am aware of. Link - https://docs.microsoft.com/en-us/sql/t-sql/data-types/data-types-transact-sql?view=sql-server-ver15
Your choices for character text are CHAR, VARCHAR, or TEXT (with text being removed in a future version of SQL so it should be avoided). Technically, you could use other datatypes like BINARY or VARBINARY, but then you are needing to convert it before you can do much with it.
I expect in your use case, VARCHAR will be the best bet, and I would pick a size that makes sense. VARCHAR(MAX) lets you use up to 2 GB of data which is definitely overkill for up to 450 characters at 8 bits (1 byte) per character. But I know for me, I like to leave room when the size is not enforced. Last thing you want is for the text to be truncated on import, but you just need to pick a good max size. If Excel is storing 450 characters max right now, I would double that as one day someone will put more data in.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply