ChrisM@Work

Forum Replies Created

Viewing 15 posts - 5,596 through 5,610 (of 10,143 total)

RE: Unable to select from table without adding schema name

Michael Valentine Jones (9/21/2012)
kingdonshel (9/21/2012)
I have server Admin password, however even when I choose the AdventureWorks Database from the dropdown and run the below query I get an errror...
^{“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 21, 2012 at 8:59 am

#1540764
RE: Unable to select from table without adding schema name

There's a cautionary note in the CREATE SCHEMA section of BOL;
"Beginning with SQL Server 2005, the behavior of schemas changed. As a result, code that assumes that schemas are equivalent...
^{“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 21, 2012 at 8:34 am

#1540738
RE: Best way to update 1.000.000 rows?!

Can you post the CREATE INDEX script?
If you're updating the whole table, then nonclustered indexes won't help performance and will impede performance if they too require updating.
Batching the update could...
^{“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 21, 2012 at 8:25 am

#1540728
RE: query to count repating alphabet in a string

Big hammer;
DECLARE @asd VARCHAR(20)
SET @asd = 'asdaaaadffa'
SELECT
Letter,
Occurrences = COUNT(*)
FROM (
SELECT Letter = SUBSTRING(@asd,n,1)
FROM (SELECT TOP(LEN(@asd)) n = ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM sys.all_columns) tally
) d
GROUP BY Letter
ORDER...
^{“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 21, 2012 at 7:25 am

#1540682
RE: Best way to update 1.000.000 rows?!

UPDATE StoreProducts SET Stock = Stock + 2
UPDATE ProdutStorages SET Stock = Stock + 2
How many tables are you updating?
^{“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 21, 2012 at 7:16 am

#1540675
RE: List Transactions that Meet Greater Than Criteria

dwain.c (9/21/2012)
egerencher (9/21/2012)
Once again. It worked. GENIUS. OK. Here comes a curve ball. Instead of sum of transamt > 1000 in 2 days, how about...
^{“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 21, 2012 at 4:42 am

#1540574
RE: Error on Subquery

Hunterwood (9/21/2012)
Thanks Chris,
I was too quick and missed the inner group by, which is the most likely to give the error...
/Markus
Hey Markus, no problem - it's always good to have...
^{“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 21, 2012 at 3:00 am

#1540530
RE: Error on Subquery

hoseam (9/21/2012)
Please to resolve this issue.
I have this query
SELECT
(select REB_TAX_RATE from PR_REB_TAX_RATE
where EFF_DATE = (select MAX(EFF_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

September 21, 2012 at 2:18 am

#1540519
RE: List Transactions that Meet Greater Than Criteria

dwain.c (9/20/2012)
....
But one of those SSNs shouldn't be there!
Gah! I hate it when that happens. Here's a new, completely different version;
;WITH
MyTrans AS (
SELECT TransID,...
^{“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 20, 2012 at 9:50 am

#1540194
RE: List Transactions that Meet Greater Than Criteria

dwain.c (9/20/2012)
...
I knew someone would come along that knew what they were doing and best me. 😀
Haha! I had no idea that this query would be any faster when I...
^{“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 20, 2012 at 4:45 am

#1539993
RE: Sum with Multi Category and Group By Acc No ?

Note that the date arithmetic is still messed up - but I'm bored with making corrections to it because you keep throwing them away. The date arithmetic I posted was...
^{“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 20, 2012 at 4:39 am

#1539986
RE: Sum with Multi Category and Group By Acc No ?

chinye2020 (9/20/2012)
ChrisM@Work (9/20/2012)
chinye2020 (9/19/2012)
...
No,Chris, When the Acc No's Open_balance is null Between the date only change the sql query to before 16-09-12...
how to do that?
Use ISNULL or COALESCE 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 20, 2012 at 3:34 am

#1539949
RE: Sum with Multi Category and Group By Acc No ?

laurie-789651 (9/20/2012)
ChrisM@Work (9/19/2012)
Easy - but first, we need to sort out your dates. You do yourself no favours by changing the date arithmetic back to the original each time I...
^{“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 20, 2012 at 3:30 am

#1539948
RE: List Transactions that Meet Greater Than Criteria

Simpler, quicker, same results:
SELECT
t.TransID,
c.SSN,
t.TransDate,
t.Amount
FROM #Customer c
INNER JOIN #TransDtl t
ON t.CustKey = c.CustKey
CROSS APPLY (
SELECT Amount = SUM(ti.Amount)
FROM #TransDtl ti
WHERE t.CustKey = c.CustKey
AND ti.TransDate BETWEEN t.TransDate-1 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

September 20, 2012 at 2:59 am

#1539939
RE: Are the posted questions getting worse?

Sean Lange (9/19/2012)
Brandie Tarvin (9/19/2012)
Lynn Pettis (9/19/2012)
Do you ever feel that no matter how many times you tell someone what the problem may be that they just aren't listening?
Yep.
My current...
^{“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 20, 2012 at 1:56 am

#1539924

Viewing 15 posts - 5,596 through 5,610 (of 10,143 total)