在SQL Server过程中使用varchar（max）或nvarchar（max）数据类型声明变量时是否存在任何性能问题.... [英] Is there any performance issue while declaring variables with varchar(max) or nvarchar(max) data type in SQL server procedure....

问题描述

嗨专家，

我有程序，其中变量数据类型声明和长度 应该从特定的物理表列属性继承。

例如 

声明@ variable1 varchar（n）;

这里n应该是物理表中特定列的长度。如果物理表中的特定列长度发生变化，则n值也应该更改。同样的事情也适用于数据类型。这就是列数据类型更改，然后变量
数据类型应该更改。

我们如何以动态方式实现此目标 在程序中？

目前，在这种情况下，我将创建长度最大的变量（对于varchar数据类型） 



是声明长度为max的变量的好方法吗？如果我用varchar（max）创建变量，是否有任何性能问题。

请提供可能的解决方案。

提前致谢，

Venkat Ram。

解决方案

Venkat，

一个有趣的问题！

根据声明（以及实际数据长度）会出现性能问题 在物理表中，当存储移动到LOB时，但我不知道变量声明会产生任何性能问题！

当然最好是你的变量声明反映了那些变量声明物理表，但没有神奇的方法来实现这一点。 我们*想要的*是这样的：

声明@myvar tablename.columnname;

好吧，我认为我们不会很快就能得到。 也许告诉ANSI SQL委员会！

同时，如果你声明所有变量最大，我认为没有任何性能问题。

... afaik ...

Josh

ps - 如果您在表中的TSQL中使用用户定义的（标量，"别名"）数据类型和程序，这是一种方法，但它是很多工作。 显然，新的SQL doc online在这个主题上编写得非常糟糕，但是这个
至少可以给你一个想法：

https://docs.microsoft.com/en -us / sql / relational-databases / databases / create-a-user-defined-data-type-alias？view = sql-server-2017

Hi Experts,

i have procedure in which variables data type declaration and length  should be inherited from the particular physical table columns properties.

for example 

Declare @variable1 varchar(n);

here n should be the length of the particular column in physical table.if the particular column length changes in physical table , then n value also should be change .same thing applies for datatype also.that is if column data type changes ,then variable datatype should change.

How can we achieve this in dynamic way  in procedure?

currently ,in this case i am going to create those variables with length max.(for varchar data types) 

is it good approach to declare the variables with length of max? is there any performance issue ,if i am creating the variables with varchar(max).

Please provide possible solutions.

Thanks in Advance,

Venkat Ram.

解决方案

Venkat,

An interesting question!

There will be performance issues depending on the declarations (and actual data lengths) in the physical tables as storage moves off to LOB, but I am not aware of any performance issues coming from declarations of variables!

Of course it's best if your variable declarations mirror those in the physical tables, but there is no magic way to make that happen.  What we'd *want* is something like:

declare @myvar tablename.columnname;

Well, I don't think we'll get that anytime soon.  Maybe tell it to the ANSI SQL committee!

Meanwhile, I don't think there's any performance issue if you declare all variables max.

… afaik …

Josh

ps - if you use user-defined (scalar, "alias") data types in TSQL in both the table and the procedure, that's one way to go, but it's a lot of work.  And apparently the newer SQL doc online is very poorly written on this topic, but this at least gives you the idea:

https://docs.microsoft.com/en-us/sql/relational-databases/databases/create-a-user-defined-data-type-alias?view=sql-server-2017

这篇关于在SQL Server过程中使用varchar（max）或nvarchar（max）数据类型声明变量时是否存在任何性能问题....的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！