where
	COL_CHECKSUM = CHECKSUM('23_12_2005') and
	COL like 'OrderCode=SAMESTRING_23_12_2005:abc:def%'

where
	like 'OrderCode=SAMESTRING_23_12_2005:abc:def%'

Change to:

where
	like 'OrderCode=SAMESTRING^_23^_12^_2005:abc:def%' escape '^'

quote:

---

Originally posted by rockmoose

Hi,

We have a 3m rows large table with a nvarchar(400) column.
That column is indexed, and there are some searches on that column with the format:
LIKE 'OrderCode=SAMESTRING_23_12_2005:abc:def%'

Now the problem is that about 80% of the rows start with, the same string: "OrderCode=SAMESTRING_", this essentially makes the sargable index seek return 2m + rows.
Sql Server intermittedly does index seeks & scans on the table.

The point is that it is too slow, and I was wondering if just removing the duplicate characters "OrderCode=SAMESTRING_" from the data would help.
Would it make the index actually work?


rockmoose

---

checksum(substring(MyCol,1,40)) = MyChecksumCol

quote:

---

Originally posted by Michael Valentine Jones

If you add the checksum column, it is a good idea to add a check constraint to make sure the checksum and column value don't get out of sync. That is one thing in favor of a computed column; they can't get out of sync.

checksum(substring(MyCol,1,40)) = MyChecksumCol

---

quote:

---

Originally posted by rockmoose

quote:

---

Originally posted by Michael Valentine Jones

If you add the checksum column, it is a good idea to add a check constraint to make sure the checksum and column value don't get out of sync. That is one thing in favor of a computed column; they can't get out of sync.

checksum(substring(MyCol,1,40)) = MyChecksumCol

---

Hmm, but that could cause problems for dml operations, unless there are instead-of triggers.

rockmoose

---

quote:

---

BOL: The connection on which the index is created, and all connections attempting INSERT, UPDATE, or DELETE statements that will change values in the index, must have six SET options set to ON and one option set to OFF. The optimizer ignores an index on a computed column for any SELECT statement executed by a connection that does not have these same option settings

---

quote:

---

Originally posted by rockmoose

For the computed column I am concerned about the:

quote:

---

BOL: The connection on which the index is created, and all connections attempting INSERT, UPDATE, or DELETE statements that will change values in the index, must have six SET options set to ON and one option set to OFF. The optimizer ignores an index on a computed column for any SELECT statement executed by a connection that does not have these same option settings

---

I can set these in the sproc that does the search (and insert).
But what happens if a connection does INSERT, UPDATE, or DELETE without those options?
Nothing?, just the index not being used, or is there an error?

rockmoose

---

drop table [dbo].[MY_CHECKSUM_TEST]
GO
CREATE TABLE [dbo].[MY_CHECKSUM_TEST] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[MY_TEXT] [varchar] (60) 
	COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[MY_CHECKSUM] AS (checksum([MY_TEXT])) 
)
GO
ALTER TABLE [dbo].[MY_CHECKSUM_TEST] 
ADD CONSTRAINT [PK_MY_CHECKSUM_TEST] 
PRIMARY KEY  CLUSTERED  ([ID])
GO

set	ANSI_PADDING,
	ANSI_WARNINGS,
	CONCAT_NULL_YIELDS_NULL,
	ARITHABORT,
	QUOTED_IDENTIFIER,
	ANSI_NULLS on 
GO
set NUMERIC_ROUNDABORT off 
GO
CREATE  INDEX [IX_MY_CHECKSUM_TEST_MY_CHECKSUM]
ON [dbo].[MY_CHECKSUM_TEST]([MY_CHECKSUM])
GO
set arithabort  OFF 
GO
set numeric_roundabort  OFF 
GO
set quoted_identifier  OFF 
GO

set QUOTED_IDENTIFIER off
set arithabort  off
go
print 'Bad Insert Settings'
go
insert into [dbo].[MY_CHECKSUM_TEST] ( [MY_TEXT] )
select [MY_TEXT] = 'aaa  '+convert(varchar(400),newid())

go
set QUOTED_IDENTIFIER on
set arithabort  on
go
print 'Good Insert Settings'
go
insert into [dbo].[MY_CHECKSUM_TEST] ( [MY_TEXT] )
select [MY_TEXT] = 'bbb  '+convert(varchar(400),newid())

go
set QUOTED_IDENTIFIER off
set arithabort  off
go
print 'Bad Update Settings'
go
update [dbo].[MY_CHECKSUM_TEST]
set [MY_TEXT] = 'xxxx  '+ [MY_TEXT]
go
set QUOTED_IDENTIFIER on
set arithabort  on
go
print 'Good Update Settings'
go
update [dbo].[MY_CHECKSUM_TEST]
set [MY_TEXT] = 'zzzz  '+ [MY_TEXT]
go
select * from [dbo].[MY_CHECKSUM_TEST]
go
truncate table [dbo].[MY_CHECKSUM_TEST]

Bad Insert Settings
Server: Msg 1934, Level 16, State 1, Line 1
INSERT failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER, ARITHABORT'.
Good Insert Settings

(1 row(s) affected)

Bad Update Settings
Server: Msg 1934, Level 16, State 1, Line 1
UPDATE failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER, ARITHABORT'.
Good Update Settings

(1 row(s) affected)

ID          MY_TEXT                                                      MY_CHECKSUM 
----------- ------------------------------------------------------------ ----------- 
1           zzzz  bbb  B661FF6B-BA03-4042-B717-98F6E4496D70              -1287381225

(1 row(s) affected)