syntax structure:
1. Cast
Syntax structure: CAST (expression as data_type [(length)])
2. Convert
Syntax structure: CONVERT (data_type [(length)], expression [, style])
3. Parse
Syntax structure: PARSE (string_value as data_type [USING culture])
Where Parse is a new feature of SQL Server expression must be a string form of the converted Type
Performance Analysis:
Summarize:
1. The difference between Convert and Cast
Convert is used specifically for SQL Server, allowing for greater flexibility in converting date and time values to decimal places.
Cast is a more ANSI standard feature in two functions, that is, although more portable (for example, the use of cast functions can be more easily used by other database software), but the function is relatively weak. However, when a decimal is converted to a numeric value and the decimal value in the original expression is preserved, you still need to use the cast
2. The difference between Convert and Parse
In addition to the difference between the syntax, is the difference between functions, convert more powerful, Parse for SQL Server 2012 new features, but also a small number of features, can only convert strings to numeric types or date types, because the number type or date type is very small, so The performance of Parse is almost impossible to change, and I have made a lot of comparisons and found that Parse is an alternative in most cases.
3. Comprehensive comparison
A. If you are doing development for SQL Server, it is recommended that you use the Convert method only
B. If you consider statements across platforms, try to use Cast, such as SQL statements that can be executed on SQL Server and executed on MYSQL
C. Parse in SQL Server 2012 is a chicken, after 1 afternoon of actual combat research and browsing MSDN, did not find its advantages, if more than the syntax, Cast the grammatical structure and it, if more than the function, and too weak, completely can not be compared with Cast and Convert, the So you can forget about Parse.
performance analysis of the experiment:
1. Convert and Parse conversions to strings comparison
Copy Code code as follows:
DBCC Freeproccache
Set STATISTICS PROFILE on
Select top 1 Parse (name as bigint) from Test_parse
Select top 1 convert (bigint,name) from Test_parse
Where name is a section of nvarchar (1000) with a value of: 1231238912378912128
Once executed, it is found that the execution plan for both is exactly the same, regardless of CPU consumption or IO consumption, without any performance difference
2. Conversion comparisons for money and DateTime types
Copy Code code as follows:
--DateTime
DBCC Freeproccache
DECLARE @date varchar (100) = ' 2012/07/19 14:57:09.760 '
Select CONVERT (DateTime, @date) as Date
Select Parse (@date as datetime using ' ZH-CN ') as Date
--money
SELECT PARSE ('? 345,98 ' as Money USING ' de-de ') as result
Select Convert (Money, '? 345,98 ', 1) as result
3. Parse's patent, character-type date to datetime conversion
Copy Code code as follows:
SELECT PARSE (' Monday December ' as DateTime2 USING ' en-us ') as result
SELECT PARSE (' Monday December ' as DateTime2 USING ' ZH-CN ') as result
--Cast and Convert are not supported for this type of string
--select cast (' Monday December ' as datetime2) as result
--select Convert (datetime, ' Monday, December ') as result
There is no difference in performance, but when it comes to comparing features, Parse is much more wrong than Convert.
Serious doubt, Parse I did not find too many advantages and application scenes, or because I talents, can not find better, have understood, please enlighten
