Data Types

Grant Fritchey implores you to use the correct data type:

Far too often then, the easy answer, just change the column to varchar. You can trim the time and output in exactly the format needed by the business. Problem solved and it was easy…

Well, until someone inputs “Janry 20, 2009” slightly mangling the spelling and suddenly your report looks all messed up. Or, they ask you to start filtering just the last two weeks, regardless of when the report was run and you now can’t easily dodate math on the column. Even after you get over that problem with a little formatting using CAST (along with ISDATE to try to catch all those other typos that are in the system now)  you notice that the performance is really slow so you go to put an index on the column and now you have an index key that is 50 bytes wide instead of the 3 bytes that the DATE data type would have been, making the index less efficient (not to mention, sorting the data is going to put February ahead of January, more formatting).

Read the whole thing.

Related Posts

Thoughts On UTF-8 Encoding In SQL Server 2019

Solomon Rutzky digs into UTF-8 support in SQL Server 2019 and has found a few bugs: Let’s start with what we are told about this new feature. According to the documentation, the new UTF-8 Collations: can be used … as a database-level default Collation as a column-level Collation by appending “_UTF8” to the end of […]

Read More

Parsing Numeric Values From Multiple Cultures

Bert Wagner shows us a good way of converting strings to numbers when multiple cultures are in play: Why are the salaries stored as nvarchar and formatted with commas, spaces, and periods? Great question!  Someone wanted to make sure these amounts would look good in the UI so storing the formatted values in the database […]

Read More

Categories

May 2016
MTWTFSS
« Apr Jun »
 1
2345678
9101112131415
16171819202122
23242526272829
3031