SELECT LIKE statment

Question

SELECT LIKE statment

Viewing 15 posts - 31 through 45 (of 48 total)

You must be logged in to reply to this topic. Login to reply

Frank Kalis SSC Guru Points: 111183 More actions · Answer 1

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]

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 2

Ninja's_RGR'us

SSC Guru

Points: 294069

September 13, 2005 at 6:52 am

#589330

Nice to see you back here Frank .

Magy SSCarpal Tunnel Points: 4217 More actions · Answer 3

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)

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 4

That can't be caused by that script... was that problem present before you did this?

Magy SSCarpal Tunnel Points: 4217 More actions · Answer 5

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.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 6

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?

Magy SSCarpal Tunnel Points: 4217 More actions · Answer 7

Ok, I tried that...I'll see if I get the error again.

THANKS! M

Magy SSCarpal Tunnel Points: 4217 More actions · Answer 8

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 9

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.

Magy SSCarpal Tunnel Points: 4217 More actions · Answer 10

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 11

yup. Or recreate the table with the option set to on so you wouldn't have to set it at each statement.

Magy SSCarpal Tunnel Points: 4217 More actions · Answer 12

I believe recreating the table would be too risky....

What does that option mean anyway?

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 13

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.

Magy SSCarpal Tunnel Points: 4217 More actions · Answer 14

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.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 15

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