All posts by Basavaraj Biradar

Difference between TINYINT and SMALLINT data type in Sql Server

Differences, 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

Differences, 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

How to Split a String in Sql Server without using a function

Sql Server, , , , , , ,

It is an usual practice to create a function to split the delimited sting in Sql Server, like the ones explained in the article Split delimited String. But if we are in a situation where you don’t have a permission to create a function, then what is the alternative to split the delimited string? This article explains how we can split a delimited string in Sql Server without needing to create a function.

Let us execute the following script to create the Player table with sample data as shown in the below image.

Table with delimited string

CREATE TABLE Player
(
	Team VARCHAR(50),
	Players VARCHAR(800)
)
GO
INSERT INTO Player(Team, Players)
VALUES  ('India','Sachin Tendulkar,Shewag,Javagal Srinath'),
		('Australia','Ricky Ponting,Michale Clarke'),
		('South Africa','AB Deviliers')
GO

Now, we can write a query like below to split the comma delimited players name without using the user defined function.

SELECT Team, Members.Member.value('.','VARCHAR(8000)') Player
FROM 
(--Convert delimited string to XML
 SELECT Team, CAST('<Players><Player>' 
        + REPLACE(Players, ',' , '</Player><Player>') 
	+ '</Player></Players>' AS XML) AS tempPlayer 
 FROM Player ) AS tempPlayer
 CROSS APPLY tempPlayer.nodes('/Players/Player') Members(Member)

RESULT:
Split string without function

[ALSO READ]
STRING_SPLIT function in Sql Server 2016
How to Split comma or any other character delimited string into a Table in Sql Server