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
2
votes
1answer
813 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 ...
2
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? ...
2
votes
4answers
577 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
944 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 ...
3
votes
3answers
788 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
81 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
937 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
68 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
730 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
479 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 ...
2
votes
1answer
938 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 ...
29
votes
3answers
7k 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
116 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) ) ...
5
votes
4answers
793 views

Implicit conversion warning despite persisted computed column vs. performance hit from UDF in persisted computed column definition

I have a need for a computed column to resolve an implicit conversion problem. I have a column that is declared as a VARCHAR that should only ever store integers, but is occasionally populated in ...
14
votes
2answers
751 views

Computed Column Index Not Used

I want to have a fast lookup based on if two columns are equal. I tried to use a computed column with an index, but SQL Server doesn't seem to use it. If I just use a statically populated bit column ...
3
votes
2answers
159 views

Can I create a computed column that requires input to select?

I have a scenario in a very old and very large application, where I have a table representing a type of resource: CREATE TABLE resource (resource_id INT, name NVARCHAR(4000)) This table is selected ...
3
votes
2answers
9k views

Computed/Generated columns in MySQL 5

How do I create a table with a formula column in MySQL version 5? For example: I have a table named Product, which has 5 columns: id int, amount int, sold_out int, faulty int, remain_amount is (...
1
vote
1answer
415 views

storing as smallint, querying as int

A multi-billion-row fact table in our database has 10 measures stored as int columns. The value ranges for some of these columns won't ever be above the +/-32K range of a smallint. To save I/O, we're ...
18
votes
5answers
3k views

Unable to create a Filtered Index on a Computed Column

In a previous question of mine, Is it a good idea to disable lock escalation while adding new calculated columns to a table?, I am creating a computed column: ALTER TABLE dbo.tblBGiftVoucherItem ADD ...
0
votes
1answer
721 views

SSAS Calculated Field - rolling sum, for a year of days

I have a fact table storing employee occurrences incurred day over day. I need to have a running sum over the past year of an employee's life. I had, on first pass, done this on ETL, making this "...
15
votes
2answers
7k views

Why is a NOT NULL computed column considered nullable in a view?

I have a table: CREATE TABLE [dbo].[Realty]( [Id] [int] IDENTITY(1,1) NOT NULL, [RankingBonus] [int] NOT NULL, [Ranking] AS ([Id]+[RankingBonus]) PERSISTED NOT NULL .... ) And a ...
3
votes
1answer
2k views

computed column as foreign key allow null

I have two tables. TABLE A: One of them stores some integer identificators, which are unique, but actually are structured entities. I will call them full_id. Actually its a two parted indetificator,...
1
vote
1answer
4k views

Aliases in calculations for MySQL

I am in the process to move a really small database from Access to MySQL and I have some queries to translate. I have this really simple query (in Access db): SELECT InvoiceNumber, Field1 + ...
15
votes
2answers
470 views

Index on a persisted computed column not seekable

I have table, called Address, that has a persisted computed column called Hashkey. The column is deterministic but not precise. It has a unique index on it that is not seekable. If I run this query, ...
0
votes
3answers
2k views

MySQL - How do I Calculate an Average Value Over a Period of Time When Only Value Changes are Recorded

I am tracking the sales price of various items, and I would like to calculate the average price of each item over a period of time. My simplified tables look like this: Items ------------------ |key ...
1
vote
0answers
107 views

MYSQL — Inventory View with calculated fields

Trying To create an inventory view. I have created this query using MS Access query builder since I am no pro at SQL and this is pretty convoluted. When translating this into MYSQL syntax I get an ...
2
votes
1answer
96 views

How can I add a numeric version_id column to an existing table?

I have a table in a postgres db with rows versioned with a timestamp that I'd like to add a numeric version_id column to. eg. existing: ID VERSION_DATE -- ----------------------- A 2015-02-05 21:...
1
vote
0answers
345 views

Should I use conventional or computed columns for trivial metrics in a fact table?

A fact table can often have measures which are basically trivial. For example Orders.NumOrders will probably be "1" for every real record (synthetic records representing "no order" may populate this ...
24
votes
3answers
3k views

Index on Persisted Computed column needs key lookup to get columns in the computed expression

I have a persisted computed column on a table which is simply made up concatenated columns, e.g. CREATE TABLE dbo.T ( ID INT IDENTITY(1, 1) NOT NULL CONSTRAINT PK_T_ID PRIMARY KEY, A ...

1 2