WANTED TO KNOW THE DIFFERENCE BETWEEN VARCHAR(Max), Text, Image in storing string value more than 100k characters RRS feed

  • Question

  • I have a xml and json string having more than 100k charaters. I want to store it in SQL server database. which one will be the best option to go for? Either varchar(max) or text or blob  Image type. I know image would be best for stoing text file, documents. My question is we can convert xml string in to xml file and store it as xml file in to image type right? will that be a best option? please advise

    Friday, February 20, 2015 7:14 PM


  • Text and image are deprecated types, avoid them in new development.

    Use varchar(max) instead of text, nvarchar(max) instead of ntext (for unicode characters), and varbinary(max) instead of "image" type.

    All "max" types are limited to 2GB size, have certain overhead, and are not easily accessible from outside of the database. To avoid those limitations, data can be stored as files in the filesystem using FILESTREAM or FILETABLE (sql 2012+) - the types best used if really large files are needed to be stored. Those two are part of the transactions and database backup, but are also accessible from outside of the database, omitting the buffer pool.

    Friday, February 20, 2015 7:43 PM