I get error on following sql after creating an index

Transact-SQL
1

CREATE TABLE [dbo].[mytab](
[Ino] [numeric](10, 0) NOT NULL,
[mytype] varchar NULL,
[mydate] [int] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO

CREATE UNIQUE CLUSTERED INDEX [mytabin] ON [dbo].[mytab]
(
[Ino] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [t1] ON [dbo].[mytab]
(
[mytype] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

insert into mytab values(1,'751ZW',20150101)
go

insert into mytab values(2,'751ZW',0)
go
insert into mytab values(3,'751ZA',0)
go
insert into mytab values(4,'751FG',0)
go

SELECT D.mydate, mytype FROM mytab D with( index(t1))
WHERE CONVERT(DATETIME,LEFT(D.mydate,10),103) BETWEEN '01-Jan-2015' AND '01-Jan-2015'
AND D.mydate <> 0
AND RIGHT(D.mytype,2) IN ('ZW','NI','SB','SV')
AND LEFT(D.mytype,2) IN ( '75')

Please note this is a test table i created for replicating. the real time table has more than 10 million records and we cannot correct the data now. Also cannot change underlying sql Because the same sql is running fine with out index and also with the index properly in another environment

I am forcing the index here just because to show the SELECT gives error when using the index. In real time without forcing the select picks up the index automatically

I want to know

  1. Why an index is giving error.
    2.Why it runs properly in 1 environment and not properly in another environment
2

Your problem is the 0 value when converting back to a date from an INT, why use 0 for a INT date field, you would be better using a default date to get around this, 19000101. Otherwise you will need to CASE or something like that to take care of the 0 values.

1 Like
3

Thanks but as i said we cannot correct the old data now. yes I am using the CASE but my question why it is not giving any error in another enviroment. Also why a Index is giving error

4

Post the exact error.

1 Like
5

Msg 241, Level 16, State 1, Line 2
Conversion failed when converting datetime from character string.

This is the error I am getting

But There is NO error in the Another Enviroment

6

Ok, so it cant convert the 0 to a date field, does the other field in the other environment have 0 values as oppose to 8 chars date int values, does the select work there where 0 values are inserted? You would get the same error without the indexes.

7

Yes the data is same in both the environment. Infact it is a copy It works there

8

I cant see how you are converting the 0 from the other environment but something like this should take care of the 0 on your test table

SELECT CASE 
WHEN MyDate = 0 THEN CAST(CAST(20010101 as CHAR(10)) as DATE)
ELSE CAST(CAST(MyDate as CHAR(10)) as DATE) 
END 
FROM MyTab
1 Like
9

Thanks But I cannot change the SQL because it is not in my hand. I dont know how the 0 is getting overridden in the another environment. Something with SQL server setting/Engine?

10

Never use any function on a table column unless you have to, because it prevents index seeks (in technical terms, it is not sargable).

Here's a rewrite of the query that follows those rules:

SELECT D.mydate, mytype
FROM mytab D WITH( INDEX(t1) )
WHERE D.mydate BETWEEN '20150101' AND '20150101'
AND D.mydate <> 0
AND D.mytype LIKE '75%'
AND RIGHT(D.mytype,2) IN ('NI','SB','SV','ZW') --can't avoid a function here

Btw, this table should almost certainly be clustered on mytype. You can also add a nonclustered index on Ino if you really need it. It's a myth that clustered index should "always" be numbers.

CREATE CLUSTERED INDEX [t1] ON [dbo].[mytab] ( [mytype] ASC ) WITH ( SORT_IN_TEMPDB = ON, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY]

CREATE UNIQUE NONCLUSTERED INDEX [mytabin] ON [dbo].[mytab] ( [Ino] ASC ) WITH ( SORT_IN_TEMPDB = ON, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY]

1 Like
11

Thanks Scott but as I said earlier I cannot rewrite the query but i can change reorder the Index but my another question is still there. Why do 1 environment shows error and Another does not show error. Both the environment is having same data