Create Index Syntax - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Create Index Syntax

Author

Topic

TQMF_SQL
Starting Member

2 Posts

Posted - 2013-10-30 : 15:04:40

I'm embarrassed to post question regarding a syntax error but I cannot figure out what's wrong with the following code snippet...

CREATE UNIQUE INDEX [IX_TESTINDEX] on dbo.TS_RAY_BOGUS (TYPE, BASE_ID, LOT_ID, SPLIT_ID, SUB_ID, SEQUENCE_NO)
WITH (IGNORE_DUP_KEY = ON); --<< This is the line generating syntax error, why?
GO

Returns the following warning message:
Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near '('.

I need to create the index in a manner that when data is mass-loaded with the known data duplicates, the process will not halt, but rather complete loading the data and give a count of records not added due to duplicate keys.

I appreciate the time you are taking to help me figure out this problem. Best regards.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-10-30 : 15:12:59

I don't get any errors when running that statement. Is the server 2008 and database in the 100 compatibility mode?

or perhaps the error is for a different line?

EDIT:
this is what I ran:


go
create table #butt (type int, base_id int, lot_id int, split_id int, sub_id int, sequence_no int)
go
CREATE UNIQUE INDEX [IX_TESTINDEX] on #butt (TYPE, BASE_ID, LOT_ID, SPLIT_ID, SUB_ID, SEQUENCE_NO)
WITH (IGNORE_DUP_KEY = ON);
GO 
drop table #butt
go

Be One with the Optimizer
TG

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-10-30 : 15:14:35

The syntax seems fine to me - don't know why it is generating a syntax error for you. I tried to copy it and parse it - it does parse correctly.

One thing you could do though, is to use the user interface to set up the index - i.e., you would right-click on indexes node in the SSMS object explorer, select new index, and set up everything exactly as you want it. Then instead of clicking OK, click the script button at the top left of the right panel. That will give you the script that would have run had you clicked ok. Compare that with your script.

TQMF_SQL
Starting Member

2 Posts

Posted - 2013-10-30 : 15:32:56

Thanks to both TG and James K

Although the server itself is SQL Server 2008, the database I was working with is at the SQL Server 2000(80) level and I'll bet that's why I'm getting the syntax error, it is most likely validating against a SQL Server 2000(80) standard. Based upon your replies, I now have a couple of options of how to proceed, thanks. I wouldn't have thought to check the database level on my own.

I consider the issue now resolved. Thanks again for your time and expertise!!

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-10-30 : 15:56:43

you're welcome.

From Books Online:
"In backward compatible syntax, WITH IGNORE_DUP_KEY is equivalent to WITH IGNORE_DUP_KEY = ON."

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources