I wouldn't want to change the table structure every time a requirement changes.

The DBA has better things to do, and many DBAs are not noted for their SQL programming skills.

Above...

I'll have a crack at answering these, since I've designed an EAV system for storing configuration data using lists and lists or lists

Q. Who implements the restrictions as to which...

Hasing can accelerate searching for equality/inequality, but after an equality match you still got to compare the base data as collisions will occur. More frequently so as the size of...

I made an entire subsystem for EAV data, in order to hold configuration information to be used by the entire enterprise.

There are stored procs, DTS packages, jobs, C# apps, web...

Gembox software makes a component that reads xls and xlsx files without using automation.

You could use it inside a CLR stored proc to return a result set with any number/type/names...

You learn something new every day (if you're lucky).

Had no idea it was incompatible with DateTime2.

Aargh! I knew that! Failure to engage brain before engaging fingers!

ROUND(@date,0,1)

is what I meant.

Just FYI

AND PM.[MId] NOT IN (SELECT MId FROM [tblContact] WHERE [Isdeleted] = 0

is equivalent to

--other joins not shown

LEFT JOIN [tblContact] tc ON tc.MId = PM.[pMId] and [Isdeleted] =...

GilaMonster is exactly right - removing the NOT INs is not a long term resolution as the deadlocks will still occur, just not as frequently. Expressing them as left joins...

That's why I cast the date as an int. The fractional part of the date is the time. Casting is pretty fast.

I'd be willing to bet a dollar it has something to do with those two "NOT IN"s in the query.If you change those to left joins I bet the problem...

Apropos of nothing, my favorite is

declare @i int

select @i=CONVERT(int,cast('01/07/2013' as datetime) )

SELECT * FROM table where

CAST(field AS int) = @i

It's very fast.

If it produces the results the results you desire, no.

If not, it may require some tweaking.

It's what you asked for, but is it what you wanted? Only you can answer...

GrpID GrpName PrvID PrvName Spc

1234 Med Associates (blank) (blank) Mixed

1234 Med Associates 6875 Bill Ham Cardiology

1234 Med Associates 3274 Sarah Jones Cardiology

8975 Pulm Associates (blank) (blank) Mixed

5781 Babies Inc. (blank)...

Ah, and if we were the boss of all creation how differently things might be designed! Unfortunately we work in the...