ChrisM@Work

Forum Replies Created

Viewing 15 posts - 5,476 through 5,490 (of 10,143 total)

RE: Temp table for Performance Impact

TheSQLGuru (10/4/2012)
1) What is the point of this (isn't PRIMARY KEY sufficient by itself and the UNIQUE redundant?):
PRIMARY KEY (ID),
UNIQUE (ID))
2) I can probably count 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

October 4, 2012 at 8:30 am

#1545580
RE: Merge Duplicate values

Do you have another column in your existing table to identify what type of phone number it is? Mobile/work/home etc?
^{“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

October 4, 2012 at 8:08 am

#1545558
RE: Paging data with CTE

PiMané (10/4/2012)
ChrisM@Work (10/4/2012)
SSC has just the article for you, right here[/url]. No need to redesign the wheel just yet 😉
My initial thoughts are right then 🙂
Thx,
Pedro
Reckon so 😉
^{“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

October 4, 2012 at 7:58 am

#1545551
RE: Multipe letters into one

dwain.c (10/4/2012)
Eugene Elutin (10/4/2012)
ChrisM@Work (10/4/2012)
Eugene Elutin (10/4/2012)
ChrisM@Work (10/4/2012)
No undocumented vapourtables, no sort:
SELECT n = a.n+b.n
FROM (VALUES(0),(10),(20),(30),(40),(50),(60),(70),(80),(90))b(n)
CROSS JOIN (VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9),(10))a(n)
😉
No sort? I would suggest to add ORDER BY, otherwise order is not...
^{“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

October 4, 2012 at 7:47 am

#1545537
RE: Multipe letters into one

Eugene Elutin (10/4/2012)
ChrisM@Work (10/4/2012)
Eugene Elutin (10/4/2012)
ChrisM@Work (10/4/2012)
No undocumented vapourtables, no sort:
SELECT n = a.n+b.n
FROM (VALUES(0),(10),(20),(30),(40),(50),(60),(70),(80),(90))b(n)
CROSS JOIN (VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9),(10))a(n)
😉
No sort? I would suggest to add ORDER BY, otherwise order is not guaranteed,...
^{“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

October 4, 2012 at 7:46 am

#1545536
RE: Paging data with CTE

PiMané (10/4/2012)
ChrisM@Work (10/4/2012)
Why, in the second query, do you join the employees table to the CTE? I can't see any reason for it - unless there are dupes on EmpID....
^{“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

October 4, 2012 at 7:30 am

#1545525
RE: Multipe letters into one

Eugene Elutin (10/4/2012)
ChrisM@Work (10/4/2012)
No undocumented vapourtables, no sort:
SELECT n = a.n+b.n
FROM (VALUES(0),(10),(20),(30),(40),(50),(60),(70),(80),(90))b(n)
CROSS JOIN (VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9),(10))a(n)
😉
No sort? I would suggest to add ORDER BY, otherwise order is not guaranteed, so you may...
^{“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

October 4, 2012 at 7:01 am

#1545506
RE: Paging data with CTE

PiMané (10/4/2012)
PiMané (10/4/2012)
ChrisM@Work (10/4/2012)
Post the actual execution plans as .sqlplan attachments. It's difficult to determine anything useful from these figures.
Thanks... here it is.
Pedro
The plans are almost the same except for...
^{“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

October 4, 2012 at 6:57 am

#1545500
RE: Multipe letters into one

No undocumented vapourtables, no sort:
SELECT n = a.n+b.n
FROM (VALUES(0),(10),(20),(30),(40),(50),(60),(70),(80),(90))b(n)
CROSS JOIN (VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9),(10))a(n)
😉
^{“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

October 4, 2012 at 6:50 am

#1545495
RE: Optimizing the data

This has to be homework because it's very simple.
Try ROW_NUMBER() OVER (PARTITION BY something ORDER BY somethingelse)
You will need to set it up as a CTE or derived table in...
^{“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

October 4, 2012 at 6:11 am

#1545474
RE: Help with Merge

This should be close.
MERGE [DBname].[dbo].[Space] AS target
USING [DBname].[dbo].[RawData_Space] AS source
ON target.SPACE_ID = source.SPACE_ID
WHEN MATCHED THEN
...
^{“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

October 4, 2012 at 6:08 am

#1545471
RE: How to use GETDATE() function in sql server to select a column value from table whose date is only 1 year back from today.

maida_rh (10/4/2012)
How to use GETDATE() function in sql server to select a column value from table whose date is only 1 year back from today.
E.g :
Select LTRating ,RHDate from Accounts...
^{“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

October 4, 2012 at 5:38 am

#1545461
RE: Paging data with CTE

Post the actual execution plans as .sqlplan attachments. It's difficult to determine anything useful from these figures.
^{“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

October 4, 2012 at 5:20 am

#1545454
RE: Why does this take so long!

deepakagarwalathome (10/3/2012)
Hi Chris
Thanks for that!I fully understand what you are trying to do with the CTE's. I have run the query with the CTE's.
The problem, however, remains the same, that...
^{“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

October 4, 2012 at 5:13 am

#1545447
RE: Why does this take so long!

deepakagarwalathome (10/2/2012)
Hi ChrisM@Work
Thanks a zillion for your response. That works fine. No problems.
A couple of questions though :-
First, whilst both 'Over Partiion By...' and the derived table sub-query do 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

October 3, 2012 at 9:51 am

#1545034

Viewing 15 posts - 5,476 through 5,490 (of 10,143 total)