Not exactly.

Order of columns not in table but in PK:

CONSTRAINT [PK_tblPromptLabels] PRIMARY KEY CLUSTERED ([LabelID],[PromptID])

And I would suggest to replace NOT IN with NOT EXISTS (...).

You mean there are 68 records in tblPromptLabels having LabelID = 27?

I think if you reverse order of columns in PK_tblPromptLabels it will fix your problem forever.

So, you mean that second version with Clustered Index Scans and Hash Joins is faster?

How many rows have you got in tblPrompts and in tblPromptLabels?

How many rows satisfy the condition...

Stop.

From the beginning.

The second query you just posted is not exactly the case.

I can explain what's going on there, but it's another topic.

We were discussing 2 cases:

- one with pl.LabelID...

IF IsNumeric(SubString(@Fname, 1, 2)) = 1

If you would use 30% of effort you wasted on this script for building tables holding all those conditions you'd get clean, short script implementing manageable solution.

By "manageable" I mean...

Did you try DBCC FREEPROCCACHE?

Did you try to compare execution plans?

EXEC('CREATE TABLE ' + {build table name} + '

......

')

If you don't believe smart and experienced people go and hit the wall.

Actually IP address is 4-byte value.

And it must be stored in BINARY(4) column.

Nothing to do with any kind of varchar here.

As soon as you've fixed you table design you not...

Just to spoil everybody's celebration.

ISDATE sometimes is not good enough.

It's good if you locked inside of the world of American standards.

But imagine situation when...

1st you need to convert string 'Mar 20 2007 3:57pm' to datetime.

You cannot do it in update because your column is "varchar", not "datetime" as it should be.

To convert it...

Which table?

What's the datatype for date column in that table?

Are you doing import (as you said in first post) or export (as you said in the next one)?

Did you read Jeff's comment to your previous topic?

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=389085

This will allow to handle any number odf words:

SELECT product_id, description

FROM products P

INNER JOIN @SplitWords S ON P.description LIKE '%' + SearchWord + '%'

GROUP BY product_id, description