When building reports in SQL Server, dates are probably one of the most common pieces of data you’ll deal with. Sometimes a report needs the full day name like “Monday”, while in other cases a short form like “Mon” is preferred, often to save space. Luckily, SQL Server has built-in functionality to handle both, without having to manually map numbers to names.
Let’s look at how we can display abbreviated and full day names in queries so that our reports are nice and easy to read.
If you spend much time writing T-SQL scripts, you’ve probably seen the GO keyword. It looks like a T-SQL command, but it’s not really part of T-SQL. Instead, it’s a batch separator recognized by SQL Server Management Studio (SSMS) and other client tools. When you hit the “Execute” button, any GO keyword in your script tells the tool that this is the end of a batch, and to send what came before it to SQL Server as one unit.
Many scripts will run fine without the GO keyword, but others will fail miserably. Understanding how GO works can save you from frustrating errors and unexpected behavior.
When working with relational databases in SQL Server, foreign keys help maintain referential integrity between tables. Sometimes these constraints are defined with actions such as CASCADE, which automatically propagates updates or deletions from a parent table to related rows in a child table.
While this functionality can be useful, it can also introduce unexpected side effects if you are not aware of where it is enabled. Knowing how to identify foreign keys with cascade actions is an important part of understanding data dependencies, troubleshooting issues, and ensuring database operations behave as intended.
Working with real-world data often means dealing with messy strings. It’s common to find values that contain unexpected special characters. Sometimes this is due to user input, sometimes it’s from imports or third-party sources.
Either way, when we need to find and filter these special characters, SQL Server gives us some handy tools to work with. For starters, there’s the LIKE operator, which anyone who’s used SQL would be familiar with. But there’s also the PATINDEX() function, which performs a slightly different task.
If you’ve worked with SQL Server long enough, you’ve probably hit the dreaded error when trying to use ORDER BY inside a view or CTE. It usually shows up as something like:
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.
This rule can come as a bit of a surprise because it feels natural to want a query “pre-sorted” when wrapping it in a view or CTE. The problem is that SQL Server doesn’t allow the ORDER BY clause in this context unless it’s in conjunction with the clauses mentioned in the error message. Without such clauses, you need to explicitly request it at the outermost SELECT.
Let’s walk through an example of how to handle this.
When working with aggregate functions in SQL Server, it’s easy to overlook that certain datatypes have strict limits. If you’re summing values in large tables (or even just summing very large numbers), you might run into an arithmetic overflow error. This happens when the result of an aggregate exceeds the maximum value the datatype can hold.
Understanding how this works and how to prevent errors will help you write reliable queries.
When you’re working with real-world data, duplicates can sometimes sneak in more often than we’d like. Maybe an import process didn’t filter things properly, or users managed to submit the same information twice. Whatever the reason, finding and handling duplicates is a common task.
A neat way to tackle this problem in SQL Server is by using window functions. Specifically, we can use ROW_NUMBER() or RANK() to find duplicate rows in a table. These functions let you assign a sequence number to rows within a group, which makes it easy to spot duplicates.
When dealing with applications that span multiple time zones, you’ll often want to store timestamps in UTC. That’s usually the best practice – it avoids confusion and ensures consistency. But sooner or later you’ll need to show users dates and times in their own local time zones. There are a few ways to handle this in SQL Server.
When working with SQL Server, numeric precision can become a silent troublemaker if you’re not careful. Overflow errors happen when a number exceeds the storage capacity of the column data type. Unlike other errors that are easy to catch in testing, numeric overflow often shows up unexpectedly in production, which can be costly. Understanding how to pick the right precision and scale for your NUMERIC or DECIMAL columns can save you headaches down the road.
Working with dates and times in SQL Server can get tricky, especially once you add time zones into the mix. One common point of confusion is the difference between the server’s time zone and the session’s time zone. If you’re not careful, you can end up with inconsistent data or timestamps that don’t line up with what your users expect.
Let’s look at how SQL Server treats time zones and how you can handle server versus session time zone differences cleanly.