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.

Filter by
Sorted by
Tagged with
5
votes
2answers
233 views

Why can't I persist binary(4) computed column?

I'm playing with storing and indexing IP addresses. I'm starting with a simple, stupid table: CREATE TABLE [dbo].[IP_addresses]( [IP_as_text] [char](16) NOT NULL, [IP] AS ([dbo].[...
0
votes
1answer
27 views

Is it bad practice to calculate within the insert query?

I am trying to do a web application for time sheets. I have three options to calculate the value of hours (AFAIK): In the form In the backend In the query Is it smart or bad practice to do this ...
1
vote
3answers
71 views

How to calculate end_date from start_date and duration in SQL Server Query

I have a table with 4 fields .Start-date, Duration, End_date, Actual_date. I am providing the start_date and duration as the input to calculate end-date. While calculating the end_date the ...
1
vote
1answer
20 views

MySQL: Unable To Create Generated Column With Expression That Uses DATE_ADD() Function

There might be a really obvious solution to my question, but I am stuck and did not manage find out what I am doing wrong, so you guys might tell me. I am not able to create a nullable stored column ...
3
votes
2answers
281 views

Why is the result of a totally deterministic and all-encompassing “case” in select query in view nullable?

I have a table, with Id as the primary key. create table Anything ( Id bigint not null primary key identity(1, 1) ) When I look at this table in Object Explorer, I see this image of course: As ...
0
votes
1answer
43 views

Foreign key referencing table with composite primary key when I only need 1 column association

So lets say we have 2 tables on SQL Server database: CREATE TABLE VersionedData ( Id INT NOT NULL, Version INT NOT NULL CONSTRAINT [PK_VerData] PRIMARY KEY (Id, Version) ) And: ...
1
vote
1answer
71 views

Refreshing a stored generated column in Postgres

How in PostgreSQL can I refresh the values of a stored generated column. Example CREATE TABLE people ( first_name TEXT, last_name TEXT ); CREATE OR REPLACE FUNCTION name(people) RETURNS ...
0
votes
1answer
57 views

Calculating the value of a column based on consecutive values of rows in another column

It is straight forward to compute column value based on values of other columns, e.g. speed/time AS acceleration where speed and time are columns. Is there a way to compute column's value based on ...
2
votes
1answer
476 views

UPDATE failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER' in Agent job

In brief, I added a computed column to a table and then Agent jobs began failing and reporting the error: UPDATE failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'...
0
votes
1answer
278 views

AutoIncrement non-primary key or Make unique composite key?

I have a db with a table that has a rollover value such as OrderNumber and another column is year. This is the primary key - a composite key - but there are other tables with which I need to use a ...
-1
votes
1answer
87 views

Can I use built-in (deterministic) SQL functions in a Computed Column or do I need to create my own function as a passthrough to leverage them? [closed]

I'm trying to create a (persisted) computed column on a table that is essentially:YEAR(DateColumn). When I try to directly call the YEAR() function inside the Formula of the Computed Column ...
-2
votes
1answer
17 views

Subtracting the previous qty to the current quantities

I have a table as: Here I want to subtract the required Qty for a part from On Hand and then the result from the required for next sequence Hoping to get this as a final result:
29
votes
4answers
3k views

Does SQL Server Only Perform Calculations In A SELECT List Once?

Take the following example: SELECT <CalculationA> As ColA, <CalculationB> As ColB, <CalculationA> + <CalculationB> As ColC FROM TableA Would CalculationA and ...
0
votes
1answer
57 views

Computed Columns - SQL Server Function

I'm looking to improve overall SQL performance of a customers application. On each table of the application, there is a computed column, that uses GETUTCDATE and a combination of three columns on that ...
2
votes
1answer
49 views

Automatic seek on an index on a calculated column

I have a table with a varchar field than I need to be unique, but it is too long for the nonclustered index that the uniqueness constraint requires, so I have added a computed column (either using ...
0
votes
1answer
84 views

Computed Column Operand type clash on Date Column When Checking For Null

This error is generated when trying to create a table with a computed column from a Date field. Operand type clash: int is incompatible with date If I comment out the computed column it works. [...
3
votes
2answers
1k views

How to change definition of a Postgres 12 GENERATED column

How to change definition of a Postgres 12 GENERATED column? CREATE TABLE test ( id serial PRIMARY KEY, val_a int4 NOT NULL DEFAULT 0, val_b int4 NOT NULL DEFAULT 0, val_sum int4 ...
-4
votes
2answers
58 views

Joining two tables together based on common identifier [closed]

I have two tables. Table 1 : 'DQ' is the golden source of information, Table 2 : 'PS' is whats currently in the DB. What I would like to do is compare the DQ table to whats inthe PS Table and ...
0
votes
2answers
49 views

Create integer id columns from existing string columns (integer coding?)

I have an PostgreSQL server with an existing table which has two fixed-width-non-unique-string (variable size) columns such as this: | ID_STRING_A | ID_STRING_B | | 'AAAA' | 'BBBB' | | ...
1
vote
1answer
1k views

Insert into table with column same values as auto increment column ( computed column )

Say I have a table with "column1" (Primary Key, Auto Increment) and "column2". How can insert into the table with column2 will have the same values as column1 which is auto increment column?
-1
votes
2answers
398 views

Create an unique integer id column for an existing string column

I have an PostgreSQL server with an existing table which has an fixed-width-non-unique-string column such as this: | ID_STRING | | 'ABCDEFG' | | 'HIJKLMN' | Now I want to compute integer ids for ...
0
votes
1answer
150 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
25 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
3answers
5k 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
189 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
3k 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
348 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 ...
10
votes
2answers
7k 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
135 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
177 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 [...
2
votes
2answers
3k 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 ...
3
votes
1answer
5k 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 ...
7
votes
2answers
276 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
272 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 ...
7
votes
1answer
413 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
329 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 ...
5
votes
5answers
4k 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
907 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
1k 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'...
7
votes
3answers
5k 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
381 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 ...
-4
votes
1answer
122 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
245 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
2k 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
5k 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
602 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 ...
3
votes
1answer
2k 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 ...
29
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
430 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,...
3
votes
1answer
426 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 ...