Category Archives: Differences

Difference between INT and BIGINT data type in Sql Server

Both INT and BIGINT are exact numeric data types, used for storing integer value. Below table lists out the major difference between INT and BIGINT Data Types.

[ALSO READ] TINYINT Vs SMALLINT

INT

BIGINT

Storage Size 4 Bytes 8 Bytes
Minimum Value -2,147,483,648 (-2^31) -9,223,372,036,854,775,808 (-2^63)
Maximum Value 2,147,483,647 (2^31-1) 9,223,372,036,854,775,807 (2^63-1)
Usage Example
DECLARE @i INT
SET @i = 150
PRINT @i

RESULT:
150

DECLARE @i BIGINT
SET @i = 150
PRINT @i

RESULT:
150

Example of Storage Size used by the variable to store the value
DECLARE @i INT
SET @i = 150
PRINT DATALENGTH( @i)

RESULT:
4

DECLARE @i BIGINT
SET @i = 150
PRINT DATALENGTH( @i)

RESULT:
8

Example of INT out of range value
DECLARE @i INT
SET @i = 2147483648
PRINT @i

RESULT:

Msg 8115, Level 16, State 2, Line 2
Arithmetic overflow error converting expression to data type int.

DECLARE @i BIGINT
SET @i = 2147483648
PRINT @i

RESULT:
2147483648

Try to store Negative value
DECLARE @i INT
SET @i = -150
PRINT @i

RESULT:
-150

DECLARE @i BIGINT
SET @i = -150
PRINT @i

RESULT:
-150

[ALSO READ] SMALLINT Vs INT

Selecting the correct data type while creating a table is very critical. In-correct selection of the data type will result in performance and storage issues over the time as the data grows. As in-correct selection of data type results requiring more storage space to store and no. of records stored in each data page will be less. And on top if index is created on such columns, it not only takes the extra space in storing the value in a row in the data page but also requires extra space in the index. Less the no. of records stored in the data page, then to serve the queries Sql Server needs to load more no. of data pages to the memory. So, it is very crucial to select the correct data type while creating table. Hope the above differences will help you in selecting the correct data type while creating the table.

ALSO READ

Difference between SMALLINT and INT data type in Sql Server

Both SMALLINT and INT are exact numeric data types, used for storing integer value. Below table lists out the major difference between SMALLINT and INT Data Types.

[ALSO READ] TINYINT Vs SMALLINT

SMALLINT

INT

Storage Size 2 Bytes 4 Bytes
Minimum Value -32,768 (-2^15) -2,147,483,648 (-2^31)
Maximum Value 32,767 (2^15-1) 2,147,483,647 (2^31-1)
Usage Example
DECLARE @i SMALLINT
SET @i = 150
PRINT @i

RESULT:
150

DECLARE @i INT
SET @i = 150
PRINT @i

RESULT:
150

Example of Storage Size used by the variable to store the value
DECLARE @i SMALLINT
SET @i = 150
PRINT DATALENGTH( @i)

RESULT:
2

DECLARE @i INT
SET @i = 150
PRINT DATALENGTH( @i)

RESULT:
4

Example of SMALLINT out of range value
DECLARE @i SMALLINT
SET @i = 32768
PRINT @i

RESULT:

Msg 220, Level 16, State 1, Line 2
Arithmetic overflow error for data type smallint, value = 32768.

DECLARE @i INT
SET @i = 32768
PRINT @i

RESULT:
32768

Try to store Negative value
DECLARE @i SMALLINT
SET @i = -150
PRINT @i

RESULT:
-150

DECLARE @i INT
SET @i = -150
PRINT @i

RESULT:
-150

Example of both SMALLINT and INT out of range value
DECLARE @i SMALLINT
SET @i = 2147483648
PRINT @i

RESULT:

Msg 8115, Level 16, State 2, Line 2
Arithmetic overflow error converting expression to data type smallint.

DECLARE @i INT
SET @i = 2147483648
PRINT @i

RESULT:

Msg 8115, Level 16, State 2, Line 2
Arithmetic overflow error converting expression to data type int.

[ALSO READ] TINYINT Vs INT

