sql server

C# – Use SqlDbType.Structured for table parameters

by

To use a table-valued parameter (TVP) with SqlCommand (ADO.NET), pass in a DataTable as a SqlDbType.Structured parameter, like this: I’ll show a full example, starting with creating a TVP type and then inserting it as a SqlDbType.Structured parameter as shown above. 1 – Create the TVP type in SQL Server To be able to pass … Read more

C# – Execute a stored procedure with Dapper

by

You can execute stored procedures with Dapper by specifying the name, parameters, and CommandType.StoredProcedure. Let’s say you have the following stored procedure (single parameter, returns movie rows): Here’s how to execute this stored procedure with Dapper and map its results to Movie objects: Use an output parameter To get a stored procedure’s output parameter, add … Read more

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 Server – Copy data from one table to another

by

To copy data from one table to an existing table, use INSERT INTO SELECT and specify the column list: If the columns are the exact same in the two tables (and there are no identity columns), you don’t need to specify the column list: When the table doesn’t exist, use SELECT INTO, specifying which columns … 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

SQL Server – Bad SELECT query performance due to missing indexes

by

When you have a slow SELECT query, the most likely cause of the problem is that you’re missing an index. When your table has lots of data, having the right indexes will make a difference in the performance. Without indexes, SQL Server has to scan through your entire table to find the rows that match … 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, Microsoft added support for handling JSON data: 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 use, so you may want to look into other options. … Read more

Using sp_msForeachTable in SQL Server

by

You can use the sp_msForeachTable stored proc in SQL Server to execute a query/command against all tables in the database. To give you an idea about how to use this, here’s an example of using it to do a SELECT query against all tables: Internally, sp_msForeachTable gets all user table names (from dbo.sysobjects) and opens … 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

Conversion failed when converting date and/or time from character string

by

Problem When you’re working with date/times in SQL and want to put a date column in ORDER BY with a CASE statement, you may run into the following error: Conversion failed when converting date and/or time from character string. Here’s an example of a query using ORDER BY with a CASE that produces the error: … 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

C# – Monitor data changes with SQL Server query notifications

by

You can use SQL Server query notifications to send push notifications to your code when data changes. This is an alternative to polling the database for changes. In this article, I’ll show how to configure this feature and work with it in the code. 1 – Enable Service Broker and configure permissions You need to … Read more

Find the distance between two coordinates using SQL and C#

by

Imagine you’re in the middle of Millennium Park in Chicago. You want some coffee, but want a good deal on it. You open an app and see the nearest coffee shops offering deals to anyone with this app. How did this app know the distance from you to the coffee shops? This article will show … Read more