Computed column - Microsoft SQL Server

BCR

I created a index on a computed column. I did not see any improvement
in performance with a join to this column and also my inserts and
updates to this table are failing. Any ideas?

Chender

Jul 20 '05 #1

Subscribe Reply

2009

Dan Guzman

Regarding your failing inserts and updates, the likely cause is that one ore
more connection options are not set properly for maintaining a computed
column index. The error message will indicate which option(s) is
incompatible. The required options specified in the Books Online
<createdb.chm ::/cm_8_des_05_8os 3.htm> are:

SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET ARITHABORT ON
SET CONCAT_NULL_YIE LDS_NULL ON
SET QUOTED_IDENTIFI ER ON
SET NUMERIC_ROUNDAB ORT OFF

All but ARITHABORT are set automatically when connection via OLEDB or ODBC.

Please post your DDL, query and sample data for help with your performance
issue.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"BCR" <bc***@yahoo.co m> wrote in message
news:32******** *************** ***@posting.goo gle.com...

I created a index on a computed column. I did not see any improvement
in performance with a join to this column and also my inserts and
updates to this table are failing. Any ideas?

Chender

Jul 20 '05 #2

Erland Sommarskog

[posted and mailed, please reply in nes]

BCR (bc***@yahoo.co m) writes:

I created a index on a computed column. I did not see any improvement
in performance with a join to this column and also my inserts and
updates to this table are failing. Any ideas?

As Dan pointed out the update/inserts are failing because of the settings.
I like to add two things. The first is that the lack of performance is
very likely due to the same problems. If the proper settings are not in
effect, SQL Server will not consider the index.

The other is that the two settings ANSI_NULLS and QUOTED_IDENTIFI ER are
saved when you create a stored procedure, view, trigger or user-defined
functions. So that if even if these are on at run-time, they will not be
in effect if they were off when the procedure was created. Some tools,
Enterprise Manager and OSQL to be precise, have QUOTED_IDENTIFI ER off by
default.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Jul 20 '05 #3

Similar topics

7238

Computed columns

by: Paulo Andre Ortega Ribeiro | last post by:

I have a table with fields called fname (First Name) and lname (Last Name). I need the user´s email thai is compose from lname and fname: LOWER(LEFT (fname,1) + lname) Is there any difference between creatig this computed column ia a table or in a view in SQL Server 2000? I can do: 1. CREATE TABLE Users(

Microsoft SQL Server

11324

Bulk inserting into table with computed columns

by: tperovic | last post by:

Using SS2K, I'm getting the following error while bulk inserting: Column 'warranty_expiration_date' cannot be modified because it is a computed column. Here is my bulk insert statement: BULK INSERT dbo.TestData FROM 'TestData.dat' WITH (CHECK_CONSTRAINTS,

Microsoft SQL Server

3693

Computed Column calculations

by: DMAC | last post by:

If i want to split a computed column into two or more columns based on the the length (its a varchar) of the computed column, how often will sql server determine what the computed column is? roughly along the lines of select comp_col,'comp_col 2'= case when datalength(comp_col)<=100 then comp_col else left(comp_col,99) + '~' end, datalength(comp_col) from aTable

Microsoft SQL Server

1700

Computed Column

by: Jim Heavey | last post by:

Hello, I have created a computed column which concatenates a name and date. My problem is that if the 10 is not 10 characters, I get an extra character placed into the computed column. Here is the code that I have for the expersion of the computed column... dtCompetition.Columns.Expression= ("trim (CompetitionName) + ' ' + substring(convert (EventStartDate,System.String),1,10)");

C# / C Sharp

1978

Computed columns in datagrid

by: Raymond Du | last post by:

Hi, Can I have computed columns in a datagrid? If yes, how? TIA

ASP.NET

1931

Computed Columns in dataset always returning NULL

by: Dave | last post by:

I am relativly new to visual basic, so this may be a no- brainer. I am attempting to use a computed column in a VB dataset defined as followe: ' 'dcCost ' Me.dcCost.ColumnName = "Cost"

Visual Basic .NET

2079

Index Computed Column?

by: jim_geissman | last post by:

Can I create an index on a variation of a column that isn't actually in the table? I have a ParcelNumber column, with values like 123 AB-670 12345ABC 000-00-040 12-345-67 AP34567890

Microsoft SQL Server

19379

Computed Column Specification

by: Dot Net Daddy | last post by:

Hello, I want to assign a column a computed value, which is the multiplication of a value from the table within and a value from another table. How can I do that?

Microsoft SQL Server

5793

Computed Columns

by: Aamir Mahmood | last post by:

Hi All I have DataTable object. Is there a way that I can know which fields (columns) in the table are computed. Apparantly the DataTable.Columns returns all columns both computed and other. Any help would be appreciated.

C# / C Sharp

9564

What is ONU?

by: marktang | last post by:

ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...

General

10148

Problem With Comparison Operator <=> in G++

by: Oralloy | last post by:

Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...

C / C++

10002

Maximizing Business Potential: The Nexus of Website Design and Digital Marketing

by: jinu1996 | last post by:

In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...

Online Marketing

9938

The easy way to turn off automatic updates for Windows 10/11

by: Hystou | last post by:

Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...

Windows Server

9823

Discussion: How does Zigbee compare with other wireless protocols in smart home applications?

by: tracyyun | last post by:

Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...

General

8822

AI Job Threat for Devs

by: agi2029 | last post by:

Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...

Career Advice

5406

Windows Forms - .Net 8.0

by: adsilva | last post by:

A Windows Forms form does not have the event Unload, like VB6. What one acts like?

Visual Basic .NET

3528

How to add payments to a PHP MySQL app.

by: muto222 | last post by:

How can i add a mobile payment intergratation into php mysql website.

PHP

2794

Comprehensive Guide to Website Development in Toronto: Expert Insights from BSMN Consultancy

by: bsmnconsultancy | last post by:

In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

General