Selecting the correct data type while creating a table is very critical. In-correct selection of the data type will result in performance and storage issues over the time as the data grows. As in-correct selection of data type results requiring more storage space to store and no. of records stored in each data page will be less. And on top if index is created on such columns, it not only takes the extra space in storing the value in a row in the data page but also requires extra space in the index. Less the no. of records stored in the data page, then to serve the queries Sql Server needs to load more no. of data pages to the memory. For example: for table column, which stores state_id, choosing an INT data type instead of TINYINT or SMALLINT column is in-efficient as the number of states in a country in worst case scenario too never exceeds a three-digit number. So, for state_id column if we choose INT data type then it will always take 4 bytes for storing it irrespective of the value stored in it. Whereas TINYINT would have taken 1 byte for storing the same value and SMALLINT would have taken 2 bytes. So, it is very crucial to select the correct data type while creating table. Hope the above differences will help you in selecting the correct data type while creating the table.

ALSO READ

Difference between TINYINT and SMALLINT data type in Sql Server

Both TINYINT and SMALLINT are exact numeric data types, used for storing integer value. Below table lists out the major difference between TINYINT and SMALLINT Data Types.

[ALSO READ] TINYINT Vs INT

TINYINT

SMALLINT

Storage Size 1 Byte 2 Bytes
Minimum Value 0 -32,768 (-2^15)
Maximum Value 255 32,767 (2^15-1)
Usage Example
DECLARE @i TINYINT
SET @i = 150
PRINT @i

RESULT:
150

DECLARE @i SMALLINT
SET @i = 150
PRINT @i

RESULT:
150

Example of Storage Size used by the variable to store the value
DECLARE @i TINYINT
SET @i = 150
PRINT DATALENGTH( @i)

RESULT:
1

DECLARE @i SMALLINT
SET @i = 150
PRINT DATALENGTH( @i)

RESULT:
2

Example of TINYINT out of range value
DECLARE @i TINYINT
SET @i = 260
PRINT @i

RESULT:

Msg 220, Level 16, State 2, Line 2
Arithmetic overflow error for data type tinyint, value = 260.

DECLARE @i SMALLINT
SET @i = 260
PRINT @i

RESULT:
260

Try to store Negative value
DECLARE @i TINYINT
SET @i = -150
PRINT @i

RESULT:

Msg 220, Level 16, State 2, Line 2
Arithmetic overflow error for data type tinyint, value = -150.

DECLARE @i SMALLINT
SET @i = -150
PRINT @i

RESULT:
-150

Example of both TINYINT and SMALLINT out of range value
DECLARE @i TINYINT
SET @i = 32768
PRINT @i

RESULT:

Msg 220, Level 16, State 2, Line 2
Arithmetic overflow error for data type tinyint, value = 32768.

DECLARE @i SMALLINT
SET @i = 32768
PRINT @i

RESULT:

Msg 220, Level 16, State 1, Line 2
Arithmetic overflow error for data type smallint, value = 32768.

[ALSO READ] SMALLINT Vs INT

Selecting the correct data type while creating a table is very critical. In-correct selection of the data type will result in performance and storage issues over the time as the data grows. As in-correct selection of data type results requiring more storage space to store and no. of records stored in each data page will be less. And on top if index is created on such columns, it not only takes the extra space in storing the value in a row in the data page but also requires extra space in the index. Less the no. of records stored in the data page, then to serve the queries Sql Server needs to load more no. of data pages to the memory. For example: for table column, which stores state_id, choosing an INT data type instead of TINYINT or SMALLINT column is in-efficient as the number of states in a country in worst case scenario too never exceeds a three-digit number. So, for state_id column if we choose INT data type then it will always take 4 bytes for storing it irrespective of the value stored in it. Whereas TINYINT would have taken 1 byte for storing the same value and SMALLINT would have taken 2 bytes. So, it is very crucial to select the correct data type while creating table. Hope the above differences will help you in selecting the correct data type while creating the table.

ALSO READ

Difference between TINYINT and INT data type in Sql Server

Both TINYINT and INT are exact numeric data types, used for storing integer data. Below table lists out the major difference between TINYINT and INT Data Types.

[ALSO READ] TINYINT Vs SMALLINT

