经验分享：实例化SqlParameter时，如果是字符型，一定要指定size属性

游北亮
博客专家认证
2012-01-31 11:32:19
加精
以前在实例化SqlParameter时，通常都是用下面的语句，没有设置size属性：
new SqlParameter("@name", SqlDbType.Varchar) { Value = name };
根据MSDN解释：如果未在 size 参数中显式设置 Size，则从 dbType 参数的值推断出该大小。
我一直以为是从SqlDbType类型推断，实际上是从参数的值推断，比如"ab"，则size值为2，"abcd"，则size值为4，且经测试发现，size的值不同时，会导致执行计划不会重用，下面的代码：
string sql = "select top 1 * from tb where name = @o";

var para = new SqlParameter("@o", SqlDbType.VarChar) {Value = "ab"};

SqlHelper.ExecuteReader(ReadConnectionString, CommandType.Text, sql, para);
通过SqlProfiler捕获到的sql如下：
exec sp_executesql N'select top 1 * from tb where name = @o',N'@o nvarchar(2)',@o=N'ab'
如果把参数的值改成abcd，则通过SqlProfiler捕获到的sql如下：
exec sp_executesql N'select top 1 * from tb where name = @o',N'@o nvarchar(4)',@o=N'abcd'
通过下面的sql，可以看出执行计划是否有重用：
--先清空执行计划缓存

DBCC FREESYSTEMCACHE ('ALL')

DBCC FREEPROCCACHE

GO

SELECT * FROM sys.dm_exec_cached_plans WHERE cacheobjtype = 'Compiled Plan'

GO

exec sp_executesql N'select top 1 * from tb where name = @o',N'@o nvarchar(2)',@o=N'ab'

GO

SELECT * FROM sys.dm_exec_cached_plans WHERE cacheobjtype = 'Compiled Plan'

GO

exec sp_executesql N'select top 1 * from tb where name = @o',N'@o nvarchar(4)',@o=N'abcd'

GO

SELECT * FROM sys.dm_exec_cached_plans WHERE cacheobjtype = 'Compiled Plan'

GO

最后的说明，实例化SqlParameter时，如果是字符型，一定要指定size属性，如：
new SqlParameter("@name", SqlDbType.Varchar, 4000) { Value = name };
如果是Int、Float、Bigint之类的参数，可以不用指定size属性
...全文
3959 101 打赏 收藏 转发到动态 举报
写回复
用AI写文章
101 条回复
切换为时间正序
请发表友善的回复…
发表回复
diywzw 2012-06-09
  • 打赏
  • 举报
 回复
支持一下








[font=微软雅黑][size=1]SIGNATURE...
[/size][/font]
http://www.meir5.com|http://www.haoyupen.com
yojinlin 2012-02-19
  • 打赏
  • 举报
 回复
學習了。
rose19890205 2012-02-17
  • 打赏
  • 举报
 回复
看看，增加经验！
谢谢
jiuhexuan 2012-02-16
  • 打赏
  • 举报
 回复
各sql类型的size，可以参考
public static int GetColumnSize(string dataType)
{
if (dataType.StartsWith("decimal")
|| dataType.StartsWith("numeric"))
{
return 17;
}
if (dataType.StartsWith("varbinary")
|| dataType.StartsWith("char")
|| dataType.StartsWith("varchar")
|| dataType.StartsWith("nchar")
|| dataType.StartsWith("nvarchar"))
{
Match m = Regex.Match(dataType, @"\((.+?)\)");
if (m.Success&& !m.Groups[1].Value.ToLower().Equals("max"))
{
return int.Parse(m.Groups[1].Value);
}
}
int size = 0;
switch (dataType.ToLower())
{
case "bit":
case "tinyint":
size = 1;
break;
case "smallint":
size = 2;
break;
case "date":
size = 3;
break;
case "int":
case "real":
case "smallmoney":
case "smalldatetime":
size = 4;
break;
case "time":
size = 5;
break;
case "bigint":
case "datetime":
case "money":
case "float":
case "timestamp":
size = 8;
break;
case "uniqueidentifier":
size = 16;
break;
case "decimal":
case "numeric":
size = 17;
break;
case "binary":
size = 50;
break;
case "ntext":
size = 1073741823;
break;
case "varbinary(max)":
case "nvarchar(max)":
case "varchar(max)":
case "xml":
case "image":
case "text":

case "geography":
case "geometry":
size = 2147483647;
break;
case "hierarchyid":
size = 892;
break;
case "variant":
size = 8009;
break;
}
return size;
}

http://codinge.com/DotNetDoc/sqlserver/23.shtml
游北亮 2012-02-13
  • 打赏
  • 举报
 回复
嗯，如果sql和存储过程一样，全部参数化，那么确实如你所说，速度和安全没有可比性
我的回答说存储过程快，确实太武断了
一般问我哪个快的人，通常sql都没有参数化，所以我也就习惯回答存储过程快了

如果之前的回答对大家造成的困扰，我这边道歉，可惜不能改前面的帖子

