ChrisM@Work

Forum Replies Created

Viewing 15 posts - 4,081 through 4,095 (of 10,143 total)

RE: How many columns in INCLUDED Index are 'Too Many' ???

T.Ashish (8/25/2013)
Dear All,
I have a query that takes 22 seconds.
If I create below index then query finishes in 5 seconds, but I'm told that this type of index can't be...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

August 27, 2013 at 6:26 am

#1645141
RE: How to use SELECT query within the WHERE clause

SELECT SUM(Boiler1)
FROM Energy_Centre_Availability eca
WHERE NOT EXISTS (
SELECT 1
FROM Energy_Centre_Boiler_Maintenance_Schedule s
WHERE eca.t_stamp > s.startDate
AND eca.t_stamp < s.endDate
)
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

August 27, 2013 at 4:38 am

#1645103
RE: How to use SELECT query within the WHERE clause

SELECT SUM(Boiler1)
FROM Energy_Centre_Availability eca
INNER JOIN Energy_Centre_Boiler_Maintenance_Schedule s
ON eca.t_stamp > s.startDate
AND eca.t_stamp < s.endDate
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

August 27, 2013 at 3:55 am

#1645092
RE: Comparing row counts and displaying results from greatest row count from a single table

;WITH SampleData AS (
SELECT * FROM (values
('8009874321', 'cars'),
('8009874321', 'Newsales'),
('8009874321', 'Newsales'),
('8009870000', 'Newsales'),
('8009870000', 'BenaSales'),
('8009870000', 'BenaSales'),
('8009870000', 'BenaSales2'),
('8009870000', 'BenaSales2')
) sampleData ([800num], CompanyName))
SELECT [800num], CompanyName
FROM (
SELECT [800num], CompanyName,
dr = DENSE_RANK() OVER(PARTITION BY...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

August 27, 2013 at 3:35 am

#1645087
RE: Are the posted questions getting worse?

Jan Van der Eecken (8/27/2013)
ChrisM@Work (8/27/2013)
The estimate for fitting my kitchen was nearly £3000.
Was that really for the fitting only? Or including cupboards, sink, hub, worktop etc?
Jan - it was...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

August 27, 2013 at 3:33 am

#1645086
RE: Join of Function or Join of View, which one is cost effective?

Why restrict yourself to a view or a function? Why not just write the query?
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

August 27, 2013 at 1:54 am

#1645045
RE: check if value is integer

twin.devil (8/27/2013)
looks like a assignment question to me ... Or he is just so happy to get the answer 😛
Four years on...he's your tutor now.
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

August 27, 2013 at 1:52 am

#1645043
RE: Where Statement

Why are you using IN and not = for these comparisons?
SV00300.CUSTNMBR in (@CUSTNMBR)
or SV00300.LOCATNNM in (@LOCATNNM)
or SV00300.Contract_Number in (@Contract_Number)
If any of these variables are a comma-delimited list, they won't be...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

August 27, 2013 at 1:50 am

#1645042
RE: Are the posted questions getting worse?

Sean Lange (8/23/2013)
SQLRNNR (8/23/2013)
Sean Lange (8/23/2013)
SQLRNNR (8/23/2013)
Koen Verbeeck (8/23/2013)
ChrisM@Work (8/23/2013)
GilaMonster (8/23/2013)
Fal (8/22/2013)
Speaking of fruit. The missus and I are getting a new kitchen in and are trying to finalise...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

August 27, 2013 at 1:18 am

#1645031
RE: Distinct Select

HanShi (8/23/2013)
... A resultset in SQL cannot be aggregated like that and will show all values for each row.
I wouldn't want to bet on that "cannot", HanShi. Perhaps "cannot easily"...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

August 23, 2013 at 5:49 am

#1644349
RE: Repeat dataset by integer value in another table.

From the sample data??
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

August 23, 2013 at 5:37 am

#1644342
RE: Repeat dataset by integer value in another table.

Thanks for posting sample data, Kelvin. What do you want the output to look like?
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

August 23, 2013 at 5:21 am

#1644335
RE: Distinct Select

If your sample data doesn't look anything like your real data, then you are likely to receive queries which don't work when run against your real data.
How about an...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

August 23, 2013 at 5:20 am

#1644333
RE: query returning to many rows

-- try this
SELECT n.*, d.category, d.n
FROM newtable n
INNER JOIN (
SELECT
category,
amended_sw_manufacturer,
amended_product_name,
amended_product_version,
n = COUNT(*)
FROM datalookuptable
WHERE category IN ('Licensable','Non Licensable')
GROUP BY category, amended_sw_manufacturer, amended_product_name, amended_product_version
) d
ON n.softwaremanufacturer...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

August 23, 2013 at 5:09 am

#1644323
RE: Distinct Select

SELECT
p.ProjectName as PName,
man.UserName AS Manager,
dev.UserName AS Developer
FROM @project p
CROSS APPLY (
SELECT STUFF( (
SELECT ', ' + man.UserName
FROM @Manager m
INNER JOIN @User man
ON m.Managerid = man.Userid
WHERE...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

August 23, 2013 at 4:58 am

#1644321

Viewing 15 posts - 4,081 through 4,095 (of 10,143 total)