Smalldatetime In Indexed Computed Columns

Nov 13, 2001

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...)





and

View 1 Replies


ADVERTISEMENT

OLE DB ARITHABORT Error With Indexed Computed Columns

Oct 31, 2002

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.

Thanks
Jason

View 3 Replies View Related

Computed Columns

Jul 4, 2007

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

View 3 Replies View Related

Computed Columns...

May 20, 2008

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? 

View 3 Replies View Related

Computed Columns

Oct 31, 2003

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?

I can do:

1. CREATE TABLE Users(
fname varchar(20),
lname varchar(20),
email as LOWER(LEFT (fname,1) + lname) )

Or

2. CREATE TABLE Users (
fname varchar(20),
lname varchar(20))

CREATE VIEW Vw_users (fname, Lname ,
email)
AS
SELECT fname, Lname ,
LOWER(LEFT (fname,1) + lname) )


Is one of them is better?

Paulo

View 6 Replies View Related

Computed Columns

Nov 6, 2006

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?

View 3 Replies View Related

Computed Columns

Jul 20, 2005

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!

View 1 Replies View Related

Computed Columns

Sep 25, 2006

Hi,

Consider the following example

create table sample

(col1 int,

col2 int ,

col3 AS col1 + col2) PERSISTED NOT NULL)

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

View 9 Replies View Related

Computed Columns Or UDFs

Jul 8, 2004

Hi,

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?

thanks,
Frank

View 1 Replies View Related

Computed Columns And Constant

Aug 22, 2006

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?

Przemek

View 1 Replies View Related

Modify Computed Columns Using T-SQL

Oct 8, 2007

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.

Thanks in Advance!!

View 12 Replies View Related

Query To Find The Indexed Columns

May 30, 2007

Dear Experts, i need one query to find all the indexed columns with table names ,column names and type of indexes....

i'm trying with sysindexes, but i was unable to finish it....i'm not clear about keys column in sysindexes...

please guide me.

thank you very much

View 12 Replies View Related

Computed Columns In Temp Tables

Jun 25, 2004

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!

View 5 Replies View Related

T-SQL (SS2K8) :: Computed Columns - Max From Other Column For The Same ID?

Jul 25, 2012

Suppose a table being

Create table myTable (ID int, col1 int, col2 int)

I know how to make a computed column being the sum of other column for the same ID e.g. "computed_column = col1 + col2".

Getting the average would be "computed_column = (col1 + col2)/2" But how to get the Max, Min?

Even "Sum(col1,col2)" or AVG(col1, col2) does not work as the formula for a computed column...

View 9 Replies View Related

Create View With Computed Columns?

Apr 1, 2015

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

View 2 Replies View Related

Computed Columns Asynchronously Updating?

Mar 19, 2008

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?

-Jeremy

___________________________
Geek At Large

View 1 Replies View Related

Table Size And Computed Columns

Jul 23, 2005

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

View 1 Replies View Related

Trying To Find A Match In Computed Columns

May 26, 2006

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

View 3 Replies View Related

Computed Columns Asynchronously Updating?

Mar 19, 2008

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?

View 4 Replies View Related

Computed Columns Used In Select Statement

Nov 12, 2007

Does tsql allow sth like

Select col1*col2 as ComputedColumn, ComputedColumn + 2 as NewColumn
From T_Table

THis is possible in Access.

View 5 Replies View Related

Simple Question About Computed Columns

Aug 30, 2007



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:

When x-y < 0 Then 0 else x-y

Thank you
M-l-G

View 3 Replies View Related

Convert Datetime Columns To Smalldatetime In Whole Database

Nov 7, 2007

Hi,

I need to convert all datetime columns to smalldatetime in the whole database. I really don't want to do it by hand and It would probably take me a whole day to figure out how to write such a procedure. If someone could help me out that would be great.
My database is divided into schemas just like AdventureWorks.
Also, no need to worry about date conversion, value could be set to current date.

Thanks

View 14 Replies View Related

Bulk Inserting Into Table With Computed Columns

Jul 20, 2005

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

View 2 Replies View Related

Transact SQL :: Returning Non Indexed Records From Sorted Table - Speed Same As Indexed?

Dec 1, 2015

I have created a table from another table where I specified that one of the fields, an number field,  is sorted in ascending order and have NOT specified that it is to be an indexed field and there are 10 million records, from 1 to 10,000,000 exactly.

Now, if I query that table, asking to return records 1-1,000 from that non indexed number field that I sorted in ascending order (where number field <= 1,000) , will it run as fast as if it were indexed?

