Home Archive by category "Fundamentals"

Category: Fundamentals

Fundamentals: varchar is NOT a one size fits all data type

A common, and fatal, mistake I often see is the use of varchar/nvarchar being used for data that should be stored in a data type more appropriate; for example a date and time value. Storing data in a (n)varchar when it should be stored in a far more appropriate data type can have disastrous consequences for both the performance of your queries and the behaviour, as well as making tasks that seem like they should be simple far more difficult. The most common data type I see stored in the wrong data type is date (and time) data; normally because…

Continue reading

Fundamentals: The semicolon (;) is a statement terminator

The title pretty much says it all here, when you’re writing T-SQL, the correct place to place the semicolon (;) is at the end of all you’re statements. Something I see quite often is batches where people don’t terminate any of their statements, but do start statements that use a CTE or Merge statement with a semicolon. This means you see syntax like the below: DECLARE @MyVariable int SET @MyVariable = 5 ;WITH MyCTE AS( SELECT *, ROW_NUMBER() OVER (PARTITION BY CONVERT(date,MyDate) ORDER BY MyDate DESC) AS RN FROM MyTable WHERE MyColumn = @MyVariable) SELECT * FROM MyCTE WHERE RN…

Continue reading

Fundamentals: White space and Line Breaks

Something that is important in any language is formatting. I don’t just mean in computer languages, such as Python (where good use of tabbing is required as part of the syntax), I mean in any written language. From T-SQL to English, Rust to Japanese and Braille to Russian, what ever language you’re writing it’s important to make sure it’s well presented. Even this post you’re reading now, I’ve tried to ensure has good formatting, and is why I use a code prettifier/beautifier. So, why is formatting so important? One of the biggest reasons is that it makes your code readable….

Continue reading