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

SQLPain (9/11/2015)

---

I ran the code again you gave, this time its giving me the following error.

Msg 195, Level 15, State 10, Line 3

'ROW_NUMBER' is not a recognized function name.

Msg 156,...

tt-615680 (9/12/2015)

---

Dear All,

I have a database which gets refreshed on the daily by restoring 1 transactional file log, at the moment it is done manually but I'm trying to create...

Alan.B (9/11/2015)

---

Thanks Eirikur, you always seen to have the answer. Very interesting stuff - I have never really played around any Undocumented Query Optimizer Trace Flags (I used to play...

Further on the topic, the input tree is generated during the parsing and binding phase. Since the query processor does not evaluate variable values during this stage, this step produces...

Alvin Ramard (9/11/2015)

---

Eirikur Eiriksson (9/11/2015)

---

Did try it briefly few years back, not bad but it didn't end up in the tool...

Did try it briefly few years back, not bad but it didn't end up in the tool chest, main reason being...

pronto

Mike Sofen-356112 (9/11/2015)

---

Eirikur Eiriksson (9/11/2015)

---

My 2 Cents, when working with large and often complex schema, cluttering a detail level ERD into a single diagram does neither make sense nor serve...