[Quote=引用 94 楼 的回复:]
引用 92 楼 youbl 的回复:

比较是有前提条件的：
1、是基于相同功能的sql
2、频繁的类似操作

存储过程预先编辑好存储于数据库，它只有在第一次调用时会编译并生成执行计划，然后再执行，后续的
频繁调用都会直接重用这个执行计划去执行，即使参数不同

而sql是一个字符串，提交给数据库，都要去编译并生成执行计划，再执行；如果提交了一模一样的sql，那就跟存储过程类似了，……
[/Quote]
依然不逆 2012-02-13
  • 打赏
  • 举报
 回复
[Quote=引用 92 楼 youbl 的回复:]

比较是有前提条件的：
1、是基于相同功能的sql
2、频繁的类似操作

存储过程预先编辑好存储于数据库，它只有在第一次调用时会编译并生成执行计划，然后再执行，后续的
频繁调用都会直接重用这个执行计划去执行，即使参数不同

而sql是一个字符串，提交给数据库，都要去编译并生成执行计划，再执行；如果提交了一模一样的sql，那就跟存储过程类似了，可以重用执行计划，sql有一点点不一样，也……
[/Quote]

1.你比较下
select * from tb
和
create proc p_test
as
set nocount on
select * from tb
go

两个的执行计划，比较下执行时间，IO，CPU的情况！

2。频繁的操作无非是适用了缓存的执行计划有优势，没别的

存储过程会编译生成执行计划，同样SQL一样可以，能否重用貌似是看缓存中是否有这个执行计划吧，没有同样会去生成！跟参数想不相同没有关系！至于你说的不一样，取决于你参数的类型和大小，也就是你发这个帖子的初哀了。


微软官方给的解释是说存储过程有这样的优点，但是没说和同样的SQL语句比较！

就好比你写了一堆代码和把这一堆代码封装到一个类或者方法中，你说最后执行的这堆代码还是你封装的方法或者类，两个最终的操作不一样吗？

速度和安全不取决你是写的存储过程还是直接的SQL语句，在于写这个代码的人的安全意识和能力！

天才在左疯子在右 2012-02-13
  • 打赏
  • 举报
 回复
嗯，受教了，以前还真没注意
游北亮 2012-02-11
  • 打赏
  • 举报
 回复
关于存储过程优点的微软官方解释：
http://msdn.microsoft.com/zh-cn/library/ms191436.aspx
游北亮 2012-02-11
  • 打赏
  • 举报
 回复
比较是有前提条件的：
1、是基于相同功能的sql
2、频繁的类似操作

存储过程预先编辑好存储于数据库，它只有在第一次调用时会编译并生成执行计划，然后再执行，后续的
频繁调用都会直接重用这个执行计划去执行，即使参数不同

而sql是一个字符串，提交给数据库，都要去编译并生成执行计划，再执行；如果提交了一模一样的sql，那就跟存储过程类似了，可以重用执行计划，sql有一点点不一样，也不会重用执行计划，即使只是多空格或变大写之类

那么一般而言，sql是不是会慢一些呢？



[Quote=引用 91 楼 的回复:]
可怕的是，自己所谓的观点有没有经过验证，在那自以为是灌输给别人，会误导人的！

先声明不是针对你本人，也不是针对你发的帖子，只是针对你回答那些问存储过程和一般sql语句的性能和安全的说法！
[/Quote]
依然不逆 2012-02-11
  • 打赏
  • 举报
 回复
可怕的是，自己所谓的观点有没有经过验证，在那自以为是灌输给别人，会误导人的！

先声明不是针对你本人，也不是针对你发的帖子，只是针对你回答那些问存储过程和一般sql语句的性能和安全的说法！
cnwin 2012-02-11
  • 打赏
  • 举报
 回复
不错,不指定的话去结果都取不正确,我遇到过.
依然不逆 2012-02-11
  • 打赏
  • 举报
 回复
[Quote=引用 87 楼 youbl 的回复:]

不解释，你认为自己对，那么坚持自己的想法就好了

引用 86 楼 的回复:
谁跟你说存储过程速度快些？存储过程无非把批处理封到一块了，最终执行的还是那个sql语句，跟拼接的sql语句有什么却别呢？
[/Quote]

可怕的是，没有验证就自以为是，会误导很多人的！
依然不逆 2012-02-11
  • 打赏
  • 举报
 回复
[Quote=引用 87 楼 youbl 的回复:]

不解释，你认为自己对，那么坚持自己的想法就好了

引用 86 楼 的回复:
谁跟你说存储过程速度快些？存储过程无非把批处理封到一块了，最终执行的还是那个sql语句，跟拼接的sql语句有什么却别呢？
[/Quote]

这不是坚持自己想法的问题，你要是不信可以自己测，或者懒的测，你去sql版找几个大牛给你解释一下！
网上很多关于存储过程的安全和速度的说法，有几个自己验证了的？三人成虎。。。。。
游北亮 2012-02-11
  • 打赏
  • 举报
 回复
不解释，你认为自己对，那么坚持自己的想法就好了

