ChrisM@Work

Forum Replies Created

Viewing 15 posts - 2,176 through 2,190 (of 10,143 total)

RE: Help with NOT EXISTS logic

doodlingdba (9/10/2015)
Thanks Chris - I will look through the FOR XML syntax and see if I can figure it out. All new stuff for me but interesting! Thanks for taking...
^{“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

September 10, 2015 at 9:09 am

#1825711
RE: charindex question

http://sqlblog.com/blogs/aaron_bertrand/archive/2009/10/06/bad-habits-to-kick-order-by-ordinal.aspx
^{“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

September 10, 2015 at 7:27 am

#1825658
RE: Help to complete in statement

adonetok (9/10/2015)
In a store procedure will get value from application for member ID like
'12456', '88997'...
How to complete store procedure in where statement below
where [MEMBER ID] IN ( @myID )
@myID =...
^{“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

September 10, 2015 at 7:25 am

#1825657
RE: Help with NOT EXISTS logic

doodlingdba (9/10/2015)
This is awesome, thanks. I haven't ever used CROSS APPLY so good to see it in action. Now I need to figure out how to only print a message...
^{“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

September 10, 2015 at 3:23 am

#1825622
RE: Having huge number of condition in a single query will screw the performance ?

squvi.87 (9/10/2015)
ChrisM@Work (9/10/2015)
Since you're going to get a table scan with the WHERE clause containing non-SARGable predicates, remove it altogether. Also, include a coded alternative in your CASEs as 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

September 10, 2015 at 2:46 am

#1825618
RE: Header and details query

Edvard Korsbæk (9/10/2015)
It will take me app. 15 minutes to create a program, that makes the report - OK
So, I use the Jack as you descibe it.
I am in 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

September 10, 2015 at 2:41 am

#1825616
RE: Help with NOT EXISTS logic

SELECT
SP.Name,
m.permission_name
FROM sys.server_principals SP
CROSS APPLY (VALUES ('VIEW ANY DEFINITION'),('VIEW ANY DATABASE'),('VIEW SERVER STATE')) m (permission_name)
WHERE SP.name = 'something'
AND NOT EXISTS (
SELECT 1
FROM sys.server_permissions SPM
WHERE SPM.grantee_principal_id =...
^{“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

September 10, 2015 at 2:38 am

#1825615
RE: Header and details query

Yes it's possible to do, but it's a little like digging a big hole underneath your car so you can get a flat tyre off and put a fresh tyre...
^{“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

September 10, 2015 at 2:14 am

#1825612
RE: Having huge number of condition in a single query will screw the performance ?

Since you're going to get a table scan with the WHERE clause containing non-SARGable predicates, remove it altogether. Also, include a coded alternative in your CASEs as the default is...
^{“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

September 10, 2015 at 1:52 am

#1825611
RE: Only one record from table with multiple records

SELECT
BD.ID
,x.MyFilter
,BD.F1
,BD.F2
,BD.F3
FROM BASE_DATA BD
CROSS APPLY (
SELECT MyFilter = CASE
WHEN BD.F1 = 0 AND BD.F2 = 1 AND BD.F3 = 0 THEN 'F2 only'
WHEN BD.F1 =...
^{“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

September 7, 2015 at 1:29 am

#1824903
RE: t-sql help with grouping

VD (9/3/2015)
Sweet! I tried that approach with the order by testdate ACS and of course the grouping was wrong. I will figure out the regrouping with dense_rank()!
thanks 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

September 3, 2015 at 9:13 am

#1824341
RE: SQL syntax help - this should be simple

mw112009 (9/3/2015)
I need the sql statement that meets the following
I need a list of memberids who meet the following criteria
Select memberid, measureid from numerator
1. There should be atleast 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

September 3, 2015 at 9:12 am

#1824340
RE: Covering date error

tt-615680 (9/3/2015)
I'm sorry if I'm not being clear but I can only see what I've already sent you, basically, when I run the following:
-- 9 - Populate the #ParsedFileList 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

September 3, 2015 at 9:03 am

#1824334
RE: Getting error on 'Gail Shaw' script!!

John Mitchell-245523 (9/3/2015)
It's got to be TextData, hasn't it? That's the only string or binary column that's being inserted. Looks like you've captured events in your trace 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

September 3, 2015 at 8:19 am

#1824310
RE: t-sql help with grouping

Thanks for the sample data - what a treat 🙂
Have a play with this. To "ice the cake", use DENSE_RANK() to renumber the groups. Ask if you're unsure but 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

September 3, 2015 at 8:17 am

#1824307

Viewing 15 posts - 2,176 through 2,190 (of 10,143 total)