SELECT LIKE statment
-
Since indexes are physical implementations, creating an index on a computed column means, that you materialize that column. It is *NOT* virtual anymore.
Another option might be to create an indexed view that incorporates this REPLACE() or whatever. Just be aware that only SQL Server 2000 Enterprise Edition automatically considers indexed views. You can, however, use any other edition to create such views and need to use the NOEXPAND hint to force the optimizer to choose it.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Nice to see you back here Frank
. -
Everything is working good now...except that I'm occassionally getting an error that would indicate a divide by zero issue....
Here is my completed statement:
ALTER TABLE Inet.dbo.dmvrequest ADD
DRclaimRaw AS LEFT(REPLACE(DRclaimNumber, ' ', ''), (64)
GO
CREATE NONCLUSTERED INDEX IX_Temps_DRclaimRaw ON Inet.dbo.dmvrequest (DRclaimRaw)
-
That can't be caused by that script... was that problem present before you did this?
-
It wasn't there before, and after I deleted that index/column, it stopped. So it must be that.
ALTER TABLE Inet.dbo.dmvrequest ADD
DRclaimRaw AS LEFT(REPLACE(DRclaimNumber, ' ', '')), (256)
GO
CREATE NONCLUSTERED INDEX IX_Temps_DRclaimRaw ON Inet.dbo.dmvrequest (DRclaimRaw)
BTW, I was wrong, that script doesn't work now...I get this message:
Server: Msg 174, Level 15, State 1, Line 2
The left function requires 2 arguments.
-
ALTER TABLE Inet.dbo.dmvrequest ADD
DRclaimRaw AS LEFT(REPLACE(DRclaimNumber, ' ', ''), 256)
GO
CREATE NONCLUSTERED INDEX IX_Temps_DRclaimRaw ON Inet.dbo.dmvrequest (DRclaimRaw)
Can you post the table ddl and some sample data, then when you add this column recreates the error?
-
Ok, I tried that...I'll see if I get the error again.
THANKS! M
-
I got the error again!
Here is the exact wordage:
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC SQL Server Driver][SQL Server]UPDATE failed because the following SET options have incorrect settings: 'ARITHABORT'.
This error happens whenever someone tries to a save(UPDATE) to the database.
When I remove the index, the error goes away.
M
-
RUN this before the update :
SET ARITHABORT ON
or I think you can also try to recreate the table with that option on too but I'm not sure it'll solve the problem.
-
What does that mean anyway?
So everytime a user saves(UPDATES), I'll need to do this?
SET ARITHABORT ON
GO
UPDATE TABLE1
SET col1=blah1
SET col2=blah2
SET col3=blah3
WHERE ID='blah'
GO
SET ARITHABORT OFF
-
yup. Or recreate the table with the option set to on so you wouldn't have to set it at each statement.
-
I believe recreating the table would be too risky....
What does that option mean anyway?
-
BOLS.
Try it on a test server AS YOU ALWAYS SHOULD ANYWAYS.
I think it's time you start TRYING to learn on your own. I'm not paid to show you every little details of sql programmings and I surely won't do it.
Sorry for being blunt but you need a wake up call.
-
be nice to a semi-new-to-SQL girl...
anyway, I forgot I already looked that up, that's how I knew it might be a devide by zero issue.
ARITHABORT
Terminates a query when an overflow or divide-by-zero error occurs during query execution.
-
Can't happen with those string manipulations.
also the question would have been, can I get divide by 0 errors and not : "that I'm occassionally getting an error that would indicate a divide by zero issue".
Viewing 15 posts - 31 through 45 (of 48 total)
You must be logged in to reply to this topic. Login to reply