pollokoff (11/13/2015)

However, using [IS NULL] does not result in the use of the index that exists on the EXPIRATION column.

...

The question was more for general knowledge. The actual query...

Brandie Tarvin (11/13/2015)

---

I've been looking at the data (i.e., the column names) and I just realized this is Adventure works. So I looked again at the problems, actually reading them...

Sorry, I should have been clearer on that point!

DelimitedSplit8K is an inline table-valued function for SQL Server, but it's not part of SQL itself, it was developed by users. ...

Best is to use a very efficient splitter, such as DelimitedSplit8K, and then do an INNER JOIN to those results:

DECLARE @custID varchar(200)

set @custID = '72793,60546,91069'

SELECT tn.*

FROM table_name tn

INNER JOIN dbo.DelimitedSplit8K...

He wants to do nothing himself. I tried to walk him thru the logic of how to code #4 in his original q, here:

http://forums.sqlteam.com/t/need-help-case-statement-should-work/4070

but he refused, instead basically just...

Explicitly set it ON immediately before DELETE/INSERT/UPDATE statements and OFF immediately afterward?

You can also do it with just a single scan of the data table:

SELECT ca1.*

FROM table_name

CROSS APPLY (

VALUES(Car, ColorOption1),(Car, ColorOption2),(Car, ColorOption3)

) AS ca1(Car,ColorOption)

I wouldn't use the gui for that, although, to be fair, the gui usually does ok on that particular task. But it's so relatively flaky on others that I...

Hmm. Typically one of the main things to address deadlocking is adjusting indexes. And the first priority in tuning is general is always to get the best clustered...

There are certain changes that could make recreating the table more efficient than just the ALTERs. Would need details of the specific change(s) to know for sure. It's...

How many unique UnitIds are there? It looks as if the clustering key on the table should be ( UnitId, StartDate, Id ) [Id is optional, just to make...

Sergiy (11/10/2015)

---

David Rich (11/9/2015)

---

While going through execution plan i observed this warning

Type conversion in expression (CONVERT_IMPLICIT(varchar(255),[e].[abc],0)) may affect "CardinalityEstimate" in query plan choice

and this is getting converted to nvarchar(255)....

David Rich (11/9/2015)

---

While going through execution plan i observed this warning

Type conversion in expression (CONVERT_IMPLICIT(varchar(255),[e].[abc],0)) may affect "CardinalityEstimate" in query plan choice

and this is getting converted to nvarchar(255). Will...

Yet another version, as an in-line table-valued function, for easy use within a query:

CREATE FUNCTION dbo.fn_first_day_and_last_day_of_week

(

@year smallint,

@week tinyint

)

RETURNS...

Good point about partial matches. Presumably you'd want to match on the longest match (?!):

insert into #import (importcity, title)

select 'new_city_for_testing', 'rancho'

insert into #city (city)

select 'rancho'

update i

set importcity...