TINYINT

INT

Storage Size 1 byte 4 bytes
Minimum Value 0 -2,147,483,648 (-2^31)
Maximum Value 255 2,147,483,647 (2^31-1)
Usage Example
DECLARE @i TINYINT
SET @i = 150
PRINT @i

RESULT:
150

DECLARE @i INT
SET @i = 150
PRINT @i

RESULT:
150

Example of Storage Size used by the variable to store the value
DECLARE @i TINYINT
SET @i = 150
PRINT DATALENGTH( @i)

RESULT:
1

DECLARE @i INT
SET @i = 150
PRINT DATALENGTH( @i)

RESULT:
4

Example of TINYINT out of range value
DECLARE @i TINYINT
SET @i = 260
PRINT @i

RESULT:

Msg 220, Level 16, State 2, Line 2
Arithmetic overflow error for data type tinyint, value = 260.

DECLARE @i INT
SET @i = 260
PRINT @i

RESULT:
260

Try to store Negative value
DECLARE @i TINYINT
SET @i = -150
PRINT @i

RESULT:

Msg 220, Level 16, State 2, Line 2
Arithmetic overflow error for data type tinyint, value = -150.

DECLARE @i INT
SET @i = -150
PRINT @i

RESULT:
-150

Example of both TINYINT and INT out of range value
DECLARE @i TINYINT
SET @i = 2147483649
PRINT @i

RESULT:

Msg 8115, Level 16, State 2, Line 2
Arithmetic overflow error converting expression to data type tinyint.

DECLARE @i INT
SET @i = 2147483649
PRINT @i

RESULT:

Msg 8115, Level 16, State 2, Line 2
Arithmetic overflow error converting expression to data type int.

[ALSO READ] SMALLINT Vs INT

Selecting the correct data type while creating a table is very critical. In-correct selection of the data type will result in performance and storage issues over the time as the data grows. As in-correct selection of data type results requiring more storage space to store and no. of records stored in each data page will be less. And on top if index is created on such columns, it not only takes the extra space in storing the value in a row in the data page but also requires extra space in the index. Less the no. of records stored in the data page, then to serve the queries Sql Server needs to load more no. of data pages to the memory. For example: for table column, which stores state_id, choosing an INT data type instead of TINYINT or SMALLINT column is in-efficient as the number of states in a country in worst case scenario too never exceeds a three-digit number. So, for state_id column if we choose INT data type then it will always take 4 bytes for storing it irrespective of the value stored in it. Whereas TINYINT would have taken 1 byte for storing the same value and SMALLINT would have taken 2 bytes. So, it is very crucial to select the correct data type while creating table. Hope the above differences will help you in selecting the correct data type while creating the table.

ALSO READ

Difference between LEFT OUTER JOIN and RIGHT OUTER JOIN in Sql Server

This article provides a comparative analysis of the Sql Server LEFT OUTER JOIN and RIGHT OUTER JOIN with extensive list of examples.

To understand the differences between Sql Server LEFT OUTER JOIN and RIGHT OUTER JOIN, let us create a demo database with two tables Customers and Orders with sample data as shown in the following image by executing the following script:

joins-demo-tables

CREATE DATABASE SqlHintsJoinDemo
GO
USE SqlHintsJoinDemo
GO
--Create Customers Table and Insert records
CREATE TABLE Customers 
( CustomerId INT, Name VARCHAR(50) )
GO
INSERT INTO Customers
VALUES(1,'Shree'), (2,'Kalpana'), (3,'Basavaraj')
GO
--Create Orders Table and Insert records into it
CREATE TABLE Orders (OrderId INT, CustomerId INT)
GO
INSERT INTO Orders 
VALUES(100,1), (200,4), (300,3)
GO

[ALSO READ] Joins In Sql Server

LEFT OUTER JOIN

RIGHT OUTER JOIN

DEFINITION

LEFT OUTER JOIN / LEFT JOIN returns all the rows from the LEFT table and the corresponding matching rows from the right table. If right table doesn’t have the matching record then for such records right table column will have NULL value in the result.

RIGHT OUTER JOIN / RIGHT JOIN returns all the rows from the RIGHT table and the corresponding matching rows from the left table. If left table doesn’t have the matching record then for such records left table column will have NULL value in the result.

