Get SQL Server query results as JSON

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

SQL Server – How to use INFORMATION_SCHEMA to find database metadata

Let’s say you want to find all columns in a database that have the word ‘Name’ in the column name. You can query INFORMATION_SCHEMA.COLUMNS, like this: This query returns the following five rows showing the table name, column name, and column data type: TABLE_NAME COLUMN_NAME DATA_TYPE Actors FirstName nvarchar Actors LastName nvarchar Actors Fullname nvarchar … Read more

Case sensitivity in SQL Server

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

SQL Server – DB is currently in use

Problem Let’s say you’re running in a test/dev environment and you’re trying to alter the database. For example, you may be trying to drop the database: But you get the following error message and your command fails: Cannot drop database “CSI” because it is currently in use. This means there are other open connections on … Read more

SQL Server – Getting and storing date/time

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

C# – How to use SqlBulkCopy to do a Bulk Insert

If you find yourself doing SQL Inserts in a loop, you may want to consider doing a Bulk Insert instead. You can use SqlBulkCopy to do a Bulk Insert from C#. Make sure to prove you have a performance problem before switching your code to use Bulk Insert. Like any optimization, it adds complexity. In … Read more

Modifying JSON in SQL Server

There are two ways to modify JSON data in a SQL Server table: Use the JSON_MODIFY() function in an UPDATE statement to change individual properties inside the JSON data. Completely replace the JSON data with a regular UPDATE statement. In this article, I’ll show how to insert, update, and delete JSON data by using the … Read more

Querying JSON in SQL Server

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

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 Server – Get the next id using OUTPUT instead of IDENTITY or SEQUENCE

There are multiple ways to increment ids in SQL Server: IDENTITY columns SEQUENCEs (added in SQL Server 2012) Atomic UPDATEs with the OUTPUT clause In this article I’ll show the third way – using an atomic UPDATE with OUTPUT. Tables – Ids and Users I have the following two tables – Ids and Users: Stored … Read more