Stack Exchange Network

Stack Exchange network consists of 175 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

Questions tagged [computed-column]

Computed columns are columns that represent a calculation or operation performed on other columns contained in the given table. Known as generated or virtual columns in some products.

0
votes
1answer
32 views

HashBytes Computed Column: Exclude Column

We want to create Hashbytes computed column Non-Persisted. Table has twenty columns, and would like to exclude IdentityId column. How could I do this in computed column? Is there a SQL library ...
0
votes
0answers
23 views

Why sum with previous and current column do't working

I want to update my specific row with that row previous value, after searching i found some post about this but those solution not working with me. So what is wrong with my query or how can i ...
0
votes
2answers
643 views

Sql Max value from multiple columns in Computed Column

In this article solution 1, it talks about finding the maximum value from many columns. I would like to conduct this in a computed/persisted column. How would I do this? https://www.mssqltips.com/...
1
vote
1answer
45 views

Is any caching mechamism used for non-persisted computed columns?

Does SQL Server cache the result of a non-persisted computed column so it can be reused without incurring cost of recalculation? Additional Context I've always given the advice that if using computed ...
2
votes
1answer
462 views

How to create a Hash Computed Column for Many Columns?

Does anyone know how to create a Hash Computed Column Persisted? I keep receiving an error below. Otherwise, I will have to utilize an update or trigger statement everytime. I know how to conduct for ...
3
votes
1answer
99 views

Datatype for a Persisted Computed Column? Should I Convert/Cast When Creating It?

What is the Datatype for a Persisted Computed Column? (a) Does SQL Server automatically infer it? (b) Also is better to cast it as a datatype, so SQL does not spend time to find what data type it ...
7
votes
2answers
2k views

Computed column cannot be persisted because the column is non-deterministic

I know this is not the first time this type of question has been asked. But why in the following scenario is the persisted computed column being created "non-deterministic". The answer should always ...
6
votes
1answer
74 views

Table with persisted computed CLR originated data marked with has_unchecked_assembly_data = 1, but DBCC CHECKTABLE does not unset this same flag

We have an OrderLines table with columns: Quantity int not null QtyCancelled int not null QtyBackorder int not null QtyPicking int not null QtyPacking int not null QtyShiped int not null with ...
2
votes
1answer
75 views

SQL Server FullText search on non-persisted columns

Let's say I have a table with 3 columns: Id, ColA and ColB. Then I created a FullText index: CREATE FULLTEXT INDEX ON [Table] ( ColA LANGUAGE 1033, ColB LANGUAGE 1033 ) KEY INDEX [...
0
votes
2answers
526 views

Is possible to reuse a already calculatesd column into a query in MySQL?

I have a query with complicated column, and I would like to use this column result in another column, for instance: SELECT ( /* Complex query */ ) as myValue, if( myValue > 10, "OK" , "" ) from ...
2
votes
1answer
2k views

Mysql Pivot rows into dynamic columns with unknown number of columns

I have an output of a query as follows: bid | code | amount | ----------------------------- 2915 | BF | -10700.00 | 2915 | YQ | -300.00 | 2915 | YR | 0.00 | 2915 | YM ...
6
votes
2answers
97 views

Deriving a computed column based on several nullable columns

I'm a bit of an SQL novice. I've been trying to create an auto generated column called TAG from 5 other columns, some of which can retain NULL marks. The syntax used to create the auto generated ...
3
votes
2answers
114 views

Is there a way to avoid having chained/nested “replace()” items when using an ISO 8601 timezone offset value in a generated column?

I am using MySQL 5.7 and have a table that has the following columns that are being used to store some data that is source from an Apache common log formatted access log; details extracted from a ...
6
votes
1answer
278 views

Why does adding a computed column prevent predicate pushdown?

I have a weird situation that I don't quite understand. I have a table like this kinda: CREATE TABLE dbo.cc_demo ( id INT IDENTITY PRIMARY KEY, up_action INT, ...
3
votes
3answers
259 views

create a visible uniquifier column for a nonunique clustered index

I have a table of events, clustered index is the creation date/time. Normally there should never be two events happening in the same millisecond, but freak occurences happen, so the column is not ...
3
votes
5answers
2k views

Creating a Persisted Computed Column with a function

I am working with the programmers on a database solution. They want to add a computed column to mimic the old keys for the older queries, procedures, and systems and index it. The new keys will be ...
4
votes
1answer
229 views

