ChrisM@Work

Forum Replies Created

Viewing 15 posts - 1,036 through 1,050 (of 10,143 total)

RE: The inline TVF

Luis Cazares (12/15/2016)
TomThomson (12/15/2016)
The quasi-BNF for inline TVFs at the CREATE FUNCTION (Transact-SQL) page is quite amusing: it gives you four options for the parentheses: have both, have neither, have...
^{“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

December 15, 2016 at 10:46 am

#1918248
RE: The inline TVF

Luis Cazares (12/15/2016)
ChrisM@Work (12/15/2016)
Luis Cazares (12/15/2016)
I've never written an iTVF using parenthesis around the query. However, the other answers were completely wrong and the correct answer states that it can...
^{“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

December 15, 2016 at 8:09 am

#1918191
RE: The inline TVF

Luis Cazares (12/15/2016)
I've never written an iTVF using parenthesis around the query. However, the other answers were completely wrong and the correct answer states that it can not that it...
^{“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

December 15, 2016 at 7:50 am

#1918182
RE: Error converting data type nvarchar to float

sgmunson (12/15/2016)
ChrisM@Work (12/15/2016)
yuvipoy (12/15/2016)
Hi
Error converting data type nvarchar to float is returning
Select Convert(mycolumn as float) from mytable;
mycolumn is nvarchar(100)
but my destination table is float , so i am...
^{“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

December 15, 2016 at 6:52 am

#1918169
RE: Error converting data type nvarchar to float

yuvipoy (12/15/2016)
Hi
Error converting data type nvarchar to float is returning
Select Convert(mycolumn as float) from mytable;
mycolumn is nvarchar(100)
but my destination table is float , so i am converting to...
^{“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

December 15, 2016 at 6:47 am

#1918164
RE: Let the optimizer do it's thing -- wrong

Indianrock (12/14/2016)
Yes ORM-generated sql. Ours is a version called Domain Objects written by a former developer here. Others include Entity Framework and Hibernate. That sql code...
^{“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

December 15, 2016 at 6:29 am

#1918159
RE: Query tuning - Help needed

Shekhu (12/15/2016)
Your query is spending maximum time in Sorting the data. I checked your view and I see it uses "TOP 100 PERCENT " ... If you are getting 100...
^{“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

December 15, 2016 at 6:23 am

#1918155
RE: Query tuning - Help needed

ffarouqi (12/14/2016)
Hi all,
I've been looking into this query and trying to see if there is any better way to write or enhance performance on these queries. Client states that these...
^{“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

December 15, 2016 at 6:20 am

#1918154
RE: The inline TVF

The "correct answer" (This can return a table from a query inside of a RETURN statement surrounded by parenthesis) is sneaky. Using ssc's most popular inline function as an example,...
^{“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

December 15, 2016 at 5:37 am

#1918145
RE: Mysteriously missing row

Also:
SELECT
ra.AgreementSerial,
0-SUM( -- check that your brackets are unambiguous
(A.[Plastics Turnover value] * x.Plastics)
- (A.[Heating Spares Turnover Value] * x.HeatingSpares)
- (A.[Boiler Turnover Value] * x.Boilers)
- (A.[Copper Turnover Value]...
^{“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

December 15, 2016 at 5:02 am

#1918140
RE: Mysteriously missing row

SELECT
ra.AgreementSerial,
SUM(- (((((PlasticsTV - HeatingSparesTV) - BoilerTV) - CopperTV) - OtherTV) - RadiatorsTV) - RenewablesTV) AS QualifyingSpend,
SUM(A.[Boiler Turnover Value]) AS BoilerTurnover, SUM(A.[Boilers Margin £]) AS BoilerMargin,
SUM(A.[Copper Turnover...
^{“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

December 15, 2016 at 5:01 am

#1918139
RE: Mysteriously missing row

Sensible formatting throws up a difference:
SELECT
ra.AgreementSerial,
SUM(- (((((A.[Plastics Turnover value] * ISNULL(ra.Plastics, 0) -- [*]
- A.[Heating Spares Turnover Value] * ISNULL(ra.HeatingSpares, 0)) - A.[Boiler Turnover Value] * ISNULL(ra.Boilers,...
^{“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

December 15, 2016 at 4:18 am

#1918122
RE: conditional where clause

Shekhu (12/15/2016)
Check the below code, ISNULL function should help to resolve this.
declare @var INT
select top 10 * from tableName where ColumName= ISNULL(@Var,tableName)
Actually it won't, it's the same problem. Read...
^{“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

December 15, 2016 at 2:55 am

#1918106
RE: GROUP BY in SQL Server 2014

zouzou (12/13/2016)
there you go
Comment out the left join, it's completely redundant - as you can see from the plan, it's optimised out anyway.
Can we please have actual rather than estimated...
^{“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

December 13, 2016 at 9:01 am

#1917648
RE: trying to get rid of clustered index scan

tcronin 95651 (12/13/2016)
Appreciate all the input, it did get rid of the scan, however below are the stats, top set is with index scan below is the seek.
(71 row(s) affected)
Table...
^{“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

December 13, 2016 at 8:20 am

#1917620

Viewing 15 posts - 1,036 through 1,050 (of 10,143 total)