SQL Server中隐式转换的问题

发布网友发布时间：2022-04-10 13:30

共2个回答

懂视网时间：2022-04-10 17:51

10; OBJECT_ID CREATED ---------- ------------------ 20 25-JUL-18 46 25-JUL-18 28 25-JUL-18 15 25-JUL-18 29 25-JUL-18 3 25-JUL-18 25 25-JUL-18 41 25-JUL-18 54 25-JUL-18 9 rows selected. select object_name from function where to_char(created,‘yyyymmdd hh24:mi‘)=‘20180725 12:51‘; 20180725 12:54 drop table tab; create table tab(id number(20),datetime date); declare i number; dd date; BEGIN dd := sysdate; i:= 0; for x in 1..5000 loop --if mod(i,100)=0 then insert into tab(id,datetime) values(i,dd); dd := dd+1; i := i+1; end loop; END; / create table tab(id varchar2(20),datetime date); 1289 20220429 13:58 29-APR-22 ************************ 测试1 ************************ select * from tab where id=1289 and datetime=‘29-APR-22‘; select * from tab where id=1289 and datetime=‘29-APR-22‘;SQL> no rows selected Execution Plan ---------------------------------------------------------- Plan hash value: 1995730731 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 21 | 5 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| TAB | 1 | 21 | 5 (0)| 00:00:01 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(TO_NUMBER("ID")=1289 AND "DATETIME"=‘29-APR-22‘) Note ----- - dynamic sampling used for this statement (level=2) Statistics ---------------------------------------------------------- 5 recursive calls 0 db block gets 33 consistent gets 0 physical reads 0 redo size 400 bytes sent via SQL*Net to client 508 bytes received via SQL*Net from client 1 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 0 rows processed ************************ 测试2 ************************ select * from tab where id=‘1289‘ and datetime=‘29-APR-22‘; select * from tab where id=‘1289‘ and datetime=‘29-APR-22‘; SQL> no rows selected Execution Plan ---------------------------------------------------------- Plan hash value: 1995730731 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 21 | 5 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| TAB | 1 | 21 | 5 (0)| 00:00:01 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("ID"=‘1289‘ AND "DATETIME"=‘29-APR-22‘) Note ----- - dynamic sampling used for this statement (level=2) Statistics ---------------------------------------------------------- 5 recursive calls 0 db block gets 33 consistent gets 0 physical reads 0 redo size 400 bytes sent via SQL*Net to client 508 bytes received via SQL*Net from client 1 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 0 rows processed 刷新cache之后再执行 SQL> select * from tab where id=‘1289‘ and datetime=‘29-APR-22‘; no rows selected Execution Plan ---------------------------------------------------------- Plan hash value: 1995730731 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 21 | 5 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| TAB | 1 | 21 | 5 (0)| 00:00:01 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("ID"=‘1289‘ AND "DATETIME"=‘29-APR-22‘) Note ----- - dynamic sampling used for this statement (level=2) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 16 consistent gets 14 physical reads 0 redo size 400 bytes sent via SQL*Net to client 508 bytes received via SQL*Net from client 1 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 0 rows processed ************************ 测试3 ************************ select * from tab where id=‘1289‘ and to_char(datetime,‘yyyymmdd‘)=‘20220429‘; SQL> select * from tab where id=‘1289‘ and to_char(datetime,‘yyyymmdd‘)=‘20220429‘; ID DATETIME -------------------- -------------- 1289 29-04-22 13:58 Execution Plan ---------------------------------------------------------- Plan hash value: 1995730731 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 13 | 5 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| TAB | 1 | 13 | 5 (0)| 00:00:01 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("ID"=‘1289‘ AND TO_CHAR(INTERNAL_FUNCTION("DATETIME"),‘yyy ymmdd‘)=‘20220429‘) Statistics ---------------------------------------------------------- 62 recursive calls 0 db block gets 51 consistent gets 0 physical reads 0 redo size 600 bytes sent via SQL*Net to client 519 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 4 sorts (memory) 0 sorts (disk) 1 rows processed ************************ 测试4 ************************ create index tab_index on tab(id,to_char(datetime,‘yyyymmdd‘)); SQL> / ID DATETIME -------------------- -------------- 1289 29-04-22 13:58 Execution Plan ---------------------------------------------------------- Plan hash value: 4028735706 ----------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 13 | 2 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| TAB | 1 | 13 | 2 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | TAB_INDEX | 1 | | 1 (0)| 00:00:01 | ----------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("ID"=‘1289‘ AND TO_CHAR(INTERNAL_FUNCTION("DATETIME"),‘yyyymmdd‘)=‘ 20220429‘) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 4 consistent gets 3 physical reads 0 redo size 600 bytes sent via SQL*Net to client 519 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed ************************ 测试5 ************************ 新创建的表 select * from tab where id=‘1289‘ and to_char(datetime,‘yyyymmdd‘)=‘20220429‘; SQL> / ID DATETIME ---------- -------------- 1289 29-04-22 22:42 Execution Plan ---------------------------------------------------------- Plan hash value: 1995730731 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 22 | 5 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| TAB | 1 | 22 | 5 (0)| 00:00:01 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("ID"=1289 AND TO_CHAR(INTERNAL_FUNCTION("DATETIME"),‘yyyym mdd‘)=‘20220429‘) Note ----- - dynamic sampling used for this statement (level=2) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 17 consistent gets 14 physical reads 0 redo size 599 bytes sent via SQL*Net to client 519 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed ************************ 测试5 ************************ create index tab_index on tab(id,to_char(datetime,‘yyyymmdd‘)); SQL> select * from tab where id=‘1289‘ and to_char(datetime,‘yyyymmdd‘)=‘20220429‘; ID DATETIME ---------- -------------- 1289 29-04-22 22:42 Execution Plan ---------------------------------------------------------- Plan hash value: 4028735706 ----------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 28 | 2 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| TAB | 1 | 28 | 2 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | TAB_INDEX | 1 | | 1 (0)| 00:00:01 | ----------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("ID"=1289 AND TO_CHAR(INTERNAL_FUNCTION("DATETIME"),‘yyyymmdd‘)=‘20 220429‘) Note ----- - dynamic sampling used for this statement (level=2) Statistics ---------------------------------------------------------- 10 recursive calls 0 db block gets 27 consistent gets 3 physical reads 0 redo size 599 bytes sent via SQL*Net to client 519 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed ************************ 测试6 ************************ select * from tab where id=1289 and to_char(datetime,‘yyyymmdd‘)=‘20220429‘; SQL> select * from tab where id=1289 and to_char(datetime,‘yyyymmdd‘)=‘20220429‘; ID DATETIME ---------- -------------- 1289 29-04-22 22:42 Execution Plan ---------------------------------------------------------- Plan hash value: 4028735706 ----------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 28 | 2 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| TAB | 1 | 28 | 2 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | TAB_INDEX | 1 | | 1 (0)| 00:00:01 | ----------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("ID"=1289 AND TO_CHAR(INTERNAL_FUNCTION("DATETIME"),‘yyyymmdd‘)=‘20 220429‘) Note ----- - dynamic sampling used for this statement (level=2) Statistics ---------------------------------------------------------- 7 recursive calls 0 db block gets 25 consistent gets 0 physical reads 0 redo size 599 bytes sent via SQL*Net to client 519 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed ************************ 测试6 ************************ select * from tab where to_char(id)=‘1289‘ and to_char(datetime,‘yyyymmdd‘)=‘20220429‘; SQL> select * from tab where to_char(id)=‘1289‘ and to_char(datetime,‘yyyymmdd‘)=‘20220429‘; ID DATETIME ---------- -------------- 1289 29-04-22 22:42 Execution Plan ---------------------------------------------------------- Plan hash value: 1995730731 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 28 | 5 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| TAB | 1 | 28 | 5 (0)| 00:00:01 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(TO_CHAR("ID")=‘1289‘ AND TO_CHAR(INTERNAL_FUNCTION("DATETIME"),‘yyyymmdd‘)=‘20220429‘) Note ----- - dynamic sampling used for this statement (level=2) Statistics ---------------------------------------------------------- 5 recursive calls 0 db block gets 35 consistent gets 0 physical reads 0 redo size 599 bytes sent via SQL*Net to client 519 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed

