How to solve Arithmetic overflow error converting IDENTITY to data type tinyint, smallint or int in Microsoft SQL Server database

Last year we had a production issue where one of our backup jobs was failing while inserting Orders aggregated from other systems into our SQL Server database. The reason was dreaded "Arithmetic overflow error converting IDENTITY to data type int" because the table was using IDENTITY feature of SQL Server to generate OrderId, and Identity has breached it a maximum value, which is around 2.1 billion, precisely 2,147,483,647. The error "Arithmetic overflow error converting IDENTITY to data type int" comes when the IDENTITY value is inserted into a column of data type int, but the value is out-of-range. For example, if the current value of Identity becomes more than 2,147,483,647, then you cannot store that into an int column because it's more than the maximum value of int in SQL Server.

Top 5 Free Microsoft SQL Server Books in 2025 - PDF Download or Online Read

Hello Guys, if you want to learn SQL Server and looking for free resources then you have come to the right place. Earlier, I have shared free SQL and database courses and today, I am going to share some of the excellent database and SQL server books that are freely available on the internet for reading online or download as PDF. If you have been reading this blog then you know that I love books, I love to read books, and I love to collect books, that's why I have a significant online and offline library. Many people question me, do I really find to read all the books which I share? Well, I don't read all books page by page, unless and until it's something like Effective Java or Clean Code.

5 Free Oracle and Microsoft SQL Server Online Courses [2025] - Best of lot

If you are an application developer, like someone developing a server-side application using Java or .NET or any other programming language which uses a database like Oracle and Microsoft SQL Server or a junior DBA, then you must learn these database technologies to effectively work in your Job. Even though your organization might have DBAs or Database Administrators to help you with the database, more often than not, it's application developers who end up writing SQL scripts to create data, upload data, query data, and writing complex stored procedures and triggers to implement application functionalities. DBAs job mostly does database installation, access control, security, and other database admin stuff.

Difference between ROW_NUMBER(), RANK() and DENSE_RANK() in SQL

The main difference between ROW_NUMBER() and RANK() in SQL server is that ROW_NUMBER doesn't handle ties while RANK() does. Though both ROW_NUMBER() and RANK() are window function used for ranking row, the way they handle duplicates differentiate them. ROW_NUMBER() function assigns a unique number to each row starting from 1 and arbitrarily assign values if two rows are equal, which means it's not guaranteed that which row will get 2nd or 3rd position if they are identical. In contrast, RANK() assigns an equivalent rank to similar rows, which creates gaps between RANK.

Difference between VARCHAR and CHAR data type in SQL Server? [Explained]

Hello all, today, I am going to share an interesting SQL Server interview question, which will not only help you in your interview but also in your day-to-day work. It explains one of the critical concepts of SQL Server, the difference between VARCHAR and CHAR data type. I am sure, you all have used both of them numerous times but because it's so common many of us ignore the difference between them and when asked to choose between VARCHAR and CHAR on interviews, they fail to give a convincing reason. The difference is not just significant from an interview point of view but also from a robust database design because an incorrect choice of data type not only limit what kind of data you can put on but also waste precious space and makes your query slower, particularly when you have to deal with the massive amount of data.

How to Fix java.sql.BatchUpdateException: Error converting data type float to numeric - Java + SQL Server

This error can come if you are inserting or updating a NUMERIC column in the Microsoft SQL Server database from a Java Program using the executeUpdate()method, I mean executing a batch update query. It could also happen if you are calling a stored procedure and passing a float value corresponding to a NUMERIC column, and the value happened to be out-of-range like generating "Arithmetic overflow error converting numeric to data type numeric" on the SQL Server end. For example, if your column is defined as NUMERIC (6,2) the maximum value it can represent is 9999.99, not 999999.99.

How to use DROP command to remove tables in Oracle, MySQL and SQL Server

Hello guys, if you want to learn about DROP command in SQL then you have come to the right place. Earlier, I have shared the best free SQL and Database courses and several tutorials to learn SELECT, GROUP BY, and other important commands, and in this article, I will show you to use the DROP command in SQL. DROP is one of the basic SQL commands, which is used to DROP database objects. Since it's part of ANSI SQL, the DROP command is supported by all major database vendors, including Oracle, MySQL, and Microsoft SQL Server. A SQL DROP TABLE statement is used to delete a table definition and all data from a table, but DROP can also be used to drop index, view, trigger, or any other database object.

What is Subquery in SQL? Correlated and Non-Correalted SubQuery Example

If you are wondering what is correlated and non-correlated subqueries in SQL and looking to understand the difference between them then you have come to the right place. Earlier, I have shared free SQL and Database courses and today, I am going to talk about one of the common SQL concepts of subqueries, a query inside another query. There are two types of subqueries in SQL, correlated subquery and self-contained subquery, also known as nested, non-correlated, uncorrelated, or simply a subquery. The main difference between them is, self-contained (non-correlated) subqueries are independent of the outer query, whereas correlated subquery has a reference to an element from the table in the outer query.

Difference between ISNULL() and COALESCE() function in SQL? Example

