What is Tinyint data type?

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

What is Tinyint data type?

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

INTStorage Size1 byte4 bytesMinimum Value0-2,147,483,648 (-2^31)Maximum Value2552,147,483,647 (2^31-1)Usage ExampleDECLARE @i TINYINT SET @i = 150 PRINT @i

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

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

RESULT:
1DECLARE @i INT SET @i = 150 PRINT DATALENGTH( @i)

RESULT:
4Example of TINYINT out of range valueDECLARE @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:
260Try to store Negative valueDECLARE @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:
-150Example of both TINYINT and INT out of range valueDECLARE @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

  • Varchar vs NVarchar
  • Varchar vs Varchar(MAX)
  • Char vs Varchar
  • Text vs Varchar(Max)
  • Union vs Union All
  • DateTime vs DateTime2
  • SET QUOTED_IDENTIFIER ON vs SET QUOTED_IDENTIFIER OFF
  • Stored Procedure vs User Defined Function
  • Primary Key vs Unique Key
  • RAISERROR vs THROW
  • Temporary Table vs Table Variable
  • Len() vs Datalength()
  • Sequence vs Identity
  • DATEDIFF vs DATEDIFF_BIG
  • LEFT JOIN vs LEFT OUTER JOIN
  • RIGHT JOIN vs RIGHT OUTER JOIN
  • JOIN vs INNER JOIN
  • LEFT OUTER JOIN vs RIGHT OUTER JOIN
  • SMALLDATETIME vs DATETIME

Share this:

  • Click to share on Facebook (Opens in new window)
  • Click to share on LinkedIn (Opens in new window)
  • Click to share on Twitter (Opens in new window)
  • Click to email a link to a friend (Opens in new window)
  • Click to print (Opens in new window)
  • Click to share on Reddit (Opens in new window)
  • Click to share on Tumblr (Opens in new window)
  • Click to share on Pocket (Opens in new window)
  • Click to share on Pinterest (Opens in new window)

Video liên quan