SQL执行异常系列之——隐式转换

标签：pre exec statistic drop row orm fun obj ips

热心网友时间：2022-04-10 14:59

直接输出400

直接select一下就是结果了

追问谢谢了

sql server数据库rdbms显示转换,隐式转换是什么意思

隐式转换，就是你不使用转换函数，默认就给转换了。比如定义一个int型的变量@a，然后给变量符值 set @a = '2' ，这个就会隐式转换，把字符转换成数字了。

SQl Server 格式转换失败 Conversion failed when converting date and...

startdate时间两个已经是datetime类型，拼接字符串的时候是需要先转换为字符的所以，你的写法中，可以将时间变量定义为varchar(10)至于字符串内部又将字符转换回date类型也不需要，SQLServer在比较数据时会发生隐式转换为同一数据类型

关于SQLServer中修改字段(列)所会引发的问题

更改类型对数据的影响取决于隐式转换，比如datetime改成varchar的话会变成AM PM一类的然后标志列，就是自增长的，不能修改类型，可以用设计器修改，设计器会先删除列再重新创建

SQL SERVER:Union后的字段类型是不是与Union之前的一致;

一旦发生兼容转化，隐式转化，都是会向优先级高的类型转化。所以Union 的前后顺序根本不重要。以上只是一个试验，并没有返回1而是1.0，因为浮点型的优先级比int类型高！如下是类型优先级表格：所以这种转化也会消耗CPU时钟，所以建立表的时候类型选取也是一门学问。记住兼容类型才能隐式转化。

