.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
david stephan
Post New Web Links

Computed Column

Posted By:      Posted Date: August 28, 2010    Points: 0   Category :Sql Server
 
This is the forst time i am going to work with computed columsn. Is there a way computed columns not store the data in the table? If yes, how would i build my query when creating a computed column? By using Persisted, does it mean that i am storing the values in the database or going to do calculations on the fly  Thanks


View Complete Post


More Related Resource Links

Filter by Computed Column

  

Hello!


After looking for a driver to open legacy dbf drivers to connect, the next level trouble a have is this one:


first, i can retrieve the data but when trying to filter by a parameter (somesc columns) i get an error.

The error says about the database can't determine some blah blah!

The error is a columm tha is formated (  1234  ) about  8 space, fixed. The values in the query appears like that. I resolve this by Trimming Ltrim(ColumnName) and i get this (1234).

Now in SqlExpress, how can i filter by the new Trimmed Expression? (This is a primary key that the DBF use)

Sample:

SELECT column1, column2, column3
TABLE FoxProDB

Now 

SELECT Ltrim(column1) as NewExp, column2, column3
TABLE FoxProDB
Where NewExpre = @NewExpres                  -Error-


Thanx!


Datetime column with [Computed column specification] formula

  
i have a datetime field in my SQL Server table i dont want to add weekend dates in this column. For this reason i always have to check the calendar. how can i use [Computed column specification] formula so that i never add weekend dates?

computed column specification - When is it calculated?

  
on insert , select...?  It's better than Trigger? thanks.  

