This should do the trick...

-- test data --

IF OBJECT_ID('tempdb..#temp') IS NOT NULL

DROP TABLE #temp;

CREATE TABLE #temp (

id INT,

color VARCHAR(15)

)

INSERT #temp (id,color) VALUES

(1,'Green'),

(2,'Yellow'),

(3,'Yellow'),

(4,'Pink'),

(5,'Green'),

(6,'Yellow'),

(7,'White'),

(8,'White'),

(9,'Pink')

-- the solution --

SELECT

ROW_NUMBER() OVER (ORDER BY x.id)...

After doing some more digging, the closest thing I could find is an apparent isssue with the Cardinality Estimator, reported by Dave Ballantyne.

New "Type Conversion in Expression....." warning in SQL2012...

Lynn... If you want to take a crack at this one (Case Sensitive Collation Causing Cardinality Warning), you have my word, I'll respond in a timely manner... As an added...

Check to make sure IntelliSense is activated...

reamades (5/19/2015)

---

Sure, the flat data file has;

OwnerID

OwnerName

OwnerAddress

OwnerCity

OwnerState

OwnerZip

PropertyDescription

County

etc.

So John Smith might have OwnerID=1 in Tarrant County. And there might be Smith, John in Reno County that has OwnerID=205. I'm still wrapping...

Nope... All CHAR(1) to CHAR(1)... (not shure how the [Union####] come into play...)

here's the warning section of the plan.

<Warnings>

...

Alan.B (5/18/2015)

---

I'm not 100% sure about this and hope someone corrects me if I'm wrong...

If the collation that the table is stored in is Case Insensitive then you use a...

I don't know that I'm 100% clear on what you want but I think this should be pretty close...

Check this with the sample data and see if it's what you're...

I agree that the ">= / <" syntax is the all around better syntax and I'll stick to it exclusively, moving forward.

Lynn Pettis (5/15/2015)

---

I would still refrain from using BETWEEN for date ranges. Biggest reason being if there is a data type change to column such as DATE to DATETIME...

WOW... I could have sworn that the last time I tested, CASTing from a DATETIME to a DATE made it unsargable...

Just tetested with this...

DECLARE

@BegDate DATE = '2015-01-10',

@EndDate DATE...

I'm with ya... I'd written a piece of obfuscation code that actually worked a little too well for it's intended purpose... Rather than trashing the code, I figured that it...

Next question:

How would you describe the differences between being "Loyal" to a company and "Dedicated" to a company? Heh... The answer will be provided either at 2AM tomorrow morning...

The following is what I'd put in the NEVER do category...

WHERE CAST(t.SomeDateTime as DATE) BETWEEN @BegDate and @EndDate

Actually... I should say "Never do again" category... I have a few report...

Both good points... Although I'd still put this in the "good idea not to do this" category as opposed to the "never do this" category...