VENN DIAGRAM
left-outer-join-venn-diagram

As per the above LEFT OUTER JOIN Venn Diagram, all the records from the left table (Customers) are returned regardless if any of those records have a match in the right table (Orders). It will also return any matching records from the right table (Orders)

right-outer-join-venn-diagram

As per the above RIGHT OUTER JOIN Venn Diagram, all the rows in the right table (Orders) are returned regardless if any of those records have a match in the left table (Customers). It will also return any matching records from the left table (Customers)

EXAMPLE

As per the data in our demo tables, Customers with CustomerId 1 and 3 in Customers table have the orders in the Orders table. Where as the customer with CustomerId 2 doesn’t have any order in the Orders table. So the LEFT JOIN on the CustomerId column between Customers and Orders table will return the Customer and Order details of the Customers with CustomerId 1 and 3 and for CustomerId 2 the Order Table columns will have NULL value in the result. Let us verify this by executing the following LEFT OUTER JOIN query:

SELECT *
FROM Customers C 
  LEFT OUTER JOIN Orders O
    ON O.CustomerId = C.CustomerId

RESULT:
left-outer-join-results

As per the data in our demo tables, only for the order with OrderId 200 we don’t have it’s corresponding customer info with CustomerId 4 in the Customers table. And for the other two orders, the corresponding customer info is present in the Customers Table. So for the orders with CustomerId 1 and 3 will have customer details and for the order with CustomerId 4, the Customers table columns will have NULL value in the result. Let us verify this by executing the following RIGHT OUTER JOIN query:

SELECT *
FROM Customers C 
  RIGHT OUTER JOIN Orders O
    ON O.CustomerId = C.CustomerId

RESULT
right-outer-join-result

EXECUTION PLAN
Let us execute the following query and verify it’s execution plan:

SELECT *
FROM Customers C 
  LEFT OUTER JOIN Orders O
    ON O.CustomerId = C.CustomerId

left-join-execution-plan

From the execution plan we can see that first all records from the LEFT TABLE (i.e. Customers) of the JOIN clause are fetched. Then for each of these LEFT TABLE (i.e. Customers) records, it is trying to fetch the matching data from the RIGHT TABLE (i.e. Orders TABLE) using Nested Loops LEFT OUTER JOIN operation.

Let us execute the following query and verify it’s execution plan:

SELECT *
FROM Customers C 
  RIGHT OUTER JOIN Orders O
    ON O.CustomerId = C.CustomerId

RESULT:
right-join-execution-plan

From the execution plan we can see that first all records from the RIGHT TABLE (i.e. Orders) of the JOIN clause are fetched. Then for each of these LEFT TABLE (i.e. Orders) records, it is trying to fetch the matching data from the RIGHT TABLE (i.e. Customers TABLE) using Nested Loops LEFT OUTER JOIN operation. From the execution plan we can observe that Sql Server indirectly converted this RIGHT OUTER JOIN query to a LEFT OUTER JOIN query like below before execution:

SELECT *
FROM Orders O 
  LEFT OUTER JOIN Customers O
    ON O.CustomerId = C.CustomerId 

So, to get the result from LEFT OUTER JOIN which is same as that of RIGHT OUTER JOIN and vice-versa, we just need to switch the order in which the tables appear in the JOIN clause.

ALTERNATIVE DEFINITION
LEFT JOIN = INNER JOIN + Rows from LEFT Table which doesn’t have matching record in the RIGHT table RIGHT JOIN = INNER JOIN + Rows from RIGHT Table which doesn’t have matching record in the LEFT table

CONCLUSION

The main difference between LEFT OUTER JOIN and RIGHT OUTER JOIN is: LEFT OUTER JOIN returns all records from the LEFT table irrespective of whether there are any matching rows in the RIGHT table. Where as RIGHT OUTER JOIN returns all records from the RIGHT table irrespective of whether there are any matching rows in the LEFT table. To get the result from LEFT OUTER JOIN which is same as that of RIGHT OUTER JOIN and vice-versa, we just need to switch the order in which the tables appear in the JOIN clause.

ALSO READ