j-1064772 (2/29/2016)

---

rstone (2/29/2016)

---

I think nulls are used more often than required, but they have their place. If we start with a rule that forbids nulls in the database, then we...

If all SQL devs knew this, the world would be a...

If we make sure that input data is uniquely identifiable on each table, i.e. non identity as that is generated after the input data, then we will be forced to...

Optimise your indexing. Tlog is used to rebuild unused indexes as well so get rid of the dead weight. Also I set threshols higher and run online daily so that...

P(A and B) = P(A) * P(B)

to put it simply

Would you mind posting the TVF definition?

I just want to be sure that it is not a multistatement TVF.

Is it defined as

CREATE FUNCTION DBO.iTVF (@Var INT)

RETURNS TABLE

AS

RETURN

SELECT blaadieblah

FROM...

type Cntrl+t in results pane.

select '8'+char(13)+char(10)+'9'+char(13)+char(10)+'6'+char(13)+char(10)

+'4'+char(13)+char(10)+'3'+char(13)+char(10)+'2'+char(13)+char(10)+'1'

Apparently there is a compatibility matrix which lets the engine know if the cast preserves the order of the data between the 2 data types.

So Cast ( intcol as small...

Instance level doesnt matter.

Each index should be hand crafted, i.e. each index should be <create index blah blah with(fillfactor = X)>

Indexes on identites, which increase monotocically, should be around 100.

Indexes...

+1

rcerney 63319 (2/1/2016)

---

Just a note that this script as written will only work with 2012+ due to the concat function. It could be easily changed though if your database...

Thanks Gail.

I have been using your article as a way to optimise a catch all proc without DSQL cos the devs are, let's say, averse to DSQL.

On re-reading, I do...

Hugo Kornelis (1/22/2016)

---

MadAdmin (1/22/2016)

---

Just throwing in my 2 pence

Instead of the non sargable

WHERE LEFT(TRANSDATE,6) = @CutOffDate

use the sargable

SELECT SUM(QUANTITY) AS TotalQty, @CutOffDate

FROM ICIVAL

WHERE TRANSDATE like @CutOffDate+'%'...

Just throwing in my 2 pence

Instead of the non sargable

WHERE LEFT(TRANSDATE,6) = @CutOffDate

use the sargable

SELECT SUM(QUANTITY) AS TotalQty, @CutOffDate

FROM ICIVAL

WHERE TRANSDATE like @CutOffDate+'%'

You can only get 99.99% improvement if the original query was doing scans. Seems like yours was not so 15% ontop of an already fast proc is very good.

Would you...