SSMS keeps changing
CAST
(MyColumnID AS varchar(10))to
CONVERT
([varchar](10),[MyColumnID],0)Is that a suggestion to use only Convert, if the engine is going to convert it anyway
SSMS keeps changing my CAST function
Hot Topic
- how to get Alias name in columns and mapped column from views
- Transfer SQL Server Objects Task gives error with SQL Server authentication
- Problem referencing a global temporary table
- How to Delete Files on Maintenance Plan
- Multiple <Create>...</Create> in one file Problem
- Data Mining Tool
- SSI and other components don't install
- sub-reports don't start in the first page
- Reporting Services 2005 and Windows Sharepoint Services
- How to load a Legacy DTS Package from .NET?
SQL Server
- New Login with Trusted Connection
- Tricky Grand Total on Column Needed
- SSIS Package deployment with C# problem
- Problem of SQL Server 2005 Reporting service installation
- how to create sql server 2005 instance(after uninstalling sql server express)
- SSIS Versioning Control
- sql/stored procedure
- Transform update sql statements into a pipeline
- Unhandled Exception in Management Studio
- Can you add Output Columns to the Script Transformation Editor on the fly?
SSMS keeps changing my CAST function
Ilske
No, the AS clause of a computed column.
Then I looked at the "Computed Text" in the column properties and this is what the code got reworked to.
Also, when I right-click script-as it.
greekgoddj
This is a known problem in the engine and the query designer. The query designer however is buggy and can rewrite queries which can produce invalid results or errors. You can search in the MSDN Product Feedback Center for lot of bugs.
The engine issue is different in that when the DDL is submitted to the engine for example it is parsed, normalized and stored. The normalization part affects expressions that are part of computed column definitions, check constraints, default expressions etc. SQL Server 2005 is even more aggressive with the normalization than older versions. The issue with CAST is that it is internally implemented using CONVERT and hence the modification to CONVERT. Similar effect can be seen if you specify default expression as CURRENT_TIMESTAMP and it will get translated to getdate(). Note that both CAST and CURRENT_TIMESTAMP are ANSI SQL syntaxes so it is preferable to use it always when you can. The scripting process relies on the metadata that was stored in the engine tables to produce the DDL for example. This will have the side-effect of having the normalized expressions.
We are aware of this particular problem and we are looking to provide a solution in a future version. If you want the source to be formatted in a specific way then you cannot rely on the engine or SMO or the tools to do that for you. You have to maintain the source (DDL scripts) in your source control and do not reverse engineer from the database.
Gabriel Gheorghiu
Just curious, what do you mean it is changing it Are you executing a CREATE or ALTER statement and the opening the stored procedure to find it changed
I usually prefer CONVERT becuase it has more flexibility, although they are essentially the same. Don't know why it would try to change your code though.
qureshi
Thanks, Umachandar
I didn't know Convert was not ANSI SQL.