ChrisM@Work

Forum Replies Created

Viewing 15 posts - 2,776 through 2,790 (of 10,143 total)

RE: IF EXISTS vs @@ROWCOUNT

IMHO the option "Neither" is correct, because MERGE is likely to outperform options 1 and 2, both of which require a test in some or all cases. Since the poster...
^{“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

November 26, 2014 at 2:20 am

#1761005
RE: Error in CASE statement

Eirikur Eiriksson (11/26/2014)
Quick suggestion, combine the conditionals, consider this example which does the same as your code
😎
SELECT
CASE
...
^{“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

November 26, 2014 at 1:38 am

#1760994
RE: Are the posted questions getting worse?

Evil Kraig F (11/24/2014)
Jeff Moden (11/24/2014)
Eirikur Eiriksson (11/24/2014)
Just a quick question to "the thread", am I missing something or is XML getting more popular? Just noticed that this topic on...
^{“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

November 25, 2014 at 6:45 am

#1760776
RE: "Order" between JOIN and APPLY

sgmunson (11/21/2014)
Can I ask what might seem like a dumb question? Why is anyone trying so hard to write this query in such a way as to make...
^{“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

November 21, 2014 at 7:37 am

#1760130
RE: "Order" between JOIN and APPLY

Thanks Serg, my bad:
SELECT a.id
,T1.F.value('(//Node1)[1]', 'varchar(100)') x
--,T2.F.value('(//Node2)[1]', 'varchar(100)') y
FROM #Table1 AS a
CROSS APPLY a.data.nodes('//Node1') AS T1(F) -- part 1
LEFT JOIN #Table1 b
CROSS APPLY a.data.nodes('//Node2') AS T2(F) -- part 2
ON...
^{“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

November 21, 2014 at 7:02 am

#1760113
RE: Convert exponential Number to non-exponential numbers...

The restrictions of the FLOAT datatype are specified here. The numbers you've posted look ok, however I'd recommend you check your data for elements which may be out of range.
^{“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

November 21, 2014 at 6:00 am

#1760086
RE: "Order" between JOIN and APPLY

If you look again at the original query:
...
FROM
Table1 AS a
CROSS APPLY data.nodes('.../Node1') AS T1(F) -- part 1
LEFT JOIN Table1 AS b
CROSS APPLY data.nodes('.../Node2') AS T2(F)...
^{“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

November 21, 2014 at 4:35 am

#1760063
RE: Convert exponential Number to non-exponential numbers...

manifbest (11/20/2014)
Hello All,
I want to find out the standard deviation from the following value but it gives me error because i am using STDEV function from the MSSQL.
My...
^{“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

November 20, 2014 at 5:56 am

#1759803
RE: "Order" between JOIN and APPLY

sgmunson (11/19/2014)
ChrisM@Work (11/19/2014)
Steve - (F) is a column name assignment:
SELECT * FROM (SELECT 1) d (ColumnName)
Ahhh, ... ok... I try really hard NOT to use that kind of...
^{“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

November 19, 2014 at 7:14 am

#1759521
RE: "Order" between JOIN and APPLY

Steve - (F) is a column name assignment:
SELECT * FROM (SELECT 1) d (ColumnName)
^{“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

November 19, 2014 at 6:43 am

#1759515
RE: SQL newbie: How to extract all columns from multiple tables and save to a new table

Ashish Dutt (11/18/2014)
...Is there any other way of accomplishing this task in lesser amount of query execution time?
Please suggest. Eagerly awaiting your conducive response.
Yes there is. You mentioned that two...
^{“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

November 19, 2014 at 2:10 am

#1759463
RE: master.mdb .trc files filling up system volume

Stored procedures marked to run on startup:
SELECT [name] FROM master.dbo.sysobjects WHERE type = 'P' AND OBJECTPROPERTY(id, 'ExecIsStartUp') = 1;
^{“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

November 18, 2014 at 8:28 am

#1759307
RE: Combining costs from across tables to populate temporary table

That's horrible code. A cursor loop is much the same as a while loop, you won't gain anything by changing your existing loop into a different one. What you really...
^{“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

November 18, 2014 at 6:22 am

#1759247
RE: cte performance issue

Complex queries run the risk of failing to obtain a satisfactory execution plan, as the optimiser doesn't have sufficient time to explore all possibilities. You'll get a plan so the...
^{“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

November 14, 2014 at 7:25 am

#1758572
RE: date

-- day 0 ('19000101') is a monday
SELECT DATENAME(dw,cast('19000101' as date)) -- monday
SELECT DATEDIFF(day,0,GETDATE()) -- 41952 (tuesday 11th November)
SELECT 41952 % 7 -- = 1 for today (tuesday)
SELECT 41951 % 7...
^{“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

November 11, 2014 at 6:22 am

#1757667

Viewing 15 posts - 2,776 through 2,790 (of 10,143 total)