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

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

Index on persisted computed column not functioning as expected

Posted By:      Posted Date: May 22, 2011    Points: 0   Category :

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.


 title VARCHAR(250) NOT NULL,
 isActive BIT NOT NULL,

View Complete Post

More Related Resource Links

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

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



I have a table which contains a computed column.

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

Filter by Computed Column



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)


SELECT column1, column2, column3


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


how to create index for a column


Hi Friends,

                       I need to create the index for a column and accessing that in sqlserver 2005. Please any one help me.


Computed Column

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

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

What is the meaning of Ascending\descending sort order of a column within an index

When I add a new index I can also specify the "sort order" as ascending or descending. What does it mean? does it affect performance? does it mean that the binary search tree will be set in a certain way?

Computed Columns - Index and Performance

This article summarized all the articles related to computed columns.

In this article today, we will see how we can get better performance using computed column. Here are few steps which we are going to follow. First, we will create regular table and populate with some data. Once data is populated we will try to query the data

How to create nonclustered index which uses function on column?

Hi, Is there any way I can perform following. The ID column is of varchar type.   CREATE NONCLUSTERED INDEX idxID ON Table_1(CAST(ID AS BIGINT) );   Regards, Jigs

gridview column index

how can i change column index of girdview ,i have to show button at right hand side

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'.

Document Library version restore not behaving as expected on the "Name" column (file name)


After a rollback to a previous version, the value of the column "Name" in a document library is not restored. All other columns are restored properly. It seems that the column "Name" (file name) is excluded from the process of versioning.

I didn't find information on this behaviour on the internet. Any suggestions ?


How do i iterate through a table that does not have an index column?


hi friends,

I have the following table variable that i store rows extracted from an xml document;

declare @tbl_ans table ( AnsText nvarchar(max), IsCorr bit)

this cannothave an index column.

assume it has 25 rows, i need insert each row to a table  that has three columns

That Answers table has following structure:

a_idx int,

a_text nvarchar(max),

is_active int,

upt_date datetime

The script should iterate through the @tbl_ans_table table and insert each to the answers table then i should fetch each a_idx in each iteration and insert it to another. But the problem is how itereate through @tbl_ans_table without an index?



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