ChrisM@Work

Forum Replies Created

Viewing 15 posts - 3,001 through 3,015 (of 10,143 total)

RE: date still showing up as YYYY-MM-DD

winston Smith (8/26/2014)
Im trying to change the date format to DD-MM-YYYY serverwide.
i have changed to british english in the server settings, and my user also has british english set but...
^{“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 26, 2014 at 6:46 am

#1741004
RE: JOIN BIG and SMALL TABLEs

Grant Fritchey (8/26/2014)
Nice Chris, you took away my two comments, on the RIGHT JOIN getting turned into an INNER JOIN and that awful string manipulation against a column. Those were...
^{“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 26, 2014 at 6:04 am

#1740990
RE: Display ALL rows even if no data exists

pwalter83 (8/25/2014)
ChrisM@Work (8/20/2014)
Paul, if you wouldn't mind running through your sample script and correcting the errors which Steve kindly pointed out, I'll take a look later today.
Hi Chris,
The sample script...
^{“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 26, 2014 at 4:27 am

#1740968
RE: JOIN BIG and SMALL TABLEs

tony28 (8/25/2014)
I added second sql plan with query with condition before update statistics..
is this so big different between before and after ?
here looks almost ok
Query 2 works very differently...
^{“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 26, 2014 at 2:21 am

#1740945
RE: JOIN BIG and SMALL TABLEs

tony28 (8/25/2014)
I added second sql plan with query with condition before update statistics..
is this so big different between before and after ?
here looks almost ok
Don't you mean AFTER you...
^{“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 26, 2014 at 2:03 am

#1740941
RE: Question regarding the difference between two different ways to populate a new table in TSQL

Cathy DePaolo (8/22/2014)
I checked the execution plans of each, and they are the same. I am of the opinion that the subquery is not going to improve performance 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

August 22, 2014 at 9:50 am

#1740307
RE: SQL Server JOINS

Eirikur Eiriksson (8/22/2014)
gbritton1 (8/22/2014)
I don't think that's a Cartesian product. If it were, you would have 89 (8*10) rows of ouput.
A CROSS JOIN would give you a Cartesian Product
A...
^{“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 22, 2014 at 9:38 am

#1740305
RE: primary key discussion

Snargables (8/22/2014)
Recently I got into a discussion w/ a coworker over the primary key of a fact table. He wanted to put the primary key on the identity and I...
^{“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 22, 2014 at 9:35 am

#1740303
RE: LEFT JOIN without predicate , still works but what is that?

Jack Corbett (8/22/2014)
I learned this syntax in a session by Itzik Ben-Gan at my first PASS Summit, I believe 2009. So you can probably find more about...
^{“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 22, 2014 at 9:28 am

#1740298
RE: Query Help

SELECT UserName, Drink, Value, CreatedDate
FROM ( -- d
SELECT UserName, Drink, Value, CreatedDate,
rn = ROW_NUMBER() OVER(PARTITION BY grp ORDER BY CreatedDate DESC)
FROM #TEMP t
OUTER APPLY ( -- ou
SELECT TOP...
^{“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 22, 2014 at 7:46 am

#1740224
RE: Parameter Sniffing

It would be interesting to see the Actual execution plans for them...
^{“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 22, 2014 at 7:07 am

#1740198
RE: Compare two columns

Brandie Tarvin (8/22/2014)
ChrisM@Work (8/22/2014)
Brandie Tarvin (8/22/2014)
Chris,
Why the STUFF() and the XML?
It's concatenation of values from different rows of the same column, Brandie.
Split up the email addresses onto different rows,...
^{“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 22, 2014 at 6:58 am

#1740192
RE: Compare two columns

Brandie Tarvin (8/22/2014)
Chris,
Why the STUFF() and the XML?
It's concatenation of values from different rows of the same column, Brandie.
Split up the email addresses onto different rows, eliminate any matches,...
^{“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 22, 2014 at 6:18 am

#1740169
RE: Compare two columns

WITH DedupedData AS (
SELECT nr, [Address],
Alias = CASE
WHEN (x1.Address_Name = x3.Alias_Name AND x1.Address_Domain IN (Alias_Domain1,Alias_Domain2)) THEN NULL
ELSE Item END
FROM @info-2
CROSS APPLY (
SELECT
Address_Name = MAX(CASE WHEN...
^{“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 22, 2014 at 6:07 am

#1740164
RE: Return records that only match set criteria in various tables

CREATE TABLE #CUSTOMERS (CustomerID INT,CustomerName VARCHAR(20))
INSERT INTO #CUSTOMERS (CustomerID,CustomerName) VALUES
(100001,'Mr J Bloggs'),
(100002,'Mr J Smith')
CREATE TABLE #POLICIES (PolicyID INT,PolicyTypeID INT,CustomerID INT)
INSERT INTO #POLICIES (PolicyID,PolicyTypeID,CustomerID) VALUES
(100001,100001,100001),
(100002,100002,100001),
(100003,100003,100001),
(100004,100001,100002),
(100005,100002,100002)
CREATE TABLE #POLICYTYPES (PolicyTypeID INT,PolTypeName VARCHAR(20),ProviderID...
^{“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 22, 2014 at 4:46 am

#1740148

Viewing 15 posts - 3,001 through 3,015 (of 10,143 total)