Why Does SQL Server Display My Data Like That?

Frequently, members of this forum ask questions regarding storage and display of data in some particular format. Why does my date display as '2003-01-10 00:00:00.000' when I input it as '01/10/2003'? Why does SQL Server add '1900-01-01' to my datetime column when I only inserted a time value? What happened to the leading zeros in my integer column? Why does SQL Server trim trailing zeros from a decimal column?

It is critically important to learn the difference between storage format and presentation format. Equally important is to differentiate between the storage engine and the user interface or client program. Too many mistakes are made trying to store data in the way users (or developers) want to see it on a screen or in a report. For example, developers often choose to store numeric values or dates as character data in order to preserve some formatting. They then find that sorts, searches, computations and other operations are much more difficult.

Data should be stored in an appropriate and natural data type. Various client tools can present the data in different formats. The T-SQL language allows conversions, concatenations, trimming, etc. The same is true of VB, ASP, and other languages. Often form and report fields can be set to certain formats.

In a recent post, someone said that SQL Server doesn't drop the trailing zeros from decimal columns and provided a query to prove it. The query ran in Query Analyzer and the trailing zeros were displayed not trimmed. It would be more accurate to say that QA didn't trim the zeros. QA is not SQL Server. It is simply a client program that displays data. It has its own default formats for displaying various data types.

If you opened the same table in Enterprise Manager and looked at the data, trailing spaces would be trimmed from decimal columns. That doesn't mean the trailing spaces don't exist in the database. EM is not SQL Server. It is a client program that formats and displays data differently than QA.

So we have two client tools, EM and QA, made by the same company, included with the same database, displaying the same data and each displays the data differently. The point I'm trying to make is that developers must learn the difference between storage and presentation of data. They must understand the tools they use and not try to use a database to format data. Use the client software to format data for presentation to the user and use the database to store the data in the most efficient and appropriate format.

As always, comments, corrections and suggestions are welcome.