Very surprising that SQL doesn't the index on which it can do almost a full seek.

Do you really need the "PersonID<>0", or could you remove that? I believe that...

Another option to generate code to be run on the other server:

DECLARE @view_template varchar(8000)

SET @view_template = '

CREATE VIEW [$schema$].[$table$]

AS

SELECT * FROM [SourceDatabase].[$schema$].[$table$] UNION ALL

SELECT * FROM [ArchiveDatabase].[$schema$].[$table$]

GO

'

SELECT REPLACE(REPLACE(@view_template,

...

Create a replacement values table, and use it to do the data replacement.

CREATE TABLE dbo.data_replacements (

column_name varchar(30) NOT NULL,

current_value varchar(100) NULL,

...

You'd have to generate dynamic SQL to do that. First, get the highest count as it exists ni your current data. Then modify the above overall structure to...

How about?:

SELECT p.Warehouse,

CASE WHEN SUM(Quantity) >= 10

THEN CAST(SUM(Quantity) AS varchar(30))

...

JediSQL (10/31/2014)

In most conditional statements, like WHERE clauses and IF statements, the conditional expression will return TRUE if and only if SQL Server considers the assertion provably true. When...

Here's one quick-and-dirty way to do this for up to 5 instances:

Edit: changed the column names to better match the table defs.

with instanceInfo as

(

select

ins.srvID

,ins.instNetName as...

CORRECTION: Previous code has bug because the tally table does not include a 0.

DECLARE @Sample TABLE(

FileNumb int,

startdate date,

...

Select FROM the job table and user LEFT OUTER JOINs on the other two tables.

[This sounds possibly like class work or home work, so I'm just giving a general guideline...

Edit: Was able to post an inline CTE this time; guess they fixed the bug at work that was blocking posting any CTEs ... Hallelujah!

DECLARE @Sample TABLE(

...

I don't like table overhead when it can be avoided by simple mathematical calcs. Calendar tables have their uses, but to me they can sometimes become the proverbial hammer...

You should only rebuild or reorganize an index when it is fragmented enough to be significant. For some tables, this might be every day. For others, only every...

Presumably some number of UDFs would get updated over time, particularly as new techniques are learned. I believe, for example, that the splitter function has gone thru several iterations,...

Jason A. Long (5/15/2015)

---

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...

Interesting points. (Although I'd prefer to put the test conditions / "known case" conditions into a table, for the usual reasons of flexibility and maintainability.)

As an aside, the function...