Dropping or modifying a column in SQL Server can look straightforward, but it often isn’t. That column might be referenced by other objects in the database, and removing it without checking can break things silently. Unlike dropping a whole table, where SQL Server is very strict about dependencies, column-level references are not always enforced or even tracked. That’s why it’s important to do some homework before making the change.
mssql
Finding Foreign Keys that Use SET NULL for Deletes and Updates in SQL Server
When you set up foreign key relationships in SQL Server, you have a choice for how changes in the parent table affect related rows in the child table. One option is SET NULL, which replaces the foreign key value with NULL whenever the parent row is deleted or updated.
This behavior is useful in scenarios where you’d rather keep the child record around but cut the link once the parent no longer exists. For example, if a project is deleted, you might want to keep related tasks but mark their ProjectId as NULL.
The problem is that it’s not always obvious which foreign keys are configured with SET NULL, especially in large databases. Fortunately, SQL Server’s system views make it possible to query this information directly.
Formatting Numbers for International Users in SQL Server (Locale-Aware)
When working with applications that serve people across different countries, you quickly realize that numbers aren’t always written the same way. A salary of 55,000.75 in the U.S. might be displayed as 55.000,75 in Germany or 55 000,75 in France. The decimal and thousands separators change depending on a user’s locale.
If you’re storing numbers in SQL Server but want to display them in a format that makes sense internationally, you’ll want to tap into SQL Server’s locale-aware formatting.
Comparing COL_LENGTH() and DATALENGTH() in SQL Server
SQL Server has a COL_LENGTH() function and a DATALENGTH() function that could easily be confused for doing the same thing. They both have “length” in their name, and they do indeed return a “length”. But the length returned is different for each function.
If you’ve ever wondered why DATALENGTH() gives you different numbers than COL_LENGTH(), read on to find out.
Simple CONCAT() Usage vs Manual String Building in SQL Server
When working with SQL Server, string concatenation is one of those everyday tasks that’s easy to take for granted. It can feel like second nature to reach for the trusty old + operator to piece together strings, but SQL Server also provides an alternative way to handle concatenations. Yes, I’m referring to the CONCAT() function.
And there’s a subtle difference between the two approaches that might sway you towards using one or the other.
Let’s compare these two approaches to building strings in SQL Server.
Step by Step Guide to Calculating and Formatting Percentages in SQL Server
When you’re writing reports in SQL Server, one of the first little annoyances you’ll probably bump into is how percentages show up. By default, SQL Server doesn’t have a built-in “percent” data type. Percentages are usually stored as decimals (for example, 0.25 for 25%), and if you just throw those into a report, they won’t look the way people expect.
So you’ll need to do a bit of work to get it nicely formatted into a percentage format that people expect to see.
Also, if you’re calculating percentages from raw values then that will require some more work.
In this article we’ll walk through an example of how to calculate and format percentages in SQL Server.
Cleaning Numeric Strings Before Conversion in SQL Server
Working with messy numeric data is one of those unavoidable realities in database development. Whether you’re importing data from CSV files, web APIs, or legacy systems, you’ll often encounter numeric values stored as strings with all sorts of unwanted characters mixed in. SQL Server’s conversion functions are pretty strict about what they’ll accept, so you need to clean up these strings before attempting any conversions.
Format Different Currencies (such as USD, EUR, AUD) in SQL Server
When working with financial data in SQL Server, you may occasionally need to present numbers as formatted currency values. Storing currency amounts as DECIMAL or MONEY types is common, but these result in raw numbers like 1234.5, which don’t tell users which currency it’s in. With a bit of formatting logic, you can make query results easier to read and more meaningful.
How to Get the Name of the Current Database in SQL Server (T-SQL)
When running queries in SQL Server, you’ll need to know which database your query is running against. If you’re using a GUI tool like SSMS, you’ll usually see the database expanded in the object explorer. Perhaps you even navigated to the database before opening a query window. In such cases, there will be no doubt which database you’re querying.
What Compatibility Level Means in SQL Server
If you’ve been working with SQL Server for a while, you’ve probably come across the term “compatibility level” at some point. Maybe you’ve seen it in database properties or heard someone mention it during a migration discussion. But what exactly does it mean, and why should you care?