how to

How to Use DEFAULT Constraints to Avoid NULL Insert Errors in SQL Server

by

When working with SQL databases like SQL Server, one common headache that can rear its ugly head from time to time is handling NULL values during inserts. Sometimes you just want a column to have a sensible default value if nothing is provided, without throwing errors or forcing developers to remember to include it every time. That’s where DEFAULT constraints come in.

A DEFAULT constraint automatically inserts a predefined value into a column when no explicit value is provided. This helps ensure consistency, prevents unwanted NULLs, and makes inserts cleaner.

Read more

Format Current Date as YYYYMMDD in SQL Server

by

The ISO 8601 standard says dates should look like YYYY-MM-DD to avoid confusion between formats like MM/DD/YYYY or DD/MM/YYYY. But sometimes you might need to remove the hyphens and display the date as YYYYMMDD. Maybe your software doesn’t accept special characters, or you’re trying to save space. Whatever the case, here are some simple ways to get today’s date into YYYYMMDD format.

Read more

How to Safely Drop and Recreate a Table If It Exists in SQL Server

by

Sometimes you need to rebuild a table from scratch. This will usually involve dropping the table and recreating it. Maybe it’s for a schema update, or maybe it’s for reloading data in a staging environment. Whatever the reason, SQL Server provides a straightforward way to handle this.

While we can certainly go right ahead and drop the table, what if it doesn’t actually exist? In this case we’ll get an error. That is unless we take measures to prevent such an error. Such measures will involve checking for the existence of the table before attempting to drop it.

Read more

Understanding and Locating SET DEFAULT Referential Actions in SQL Server

by

When you define a foreign key in SQL Server, you can choose what happens to the child rows when the parent row is deleted or updated. One option is SET DEFAULT. With this setting, SQL Server updates the foreign key column in the child table to its default value whenever the parent key is deleted or updated.

It’s not the most common option, but it can be useful if you want to preserve child records while moving them to a “default” category, user, or state.

Read more

Cascading Deletes with Foreign Keys in SQL Server

by

One of the neat features in SQL Server is the ability to set up cascading deletes on foreign keys. Instead of writing a bunch of manual DELETE statements to keep related tables in sync, you can let the database handle it for you. This is especially useful when you’re working with parent-child relationships, where deleting a parent should also remove all of its children automatically.

Read more

Using SUM() and AVG() with GROUP BY in SQL Server

by

When working with data, we often need to roll up numbers by categories. For example, calculating sales totals by region, or averaging test scores by class. SQL Server’s SUM() and AVG() functions can work perfectly for this scenario when combined with the GROUP BY clause. This combo can provide quick insights without having to do the math yourself. Let’s walk through how this works with an example.

Read more

Using Multiple CTEs in a Single Query

by

Common Table Expressions (CTEs) are a handy way to break down a complex query into readable, reusable pieces. When you need several intermediate results – say, a filtered set, an aggregation, and a ranking – you can stack multiple CTE definitions together. PostgreSQL, SQL Server, MySQL 8+, and many other engines support this syntax.

Read more

How to Add Days and Months to Dates in SQL Server

by

When working with SQL Server, you may often find yourself having to shift a date by a certain number of days or months. While it sounds simple, the right function and approach can save you from subtle bugs and errors, especially when dealing with edge cases like leap years or month-end rollovers.

Let’s take a look at how to add days and months to dates in SQL Server.

Read more

Troubleshooting Date Format Errors in SQL Server Imports

by

Importing data into SQL Server is usually quite straightforward. That is, until you run into date and time formatting issues. Dates that look fine in a CSV, Excel, or flat file can suddenly throw errors or, worse, silently load with the wrong values. Since SQL Server is strict about how it interprets dates, mismatches between source file formats and SQL Server’s expectations are one of the most common headaches during imports.

This article looks at why these errors happen, what SQL Server expects, and how to troubleshoot these pesky date format issues.

Read more

Fixing Multiple SELECT Expressions Errors in SQL Server

by

If you’ve worked with SQL Server long enough, you’ve probably run into the dreaded “Only one expression can be specified in the select list when the subquery is not introduced with EXISTS” error. This error usually pops up when you try to use a subquery in a place where SQL Server expects a single value, but your query is returning multiple columns or multiple rows.

It’s a simple mistake, but it can be frustrating until you understand why it happens and how to fix it.

Read more