ChrisM@Work

Forum Replies Created

Viewing 15 posts - 76 through 90 (of 10,143 total)

Reply To: CHOOSE function returns NULL when it shouldn't
Jonathan AC Roberts wrote:
ChrisM@Work wrote:
Jonathan AC Roberts wrote:
But this one doesn't return NULL, so a bit puzzling to me
```
SELECT CHOOSE((CAST(T.[Rand]* 10 AS INT))%3.00 + 1, 'one', 'two', 'three') Choice,
  ...
```
^{“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 15, 2019 at 7:12 am

#3688792
Reply To: CHOOSE function returns NULL when it shouldn't
Jonathan AC Roberts wrote:
But this one doesn't return NULL, so a bit puzzling to me
```
SELECT CHOOSE((CAST(T.[Rand]* 10 AS INT))%3.00 + 1, 'one', 'two', 'three') Choice,
    ...
```
- This reply was modified 6 years, 8 months ago by ChrisM@Work.
^{“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 14, 2019 at 1:17 pm

#3688482
Reply To: Making Good Use of SQL Server Auditing

Steve Jones - SSC Editor wrote:
Links should be fixed here.

Works for me. Thanks Steve.
^{“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 5, 2019 at 8:06 am

#3668714
Reply To: Making Good Use of SQL Server Auditing
SQLBlimp wrote:
Comments posted to this topic are about the item Making Good Use of SQL Server Auditing.
The link is to a Tony Davis article about Data Compression. [ Now...
- This reply was modified 6 years, 10 months ago by administrator.
- This reply was modified 6 years, 10 months ago by administrator.
^{“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
July 25, 2019 at 10:08 am

#3665981
Reply To: Performance Issue.. and where to start tracking it down?

Your script contains two queries, do you know how much of the total execution time each one takes?
Can you provide an execution plan for each of them, as a .sqlplan...
^{“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

July 3, 2019 at 10:54 am

#3659679
Reply To: Are the posted questions getting worse?

Steve Jones - SSC Editor wrote:
So, I don't want to argue through all this. We had a number of things, not just management, that pushed a release. Quite a few of the items wouldn't...
^{“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

July 1, 2019 at 2:03 pm

#3658892
Reply To: Help needed with query within query
@itsrainman you've coded this:
```
WHERE 
(i.[Despatch Date] IS NULL) 
AND (i.[Cal Date] IS NULL) 
AND (i.[Despatch to subcontractor] IS NOT NULL) 
AND (i.[Return from Subcontractor] IS NOT NULL)...
```
^{“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
June 20, 2019 at 2:40 pm

#3655796
Reply To: Best practice(s) for refreshing data in several tables

Hi Mike
One of our solutions here uses a snapshot of a replicated database to replace a backup/restore process which used to take hours (about 12 of them). We don't talk...
^{“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

June 20, 2019 at 12:40 pm

#3655738
Reply To: Best practice(s) for refreshing data in several tables

Have you looked into database snapshots for this? It would save you a heck of a lot of coding.
^{“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

June 20, 2019 at 9:58 am

#3655653
Reply To: Select from view where not exists in table
```
-- Work with what you can see.
-- Compare the columns from both sides, add to the matching columns list when you've figured out what's going on.
SELECT
T.[CustomerID],
T.[RelatedReferenceID],
T.[RelatedReferenceType],
T.[Due Date],
'#'...
```
^{“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
June 17, 2019 at 12:52 pm

#3654420
Reply To: Pull Restore Date

SQLKicksButt wrote:
I am going to develop a dashboard to monitor various stats about our Testing Environments. One piece of information I want to pull is the date that a refresh...
^{“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

June 14, 2019 at 3:27 pm

#3653861
Reply To: Group by month/year
```
-- Use EOMONTH(H.startdate) instead of datename(MONTH,H.startdate), datepart(YEAR,H.startdate)
-- and remove h.Quantity from the GROUP BY
SELECT
RTRIM(COALESCE (E.PreferredName, E.FirstNames)) + ' ' + E.LastName AS [Name],
sum(H.Quantity) as [Hours],
datename(MONTH,x.YearMonth) as [month],
datepart(YEAR,x.YearMonth)...
```
- This reply was modified 7 years ago by ChrisM@Work.
^{“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
June 12, 2019 at 7:33 am

#3652616
Reply To: First Monday of the Month

How nice it is to see this again - a bunch of folks working the hell out of a problem to come up with their solutions, each one faster and...
^{“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

June 3, 2019 at 1:09 pm

#3649718
Reply To: First Monday of the Month
This looks about right, and if it is, then the start and end dates can be calculated.
```
;WITH SampleDates AS (
SELECT * FROM (VALUES 
(GETDATE()+0),
('2019-01-26'),
('2019-01-27'),
('2019-01-28'),
('2019-01-29'),
('2019-01-30'),
('2019-01-31'),
('2019-02-01'),
('2019-02-23'),
('2019-02-24'),
('2019-02-25'),
('2019-02-26'),
('2019-03-23'),
('2019-03-24'),
('2019-03-25')
) d (thedate)
)

SELECT 
thedate,
z.RealStartDate,
RealEndDate...
```
^{“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
May 31, 2019 at 12:38 pm

#3648952
Reply To: Merge/Split/Switch partition's affect on indexes?

If you can batch up those inserts so that they are taking place under your control every 24 hours rather than at random every few minutes or so, your whole...
^{“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

May 24, 2019 at 3:04 pm

#3647013

Viewing 15 posts - 76 through 90 (of 10,143 total)