Differences between tinyint, smallint, int, and bigint in Sqlserver and 10 hexadecimal conversion

I. Bigint type comparison: integer data from-2 ^ 63 (-9223372036854775808) to 2 ^ 63-1 (9223372036854775807). The storage size is 8 bytes. If a byte is 8 bits, bigint has 64 bits of int: integer data from-2 ^ 31 (-2,147,483,648) to 2 ^ 31-1 (2,147,483,647, the storage size is 4 bytes. Int type

I. Bigint type comparison: integer data from-2 ^ 63 (-9223372036854775808) to 2 ^ 63-1 (9223372036854775807). The storage size is 8 bytes. If a byte is 8 bits, bigint has 64 bits of int: integer data from-2 ^ 31 (-2,147,483,648) to 2 ^ 31-1 (2,147,483,647, the storage size is 4 bytes. Int type

I. Type comparison

Bigint: integer data from-2 ^ 63 (-9223372036854775808) to 2 ^ 63-1 (9223372036854775807). The storage size is 8 bytes. One byte is 8 bits, so bigint has 64 bits

Int: integer data from-2 ^ 31 (-2,147,483,648) to 2 ^ 31-1 (2,147,483,647). The storage size is 4 bytes. Int type, which can store up to 32 bits of data

Smallint: integer data from-2 ^ 15 (-32,768) to 2 ^ 15-1 (32,767). The storage size is 2 bytes. Smallint has 16 digits.

Tinyint: integer data from 0 to 255. The storage size is 1 byte. Tinyint has eight digits.

II. Note

Supported by integerBigintData type. However,BigintUsed in some special cases, when the integer value exceedsIntYou can useBigint. In SQL Server,IntThe data type is the main integer data type.

In the data type priority table,BigintLocated inSmallmoneyAndInt.

Only when the parameter expression isBigintThe function returns the data type.Bigint. SQL Server does not automatically convert other integer data types (Tinyint,SmallintAndInt)Bigint.

3. Summary

Therefore, it is very important to select the most appropriate data type. For example, if you have 10 18 digits, all are digital ID card numbers, and 10 15 digits are all digital ID card numbers. Let you find the 20 records at the fastest query speed. When designing this field, you should pay attention to the following points:

1. The fastest query speed is nothing more than using int data to store the 20 ID card numbers. However, the 15-bit and 18-bit data seems to only meet the bigint condition.

2. Why not use varchar or char. Directly save it as a string. The query speed of int is certainly faster than that of varchar and char. int type data is the first choice, but there are several int types in sqlserver. Select the most appropriate data type.

Else ---------------------------------------------------------------------------------------------------------------------------------

Code:

Select
STUFF (master. dbo. fn_varbintohexstr (
Cast (-- this is the main function to implement code. Other code is used to remove the front 0x
CONVERT (bigint, 17) as binary (1) -- '1' indicates that the length of the hexadecimal number generated is the power of '1' of 2, that is, FF
)
) COLLATE Latin1_General_CI_AS_KS_WS), 1, 2 ,'')

Result: 11

-- By Aaron West, 5/4/2005 
-- This version allows negative numbers 
CREATE FUNCTION dbo.HexToINT 
( 
 @Value VARCHAR(8) 
) 
RETURNS INT 
AS 
BEGIN 
   if @Value LIKE '%[^0-9A-Fa-f]%' 
      RETURN NULL 
   DECLARE @I BIGINT 
   SET @I = CAST(CAST(RIGHT(UPPER('00000000' + @Value),8) AS BINARY(8)) AS BIGINT) - CAST(0x3030303030303030 AS BIGINT) 
   SET @I=@I-((@I/16)&CAST(0x0101010101010101 AS BIGINT))*7 
   RETURN CAST(CAST( 
                    (@I&15) 
                    +((@I/16)&240) 
                    +((@I/256)&3840) 
                    +((@I/4096)&61440) 
                    +((@I/65536)&983040) 
                    +((@I/1048576)&15728640) 
                    +((@I/16777216)&251658240) 
                    +(@I/CAST(0x0100000000000000 AS BIGINT)*268435456)  
                    AS BINARY(4))AS INT) 
END 
GO 
 
SELECT  
    dbo.HexToINT('0ABC') ,  
    dbo.HexToINT('7FFF') ,  
    dbo.HexToINT('0FFF') ,  
    dbo.HexToINT('0')    AS Zero,  
    dbo.HexToINT('7FFFFFFF') AS MaxValue, 
    dbo.HexToINT('80000000') AS MaxNeg, 
    dbo.HexToINT('FFFFFFFF') AS NegOne 

create function dbo.ufn_vbintohexstr 
( 
 @vbin_in varbinary(256) 
) 
returns varchar(514) 
as 
Begin 
   declare @x bigint 
   declare @len int 
   declare @loop int 
   declare @value varbinary(514) 
   set @value = 0x 
   set @loop = 1 
   set @len = datalength(@vbin_in) 
   if (@len & 1) <> 0 
      set @vbin_in = 0x00 + @vbin_in 
   if (@len & 3) < 3 
      set @vbin_in = 0x0000 + @vbin_in 
   while @loop <= @len 
      begin 
         set @x = CAST(substring(@vbin_in,@loop,4)AS BIGINT) 
         set @x =65536* 
          ( (@x&0xF0000000)*4096 
           +(@x&0x0F000000)*256 
           +(@x&0x00F00000)*16 
           +(@x&0x000F0000) ) 
           +(@x&0xF000)*4096 
           +(@x&0x0F00)*256 
           +(@x&0x00F0)*16 
           +(@x&0x000F) 
         set @x = (@x | 0x3030303030303030)+ 
             ((@x+0x0606060606060606)/16 
                & 0x0101010101010101)*7 
         select @value  = @value + CAST(@x AS BINARY(8)) 
         set @loop = @loop + 4 
      end 
   return '0x'+ right(CAST(@value AS VARCHAR(514)), @len*2) 
end 
GO 
select dbo.ufn_vbintohexstr(0x123456789abcdef1234) 
-- outputs: 0x0123456789ABCDEF1234 
GO