...System.Data.Common.DbDataReader”隐式转换为“System.Da_百度...

你用的 Sql Server，那最好就用 SqlCommand，就不需要强制转换了。否则你就修改 SqlDataReader dr = cmd.ExecuteReader();为 SqlDataReader dr = (SqlDataReader)cmd.ExecuteReader();

sqlserverfloat用法

float是近似数值，存在精度缺失，Decimal是精确数值，不存在精度损失。当数值不允许精度丢失时，使用 decimal数据类型存储。在计算小数的除法时，SQL Server 内部隐式升级数据类型，根据小数数值的数据类型，就近向float(24) 或float(53)转换。一：近似数值，存在精度损失 1，float 表示近似数值 float数据...

sql server 数据库二维数值模式转换一维数值模式

1、语法 ISNULL ( check_expression , replacement_value )2、备注如果 check_expression 不为 NULL，则返回它的值；否则，在将 replacement_value 隐式转换为 check_expression 的类型（如果这两个类型不同）后，则返回前者。3、参数 check_expression 将被检查是否为 NULL 的表达式。check_expression...

如何在SQL server 将日期补足8位(字段是字符型,不能转换为日期型)

DateTime到字符串是隐式转换所以你不用管。。。update table set datetimecolumn=Convert(datetime,datetimecolumn,120)这样就可以了，，，datetimecolumn就是你要转换的那个字段的名字。。。

sqlserver怎么把nchar类型转换成int类型我下面的转换不行啊?_百度...

从nchar类型到int类型的转换，是隐式的转换

sql server数据库中 smallint, int ,bigint ,tinyint的区别与长度

bigint 可以精确的表示从 -2^63---2^63-1 之间的整数，数据类型存储为8字节存储空间，当bigint值在int数据类型支持的范围内时， sql server 才将 bigint 隐式转换成int, 如果bigint 表达式包含了一个在int 数据类型支持范围之外的值，就会在运行时出现转换错误。在数据类型优先表中，bigint 数据...

隐式转换和强制转换 js强制转换和隐式转换隐式转换和显示转换 scala的隐式转换 oracle 隐式转换隐式转换是什么隐式转换规则 js隐式转换无法将类型隐式转换为

SQL Server中 隐式转换的问题

SQL Server中隐式转换的问题