Sqlserver中tinyint, smallint, int, bigint的区别 及 10进制转

一。类型比较 bigint：从-2^63(-9223372036854775808)到2^63-1(9223372036854775807)的整型数据，存储大小为 8 个字节。一个字节就是8位，那么bigint就有64位 int：从-2^31(-2,147,483,648)到2^31-1(2,147,483,647)的整型数据，存储大小为 4 个字节。int类型

一。类型比较

bigint：从-2^63(-9223372036854775808)到2^63-1(9223372036854775807)的整型数据，存储大小为 8 个字节。一个字节就是8位，那么bigint就有64位

int：从-2^31(-2,147,483,648)到2^31-1(2,147,483,647)的整型数据，存储大小为 4 个字节。int类型，最大可以存储32位的数据

smallint：从-2^15(-32,768)到2^15-1(32,767)的整数数据，存储大小为 2 个字节。smallint就是有16位

tinyint：从0到255的整数数据，存储大小为 1 字节。tinyint就有8位。

二。注释

在支持整数值的地方支持 bigint 数据类型。但是，bigint 用于某些特殊的情况，当整数值超过 int 数据类型支持的范围时，就可以采用 bigint。在 SQL Server 中，int 数据类型是主要的整数数据类型。

在数据类型优先次序表中，bigint 位于 smallmoney 和 int 之间。

只有当参数表达式是 bigint 数据类型时，函数才返回 bigint。SQL Server 不会自动将其它整数数据类型（tinyint、smallint 和 int）提升为bigint。

三。总结

所以选择最恰当的数据类型是非常重要的，例如，给你10个18位的全是数字的身份证号码，和10个15位的全是数字的身份证号码。让你以最快的查询的速度查找出来，这20个记录，那么你在设计这个字段的时候，应该有几个地方要注意：

1. 最快的查询速度，无非我们选择int类型的数据来存储这20个身份证号码。但是15位和18位的数据貌似只有bigint才满足条件。

2. 有人要说为啥不用varchar或者char呢。直接存成一个字符串。这个问题问的比较好，首先，int的查询速度肯定比varchar和char要快，那么int类型的数据是首选，但是int在sqlserver中有好几种。这个就要选择最恰当的数据类型。

 

---------------------------------------------------------------------------------------------------------------------------------

code:

select 
STUFF((master.dbo.fn_varbintohexstr(
cast( --这是主要功能实现代码 ，其他代码的作用就是将前边的 0x 去掉 
CONVERT(bigint, 17) as binary(1) --'1'表示生成的16进制数的长度为2的'1'次方个,也就是最大为 FF
)
) COLLATE Latin1_General_CI_AS_KS_WS ),1,2,'') 

结果： 11

 

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

 

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