Data type conversion has been one of the most used features of any programming language. Microsoft SQL Server users are familiar with the two functions: CAST() and CONVERT() and use them regularly, and more specifically, interchangeably. A few months ago, I had written a post comparing the performance of CAST v/s CONVERT, and was able to prove that there is no difference in performance of the two functions.You can read that post here: http://beyondrelational.com/blogs/nakul/archive/2011/01/03/cast-v-s-convert.aspx.
Recently, a colleague and I were discussing a particular design element and found that a data type casting was required. During this discussion, the following questions almost simultaneously came to our minds –
- If the performance and end results are the same, what is the difference between CAST & CONVERT aside from the fact that they have different syntax elements?
- Do we really need 2 data conversion functions?
| Parameter | CAST | CONVERT |
| ANSI standard | Yes | No |
| Data-type coverage | Limited | Full (Date & Date Time values supported) |
| Performance | No difference | No difference |
| Microsoft SQL Server implementation | ?? | ?? |
| Books On Line Page | http://msdn.microsoft.com/en-us/library/ms187928.aspx | http://msdn.microsoft.com/en-us/library/ms187928.aspx |
So, I ran a little test, and today, I will share with you my findings.
The CAST v/s CONVERT Test
The test is quite simple – we have two identical T-SQL statements, one using CAST, the other using CONVERT. We will use the Properties window of SSMS to analyze the execution plan and try to see what can be found about the underlying implementation.
Let’s take a look at our test statements:
01./***********************************************************02.Press Ctrl+M (or go to Query->Include Actual Execution Plan)03.************************************************************/04.USE AdventureWorks2008R205.GO06.--Use CONVERT07.select CONVERT(VARCHAR(10),BusinessEntityID) FROM HumanResources.Employee08. 09.--Use CAST10.select CAST(BusinessEntityID AS VARCHAR(10)) FROM HumanResources.Employee- Connect to your SQL Server instance using SQL Server Management Studio (SSMS)
- Copy the T-SQL code above into SSMS Query editor window
- Run the above T-SQL statements against your SQL Server instance
- Change over to the Execution Plan tab in the Results pane
- In the results pane, notice that both CAST & CONVERT have been implemented as “Compute Scalar” operators
- Press the F4 key or go to View –> Properties to launch the properties window
- Expand the “Defined Values” node
CONVERT implementation
CONVERT does not come up with any surprises, and has a straightforward internal implementation as demonstrated in the Properties window.
CAST implementation
When we move to the properties for the query using the CAST operation, we see that under the hood, SQL Server does take us for a ride. Internally, CAST is implemented as a CONVERT call. There is no difference between CAST & CONVERT besides the fact that CAST is an ANSI standard, while CONVERT is not. No wonder both CAST & CONVERT demonstrate the same performance.
Surprised? Try it for yourself - I was not prepared to see what I saw when I first ran through the test. I restarted the entire server and got a cup of coffee to make sure I was not dreaming!
Conclusion
I guess all I need to do now is to complete the little grid I had above:
| Parameter | CAST | CONVERT |
| ANSI standard | Yes | No |
| Data-type coverage | Limited | Full (Date & Date Time values supported) |
| Performance | No difference | No difference |
| Microsoft SQL Server implementation | CONVERT | CONVERT |
| Books On Line Page | http://msdn.microsoft.com/en-us/library/ms187928.aspx | http://msdn.microsoft.com/en-us/library/ms187928.aspx |
What would I recommend?
Although we now know that to Microsoft SQL Server, data type conversion only happens via CONVERT, I would still prefer to go with CAST unless I am converting dates or date-time values. The reason being CAST is an ANSI standard, CONVERT is not. CONVERT is implementation specific, and therefore may change in terms of the number of parameters or underlying processes. To the calling T-SQL statement, it is safe to assume that CAST would remain unchanged (unless the standard changes) and therefore, it would be the responsibility of Microsoft SQL Server to translate the CAST to a CONVERT implementation.
Do share your thoughts on the whole CAST v/s CONVERT myths and controversies floating around in the SQL Server world. I will be more than happy to research more and share my findings with you.
Until we meet next time,
ไม่มีความคิดเห็น:
แสดงความคิดเห็น