Tag Archives: DIFFERENCES

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

PARSE CONVERSION FUNCTION IN SQL SERVER 2012

Conversion Functions, Functions, Sql Server, Sql Server 2012, , , , , , , ,

PARSE is one of the new built-in conversion function introduced as a Part of Sql Server 2012. PARSE function converts the string expression to the requested data type. It tries it’s best to translate the string value to requested type but if it fails to translate then raises an exception.

Important Note: This PARSE function is not a native Sql function, instead it is a .NET Framework Common Language Run-time dependent function. Then obviously it will have the performance overhead and also requires the presence of .NET CLR on the database Server. Continue to use the existing CAST and CONVERT functions wherever it is possible.

Syntax:  PARSE ( string_value AS data_type [ USING culture ] )

Parameter Details:
string_value   : String expression which needs to be parsed.
data_type      : Output data type, e.g. INT, NUMERIC, DATETIME etc.
culture        : Optional string that identifies the culture in which string_value is formatted. If it is not specified, then it takes the language of the current session.

Now let us understand this PARSE function with examples:

-- PARSE String to INT
SELECT PARSE('1000' AS INT) AS 'String to INT'
-- PARSE String to Numeric
SELECT PARSE('1000.06' AS NUMERIC(8,2)) AS 'String to Numeric'
-- PARSE String to DateTime
SELECT PARSE('05-18-2013' as DATETIME) AS 'String to DATETIME'
-- PARSE String to DateTime
SELECT PARSE('2013/05/18' as DATETIME) AS 'String to DATETIME'
-- PARSE string value in the India date format to DateTime 
SELECT PARSE('18-05-2013' as DATETIME using 'en-in') 
 AS 'String in Indian DateTime Format to DATETIME'
-- PARSE string value is in the US currency format to Money 
SELECT PARSE('$2500' as MONEY using 'en-US') 
 AS 'String in US Currency Format to MONEY'

RESULT:
PARSE_SQL_SERVER_2012_CONVERSION_FUNCTION

Below example demonstrates how PARSE function tries it’s best convert the input string value to  a specified data type even when the specified value is not in the correct format.

SELECT PARSE('08-JUNE-2013' AS DATETIME)
SELECT PARSE('08-JUN-2013' AS DATETIME)
SELECT PARSE('2013JUNE08' AS DATETIME)
SELECT PARSE('08/JUN/2013' AS DATETIME)

All the above statements will return the same result and it is: 2013-06-08 00:00:00.000

Now try to PARSE an invalid string value to DATETIME:

This conversion fails as February month will not have 31st day.

--PARSE invalid String to DATETIME
SELECT PARSE('2012/02/31' as DATETIME)
GO

Result:
Msg 9819, Level 16, State 1, Line 1
Error converting string value ‘2012/02/31’ into data type datetime using culture ”.

Below PARSE function will fail to translate the date. Because the culture parameter specified is en-us, but the string value specified is not in US format. US Date format excepts month first instead of day and then day and year.

--PARSE invalid String to DateTime
SELECT PARSE('18-05-2013' as DATETIME using 'en-us')
GO

Result:
Msg 9819, Level 16, State 1, Line 1
Error converting string value ’18-05-2013′ into data type datetime using culture ‘en-us’.

Difference between PARSE and CONVERT function.

Below example demonstrates the difference between PARSE and CONVERT function. PARSE function will successfully converts the string ‘Saturday, 08 June 2013’ to date time, but the CONVERT function fails to convert the same value. That is PARSE function tries it’s best to convert the input string value to the requested type, but CONVERT function requires the input string to be exact format no variations allowed.

--PARSE Function Succeeds
SELECT PARSE('Saturday, 08 June 2013' AS DATETIME) 
                 AS 'PARSE Function Result' GO
SELECT PARSE('Sat, 08 June 2013' AS DATETIME) 
                 AS 'PARSE Function Result' GO
SELECT PARSE('Saturday 08 June 2013' AS DATETIME) 
                 AS 'PARSE Function Result' GO

--CONVERT Function Fails
SELECT CONVERT(DATETIME, 'Saturday, 08 June 2013') 
                 AS 'CONVERT Function Result' GO
SELECT CONVERT(DATETIME, 'Sat, 08 June 2013') 
                 AS 'CONVERT Function Result' GO
SELECT CONVERT(DATETIME, 'Saturday 08 June 2013') 
                 AS 'CONVERT Function Result' GO

RESULT:

PARSE Function Result
———————–
2013-06-08 00:00:00.000

PARSE Function Result
———————–
2013-06-08 00:00:00.000

PARSE Function Result
———————–
2013-06-08 00:00:00.000

CONVERT Function Result
———————–
Msg 241, Level 16, State 1, Line 3
Conversion failed when converting date and/or time from character string.

CONVERT Function Result
———————–
Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.

CONVERT Function Result
———————–
Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.

You may like to read the below new built-in function’s introduced in Sql Server 2012:

New Built in Functions introduced in Sql Server
CONVERSION FUNCTIONS
PARSE TRY_PARSE
TRY_CONVERT
STRING FUNCTIONS
CONCAT FORMAT
LOGICAL FUNCTIONS
CHOOSE IIF
DATE AND TIME FUNCTIONS
EOMONTH
DATEFROMPARTS DATETIMEFROMPARTS
SMALLDATETIMEFROMPARTS DATETIME2FROMPARTS
TIMEFROMPARTS DATETIMEOFFSETFROMPARTS