Sql-server – Selecting the right data type for text

database-designsql server

In my database, I have columns that should contain text pieces.

  • What is the best data type used for texts in SQL?
  • How can I show that text as a numbered or bullet list?
  • Can I search for a certain word or sentence in that text?
  • Can I make some words in the text piece as a hyperlinks to refers to another columns content?

Example about the Augmentin medicine

  • Augmentin is a penicillin antibiotic.
  • Augmentin contains a combination of amoxicillin and clavulanate potassium.
  • Amoxicillin is an antibiotic in a group of drugs called penicillins.
  • Clavulanate potassium is a form of clavulanic acid, which is similar to penicillin.
  • Augmentin is used to treat many different infections caused by bacteria, such as sinusitis, pneumonia, ear infections, bronchitis, urinary tract infections, and infections of the skin.

Best Answer

Data type - VARCHAR for ANSI which uses 1 byte per character or NVARCHAR for UNICODE which uses 2 bytes per character. In your case, I would suggest NVARCHAR, so you can store multiple languages within the same columns. Avoid TEXT and NTEXT as they are deprecated.

Searching - It is possible to use LIKE predicate, for simple searching with Wild Cards, but I suggest you investigate the use of Full Text indexing which will increase query performance and also allow you to use the FULLTEXT predicate for very flexible fuzzy searching.

Bullets and hyperlinks - I think you are thinking of SQL in the wrong way here. I think you should be thinking of those tasks within your client application. To facilitate this from the back end, hyperlinks I would split into a separate table with a key that you can then report on. For bullets, again strongly suggest controlling this through the client side, but if you really wanted to, you could insert a U+2022 at the beginning of each row. If there are multiple bullets per row of data, you could store this char at the beginning of each bullet and then use a string splitting function when reading from the table. Many ways of accomplishing this but suggest using XML sub query.

Related Question