Error when using Computed Column

  
There are 2 errors that I  get when creating computed colums (Persisted) step1 first a created a UDF and i get the follwing error Msg 4512, Level 16, State 3, Procedure UdfGetMonthlyAmount, Line 77 Cannot schema bind function 'dbo.UdfGetMonthlyAmount' because name 'Subscriber.Reference.Period' is invalid for schema binding. Names must be in two-part format and an object cannot reference itself. It says there is a problem in this stmnt - SELECT @getFreqText = Name FROM Subscriber.Reference.Period WHERE Id = @frequencyId The UDF is as following: USE [Subscriber] GO /****** Object: UserDefinedFunction [dbo].[UdfGetMonthlyAmount] Script Date: 08/27/2010 12:55:00 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO create FUNCTION [dbo].[UdfGetMonthlyAmount] -- #description Returns the Subscriber's customized Forecast date as defined in Subscriber table -- #tables Subscriber -- #parameter: @SubscriberId ( @frequencyId int, @amount money ) RETURNS money WITH SCHEMABINDING AS BEGIN DECLARE @getFreqText varchar(38) DECLARE @monthlyAmount money --AVG DAYS = (365/12) --AVG WEEKS DECLARE @avgDays money = 30.4167 DECLARE @avgWeeks money = 4.3452 SELECT @getFreqText = Name FROM Subscriber.Reference.Period WHERE Id = @frequencyId IF (@getFreqText = 'Daily') BEGIN SET @monthlyAmount = (SELECT ISNULL(@amount,0) * @avgDays) END ELSE I

how to set the computed column into inbuilt database into vs2008 ?

  
hi..i have designed a database into the vs 2008 mobile database.my batabase has three column column1(int),column2(int),column3(int) and i want to set the value of val(column3)=val(column1)+val(column2);how and where to set the computed column value.

Binding a computed value to datagrid column

  
Hi, I have a WPF Datagrid with tree colums (A, B,C) which are bound to an Observable collection. The value of C is C=A+B. The value of A and B are updated via an event that I have subscribed to ( a realtime data feed). What is the best way to update the value of C each and every time A or B get chaged ? I'm new to WPF and should I use a converter to achive this ? Thanks V

Steps to alter function used as computed column

  
What is the best way to change a function that is currently being referenced from a calculated column in a table?  I get the following error when I try to alter it.   Msg 3729, Level 16, State 3, Procedure fn_sdtAcctJoin2, Line 232 Cannot ALTER 'dbo.fn_sdtAcctJoin2' because it is being referenced by object 'tblMembers'.

computed column to update it with max(value)+1

  

HI ,

  i have a column match_id up till now i have inserted values in it,now i have to make it a computed column with formula on it.

   now i am not going to insert the value for match_id column ,insted of that when i insert a row in that table then value of match_id column should be max(match_id)+1 .

foe eg.

a  b   match_id

1 2  1200

4 3  1201

8 9  1202

now  when i iniser a=12 and b=56 then match_id should calculated as max(match_id)+1 i.e

1202+1=1203 ,i had already  used identity column so tell me how to achive it???

Thank's

Digambar 

 


SQL 2008 SP2: adding a "persisted computed column" creates an "Internal Query Processor Error: The q

  
Hi, a simple (and long-existing) stored procedure started throwing this error after adding a persisted computed column on another table that is not used in the stored procedure (but is referenced by a foreign key in the table used in the stored proc.)
Msg 8624, Level 16, State 1, Procedure pSetPersonExtraAnswer, Line 26
Internal Query Processor Error: The query processor could not produce a query plan. For more information, contact Customer Support Services.

I think I have traced the "culprit" to the following update.
ALTER TABLE dbo.Persons ADD fullName AS (firstName + ' ' + lastName) PERSISTED
GO


Here is the very weird thing:

If I restore the DB, immediately add the persisted computed column and run the problematic SP call, I get the error in Management Studio.

If I restore the DB, run the problematic SP call and then add the computed column, I no longer get an error from SSMS when I rerun the SP call. But, I still get it from my ASP.NET (via enterprise library) code!

When the error happens, altering the pro

Error when replicating a Computed Column.

  

Hello,

I have a table with computed column as a primary key (Order ID + Branch ID). The problem is when i try to publish the articles it gives me the following error. I need this computed column as a primary key for replication the Order_ID in different branches. Please Help.

The error message is in the following link:

http://tinypic.com/view.php?pic=msf2x5&s=7

Regards,

 


Modify the COMPUTED COLUMN to PERSISTED

  

I have a table which contains a computed column.

Can I alter the table to make the computed column into persisted computed column?


How to alter computed Column in SQL Server

  
I have a table with around 50 columns and in this table most of them are computed columns

Now I have requirement to change the definition of one of the computed column

Say:

Previously : Net_Profit As (Column1+Column2+Column3+Column4)

Now it should be: : Net_Profit As (Column1+Column2+Column3+Column4+Column5)

Here what will be best way to solve this problem

Shall I create a script of whole table along data 
and drop previous one and execute new script with adding new column

OR

There is other better way for this.

Actually I have very important data in this table which i cann't afford to loose at any cost


Computed Column Specification

  

I have a computed column specified for a division, how can i avoid having the 0 division error? Is there a way to create an if statement so if the divisor of my formula is 0 I want the value to be 0...

Thanks for your help


Index on persisted computed column not functioning as expected

  

In the below example, I'd expect both queries to be seeks against their appropriate index, however the engine doesn't seem to be able to handle ISNULL appropriately.  I've changed my computed columns to be non nullable by actually specifying not null instead of using isnull.  Any feedback on this strange behavior would be appreciated, as I have always thought ISNULL was the appropriate way to make a computed column non-nullable until I discovered not null actually functions, but is not exposed through design view mode.

 

CREATE TABLE #t
( ID INT IDENTITY(1, 1) PRIMARY KEY ,
 title VARCHAR(250) NOT NULL,
 inUse BIT NOT NULL,
 isActive BIT NOT NULL,
 test AS ISNULL(CAST(

Tooltip for GridView Column Headers (ASP.Net)

  
I just wanted to add auto tooltips for my GridView headers. There are couple of ways but I don't want to use javascript and wanted to use simple 'title' attribute. It took couple of hours to get the working code supporting sortable columns. I thought it will be very useful for others. Here's the code and logic behind it.

Multiple Column Dropdownlist for the ASP.NET DataGrid

  
Based on my previous control "Multiple Column DropDownList for ASP.NET", I received many emails asking for the same control to be used in the DataGrid for web applications. Here we go.. This control can be used as the regular MS DropDownList in the DataGrid and also as a regular dropdownlist. It has all the properties, like DataTextField, DataValueField, DataSource, SelectedIndex etc. The download file contains the samples both in VB.NET and C#. In this sample, I have used the Northwind database of SQL Server.

Alternating row background for first column of a report

  

is there a way to make the first column of a report use the an alternating background color? 

I have the background color set for the entire row however it does not work for the first column if the color is set to an expression.  If I just set it to a color i.e. red it works just fine. If I insert a dummy column to the left it works just fine (but the dummy column does not work, presumably because it is first??).

In the file below only the textbox for part number does not show an alternating background.  All others work correctly.

<TableRows>
            <TableRow>
              <TableCells>
                <TableCell>
                  <ReportItems>
                    <Textbox Name="PartNumber">
                      <rd:DefaultName>PartNumber</rd:DefaultName>
 

Categories: 
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend