SQL Server

Articles about using the SQL Server database engine as a .NET developer.

SQL – Aggregate functions with GROUP BY

by

There are five main aggregate functions in SQL: COUNT(), SUM(), AVG(), MIN(), and MAX(). These calculate aggregate values for sets of rows. When you use them with GROUP BY, they calculate the aggregate values for each group. Let’s say you want to get movie streaming stats for each user. Here’s an example of using GROUP … Read more

SQL – Using GROUP BY with ORDER BY

by

GROUP BY groups rows based on one or more column and produces grouped rows. ORDER BY sorts rows. You can use these together to sort groups. There are two options you can use for sorting groups: I’ll show examples below. At the end, I’ll talk about how SQL Server may implicitly sort groups (and why … Read more

Get SQL Server query results as JSON

by

The simplest way to get query results as JSON is to use FOR JSON PATH in the query (note: this was added in SQL Server 2016): It returns the results as a single JSON string with one JSON object per row: Note: SQL Server returns the JSON without indenting. All examples in this article show … Read more

Case sensitivity in SQL Server

by

In SQL Server, the collation property controls case sensitivity. Case sensitivity affects sorting and queries (even the column names must match exactly if you’re using a case-sensitive collation at the database-level). The default collation when you create a database in SQL Server is SQL_Latin1_General_CP1_CI_AS. The CI stands for case-insensitive, which means SQL Server databases are … Read more

SqlException: Cannot insert explicit value for identity column

by

When you have a table with an identity column, and you try to specify the value for identity column when inserting a record, you’ll get the following exception: Microsoft.Data.SqlClient.SqlException (0x80131904): Cannot insert explicit value for identity column in table ‘Movies’ when IDENTITY_INSERT is set to OFF. This error means you have an identity column in … Read more

Cannot drop database because it is currently in use

by

Problem When you try to drop the database, you get the following error and the drop fails: Cannot drop database because it is currently in use. This means there are other open connections on the database and it won’t let you drop the database. Disclaimer: The solutions in this article involve kicking out other active … Read more

SQL Server – Getting and storing date/time

by

In this article, I’ll show built-in functions in SQL Server for getting the current datetime and how to get individual parts of the datetime (such as the year). Then show I’ll show how to store datetimes using the four different date/time data types (date, time, datetime2, and datetimeoffset). Getting the current datetime SQL Server has … Read more

Modifying JSON in SQL Server

by

There are two ways to modify JSON data in a SQL Server table: In this article, I’ll show how to insert, update, and delete JSON data by using the JSON_MODIFY() function. I’ll also show how to deal with JSON arrays. Note: This is part 2 of the mini-series about the SQL Server JSON API. The … Read more

Querying JSON in SQL Server

by

In SQL Server 2016 they added support for handling JSON data. You can add JSON data, query it, and modify it. The JSON API in SQL Server is well-suited for simple scenarios where you only need to do basic querying and manipulation. For advanced querying, the SQL Server JSON API gets complicated and hard to … Read more

SQL Server – Foreach Table

by

To execute a query against all tables in a database, you can use the sp_msForeachTable stored proc, like this: Note: The ? is a placeholder for the table name. In this article I’ll show two examples of using sp_msForeachTable. Example – Get record counts for all tables This query saves record counts to a temp … Read more

SQL – Filtering GROUP BY with HAVING and WHERE

by

There are two ways to filter GROUP BY results: In other words, WHERE controls which rows will be considered for grouping, GROUP BY groups the filtered rows, and then HAVING filters the groups. I’ll show examples below. Example – Using WHERE with GROUP BY Let’s say you have the following table and you want to … Read more

Saving changes is not permitted in SQL Server

by

Problem You’re trying to save a table change in SQL Server Management Studio (SSMS) and you get the error message: Saving changes is not permitted. The changes that you have made require the following tables to be dropped and re-created. You have either made changes to a table that can’t be re-created or enabled the … Read more

SqlTypeException: SqlDateTime overflow

by

Problem I am executing a SQL query and trying to set a datetime column to DateTime.MinValue. I’m getting the following exception: System.Data.SqlTypes.SqlTypeException: ‘SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.’ The problem is that DateTime.MinValue is 1/1/0001 12:00:00 AM and the SQL Server DateTime minimum value is 1/1/1753 12:00 AM. Solution … Read more

‘CREATE/ALTER’ must be the first statement in a query batch

by

Problem When you’re executing a CREATE/ALTER statement to create a procedure/view/function/trigger, you get one of the following errors: ‘CREATE/ALTER PROCEDURE’ must be the first statement in a query batch ‘CREATE VIEW’ must be the first statement in a query batch. ‘CREATE FUNCTION’ must be the first statement in a query batch. ‘CREATE TRIGGER’ must be … Read more