Viewing 15 posts - 1,336 through 1,350 (of 1,491 total)
As B, C and D are joining to A, you should get the same results from all three queries.
The optimizer may behave differently with different join orders. You need to...
November 24, 2006 at 11:54 am
Gabola71,
Have you managed to get the execution plans for the query before and after COALESCE was applied?
If you are short on time, it would be good just to compare the...
November 24, 2006 at 11:45 am
1. Yes:
'X' + NULL = NULL
while
'X' + ISNULL(NULL, '') = 'X'
or
'X' + COALESCE(NULL, '') = 'X'
etc
2. OK
3. Ouch. Bad design on top of bad design!
November 24, 2006 at 7:03 am
1. As you LEFT JOIN to t2, you need an ISNULL around any mention of t2.criteriadata.
2. Can linenum ever exceed 1?
3. Can key/value pairs ever cross a linenum boundary?
November 24, 2006 at 6:42 am
I would be inclined to:
1. add OPTION (MAXDOP 1) to Farrell's query, as it may be safer.
2. create the temp table/variable first, to reduce schema locking.
Also, why not use the...
November 22, 2006 at 12:32 pm
You could try re-directing the output to the nul console by adding > nul:
select @cmd ='C:\"Program Files (x86)"\"Microsoft SQL Server"\90\DTS\Binn\DTExec.exe /DTS "'
November 22, 2006 at 9:00 am
This seems like a pointless exam question. DEFAULT VALUES may be what you want:
DECLARE @t TABLE
(
TID int IDENTITY NOT NULL
)
INSERT INTO @t DEFAULT VALUES
INSERT INTO @t DEFAULT VALUES
INSERT INTO @t...
November 22, 2006 at 7:40 am
Sergiy,
While appreciating your comments about the join expression and the need for correct indexes, I think the COALESCE functions here may be tricking the optimizer. (Gabola did say that the...
November 17, 2006 at 11:42 am
If tblLinks also has an ID column, then the optimizer may be getting confused about which ID column to use.
With more than one table, it is good practise to use...
November 17, 2006 at 11:27 am
or
DELETE FROM titleauthor
WHERE EXISTS (
SELECT *
FROM titles T
WHERE T.title LIKE '%Straight%'
AND T.title_id = titleauthor.title_id )
November 17, 2006 at 8:59 am
Interesting. It could act as a non-vendor specific JOIN hint.
If you look at the query plans for the original and COALESCE version, is there a difference between the type of...
November 16, 2006 at 11:43 am
-- Test Data
DECLARE @t TABLE
(
StopCol varchar(20) NOT NULL
,other_col char(1) NOT NULL
)
INSERT INTO @t
SELECT '176.7', 'y' UNION ALL
SELECT '4476.7', 'X' UNION ALL
SELECT '176', 'y' UNION ALL
SELECT '4476', 'X'
-- Show test data
SELECT...
November 16, 2006 at 9:08 am
Assuming the PK is (Site, Item), try something like the following:
-- Insert
INSERT INTO preferred_supplier
SELECT SITE, ITEM, PREF_SUPPLIER, [DESCRIPTION]
FROM pref_temp T
WHERE NOT EXISTS (
SELECT *
FROM preferred_supplier S
WHERE S.Site = T.Site AND...
November 16, 2006 at 5:55 am
You need to use CONVERT instead of CAST.
The style will be 101 for US dates or 103 for UK dates.
Ideally you should alter the table so that dates are stored...
November 16, 2006 at 5:19 am
-- Simple; assuming no time component in date and consecutive days
DECLARE @t TABLE
(
tDate datetime NOT NULL
,Client int NOT NULL
,A int NOT NULL
)
INSERT INTO @t
SELECT '20061101', 1, 100 UNION ALL
SELECT '20061102',...
November 16, 2006 at 4:37 am
Viewing 15 posts - 1,336 through 1,350 (of 1,491 total)