Quick suggestion

😎

USE tempdb;

GO

SET NOCOUNT ON;

/* Sample data from OP */

DECLARE @EXISTING TABLE

(

[Month(YYYYMM)] INT NOT NULL

,Department...

Thank you Jason for this write up, very good reference indeed!

😎

ALTER is a short-cut for...

Have you checked the [Install dir]\MSSQL\Log\ERRORLOG file?

😎

Walk

Jeff Moden (9/13/2015)

---

Because it's still an estimate of cost, I don't trust even the Actual Execution Plan when trying to determine which code will be faster or use fewer resources....

There is a subtle difference between these two queries, when measured back to back on 10^6 numbers then the former is around 25% faster than the latter although the difference...

For fun

😎

SELECT ROUND(0.999,-1);

Using heap to speed the inserts is like a very bad Credit Card deal, no transaction fees but X000% Annual interest rate:pinch:

😎

Jeff Moden (9/12/2015)

---

It's a really good thing that...

chetta (9/12/2015)

---

Hello,

I have an insert statement similar to:

insert into dbo.CHILD (ChildMotherID, ChildNo)

values

(

(select MotherID from dbo.Mother where MotherName = 'Suzie Q'),

(select max(ChildrenAmt)+1 from dbo.Children where ChildrenMotherID =

(select MotherID from...

Two quick suggestions, first uses the DelimitedSplit8K function for a simple delimited list of parameters and the second uses XML for more complex parameter structure.

😎

Simple parameter string

USE tempdb;

GO

SET NOCOUNT ON;

GO

IF...

pietlinden (9/11/2015)

---

Something like...

CREATE TABLE Actuals (

FY INT

,P1 SMALLMONEY

,P2 SMALLMONEY

,P3 SMALLMONEY

,P4 SMALLMONEY

,P5 SMALLMONEY

,P6 SMALLMONEY

,P7 SMALLMONEY

,P8 SMALLMONEY

,P9 SMALLMONEY

,P10 SMALLMONEY

,P11 SMALLMONEY

,P12 SMALLMONEY

CONSTRAINT pkActuals PRIMARY KEY (FY)

);

GO

-- insert some dummy values...

INSERT INTO dbo.Actuals(FY,P1,P2,P3,P4,P5,P6,P7,P8,P9,P10,P11,P2)

--...

Quick suggestion with inline tally table

😎

DECLARE @BoxesNeeded INT = 15;

;WITH T(N) AS (SELECT N FROM (VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)) AS X(N))

, NUMS(N) AS (SELECT TOP(@BoxesNeeded) ROW_NUMBER() OVER (ORDER...

Steve Jones - SSC Editor (7/23/2015)

---

We have debated about it. There is a significant amount of work for some parts of the site. We do have a mobile QotD that...

Quick suggestion

😎

/* Safe place */

USE tempdb;

GO

SET NOCOUNT ON;

GO

DECLARE @METADATA TABLE

(

ReportID ...