在sql中使用的最佳数据转换方法是什么

I need to convert a value to a different datatype in sql. And as I know I can use any of the CONVERT, TRY_CONVERT, PARSE and TRY_PARSE built in functions for that.

我需要在sql中将值转换为不同的数据类型。我知道我可以使用函数内建的任何转换，TRY_CONVERT, PARSE和TRY_PARSE。

SELECT 
    CONVERT(DECIMAL(18,2),'12');

SELECT 
    TRY_CONVERT(DECIMAL(18,2),'12');

SELECT 
    PARSE('12' AS DECIMAL(18,2));

SELECT 
    TRY_PARSE('12' AS DECIMAL(18,2));
SELECT 
    CAST('12' AS DECIMAL(18,2));

What I need to clarify is, among the above mentioned methods, performance wise what is the best method to convert some value to a different data type?

我需要澄清的是，在上面提到的方法中，性能方面，将某个值转换为不同数据类型的最佳方法是什么?

2 个解决方案

#1

Lets just talk about Convert, Cast and Parse to compare performance as others are just a variant of these.

让我们只讨论转换、转换和解析来比较性能，因为其他的只是这些的一个变体。

CONVERT is SQL Server specific, CAST is ANSI.

转换是SQL Server专用，CAST是ANSI。

CONVERT is more flexible in that you can format dates etc. Other than that, they are pretty much the same. If you don't care about the extended features, use CAST.

转换更灵活，因为你可以格式化日期等等。除此之外，它们几乎是一样的。如果您不关心扩展的特性，请使用CAST。

PARSE function is not a native Sql function, instead it is a .NET Framework Common Language Run-time dependent function. Then obviously it will have the performance overhead and also requires the presence of .NET CLR on the database Server. Continue to use the existing CAST and CONVERT functions wherever it is possible.

解析函数不是本机Sql函数，而是. net框架通用语言运行时相关函数。显然，它会有性能开销，并且还需要在数据库服务器上使用。net CLR。继续使用现有的CAST并在任何可能的地方转换函数。

For SQL Server: GENERALLY I will say- CONVERT is your guy.

对于SQL Server:通常我会说- CONVERT是你的对象。

Refer to the link below if you are have a specific datatype to convert. Example has TRY_PARSE and TRY_CONVERT as well.

如果您有一个特定的数据类型转换，请参考下面的链接。示例还有TRY_PARSE和TRY_CONVERT。

Reference: Performance comparison on large data set

参考:大数据集的性能比较

#2

This is just a benchmark test;

这只是一个基准测试;

declare @val DECIMAL(18,2);
declare @now datetime, @then datetime
DECLARE @intFlag INT

SET @intFlag = 1
select @then = getdate()
WHILE (@intFlag <=200000) begin
    set @val = CONVERT(DECIMAL(18,2),'12');
    SET @intFlag = @intFlag + 1
end
select @now = getdate()
print('CONVERT: ' + convert(varchar(50), datediff(millisecond, @then, @now)))

SET @intFlag = 1
select @then = getdate()
WHILE (@intFlag <=200000) begin
    Set @val = TRY_CONVERT(DECIMAL(18,2),'12');
    SET @intFlag = @intFlag + 1
end
select @now = getdate()
print('TRY_CONVERT: ' + convert(varchar(50), datediff(millisecond, @then, @now)))

--for other type of "conversion" also not including here....

Here is the OutPut:

这是输出:

CONVERT: 106
TRY_CONVERT: 110
PARSE: 4726
TRY_PARSE: 4736
CAST: 106

#1