Type conversion in expression may affect “CardinalityEstimate” - on a computed column?

I had a look at this question: Type conversion in expression may affect “CardinalityEstimate” in query plan choice? But that was related to collation and not with computed columns. I have the ...
1
vote
1answer
217 views

Is there any approach for encrypting mysql column by key?

I have a scenario for encrypting user information in mysql db. Is there any approach to encrypting column by key. This key information will be there in application configuration file. This key we won'...
3
votes
3answers
1k views

Bad Practice to Store Calculated Data in Database? [closed]

Is it bad practice to store calculated data in each row, or is it better to calculate at the application layer with every read from the database. Storing in the database avoids the need to calculate ...
4
votes
2answers
370 views

Is there any need to persist a (precise) computed search column that I've indexed?

I have a number of columns that store data that may contain some extra characters for formatting/grouping. Think white space and dashes in data like phone numbers and license plate numbers. When ...
-5
votes
1answer
101 views

How do I ensure a relationship is commutative and unique? [closed]

In PostgreSQL, we can ensure the relations stored inside a table a commutative-ly (or bidirectional etc) unique. For instance, CREATE TABLE foo ( a int, b int, UNIQUE ( greatest(a,b), least(a,b)...
3
votes
1answer
211 views

Add computed column with “IN” syntax

I have a query in SQL Server 2008R2 that I want to run against a computed column that in turn I am going to index - in order to speed things up. The column is of type int right now. I cannot get the ...
1
vote
0answers
701 views

Why Turning ANSI_WARNINGS Off Causes Error Updating Col Used as a Source of a Computed Col?

I have a stored procedure that updates the source column used in a computed column. That stored procedure has SET ANSI_WARNINGS OFF;. When the procedure executes, and tries to update the source ...
5
votes
2answers
2k views

Referencing another table from a computed column

I am working on a project where many of the entities in the data model do not have a name column, but instead the "name" needs to be constructed from a concatenation of multiple columns. Initially I ...
5
votes
4answers
409 views

Interpolate dates along a line

I have a road_condition table in Oracle 12c: create table road_condition ( cond_id number(5,0), road_id number(5,0), cond_date date, condition number(5,0) ); insert into ...
2
votes
1answer
800 views

Postgres 9.6.5 - Can I use a query to calculate a value from two columns and insert the calculated value in a new column in the same table?

I am running the following version of Postgres on CentOS 7: PostgreSQL 9.6.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-11), 64-bit I am new to database operations ...
28
votes
2answers
2k views

Is there a way to prevent Scalar UDFs in computed columns from inhibiting parallelism?

Much has been written about the perils of Scalar UDFs in SQL Server. A casual search will return oodles of results. There are some places where a Scalar UDF is the only option, though. As an ...
1
vote
2answers
350 views

1 minus percentage returns negative number

I have a sidewalk_inspection table: create table user.sidewalk_insp ( insp_id int, deficiency_length number(10,2), sidewalk_length number(10,2) ); insert into user.sidewalk_insp (insp_id,...
0
votes
1answer
83 views

Creating a query with a computed field in FileMaker

I am trying to evaluate FileMaker. So far it's pretty confusing, and I flatter myself that I have a fair bit of experience with databases. But anyway, here's what I'm trying to do: I have a table ...
3
votes
1answer
167 views

How to SUM by date range within a view

I have a view in which I need to Group By or SUM OVER Partition By year and quarter, but the year and quarter will be computed within the view. I would like to keep it all within a view instead of ...
9
votes
5answers
1k views

Alternative to Self Join

I have asked a question here: https://stackoverflow.com/questions/43807566/how-to-divide-two-values-from-the-same-column-but-at-different-rows about dividing values from the same table, at the same ...
-1
votes
1answer
728 views

Use An Aggregate With Computed Column

I am wanting to alter my table to have a column that is the SUM() of two other fields. I have this syntax ALTER TABLE SalesData ADD AC AS SUM(COALESCE(val1,0)+COALESCE(val2,0)) However it produces ...
0
votes
2answers
478 views

Percent of total: Is there a more efficient way than using a subselect?

I have an ASSET_SUMMARY_VW view: +-----------------------+---------------+ | ASSET_TYPE | COST | +-----------------------+---------------+ | TRANSPORTATION | 1,000,000,000 |...
1
vote
1answer
2k views

computed columns vs views [closed]

I'm trying to think about the best way to do this. If i have a table like this in sql server: StudentPointsTable StudentID TestDate Points I need to have Averages and standard deviation calculated ...
15
votes
3answers
7k views

PostgreSQL: Generated Columns

Does PostgreSQL support generated columns? Also know as virtual columns. I am not talking about IDENTITY columns. I can’t find any information on this remarkable feature but I know that it is ...
10
votes
3answers
1k views

Creating Non-Clustered Index on Non-Persisted Computed Column SQL Server

I am struggling to find any documentation on how SQL Server actually stores a non-persisted computed column. Take the following example: --SCHEMA CREATE TABLE dbo.Invoice ( InvoiceID INT ...
3
votes
1answer
683 views

Calculated Field Updating Every Row

I added a calculated field to my table, and only want to update NULL rows (or "new" rows). Something is going awry, and every row in my table is being updated with the current DateTime when 1 row is ...
3
votes
1answer
39 views

Create a function column that is the result of OR between 3 columns

I have a table with 3 columns (A,B,C). In each row, only one of the columns is not null. How do I create a function column or a computed column that will consist from all the non null values? ...
3
votes
4answers
434 views

Computed field when NULL

I have two fields in my table : A int, B int. I want to add two computed fields (CF1 & CF2) that uses these two fields A & B. CF1 = (case when [A]>[B] then (CF1=3) else case when [A]<[B]...
3
votes
0answers
702 views

Does PostgreSQL support `unnest` as a column?

Does PostgreSQL document and support this behavior of the SRF unnest() as a computer/virtual column, SELECT unnest(array_agg(id)) AS id FROM foo GROUP BY CUBE (a,b,c,d) HAVING count(*) > 1 If ...
4
votes
3answers
667 views

computed column making the select query to run slow [closed]

We are creating indexes, as per the requirements, on one server and copying all the indexes to the other servers. All servers have similar objects & schema but different resource configuration ...
1
vote
1answer
78 views

Unexpected Indexing performance (Everything's equal)

I wanted to see the effects of having indexes on calculated columns, so I created a table like so: CREATE TABLE [Domain\UserName].[CompColIndexing]( [a] [int] NOT NULL, [...
10
votes
1answer
738 views

Changing primary key from IDENTITY to being persisted Computed column using COALESCE

In an attempt to decouple an application from our monolithic database, we've tried to change INT IDENTITY columns of various tables to be a PERSISTED computed column that uses COALESCE. Basically, we ...
3
votes
0answers
63 views

OPTION (RECOMPILE) is Always Faster; Why? [duplicate]

Despite having read botht hese 2 questions about procedure recompile below Stored Procedure With Recompile OPTION (RECOMPILE) is Always Faster; Why? I am still having a problem - my query is ...
0
votes
2answers
499 views

Rank rows using weighted average with multiple sub queries

So what I want to do, is to rank students based on their weighted average which will be calculated by separating the grades based on the type (whether "exam" or "not exam). The average for both will ...
16
votes
2answers
411 views

Is it legal for SQL Server to fill PERSISTED columns with data that does not match the definition?

I'm following up on this question about strange values in a PERSISTED computed column. The answer there makes a few guesses about how this behavior came to be. I'm asking the following: Is this not ...
1
vote
1answer
795 views

Use a scalar hashing function in a computed column - non-deterministic?

I have a scalar function select [dbo].[fn_get_hash_fields] ('blah','dbo') to which is passed a table and a schema and which returns an expression representing a concatenation of all column values in ...
27
votes
3answers
6k views

When are computed columns computed?

When are the values for computed columns determined? When the value is retrieved? When the value is changed? Some other time? I'm guessing this is a novice question since I'm not finding anything in ...
1
vote
1answer
100 views

How do I calculate a field based on multiple non-numeric records

I have a several tables that I have joined together to get raw data. Some information about my query in the most basic form: Table1 has a column [FacilityID] that is a unique identifier in that ...
8
votes
3answers
3k views

Avoiding a divide by zero error in a computed column

How do you avoid divide by zero error in the below table? CREATE TABLE [dbo].[TblDivision] ( [Numerator] int NOT NULL, [Denominator] int NOT NULL, [Result] AS (Numerator/ Denominator) ) ...