Not null constraints are a great way to add another layer of validation to your data.
Of course, you could perform this validation in your application layer as well, but be aware that inconsistencies happen: someone will forget to add the validation, someone will remove it by accident, someone will bypass validations in a console and insert nulls, etc. The only way to really be sure is to enforce it in your column definition. If you're validating nulls on the database layer as well, you're protected.
NOT NULL for a column in SQL Server, use the
ALTER TABLE .. ALTER COLUMN command and restate the column definition, adding the
NOT NULL attribute.
alter table products alter column brand_id smallint not null;