Go tinyint, smallint, int, bigint in SQL Server and 10 binary conversion 16 binary method

One. Type comparison

bigint: Integer data from -2^63 (-9223372036854775808) to 2^63-1 (9223372036854775807) with a storage size of 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) with a storage size of 4 bytes. int type, maximum 32-bit data can be stored

smallint: Integer data from -2^15 (-32,768) to 2^15-1 (32,767) with a storage size of 2 bytes. smallint is a 16-bit

Tinyint: Integer data from 0 to 255 with a storage size of 1 bytes. There are 8 tinyint.

Two. Comments

Support for bigint data types where integer values are supported. However,bigint is used in some special cases when the integer value exceeds the range supported by the int data type, bigintcan be used. In SQL Server, theint data type is the primary integer data type.

In the data type precedence table,bigint is located between smallmoney and int .

The function returns bigintonly if the parameter expression is a bigint data type. SQL Server does not automatically promote other integer data types (tinyint,smallint , and int) to bigint.

Three. Summarize

So it is very important to choose the most appropriate data type, for example, give you 10 18-bit full digital ID number, and 10 15-bit full digital ID card number. Let you find the fastest query speed, these 20 records, then you design this field, there should be a few places to note:

1. The fastest query speed, nothing more than we choose int type of data to store these 20 ID numbers. However, the 15-bit and 18-bit data appear to be only bigint to meet the criteria.

2. Someone would say why not use varchar or char. Stored directly as a string. This question is better asked, first of all, the query speed of int is certainly faster than varchar and char, then the data of type int is preferred, but int has several kinds in SQL Server. This is about choosing the most appropriate data type.

Code

Select STUFF (Master.dbo.fn_varbintohexstr ( --This is the main function implementation code, the other code is to remove the front 0x convert (bigint,) as b Inary (1)--' 1 ' indicates that the generated 16 binary number is 2 ' 1 ', which is the maximum FF) , COLLATE latin1_general_ci_as_ks_ws)

Results: One

-by Aaron West,5/4/2005--This version allows negative numbers CREATE FUNCTION dbo. HexToINT (@Value VARCHAR (8)) RETURNS INT as BEGINif@Value like'%[^0-9a-fa-f]%'RETURN NULL DECLARE @i BIGINT SET @i= Cast (The cast (Right (UPPER ('00000000'+ @Value),8) as BINARY (8) as BIGINT)-CAST (0x3030303030303030As BIGINT) SET @i[Email protected] ((@i/ -) &cast (0x0101010101010101As BIGINT)) *7RETURN Cast (cast (@i& the)                     + ((@i/ -) & -)                     + ((@i/ the) &3840)                     + ((@i/4096) &61440)                     + ((@i/65536) &983040)                     + ((@i/1048576) &15728640)                     + ((@i/16777216) &251658240)                     + (@i/cast (0x0100000000000000As 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 ( the)) returns varchar (514)  asBegin DECLARE @x bigint declare @lenintDeclare @loopintdeclare @value varbinary (514)    Set@value =0xSet@loop =1    Set@len =datalength (@vbin_in)if(@len &1) <>0       Set@vbin_in =0x00+@vbin_inif(@len &3) <3       Set@vbin_in =0x0000+@vbin_in while@loop <=@len beginSet@x = CAST (substring (@vbin_in, @loop,4) as BIGINT)Set@x =65536*((@x&0xf0000000)*4096+ (@x&0x0f000000)* the+ (@x&0x00f00000)* -+ (@x&0x000f0000) )            + (@x&0xf000)*4096+ (@x&0x0f00)* the+ (@x&0x00f0)* -+ (@x&0x000f)          Set@x = (@x |0x3030303030303030)+((@x+0x0606060606060606)/ -&0x0101010101010101)*7          Select@value = @value + CAST (@x as BINARY (8))          Set@loop = @loop +4Endreturn '0x'+ Right (CAST (@value as VARCHAR (514)), @len *2) End GOSelectDBO.UFN_VBINTOHEXSTR (0x123456789abcdef1234) --Outputs:0x0123456789abcdef1234GO

Turn-picked http://www.cnblogs.com/yyjj/archive/2012/03/06/2381592.html#undefined