In other words, does SQL know somehow that these records are sorted in ascending order and so will not do a full table scan, stopping at 1,000 to return my data set?

Or is there no way for SQL to know this and only specifying an indexed field allows SQL to know that its in some order and so it doesn't have to do the full scan?

View 15 Replies View Related

The Conversion Of Char Data Type To Smalldatetime Data Type Resulted In An Out-of-range Smalldatetime Value

Mar 30, 2007

I am using Visual Studio 2005 and SQL Express 2005. The database was converted from MS Access 2003 to SQL Express by using the upsize wizard.



I would like to store the current date & time in a column in a table. This column is a smalldatetime column called 'lastlogin'.

The code I'm using is:



Dim sqlcommand As New SqlCommand _

("UPDATE tableXYZ SET Loggedin = 'True', LastLogin = GetDate() WHERE employeeID = '" & intEmployeeID.ToString & "'", conn)



Try

conn.Open()

sqlcommand.ExecuteNonQuery()

conn.Close()

Catch ex As Exception

MessageBox.Show(ex.Message)

End Try



This code works fine on my local machine and local SQL server. However at the client side this code results in the error as mentioned in the subject of this thread. I first used 'datetime.now' instead of 'getdate()', but that caused the same error. Then I changed the code to 'getdate()', but the error still remains.



The server at the client is running Windows Server 2000 UK . My local machiine is running WIndows XP Dutch.

Maybe the conversion from Dutch to UK has something to do with it. But this should be solved by using the 'Getdate()' function..... ?













View 1 Replies View Related

Computed Column Referencing A Computed Column

Sep 5, 2007

Hi all,

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.

Could anyone offer some advise on my situation.

Any input apreciated.

View 1 Replies View Related

RS2k Issue: PDF Exporting Report With Hidden Columns, Stretches Visible Columns And Misplaces Columns On Spanned Page

Dec 13, 2007

Hello:

I am running into an issue with RS2k PDF export.

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

View 1 Replies View Related

Over Indexed?

Apr 24, 2008

what do u think about a table that has 19 indexes and some of them are covering index?

View 3 Replies View Related

INDEXED

Jan 5, 2006

hi all,

In the properties of the table in the MS Access there is an Indexed column. If someone would had set that to "Yes (Duplicates Ok)" how can one implement this to MSSQL???

View 7 Replies View Related

Smalldatetime

Mar 4, 2004

im getting this error while inserting into table (description of table given below)

mytable
mycode smallint identity,
mydataitem varchar(100),
mydatefrom smalldatetime,
mydateto smalldatetime,
myopcode smallint,
lastupdate smalldatetime

my sql statement was

INSERT INTO mytable
(mydataitem,mydatefrom,mydateto,myopcode,lastupdat
e)
VALUES
('SAMPLEDATA','01/01/2003','18/05/2003',1,'05/Mar/2004')

THE ERROR RETURNED IN MY CLIENT APP IS
[Microsoft][ODBC SQL Server Driver][SQL Server]The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value.

AND THAT IN QA IS

Server: Msg 296, Level 16, State 3, Line 1
The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value.
The statement has been terminated.

can ne 1 point out the mistake in my sql statement ?


__________________
Cheers....

baburajv

View 4 Replies View Related

Using Smalldatetime

Jul 3, 2007

I have an Execute SQL Task that pulls the max date from a sql table.

SELECT max(date_Idx) FROM dbo.FactDailyInventorySnapshot



This field is defined as a smalldatetime. I want to store this max date in a variable in SSIS called LastDate.



Then in a data flow, in an OLE DB Source, I want to use it as a parm in a sql command to compare to a smalldatetime field.



My question is, what type of variable do I declare it in in SSIS, and what is the correct parameter mapping inside of my sql query? I thought I had exhausted all combinations of variable types and parameter types. I have even tried casting it as different data types when I do the exec sql task of getting the max date. Any help would be appreciated. Thanks!

View 1 Replies View Related

Indexed Fields

Jun 22, 2000

I have a table with three fields that are marked as indexed fields with unique keys. I want
to remove one of the fields. When I do this and hit the rebuild button -- I get an error, that
there is a duplicate field, so it will not just let me remove the one field and leave the other
two?? How can I get around this?

View 2 Replies View Related

Indexed View

Jan 10, 2002

Hi All,

I have over 100 views in a database. How can I check which view we used indexed view?

Thanks.

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved