Give this a shot...

IF OBJECT_ID('tempdb..#MgtStructure', 'U') IS NOT NULL

DROP TABLE #MgtStructure;

CREATE TABLE #MgtStructure (

EmployeeID CHAR(1),

ManagerID CHAR(1)

);

INSERT#MgtStructure (ManagerID, EmployeeID) VALUES

('A', 'B'),

('A', 'C'),

('A', 'D'),

('B', 'C'),

('D', 'E'),

('F', 'G'),

('F', 'K'),

('H', 'I'),

('H', 'J'),

('H', 'A');

WITH...

Please post consumable sample test data from the CAD table... So that we have something to test with.

Yes. Your query isn't doing anything...

Jacob Wilkins (12/10/2015)

---

He did mention that the...

sql84 (12/10/2015)

---

I have table with about 400 columns and 4 million rows.

the only purpose of this table to be used by a reporting tool. this table is refreshed(dropped and recreated)...

Dee Dee-422077 (12/9/2015)

---

Thanks ZZartin. The 1st suggestion is the one I need but when I run the query. It's extremely slow..i wait for 10 minutes and still...

Raul Undreiner (12/9/2015)

---

Hello all,

I have a table with about a million rows. There is a category column with around 20 different values. How can I produce a sample extract of...

If I were a betting man, I'd put my $$$ on Jacob being correct... Lazy/sloppy coding can & will come back to bite you in some pretty surprising ways.

Just for the fun of it, here's another...

IF OBJECT_ID('tempdb..#T1','U') IS NOT NULL

DROP TABLE #T1;

CREATE TABLE #T1 (GoodId INT,LocNum INT);

INSERT#T1 (GoodId,LocNum) VALUES (1,500), (1,501), (1,502), (2,501),

(2,502), (3,500), (3,502), (4,500), (4,501),...

It's probably worth mentioning that the CAST/CONVERT in the predicate only works when using certain datatypes.

Casting a DATETIME (and apparently FLOAT) to DATE will allow a seek but casting a...

Orlando Colamatteo (12/4/2015)

---

I simply won't try doing anything production-related without SQL Prompt Pro installed. It is by no means free but it's worth every penny. My snippet library is extensive...

You'll note that the vast majority of the expense of the function above is tied to a sort operation (90%). This can be alleviated by adding a persisted computed column...

Something like this perhaps???

CREATE FUNCTION dbo.tfn_LikeColour

(

@Value VARCHAR(8000)

)

RETURNS TABLE WITH SCHEMABINDING AS

RETURN

WITH ColourList AS ( -- replace this section with your Colour table when the time comes.

SELECT

x.Colour

FROM

( VALUES...

Sorry... Missed the requirement to to include Location in the partitioning.

Try the following...

IF OBJECT_ID('tempdb..#Dates', 'U') IS NOT NULL

DROP TABLE #Dates;

CREATE TABLE #Dates (

WeekendDate DATE

);

INSERT #Dates (WeekendDate) VALUES

('11/01/2015'),

('11/08/2015'),

('11/15/2015'),

('11/22/2015'),

('11/29/2015');

IF OBJECT_ID('tempdb..#Product',...

No problem. Keep us posted. 🙂