[Quote=引用 86 楼 的回复:]
谁跟你说存储过程速度快些？存储过程无非把批处理封到一块了，最终执行的还是那个sql语句，跟拼接的sql语句有什么却别呢？
[/Quote]
phinexhe 2012-02-09
  • 打赏
  • 举报
 回复
est.text AS batchtext,
SUBSTRING(est.text, (eqs.statement_start_offset/2)+1,
(CASE eqs.statement_end_offset WHEN -1
THEN DATALENGTH(est.text)
ELSE eqs.statement_end_offset END -
((eqs.statement_start_offset/2) + 1))) AS querytext,
eqs.creation_time, eqs.last_execution_time, eqs.execution_count,
eqs.total_worker_time, eqs.last_worker_time,
eqs.min_worker_time, eqs.max_worker_time,
eqs.total_physical_reads, eqs.last_physical_reads,
eqs.min_physical_reads, eqs.max_physical_reads,
eqs.total_elapsed_time, eqs.last_elapsed_time,
eqs.min_elapsed_time, eqs.max_elapsed_time,
eqs.total_logical_writes, eqs.last_logical_writes,
eqs.min_logical_writes, eqs.max_logical_writes,
eqs.query_plan_hash
FROM
sys.dm_exec_query_stats AS eqs
CROSS APPLY sys.dm_exec_sql_text(eqs.sql_handle) AS est
ORDER BY eqs.total_physical_reads DESC


没指定size导致执行的batchtext不一样
旋转拖把品牌http://www.tuobapinpai.com
还有个要注意，不光要指明size的大小，还有制定具体的类型
依然不逆 2012-02-09
  • 打赏
  • 举报
 回复
[Quote=引用 79 楼 youbl 的回复:]

当然是存储过程快，因为存储过程已经编译好了
而sql要先分析编译，创建执行计划再执行的

注：存储过程里拼接动态sql的不算，动态sql跟代码里拼sql没区别

引用 73 楼 y_lightc_q 的回复:
弱弱问一下，拼SQL，和存储过程，那个快？神马情况下会拼SQL？
再短的sql，我也会写一个存储。。。
[/Quote]

谁跟你说存储过程速度快些？存储过程无非把批处理封到一块了，最终执行的还是那个sql语句，跟拼接的sql语句有什么却别呢？

至于存储过程安全，只能说存储过程已经是通过sql语法验证过的，也就是sql的一部分了！这个相对从程序里面传入的sql执行起来要验证语法啊，等！

JinJJ1402 2012-02-09
  • 打赏
  • 举报
 回复
还没有注意过，顶
yy120925 2012-02-08
  • 打赏
  • 举报
 回复
好像存储结构更安全~我也不大懂~同样请教~

[Quote={73楼}]{拼SQL，和存储过程，那个快}[/Quote]
liyf_liyunfeng 2012-02-08
  • 打赏
  • 举报
 回复
受教了。感谢LZ
Y_lightc_Q 2012-02-08
  • 打赏
  • 举报
 回复

弱弱问一下，拼SQL，和存储过程，那个快？神马情况下会拼SQL？
再短的sql，我也会写一个存储。。。

加载更多回复（48）
总结：实例化SqlParameter时，如果是字符型，一定要指定size属性，还有制定具体的类型
以前在实例化SqlParameter时，通常都是用下面的语句，没有设置size属性： new SqlParameter("@name", SqlDbType.Varchar) { Value = name }; 根据MSDN解释：如果未在 size 参数中显式设置 Size，则从 dbType ...
项目总结——SqlParameter的参数设置长度(size属性)
看到很多朋友在实例化SqlParameter时，通常都没有指定参数的长度就直接给参数赋值了。就像下面的写法： new SqlParameter("@address", SqlDbType.Varchar) { Value = address};  以前也一直都觉的加与不加...
SqlParameter 使用
看到很多朋友在实例化SqlParameter时，通常都没有指定参数的长度就直接给参数赋值了。就像下面的写法： new SqlParameter("@address", SqlDbType.Varchar) { Value = address};  以前也一直都觉的加与...
请为你的SqlParameter的参数设置长度(size属性)
看到很多朋友在实例化SqlParameter时，通常都没有指定参数的长度就直接给参数赋值了。就像下面的写法： new SqlParameter("@address", SqlDbType.Varchar) { Value = address}; 以前也一直都觉的加与不加参数长度...
NET中应用SQL参数化时当要注意的一些细节问题
在通过ADO.NET与SQL进行数据交互时，为了防止SQL注入，一般采用参数化的方式，而SqlParameter对应的构造函数如下 // 摘要: // 初始化 System.Data.SqlClient.SqlParameter 类的新实例。 public SqlParameter(); /...
C#

111,082

社区成员

642,569

社区内容

发帖
与我相关
我的任务
社区描述
.NET技术 C#
社区管理员
  • C#
  • AIGC Browser
  • by_封爱
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告

让您成为最强悍的C#开发者

试试用AI创作助手写篇文章吧

+ 用AI写文章