ChrisM@Work

Forum Replies Created

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

RE: Are the posted questions getting worse?

Lynn Pettis (2/19/2009)
First, I don't claim to know everything about SQL, but I do feel that I know a little about most of it (Jack of all trades, master 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

February 19, 2009 at 8:50 am

#946805
RE: Distinct going slower than *

1. What happens when you compare like with like?
-- Keeping dupes:
SELECT t.EMP_ID, t.EMPLOYEE_NAME
FROM dbo.IREP_V_DAILY_COUNTS_TERRITORY t
WHERE t.EMP_ID IS NOT NULL
AND t.WORK_DATE BETWEEN '01/01/2009' AND '01/31/2009'
ORDER BY t.EMPLOYEE_NAME
--...
^{“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

February 19, 2009 at 8:42 am

#946790
RE: To find max length of datatype

Looks like Ramesh has this sussed, but what do you get for this, as a matter of interest (I don't have 2k5 to play with right now)...
DECLARE @MyEnormousBelly VARCHAR(max)
SET @MyEnormousBelly...
^{“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

February 19, 2009 at 7:05 am

#946637
RE: Writing out a one column result set as a concatenated string

Sure...read this[/url] and this[/url].
These two articles will show you exactly how it's done and you will almost certainly learn other useful tricks along the way.
^{“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

February 18, 2009 at 9:14 am

#946052
RE: NOT EXISTS vs NOT IN

If you post the sproc, Karthik, there's a chance that someone might figure out why it takes so long to run...
^{“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

February 18, 2009 at 3:51 am

#945776
RE: NOT EXISTS vs NOT IN

karthikeyan (2/17/2009)
Thanks Chris and sqlmaster!
I am facing a starnge problem while executing the below query. if i execute it seperate it took just 1 second to complete the execution. If...
^{“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

February 18, 2009 at 2:27 am

#945736
RE: Removing all charachters after the last '.'

-- If the number of dots is never greater than 3, then this will work:
DECLARE @String varchar(20)
SET @String = '123.45.678.90'
SELECT REPLACE(@String+'~', PARSENAME(@String+'~',1), '')
Result:
123.45.678.
^{“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

February 17, 2009 at 8:25 am

#945237
RE: Help creating SQL query

DROP TABLE #TableA
CREATE TABLE #TableA (Item INT, Value VARCHAR(1))
INSERT INTO #TableA (Item, Value)
SELECT 1, 'A' UNION ALL
SELECT 1, 'A' UNION ALL
SELECT 1, 'B' UNION ALL
SELECT 2, 'A' UNION ALL
SELECT 2,...
^{“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

February 17, 2009 at 8:22 am

#945232
RE: Correlated Sub Query (RBAR WTF?)

What's the result of running the highlighted part?
select A.fldA, A.fldB from tableA A where A.fldKey IN
(
select AA.fldKey from tableA AA inner join tableB B ON
AA.fldKey =...
^{“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

February 17, 2009 at 8:20 am

#945231
RE: Correlated Sub Query (RBAR WTF?)

You don't need tableA bulking up the two subselects:
select A.fldA, A.fldB
from tableA A
where A.fldKey IN
(
select B.fldKey
from tableB B
inner join tableC C ON B.fldChild =...
^{“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

February 17, 2009 at 7:46 am

#945202
RE: Best way to calculate duration from 1 table?

Here's the old-fashioned way:
DROP TABLE #Table
CREATE TABLE #Table (ObjectID INT, ObjectStateID INT, [DateTime] DATETIME)
INSERT INTO #Table (ObjectID, ObjectStateID, [DateTime])
SELECT 1, 1, '2009-02-17 12:10:47.000' UNION ALL -- waiting
SELECT 1, 2, '2009-02-17...
^{“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

February 17, 2009 at 6:40 am

#945138
RE: query to get results from 4 tables

shiva (2/17/2009)
i want shiftcode's of 100 to 103 users between give dates
how to get
plzz help me....
The shiftcode of user 100 is s1, and of user 103 is s2.
Without knowing what...
^{“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

February 17, 2009 at 5:00 am

#945089
RE: NOT EXISTS vs NOT IN

LEFT JOIN:
SELECT f.sec_id, f.symbol
FROM fund f
LEFT JOIN exception e ON e.symbol = f.symbol AND err_cd IN (20,30)
WHERE e.symbol IS 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

February 17, 2009 at 4:19 am

#945068
RE: NOT EXISTS vs NOT IN

Karthik, please can you post your queries for
NOT IN & NOT EXISTS to do this,but it leads to performance isssue.
Cheers
ChrisM
^{“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

February 17, 2009 at 4:02 am

#945057
RE: Removing all charachters after the last '.'

Is there a maximum number of dots?
^{“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

February 17, 2009 at 3:54 am

#945050

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