I am working with data that has a lot of records that get updated and inserted frequently, and to avoid having to create formulas through code all over the place, I am experimenting with computed column formulas. I have a question though..
It works well for any addition or subtraction (columnA+columnB), however, when I try to use division (columnA/columnB), it only returns integers, no decimals. I would like to have decimals, particularly with a specific scale and precision and I would really like to attempt this without any coding. Any suggestions?
Is it possible to get a Computed column in one table to carry out a SELECT & SUM from a column in another table? I have tried a SELECT / FROM / WHERE construct but SQL complains about that. Regards Clive
I have a table with fields called fname (First Name) and lname (Last Name). I need the userīs email thai is compose from lname and fname: LOWER(LEFT (fname,1) + lname)
Is there any difference between creatig this computed column ia a table or in a view in SQL Server 2000?
Dear all, Pls help me with this I have 2 tables Trip (TripID, Duration) Reserve(ReserveID, TripID, StartDay, EndDate) in which EndDate = StartDay + Trip.Duration How can I do this?
I have a table with fields called fname (First Name) and lname (LastName). I need the userīs email thai is compose from lname and fname:LOWER(LEFT (fname,1) + lname)Is there any difference between creatig this computed column ia a tableor in a view in SQL Server 2000?I can do:1. CREATE TABLE Users(fname varchar(20),lname varchar(20),email as LOWER(LEFT (fname,1) + lname) )Or2. CREATE TABLE Users (fname varchar(20),lname varchar(20))CREATE VIEW Vw_users (fname, Lname ,email)ASSELECT fname, Lname ,LOWER(LEFT (fname,1) + lname) )Is one of them is better?Paulo*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
basically col3 is a computed column. Now when ever a row in col1 or col2 is updated the computed column will reflect the new value. how does this happen in the background. does this use row level triggers or what other mechanism is used to maintain col3 - computed column
What is the difference between a computed column and a UDF? Is a computed column the same as the "Formula" field under Design Table in Enterprise Manager? Also, what is the proper syntax for the Formula field? Can I use regular SQL on it or is there more to it?
Hi, I have a small problem with my database. I've got following situation: I have a computed column, which value is base on currency rate: rent * rate. Users have to have possibility to change currency rate easily (maybe another table or constant). Is there any way to create formula, which would compute value properly, via constant or something like this? Or the easiest workaround would be load data into dataset (I'm building asp.net application - database will be very small - couple of hundreds of records) and make calculations programmatically?
Hi, I have a table with 4 columns let us say A,B,C,D. column D is computed column with formula A + '-' + B Now, i want to add one more condition to the formula which looks like "A + '-' + B + '-' + C".
Please let me know how to do this using T-SQL as i cannot open the table in design mode in production server.
i have a table containing the column "current month" and "current day" as smallint which contains the number of months since 1900-01-01 and the day of this month. now i want to trnslate this column in a smalldatetime ( not datetime !) value using a computed column and then create an index on that column.
the formula should be: dateadd(d,[current day]-1, dateadd(m,[current month],convert(smalldatetime,'1900-01-01')) )
trying to create an index on this column results in an error message saying that the formula is nondeterministic or imprecise
removing the convert statement leaving only the date results in a column of type datetime and creating the index works fine
replacing convert(smalldatetime,'1900-01-01') with a column name which has the type smalldatetime also allows to create an index but thats not what i want to do.
it seems that sql2000 thinks a convert from a string to a date is nondeterministic. Is there any possibility to create a const of type smalldatetime without using convert?
Any idea?
(besides this, datediff(d,'yyyy-mm-dd',anydate) is nondeterministic but datediff(d,dateadd(d,0,'yyyy-mm-dd'),anydate) is deterministic. strange...)
I am having a problem with using UDF as part of a temp table computed column. Here's the sample code: IF EXISTS( SELECT 1 FROM information_schema.routines WHERE routine_name = 'fn_test') DROP FUNCTION dbo.fn_testGO CREATE FUNCTION dbo.fn_test( @x int, @y int) RETURNS INT AS BEGIN DECLARE @z INT SET @z = @x + @y RETURN @z END GO
CREATE TABLE #X ( x INT, y INT, z AS (dbo.fn_test(x,y)) ) I receive the following error:
Server: Msg 208, Level 16, State 1, Line 2 Invalid object name 'dbo.fn_test'.
I do not get this error if I use a regular table. HELP!
I have a table that I cannot allow a computed field to exist on (due to a 3rd party software), so I am thinking I could create a view with a computed field that is persistent, is that possible?
the syntax below will not work, I am not even sure if this is possible, but if it can work, that would be great.
I am wanting to get the sum of jetfoot1, 2 & 3 and have the total added up as "total"
create view ViewSumReport as select JETFOOT1,JETFOOT2,JETFOOT3,(JETFOOT1+JETFOOT2+JETFOOT3)as [total] persisted from dbo.fielddata GO
I have a checksum calculation as a persisted, indexed computed column on a temporary table that I used to compare against original records to detect changes.
It seems that the update/ insert statements in my procs get out of sync on larger tables (500,000 rows +) with the checksum calculations. The only thing I can think of is that the column calculations are performed asynchronously in relation to the updates/ inserts. This is a problem for me.
Is my assumption correct? If it is, how can I adjust for this, i.e., force the computations to be performed synchronously or wait for the computations to complete before running comparisons?
In SQL Sever, do the size of computed columns gets added to the totalsize of the tables? Does SQL server stores the actual values incomputed columns?Thanks_GJK
I need to create an function similar to the "MATCH" function in Excelthat evaluates a number within a set of numbers and returns whetherthere is a match. I have put the example of what I see in excel in thecheck column. The "0" answer in the result column is in the fourthaccount in the list. Somehow I need to loop through the accountscomparing the result to the total and indicate a match in the checkcolumn. It wouldn't even need to tell me the row number; it could be a0 or 1.account total result check123770266.84124.2112377026131.050 412377026164.38-33.33123770260131.051237702678.7152.3412377167-31.34221.891237716731.34159.211237716738.55152 51237716731.34159.211237716715238.5512377167490.91-300.36123771670190.55123771670190.5512377167-31.3443.341237716731.34-19.341237716738.55-26.551237716731.34-19.3412377167152-14012377167490.91-478.9112377167012123771670121237736347.058412377363131.05012377363-45.38176.4312377363-47.05178.11237736347.0484.0112377363-47.04178.091237736347.058412377363541.11-410.06123773630131.0512377363672.15-541.11237750737.64152.91
I have a checksum calculation as a persisted, indexed computed column on a temporary table that I used to compare against original records to detect changes.
It seems that the update/ insert statements in my procs get out of sync on larger tables (500,000 rows +) with the checksum calculations. The only thing I can think of is that the column calculations are performed asynchronously in relation to the updates/ inserts. This is a problem for me.
Is my assumption correct? If it is, how can I adjust for this, i.e., force the computations to be performed synchronously or wait for the computations to complete before running comparisons?
Hello, does anyone know a website, where I can read something about the syntax of Computed Columns? I don't know how to enter the following expression in the computed columns field of MS SQL Server:
Instead of using Full-Text indices, which I don't like to manage, we've tried to use seperate tables that contain recordID, the word, a count of the word in the parent field and computed column which is the CHECKSUM() of the word column. I indexed the checksum column with a clustered index.
Works great in Query Analyser. But when the ASP page calls it, I get this message:
Microsoft OLE DB Provider for SQL Server (0x80040E14) INSERT failed because the following SET options have incorrect settings: 'ARITHABORT'.
Same for updates and deletes. The question is how should these SET settings be done? Any ideas would be greatly welcomed.
Using SS2K, I'm getting the following error while bulk inserting:Column 'warranty_expiration_date' cannot be modified because it is acomputed column.Here is my bulk insert statement:BULK INSERT dbo.TestDataFROM 'TestData.dat'WITH (CHECK_CONSTRAINTS,FIELDTERMINATOR='|',MAXERRORS = 1,FORMATFILE='TestData.fmt')The computed column is not referenced in the format file and the data filedoes not contain the computed data.Thanks
I have a table with 3 computed columns, the first two reference a function for there value. The last computed column should be total of the other computed columns, however when trying to write the formula for this column SQL Server rejects it.
Am i correct in thinking it is not possible to reference other computed columns in a computed column's forumla.
My current work around is to call the functions again that the other computed columns use to generate the total, though this seems to me like a performance issue.
Case: Exporting Report to PDF/Printing/TIFF Report: Contains 1 table with 19 Columns. 1 column is static, the other 18 are visible at the users descretion. Report when printed/exported to pdf spans 2 pages naturally, 16 on the first page, 3 on the second, and the column widths have been adjusted to provide a perfect page span .
User A elects to hide two of the columns, and show the rest. The report complies and the viewable version is perfect, the excel export is perfect.. the PDF export on the first page causes every fith column, starting with the last column that was hidden to be expanded to take up additional width. On the spanned page, it renders the first column on that page correctly, then there is a white space gap equal to the width of the hidden columns and then the rest of the cells show with the last column expanded to take up the same width that the original 2 columns were going to take up, plus its width.
We have tried several different settings to see if it helps this issue or makes it worse. So far cangrow/canshrink/keep together have made no impact. It is not possible to increase the page size due to limited page size selection availablility for the client. There are far too many combinations of what the user can elect to show or hide to put together different tables to show and hide on the same report to remove this effect.
Any help or suggestion on this issue would be appreciated
Maybe I am missing something very obvious, but I couldn't do it: begin trangocreate table foo (f1 int not null,f2 int not null,f3 as (f1 + f2) not null primary key clustered)gorollback trango This returns:Server: Msg 8183, Level 16, State 1, Line 8Only UNIQUE or PRIMARY KEY constraints are allowed on computed columns.
I want to create computed column in table. Suppose I have three physical column A,B and C I want to create compute column with computed column. A+B= X X+C=Z Is it possible.
I created a index on a computed column. I did not see any improvementin performance with a join to this column and also my inserts andupdates to this table are failing. Any ideas?Chender
i have to use a datetime field in all the tables in a database as a updated timestamp. i.e. : whenever an update happens to a row in a table, this column called LASTUPDATED has to be updated with current date time.Is there any way to implement this without using the trigger ? can i use COMPUTED column to acheive this ?
I have a SQL table that maintains a field on the status of a report being completed. I have in the record the date the report is due (DateDue) I also have a field called DaysLate which I have set to be a calculated field with formula: DATEDIFF(dd, DateDue, GETDATE()) Thsi works but when the report is *not* late I'd like this to be null is there I way I can do this conditional calculation in a calculated field? Regards Cvive
I'm having a problem with a computed field in a table. I have a stored procedure that inserts a row into a table and returns the id and the computed value. The computed colmn is returned as a decimal (29,6) but for some reason the value is returned with no decimals (confirmed by the Profiler). The value is calculated and displayed in the database properly with decimals. Also, Is Persisted is OFF.
Below is the stored procedure, computed column foruma (both give same result), and the profiler trace.
-- Get the identity value SET @PurchaseOrderDetailId = SCOPE_IDENTITY()
-- Select computed columns into output parameters SELECT @LineTotal = [LineTotal] FROM [Purchasing].[PurchaseOrderDetail] WHERE [PurchaseOrderDetailID] = @PurchaseOrderDetailId
Computed Column Formula ================================================== ============== isnull(CONVERT(DECIMAL (29,6),[OrderQty]*([UnitPrice]*[PackingQty])),0.000000) or isnull([OrderQty]*([UnitPrice]*[PackingQty]),0.000000)
Profiler Trace ================================================== ============== declare @p1 int set @p1=115 declare @p16 numeric(29,0) <- should be numeric(29,6) set @p16=5 <- value should be 5.259200 exec Purchasing.PurchaseOrderDetail_Insert @PurchaseOrderDetailId=@p1 output,@PurchaseOrderId=68,@OrderQty=4,@VendorProd uctId=28,@Description=N'93678975 - GL-2222',@UnitPrice=0.657400,@PackingMethod=N'Bags (2)',@PackingQty=2.0000,@DueDateValue=NULL,@Modifi edDate=''2008-05-19 15:06:37:610'',@LineTotal=@p16 output select @p1, @p16
I have a question about Computed Column Specification which you can specify as a formula for each column inside a table.
I have now columns named Age and Class.
Classes are "Kids" (ID #1) , "Junior" (ID #2) and "Senior" (ID #3) Kids, which is for age of 6 till 12 Junior, which is for 12 till 16 Senior, 16 and above.
I have already searched for hours (I really did) on the internet for a solution, but ended with more questions because of the complicated solutions.
Now the Age is shown as a result of a formule of DOB (Date of Birth column), now I want the exact same thing, but the age must specify which Class the user is in.
Example, when I add a user with the birthdate 25/03/1988 (DD/MM/YYYY) he/she gets 24 as age.
With this formula : (datediff(year,[age],getdate()))
Now I want that the user gets "Senior" as Class (same table).
Senior is ID 3 in this case.
Now I do know how Case, When and Then works, but the validation fails. After reading some forums I understood that I should use a create function method. I am not really experienced with creating functions. Also the coding looks more different as I am used to. How to link the Computed Column to a created formula.
