The situation: we have a large database with a number of denormalized tables. We frequently have to resummarize the data to keep the summary tables in synch. We've talked on and off about using computed columns to keep the data fresh. We've also talked about triggers, but that's a separate discussion. In our summary tables, we denormali...
I have a select statement with calculated columns and I would like to use the value of one calculated column in another. Is this possible? Here is a contrived example to show what I am trying to do. SELECT [calcval1] = CASE Statement, [calcval2] = [calcval1] * .25 ...
I have a nullable DateTime column in my SQL Server 2005 table called DateTimeDeleted. I'd like to have a BIT type computed column that is 1 if DateTimeDeleted is not null, otherwise 0. However, I can't seem to get the syntax for the formula correct. I've tried: (TsDeleted IS NULL) = 0 but it gives me a syntax error. Help! :) ...
If you set up a table's column to be a computed column whose Formula calls a Function, it becomes a pain to change that underlying Function. With every change, you have to find every single column whose Formula that references the Function, remove the reference, save the Table, alter the Function, add everything back, and save again. Ev...
Here's my simplified table (SQL Server 2005): table1: col1 int, col2 int, col3 cast(col1/col2 as int) [computed column] for some reason the above doesn't work. i just want to save a WHOLE part of col1/col2, how do i do that? example: col1 = 5, col2 = 3 ... col3 should be 1 ...
I have some computed columns in a table and need to know if I should set Is Persisted to true. What are the advantages? Are there any disadvantages? What does 'Is Persisted' mean? ...
I am trying to set this col but I get an error: FirstName + (CASE WHEN LEN(FirstName + LastName) > 0 THEN ' ' ELSE '') + LastName ...
Hi everybody I'm working with 2 related tables in a Microsoft SQL Server 2008 environment which are connected via a GUID. In one table, the field has the type varchar(50), the other one is properly types as uniqueidentifier. This is obviously bad but I can't change this now because it's given by a legacy piece of software. The conversi...
I'm joining to a table dozens of different times, and every time, I join (or filter) based on the results of a SUBSTRING of one of the columns (it's a string, but left-padded with zeros, and I don't care about the last four digits). As a result, even though this column is indexed and my query would use the index, it does a table scan bec...
We have a SQL 2005/2008 database that has a table with a computed column. We're using the computed column as a discriminator in NHibernate so having it in the database is proving to be very useful. In order to gain the benefits of faster integration tests, I'd like to be able to run our integration tests against an in-memory database su...
I want to use a computed bit column that will be true if another column in the table is not null. What's the correct formula for this? HasLabel = computed column (bit) Label = varchar NULL The following formula does not validate - what am I missing? Formula for HasLabel = Label IS NOT NULL ...
Is it possible to edit computed-column values? I have a computed-col in my table, sometimes, I need to enter an individual value that is not the computed value. Is there a way to store it in the computed col, or I have to make an additional col? ...
Hi all! I want to be able to store a decimal value in one column, and in the other column to store an int that represents the option (will explain): should be base -% should be base -absolute should be base +% should be base +absolute 1 & 2 is a discount 3 & 4 is an upcharge 1 & 3 reduces/raises the amount by percentage (i.e. amount *...
Hi. This seems like it should be simple but it's driving me up the wall. I have two columns - 'tx_date' and 'time' stored each as char(10). (bad database design I know, but wasn't my design) From a query I can convert them into a datetime just fine - "...convert(datetime,tx_date,time,11)..." (so tx_date "09/11/27" and time "07:12:...
Hello. I have a computed column called Cost that returns money. I want to have another column that returns (Cost * 2), but it doesn't allow me. ...
I try to have this computed column: CREATE TABLE dbo.Item ( ItemId int NOT NULL IDENTITY (1, 1), SpecialItemId int NULL, --I tried this IsSpecialItem AS ISNULL(SpecialItemId, 0) > 0, --I tried this IsSpecialItem AS SpecialItemId IS NOT NULL --Both don't work ) ON [PRIMARY] ...
I use the following statement to create a percentage calculation in a view: round(((Surveys.RejectedAsAdvertising / Surveys.Responses) * 100),0) AS PercentageAdvertising Basically, I divide the number of responses to a certain question (in this case, is something considered to be advertising) by the total number of responses. In the e...
Hi all, I have the following query, which uses a CASE statement. Is there anyway to add into the where clause WHERE IsBusinessDayFinal = 0? without using a temporary table? thanks so much! SELECT ac.DateTimeValue, CASE WHEN pc.IsBusinessDay IS NOT NULL THEN pc.IsBusinessDay ELSE ac.IsBusinessDay END AS IsB...
Hello! I have some computed-columns of two types: Computed columns based on columns in current table (i.e. Price * Tax) Computed columns based on other columnes (i.e. Price * fn_GetTax(OrderId)) Would you say it's better to use these columns in CLR on the client only and then save from the server the calculation and transferring per...
So far I've been using ISNULL(dbo.fn_GetPrice(ItemId), 0) to make it not nullable (rather call it default-valued, but whatever). Is this the right way? ...