If you are on Sql Server 2012 or 2014, you should definitely look into the over clause and the window functions, perfect for this type of work.

😎

Here is one way of doing this

😎

USE tempdb;

GO

CREATE TABLE #events

(

eventID int,

eventname char(30),

startdate date,

enddate date

)

INSERT INTO #events VALUES (1,'testevent 1','2014-05-02','2014-05-30');

INSERT INTO #events VALUES (2,'testevent...

Grant Fritchey (5/12/2014)

---

Right. No way to do this...

Start by looking at these views in the database

[font="Courier New"]INFORMATION_SCHEMA.TABLE_CONSTRAINTS

INFORMATION_SCHEMA.DOMAIN_CONSTRAINTS

INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE

INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS

INFORMATION_SCHEMA.CHECK_CONSTRAINTS

INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE[/font]

😎

I don't think it is a good idea...

This is not a dynamic solution, the number of columns are set in the code.

😎

USE tempdb;

GO

DECLARE @POLHIST TABLE

(

POLICY_DETAILS_ID VARBINARY(16) NOT NULL

...

For comparison, here is a SQL 2012/2014 window function version. Without an POC index, Lynn's code is still faster.

😎

SELECT

IdCompany

,CompanyName

,CompanyCode

FROM

(

...

And its also a better one:ermm:, probably 20% quicker than my code! Nice!

😎

Here is a quick "translation" to play around with

😎

USE tempdb

Go

DECLARE @CHEST_COUNT INT = 1000;

/* Seed for the Inline Tally */

;WITH TN(N) AS (SELECT N FROM (VALUES...

KGJ-Dev (5/11/2014)

---

yes the hierarchy is fixed. I believe your words. I will start working with the real time data and will post back.

Once again thank you so much for...

KGJ-Dev (5/11/2014)

---

Hi Eirik,

I am sorry for the error data and Thank you for the reply and query. I understand how to split. Have a question about i have 100*1000...

If this is a fixed hierarchy, then it is straight forward. First get all distinct parent entries and the append the children. There is one error in the data, the...

Hi David and Lynn,

thank you both for the input, I appreciate it.

Firstly, this is a valuable discussion and would merit a thread on its own.

Thanks to columnar editing in...

Jeff Moden (5/10/2014)

---

If you want real performance, I'd recommend that you stop counting altogether. Indexed views have an overhead on inserts in that they have to recalculate the totals...

Too bad my ESP (Extrasensory perception) connection is down:-P The create table statements for both tables and the update statement sql code...