Database foundations
Data types and choosing well
Money, text, time and identifiers are not interchangeable.
Understand the concept
A data type determines valid stored values and calculation behaviour. int stores integers. decimal(p,s) stores exact decimal values: p is total precision and s is scale. float is approximate; financial totals commonly use decimal. Storing a monetary amount as text makes sorting, arithmetic and validation harder.
In SQL Server, nvarchar stores Unicode text, and an N prefix marks a Unicode literal. Use date for a date, datetime2 for a timestamp, and consider datetimeoffset when an offset is required. Separate UTC storage policy from local display. uniqueidentifier stores GUIDs; identifier choices also affect indexes and distribution.
Choose lengths based on real requirements. Making every string nvarchar(max) weakens validation and can limit indexing choices. Conversions can overflow, truncate or round. Matching application parameter types to column types matters for correctness and query plans.
Use this when designing schemas and application parameters, especially amounts, timestamps and Unicode fields.
Using float for exact monetary arithmetic, or keeping dates and numbers only as text.
One topic. Three levels of depth.
SELECT CAST(12.345 AS decimal(10,2)) AS RoundedAmount;The result is 12.35, rounded to two decimal places.
This page does not execute SQL. Practise in the downloadable SqlStudioLab using SSMS. Before rerunning temporary-table examples, remove only the relevant #table. Views, procedures and functions create persistent lab objects; never use a production connection.
Try it yourself first.
Show the difference between integer and decimal division.
Show the solution and explanation
SELECT 5 / 2 AS IntegerDivision, CAST(5 AS decimal(10,2)) / 2 AS DecimalDivision;Integer division yields 2; decimal division yields 2.5. Displayed scale depends on the resulting type.