Stack Overflow
Stack Overflow

Questions tagged [persisted-column]

19 questions
6
votes
2 answers

Why is my CASE expression non-deterministic?

I am trying to create a persisted computed column using CASE expression: ALTER TABLE dbo.Calendar ADD PreviousDate AS case WHEN [Date]>'20100101' THEN [Date] ELSE NULL END PERSISTED MSDN clearly says that CASE is deterministic,…
A-K
  • 16,804
  • 8
  • 54
  • 74
2
votes
2 answers

user defined function (TSQL) non deterministic and computed persisted column

I have a table, which among it's columns there are two columns of type INT and they are called: ExpirationMonth and ExpirationYear I want to add a new column to this table, whose value will be calculated based on the values of ExpirationMonth and…
Christos
  • 53,228
  • 8
  • 76
  • 108
2
votes
1 answer

Can a PERSISTED column reference an existing persisted field?

Can persisted column reference another persisted column and are there any rules to this such as do they calculate the persisted fields sequentially or at least calculate the ones that are referenced by other columns first?
aSystemOverload
  • 2,994
  • 18
  • 49
  • 73
1
vote
0 answers

SQLSVR - not able to create index with computed GETDATE column

What I want to achieve: To create an index with an existing date column data converted into UTC timing (Query is written below) Issue: There's a column in table with local server date values, I need to convert them into UTC date value and then…
Veer
  • 23
  • 2
1
vote
2 answers

SQL Server 2008 - Can you index a persisted column

Is it possible to index a persisted field? When trying to change a primary key to a persisted field or add a primary key to a field I've made persisted field I get the following: Cannot define PRIMARY KEY constraint on nullable column in table …
aSystemOverload
  • 2,994
  • 18
  • 49
  • 73
1
vote
1 answer

Nested Logic in Persisted Columns Formula

I'm new to and having trouble with Formulae Syntax in Persisted Columns. A - I need case when (CustomerAccountID IS NULL and MissCustNameMatched=0) OR errLicensingProgMissing=1 OR errLicensingSubMissing=1 then (1) else (0) end This won't validate…
aSystemOverload
  • 2,994
  • 18
  • 49
  • 73
0
votes
1 answer

SQL select and alter table in same statement

I have a table where I would like to select certain columns and then create transformed columns based on that selection. Due to security reasons, I'm not able to create a new table and thought there may be a way to SELECT and ALTER in the same…
paranormaldist
  • 489
  • 5
  • 16
0
votes
1 answer

Bad design to compare to computed columns?

Using SQL Server I have a table with a computed column. That column concatenates 60 columns: CREATE TABLE foo ( Id INT NOT NULL, PartNumber NVARCHAR(100), field_1 INT NULL, field_2 INT NULL, -- and so forth field_60 INT…
Developer Webs
  • 983
  • 9
  • 29
0
votes
1 answer

How does one use Variables in a Persisted Computed Column?

I have a persisted computed column I am using to hold a DATETIME2 calculated from a FLOAT time column ([Potentially Bad Time]). I am handling underflow and overflow of all of the components of the time using DATEADD but currently am having to keep…
Matt Arnold
  • 668
  • 2
  • 8
  • 21
0
votes
1 answer

SQL Server - DATE conversion from DATETIME is non-deterministic but only in user-defined function

Why is this type conversion rejected as non-deterministic for a PERSISTED computed column in return tables of user-defined functions (UDF) in SQL Server? CREATE FUNCTION MyTimeIntervalFunction(@Param1 INT) RETURNS @MyTimeInterval TABLE ( …
Elaskanator
  • 1,135
  • 10
  • 28
0
votes
3 answers

Using multiple rows from multiple tables for Persisted Computed Column with a Scalar UDF

I'm trying to create a new field in the Order Transactions Table as a Persisted computed column using a Scalar UDF value as the value for the field. I understand that a requirement for a Persisted column is that the value is deterministic, meaning…
Riku Das
  • 91
  • 1
  • 14
0
votes
1 answer

How would I add the PERSIST function to a ranking in SQL Server?

How would I go about adding the PERSIST function to a ranking? I plan on creating a view from this code and will be frequently running subqueries on it. So of course I don't want the ranking number to change based off of whatever subquery I run. I…
Joey Weinberg
  • 11
  • 2
0
votes
1 answer

Persisted Computed Column to calculate area?

I have a table (rugs) that has columns for length and width (both 2 place decimals). I'm trying to create a computed column named area which simply multiplies the length by width to generate the area but hitting syntax errors when running the…
Ryan Malone
  • 7
  • 6
0
votes
1 answer

Rails - check if a user is persisted by checking if an email address inserted in another form is now

I have models called User and Invite in my Rails 4 app. I allow users to invite others to their project teams. My user model has an attribute called :email. I have an invite model for this purpose, it has the following attributes: create_table…
Mel
  • 2,481
  • 26
  • 113
  • 273
0
votes
1 answer

SQL Server: One large persisted computed column for Fulltext Indexing

It appears to me as the easiest, most straightforward solution, but please correct me if I'm wrong. Instead of having a fulltext index on all individual columns of a table, isn't it better to just generate one single wide computed column and run the…
Alex
  • 75,813
  • 86
  • 255
  • 348
1
2