ChrisM@Work

Forum Replies Created

Viewing 15 posts - 1,966 through 1,980 (of 10,143 total)

RE: Cursor stop query at specific time

Too many errors. Avoid writing too much at once without testing.
DROP TABLE #table_1;CREATE TABLE #table_1 (StatementName VARCHAR(500))
INSERT INTO #table_1 (StatementName) VALUES ('Nothing')
DROP TABLE #table_3;CREATE TABLE #table_3 (StatementName VARCHAR(500), Flag CHAR(1))
DECLARE...
^{“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 30, 2015 at 8:50 am

#1836719
RE: Index and Optimiser question.

You have this index definition in your first post:
CREATE NONCLUSTERED INDEX IX_Large_Table_ABDFHJ ON dbo.Large_Table
(A,B,D,F,H,J) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
Why?
^{“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 30, 2015 at 8:37 am

#1836712
RE: Index and Optimiser question.

ben.brugman (10/30/2015)
The Question was:
Can SQL-server use an index and then use the covering index to collect the data?
(And therefore not using the 'main' table).
Answers:
GilaMonster (10/29/2015)
No. Lookups are always to 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

October 30, 2015 at 7:32 am

#1836679
RE: Commercial Software with All Tables as Heaps and Terrible Performance

Eirikur Eiriksson (9/14/2015)
Wayne West (9/14/2015)
Matt Miller (#4) (9/14/2015)
Eirikur Eiriksson (9/14/2015)
Jeff Moden (9/14/2015)
Eirikur Eiriksson (9/13/2015)
Using heap to speed the inserts is like a very bad Credit Card deal, no transaction fees...
^{“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 30, 2015 at 7:01 am

#1836658
RE: Question/Advice needed Sliding Window partitioning range right,what the effecient way to implement/are staging tables needed

Resender (10/30/2015)
Okay yesterday I was in a bit of a panic and made the post Partition Sliding window causing loads of... in a haste without thinking it through,...
^{“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 30, 2015 at 5:04 am

#1836630
RE: Ophaned index

Steve Hurst (10/30/2015)
Two things, first the index does not show against the table in SSMS under the Tables (table_name) Indexes. Second, it came to light when running a script to...
^{“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 30, 2015 at 3:55 am

#1836614
RE: Using Dynamic SQL To Order Column Names

Do you mean like this?
Select Top(10) HotelName, Price, CustomerRating
From dbo.VacationDestinations
where Price <= @price AND CustomerRating = @CustomerRating
Order By
CASE WHEN @ratingWeight >= @priceWeight THEN CustomerRating ELSE Price END DESC,...
^{“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 30, 2015 at 2:54 am

#1836601
RE: SQL2008R2 FIFO Allocation Recursive CTE

A change to the earlier query after mixing up some more data:
SELECT
DeductionID, DeductionAmount, --d.[from], d.[to],
PaymentID, PaymentAmount, --p.[from], p.[to],
DeductionBalance = CASE WHEN d.[to] > p.[to] THEN d.[to] -...
^{“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 29, 2015 at 11:20 am

#1836464
RE: NULL and empty string question...

Luis Cazares (10/29/2015)
g.britton (10/29/2015)
SQL uses three-value logic. So anything = NULL evaluates to False and anything <> NULL also evaluates to FALSE. Also, NULL = NULL is FALSE...
^{“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 29, 2015 at 10:31 am

#1836449
RE: SQL2008R2 FIFO Allocation Recursive CTE

J Livingston SQL (10/29/2015)
Bruceo (10/29/2015)
By the way, how do you format your results like that? I looked through all the shortcuts and could not figure it out!
Is that an image...
^{“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 29, 2015 at 10:26 am

#1836448
RE: Are the posted questions getting worse?

Ed Wagner (10/29/2015)
yb751 (10/29/2015)
I was on my last eye twitch yesterday (so to speak) so I put together this image. (attached)
I was thinking about unleashing it but then I thought...
^{“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 29, 2015 at 9:06 am

#1836418
RE: SQL2008R2 FIFO Allocation Recursive CTE

J Livingston SQL (10/29/2015)
ChrisM@Work (10/29/2015)
Try this - the result of some fiddling around earlier:
-- Set up sample data
DECLARE @Deductions TABLE (DeductionID int IDENTITY(1,1),DeductionAmount money);
INSERT @Deductions (DeductionAmount) VALUES (1000),(200),(50),(600);
DECLARE @Payments 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

October 29, 2015 at 8:14 am

#1836387
RE: SQL2008R2 FIFO Allocation Recursive CTE

You're welcome, thanks for the feedback.
^{“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 29, 2015 at 7:38 am

#1836367
RE: SQL2008R2 FIFO Allocation Recursive CTE

Try this - the result of some fiddling around earlier:
-- Set up sample data
DECLARE @Deductions TABLE (DeductionID int IDENTITY(1,1),DeductionAmount money);
INSERT @Deductions (DeductionAmount) VALUES (1000),(200),(50),(600);
DECLARE @Payments TABLE (PaymentID int IDENTITY(1,1),PaymentAmount money);
INSERT...
^{“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 29, 2015 at 7:01 am

#1836349
RE: CASE WHEN SELECT Help.

dramaqueen (10/29/2015)
Hi ChrisM@Work.
I am still getting the error
Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to float.
The data in A.Value is a VARCHAR and contains Y,...
^{“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 29, 2015 at 6:26 am

#1836332

Viewing 15 posts - 1,966 through 1,980 (of 10,143 total)