Hi,

1. I need a computed column in a SQL server table. Is it possible to define it from within DDT?

2. When I create it in SQLServer Management Studio, and import the database in DDT, I can't see that it is a computed column.

You will want to create a custom property within the BO in this case. In this example, the result set coming back should have the computed column, so you will want to create a custom property to expose this computed value.



When you refer to a computed column, how are you creating this value? Are you performing an aggregate type of command such as a SUM() or something along those lines? In either case, you will still need to create a custom property, but I wanted to make sure that I was sending you down the right path and wanted to make sure there were no other alternatives in this case.

Trent,

I refer to a computed column with no agregate in the formula, but a calculation on other columns on the same line 

examples:
 cast (column_b*column_c/column_d) as Char(10))
 my_udf(column_x, column_y)

and I'll have use of these computed columns, with CLR functions in the definition... client application have no need to know anything about these functions, only the result as if it was a read-only column

If it is impossible today, I know that custom property on the BO is a workaround,  but how could I use DDT on an imported SQL database where already exist these computed columns?

I am not understanding your question here. The DDT will not recognize a computed column (as there is no need) since it not necessary to distribute this column. This type of logic is generally used within a SPROC or VIEW. In the case of a view, the DD can only interpret a result set (which we use SMO to give us the columns). So if the column cannot be interpreted as a distributable column, then it will not appear within the DDT as such. Thus, you would have to create a custom property as previously mentioned in order to directly access it as a strong-typed property. If you have no need to binding to this column, then you could just reference the value through the CurrentRow["FieldName"] property of the BO.

Trent,

please, apologize for my bad explanation (remember english is not my native language  ).

My computed column are sometimes PERSISTED, and sometimes not. I need thes columns either for legacy code, either for technical calculations (and I do not want to put these calculation in the client, because it is a customer requirement that  business rules are developed in T-SQL on the server).

It's always necessary to distribute the computed columns : not the calculation formula, the column as a strong typed result, binded at the UI level.

• If a computed column is already in a table, client application must read it. So when importing the database in DDT, BO mapper needs to recognize it as a simple column, without knowing anything about the calculation formula. That is OK, and run as needed.
• But... if I need to modify an existing computed column, or to add a new computed column in a table, I prefer to do it from within DDT than from SSMS (a single tool for Data definition seems a good choice, especially when the tool - DDT - is good ).
  OK, It is possible in a post-deployement script (I tried it and it runs with no errors) but without any help provided by the DDT user interface. And the specificity of a computed column is not immediately visible in DDT.

Could you please consider this as a future enhancement?
( a checkbox for computed column, a textbox for the formula, a checkbox for Persisted)

Thanks again

Michel,

Thanks for that explanation.  I do understand what you are talking about, but SQL columns with Computed formulas are not currently supported with the DDT, regardless of whether or not the values for those columns are persisted. As Trent mentioned, utilizing a custom property on the BO and handling the computation either within the BO, or in the query (be it an in-line query, a SPROC, or a CLR SPROC) is the supported method to accomplish that type of functionality.

As far as adding the computed column as a future enhancement, we will of course look at adding that functionality, but it will not be in the near future. We are currently populating those column lists using SMO, which doesn't return the necessary information to interpret computed columns. As such, we won't be able to look at adding this until we do a low-level re-write of the DDT.

Thanks,

Dustin

Dustin,

thanks for that answer. Naively, I thought that SMO was bringing all informations about databases. I just forgot the mysterious MS world, where simplicity is not always where one could expect...

As post-deployment scripts seems to be a workaround, I'll use that way to create and modify computed columns.
BTW, do I need to write a IF NOT EXISTS... in the scripts, or is it 'automagically' checked by DDT during deployement?

The T-SQL within the Pre and Post deployment scripts is executed by the DDT before and after deployment, but the scripts themselves are not stored anywhere on the SQL server, so they won't exist the next time you go to deploy the database. Is that what you are referring to (i.e. making sure the script itself doesn't exist)?

If you mean the column on the table, then yes, you will need to do the usual tests to ensure everything is kosher before you go and try to add a new column. The DDT doesn't know what you are doing within those scripts, so it can't do anything fancy to help make sure you don't try and create the column multiple times .

Dustin,

I meant the column on the table. So if DDT does know nothing within the scripts, I'll do usual tests, as I used to do.

Thanks again.

Hi Ben, Trent,
Are you considering adding support to SQL Compute Columns to SF 2.0?

I know we can do some work around adding a BO Custom Field Property, but having an SQL Compute Columns field is much more better, faster and easier to use this column no matter how much records you will be working with.

Edhy Rijo