Even though both ISNULL() and COALESCE() function provides alternate values to NULL in T-SQL and Microsoft SQL Server e.g. replacing NULL values with empty String, there are some key differences between them, which is often the topic of SQL Server interview. In this article, you will not only learn the answer to this question but also learn how to use COALESCE and ISNULL function properly. One of the main differences between them is that COALESCE() is a standard SQL function but ISNULL() is Microsoft SQL Server-specific, which means it's not guaranteed to be supported by other database vendors like Oracle, MySQL, or PostgreSQL.

Difference between table scan, index scan, and index seek in SQL Server Database? Example

Hello guys, a good understanding of how the index works and how to use them to improve your SQL query performance is very important while working in a database and SQL and that's why you will find many questions based upon indexes on Programming Job interviews. One of such frequently asked SQL questions is the real difference between table scan, index scan, and index seek? which one is faster and why? How does the database chooses which scan or seek to use? and How you can optimize the performance of your SQL SELECT queries by using this knowledge. In general, there are only two ways in which your query engine retrieves the data, using a table scan or by using an index.

How to find Nth Highest Salary in MySQL and SQL Server? Example LeetCode Solution

Nth Highest Salary in MySQL and SQL Server - LeetCode Solution
---------------------------------------------------------------
Write a SQL query to get the nth highest salary from the Employee table.

+----+--------+
| Id | Salary |
+----+--------+
| 1 | 100 |
| 2 | 200 |
| 3 | 300 |
+----+--------+

For example, given the above Employee table, the nth highest salary where n = 2 is 200. If there is no nth highest salary, then the query should return null.

Difference between VARCHAR and NVARCHAR in SQL Server

Hello guys, if you are confused between VARCHAR and NVARCHAR data types of SQL Server and wondering what is the difference between VARCHAR and NVARCHAR, or you have been asked this question on technical Interviews and you couldn't answer then you have come to the right place. There is a subtle difference between these two character data types in SQL Server, while both supports variable length, the VARCHAR data type is used to store non-Unicode characters while NVARCHAR is used to store Unicode characters. It also takes more space than VARCHAR.

Can you join two unrelated tables in SQL? Cross Join Example

In one of the recent programming job interviews, one of my readers was asked the question, how do you join two tables which are not related to each other? i.e. they don't have any common column? is it possible in SQL? My reader got confused because he only knows about INNER join and OUTER join which require a key column like dept_id which is the primary key in one table like Department and foreign key in another table like Employee. He couldn't answer the question, though he did tell them about you can select data from multiple tables by typing multiple table names in from clause using a comma.

Difference between WHERE and HAVING clause in SQL? Example

The main difference between the WHERE and HAVING clauses comes when used together with the GROUP BY clause. In that case, WHERE is used to filter rows before grouping, and HAVING is used to exclude records after grouping. This is the most important difference, and if you remember this, it will help you write better SQL queries. This is also one of the important SQL concepts to understand, not just from an interview perspective but also from a day-to-day use perspective. I am sure you have used the WHERE clause because it's one of the most common clauses in SQL along with SELECT and used to specify filtering criteria or conditions.

Top 5 Microsoft SQL Server Books for DBAs and Developers

Top 5 Microsoft SQL SERVER Books for DBAs and Developers

SQL Server Execution Plans by Grant Fritchey
Learn the basics of capturing plans, how to interrupt
them in their various forms, graphical or XML, and then
how to use the information you find. Diagnose the most
common causes of poor query performance so you
can optimize your SQL queries and improve your
indexing strategy.

SQL Server Concurrency: Locking, Blocking
and Row Versioning
by Kalen Delaney
Your application can have world-class indexes and
queries, but they won’t help you if you can’t get your data
because another application has it locked. That’s why
every DBA and developer must understand SQL Server
concurrency, and how to troubleshoot any issues.

SQL Server Transaction Log Management
by Tony Davis and Gail Shaw
Tony Davis and Gail Shaw strive to offer just the right level
of detail so that every DBA can perform all of the most
important aspects of transaction log management.

Troubleshooting SQL Server: A Guide for the
Accidental DBA
by Jonathan Kehayias and Ted Krueger
Three SQL Server MVPs provide fascinating insight into
the most common SQL Server problems, why they occur,
and how they can be diagnosed using tools such as
Performance Monitor, Dynamic Management Views, and
server-side tracing performance, so you can optimize
your SQL queries and improve your indexing strategy.

Defensive Database Programming
by Alex Kuznetsov
The goal of defensive database programming is to
help you to produce resilient T-SQL code that robustly
and gracefully handles cases of unintended use,
and is resilient to common changes to the database
environment.

That's all about top 5 good Microsoft SQL SERVER books.

Further Learning

1. The Complete SQL Bootcamp (Udemy)
2. Complete SQL + Databases Bootcamp: Zero to Mastery (ZTM)
3. SQL for Data Science [Coursera]
4. Database Design Fundamentals for Software Engineers [Educative]
5. SQL QuickStart Guide (Book)

Java67

Pages