Mixing
Missing Rows when running SELECT in SQL Server
I have a simple select statement. It's basically 2 CTE's, one includes a ROW_NUMBER() OVER (PARTITION BY, then a join from these into 4 other tables. No functions or anything unusual.
WITH Safety_Check_CTE AS
(
SELECT
Fact_Unit_Safety_Checks_Wkey,
ROW_NUMBER() OVER (PARTITION BY [Dim_Unit_Wkey], [Dim_Safety_Check_Type_Wkey]
ORDER BY [Dim_Safety_Check_Date_Wkey] DESC) AS Check_No
FROM
[Pitches].[Fact_Unit_Safety_Checks]
), Last_Safety_Check_CTE AS
(
SELECT
Fact_Unit_Safety_Checks_Wkey
FROM
Safety_Check_CTE
WHERE
Check_No = 1
)
SELECT
COUNT(*)
FROM
Last_Safety_Check_CTE lc
JOIN
Pitches.Fact_Unit_Safety_Checks f ON lc.Fact_Unit_Safety_Checks_Wkey = f.Fact_Unit_Safety_Checks_Wkey
JOIN
DIM.Dim_Unit u ON f.Dim_Unit_Wkey = u.Dim_Unit_Wkey
JOIN
DIM.Dim_Safety_Check_Type t ON f.Dim_Safety_Check_Type_Wkey = t.Dim_Safety_Check_Type_Wkey
JOIN
DIM.Dim_Date d ON f.Dim_Safety_Check_Date_Wkey = d.Dim_Date_Wkey
WHERE
f.Safety_Check_Certificate_No IN ('GP/KB11007') --option (maxdop 1)
Sometimes it returns 0, 1 or 2 rows. The result should obviously be consistent.
I have ran a profile trace whilst replicating the issue and my session was the only one in the database.
I have compared the Actual execution plans and they are both the same, except the final hash match returns the differing number of rows.
I cannot replicate if I use MAXDOP 0.
sql-server tsql sql-execution-plan edited Nov 19 '18 at 14:34
paparazzo
37.5k1673136
asked Nov 19 '18 at 14:24
Mike Fleming
314
add a comment |
I have a simple select statement. It's basically 2 CTE's, one includes a ROW_NUMBER() OVER (PARTITION BY, then a join from these into 4 other tables. No functions or anything unusual.
WITH Safety_Check_CTE AS
(
SELECT
Fact_Unit_Safety_Checks_Wkey,
ROW_NUMBER() OVER (PARTITION BY [Dim_Unit_Wkey], [Dim_Safety_Check_Type_Wkey]
ORDER BY [Dim_Safety_Check_Date_Wkey] DESC) AS Check_No
FROM
[Pitches].[Fact_Unit_Safety_Checks]
), Last_Safety_Check_CTE AS
(
SELECT
Fact_Unit_Safety_Checks_Wkey
FROM
Safety_Check_CTE
WHERE
Check_No = 1
)
SELECT
COUNT(*)
FROM
Last_Safety_Check_CTE lc
JOIN
Pitches.Fact_Unit_Safety_Checks f ON lc.Fact_Unit_Safety_Checks_Wkey = f.Fact_Unit_Safety_Checks_Wkey
JOIN
DIM.Dim_Unit u ON f.Dim_Unit_Wkey = u.Dim_Unit_Wkey
JOIN
DIM.Dim_Safety_Check_Type t ON f.Dim_Safety_Check_Type_Wkey = t.Dim_Safety_Check_Type_Wkey
JOIN
DIM.Dim_Date d ON f.Dim_Safety_Check_Date_Wkey = d.Dim_Date_Wkey
WHERE
f.Safety_Check_Certificate_No IN ('GP/KB11007') --option (maxdop 1)
Sometimes it returns 0, 1 or 2 rows. The result should obviously be consistent.
I have ran a profile trace whilst replicating the issue and my session was the only one in the database.
I have compared the Actual execution plans and they are both the same, except the final hash match returns the differing number of rows.
I cannot replicate if I use MAXDOP 0.
sql-server tsql sql-execution-plan edited Nov 19 '18 at 14:34
paparazzo
37.5k1673136
asked Nov 19 '18 at 14:24
Mike Fleming
314
Please edit the question and specify the SQL Server version + Service Packs installed. Runselect @@VERSIONif needed. And see here for an (old) very similar issue regarding MAXDOP: sqlteam.com/forums/topic.asp?TOPIC_ID=156173
– Peter B
Nov 19 '18 at 14:35
2
My guess is ORDER BY [Dim_Safety_Check_Date_Wkey] is not deterministic.
– paparazzo
Nov 19 '18 at 14:47
Microsoft SQL Server 2016 (SP2-CU3) (KB4458871) - 13.0.5216.0 (X64) Sep 13 2018 22:16:01 Copyright (c) Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2012 R2 Standard 6.3 <X64> (Build 9600: ) (Hypervisor)
– Mike Fleming
Nov 19 '18 at 14:53
Hi Paparazzo, that looks correct. Thanks
– Mike Fleming
Nov 19 '18 at 15:04
add a comment |
I have a simple select statement. It's basically 2 CTE's, one includes a ROW_NUMBER() OVER (PARTITION BY, then a join from these into 4 other tables. No functions or anything unusual.
WITH Safety_Check_CTE AS
(
SELECT
Fact_Unit_Safety_Checks_Wkey,
ROW_NUMBER() OVER (PARTITION BY [Dim_Unit_Wkey], [Dim_Safety_Check_Type_Wkey]
ORDER BY [Dim_Safety_Check_Date_Wkey] DESC) AS Check_No
FROM
[Pitches].[Fact_Unit_Safety_Checks]
), Last_Safety_Check_CTE AS
(
SELECT
Fact_Unit_Safety_Checks_Wkey
FROM
Safety_Check_CTE
WHERE
Check_No = 1
)
SELECT
COUNT(*)
FROM
Last_Safety_Check_CTE lc
JOIN
Pitches.Fact_Unit_Safety_Checks f ON lc.Fact_Unit_Safety_Checks_Wkey = f.Fact_Unit_Safety_Checks_Wkey
JOIN
DIM.Dim_Unit u ON f.Dim_Unit_Wkey = u.Dim_Unit_Wkey
JOIN
DIM.Dim_Safety_Check_Type t ON f.Dim_Safety_Check_Type_Wkey = t.Dim_Safety_Check_Type_Wkey
JOIN
DIM.Dim_Date d ON f.Dim_Safety_Check_Date_Wkey = d.Dim_Date_Wkey
WHERE
f.Safety_Check_Certificate_No IN ('GP/KB11007') --option (maxdop 1)
Sometimes it returns 0, 1 or 2 rows. The result should obviously be consistent.
I have ran a profile trace whilst replicating the issue and my session was the only one in the database.
I have compared the Actual execution plans and they are both the same, except the final hash match returns the differing number of rows.
I cannot replicate if I use MAXDOP 0.
sql-server tsql sql-execution-plan edited Nov 19 '18 at 14:34
paparazzo
37.5k1673136
asked Nov 19 '18 at 14:24
Mike Fleming
314
I have a simple select statement. It's basically 2 CTE's, one includes a ROW_NUMBER() OVER (PARTITION BY, then a join from these into 4 other tables. No functions or anything unusual.
WITH Safety_Check_CTE AS
(
SELECT
Fact_Unit_Safety_Checks_Wkey,
ROW_NUMBER() OVER (PARTITION BY [Dim_Unit_Wkey], [Dim_Safety_Check_Type_Wkey]
ORDER BY [Dim_Safety_Check_Date_Wkey] DESC) AS Check_No
FROM
[Pitches].[Fact_Unit_Safety_Checks]
), Last_Safety_Check_CTE AS
(
SELECT
Fact_Unit_Safety_Checks_Wkey
FROM
Safety_Check_CTE
WHERE
Check_No = 1
)
SELECT
COUNT(*)
FROM
Last_Safety_Check_CTE lc
JOIN
Pitches.Fact_Unit_Safety_Checks f ON lc.Fact_Unit_Safety_Checks_Wkey = f.Fact_Unit_Safety_Checks_Wkey
JOIN
DIM.Dim_Unit u ON f.Dim_Unit_Wkey = u.Dim_Unit_Wkey
JOIN
DIM.Dim_Safety_Check_Type t ON f.Dim_Safety_Check_Type_Wkey = t.Dim_Safety_Check_Type_Wkey
JOIN
DIM.Dim_Date d ON f.Dim_Safety_Check_Date_Wkey = d.Dim_Date_Wkey
WHERE
f.Safety_Check_Certificate_No IN ('GP/KB11007') --option (maxdop 1)
Sometimes it returns 0, 1 or 2 rows. The result should obviously be consistent.
I have ran a profile trace whilst replicating the issue and my session was the only one in the database.
I have compared the Actual execution plans and they are both the same, except the final hash match returns the differing number of rows.
I cannot replicate if I use MAXDOP 0.
sql-server tsql sql-execution-plan sql-server tsql sql-execution-plan edited Nov 19 '18 at 14:34
paparazzo
37.5k1673136
asked Nov 19 '18 at 14:24
Mike Fleming
314
edited Nov 19 '18 at 14:34
paparazzo
37.5k1673136
asked Nov 19 '18 at 14:24
Mike Fleming
314
edited Nov 19 '18 at 14:34
paparazzo
37.5k1673136
edited Nov 19 '18 at 14:34
paparazzo
37.5k1673136
edited Nov 19 '18 at 14:34
paparazzo
37.5k1673136
37.5k1673136
asked Nov 19 '18 at 14:24
Mike Fleming
314
asked Nov 19 '18 at 14:24
Mike Fleming
314
asked Nov 19 '18 at 14:24
Mike Fleming
314
314
Please edit the question and specify the SQL Server version + Service Packs installed. Runselect @@VERSIONif needed. And see here for an (old) very similar issue regarding MAXDOP: sqlteam.com/forums/topic.asp?TOPIC_ID=156173
– Peter B
Nov 19 '18 at 14:35
2
My guess is ORDER BY [Dim_Safety_Check_Date_Wkey] is not deterministic.
– paparazzo
Nov 19 '18 at 14:47
Microsoft SQL Server 2016 (SP2-CU3) (KB4458871) - 13.0.5216.0 (X64) Sep 13 2018 22:16:01 Copyright (c) Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2012 R2 Standard 6.3 <X64> (Build 9600: ) (Hypervisor)
– Mike Fleming
Nov 19 '18 at 14:53
Hi Paparazzo, that looks correct. Thanks
– Mike Fleming
Nov 19 '18 at 15:04
add a comment |
Please edit the question and specify the SQL Server version + Service Packs installed. Runselect @@VERSIONif needed. And see here for an (old) very similar issue regarding MAXDOP: sqlteam.com/forums/topic.asp?TOPIC_ID=156173
– Peter B
Nov 19 '18 at 14:35
2
My guess is ORDER BY [Dim_Safety_Check_Date_Wkey] is not deterministic.
– paparazzo
Nov 19 '18 at 14:47
Microsoft SQL Server 2016 (SP2-CU3) (KB4458871) - 13.0.5216.0 (X64) Sep 13 2018 22:16:01 Copyright (c) Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2012 R2 Standard 6.3 <X64> (Build 9600: ) (Hypervisor)
– Mike Fleming
Nov 19 '18 at 14:53
Hi Paparazzo, that looks correct. Thanks
– Mike Fleming
Nov 19 '18 at 15:04
Please edit the question and specify the SQL Server version + Service Packs installed. Run
select @@VERSION if needed. And see here for an (old) very similar issue regarding MAXDOP: sqlteam.com/forums/topic.asp?TOPIC_ID=156173– Peter B
Nov 19 '18 at 14:35
Please edit the question and specify the SQL Server version + Service Packs installed. Run
select @@VERSION if needed. And see here for an (old) very similar issue regarding MAXDOP: sqlteam.com/forums/topic.asp?TOPIC_ID=156173– Peter B
Nov 19 '18 at 14:35
2
2
My guess is ORDER BY [Dim_Safety_Check_Date_Wkey] is not deterministic.
– paparazzo
Nov 19 '18 at 14:47
My guess is ORDER BY [Dim_Safety_Check_Date_Wkey] is not deterministic.
– paparazzo
Nov 19 '18 at 14:47
Microsoft SQL Server 2016 (SP2-CU3) (KB4458871) - 13.0.5216.0 (X64) Sep 13 2018 22:16:01 Copyright (c) Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2012 R2 Standard 6.3 <X64> (Build 9600: ) (Hypervisor)
– Mike Fleming
Nov 19 '18 at 14:53
Microsoft SQL Server 2016 (SP2-CU3) (KB4458871) - 13.0.5216.0 (X64) Sep 13 2018 22:16:01 Copyright (c) Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2012 R2 Standard 6.3 <X64> (Build 9600: ) (Hypervisor)
– Mike Fleming
Nov 19 '18 at 14:53
Hi Paparazzo, that looks correct. Thanks
– Mike Fleming
Nov 19 '18 at 15:04
Hi Paparazzo, that looks correct. Thanks
– Mike Fleming
Nov 19 '18 at 15:04
add a comment |
2 Answers
2
active
oldest
votes
In case you use my comment as the answer.
My guess is ORDER BY [Dim_Safety_Check_Date_Wkey] is not deterministic.
answered Nov 19 '18 at 15:08
paparazzo
37.5k1673136
add a comment |
In the CTE's you are finding the [Fact_Unit_Safety_Checks_Wkey] that's associated with the most resent row for any given [Dim_Unit_Wkey], [Dim_Safety_Check_Type_Wkey] combination... With no regard for weather or not [Safety_Check_Certificate_No] is equal to 'GP/KB11007'.
Then, in the outer query, you are filtering results based on [Safety_Check_Certificate_No] = 'GP/KB11007'.
So, unless the most recent [Fact_Unit_Safety_Checks_Wkey] happens to have [Safety_Check_Certificate_No] = 'GP/KB11007', the data is going to be filtered out.
answered Nov 19 '18 at 15:17
Jason A. Long
3,7701412
If the most recent row is repeatable then the count should repeat.
– paparazzo
Nov 19 '18 at 15:44
@paparazzo - Yes, assuming that there are no changes to the underlying data, the query should yield repeatable results. But, I don't see anything in the OP that would indicate that is the case.
– Jason A. Long
Nov 19 '18 at 16:20
See my answer. If the OP states results should be the same then I trust they are not changing data.
– paparazzo
Nov 19 '18 at 16:22
I saw your answer. A unique set of date values will always sort in the same order. That said, it's entirely possible that that the same date key is exists on multiple rows, essentially creating ties and those ties could be getting broken differently with each execution. Assuming that's the case, my answer still applies. As far as trusting the what the OP is or isn't doing... I don't take anything for granted.
– Jason A. Long
Nov 19 '18 at 16:42
Not the same. OP did comment I was correct. Good day. I not not vote you down.
– paparazzo
Nov 19 '18 at 16:45
|
show 1 more comment
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53376677%2fmissing-rows-when-running-select-in-sql-server%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
In case you use my comment as the answer.
My guess is ORDER BY [Dim_Safety_Check_Date_Wkey] is not deterministic.
answered Nov 19 '18 at 15:08
paparazzo
37.5k1673136
add a comment |
In case you use my comment as the answer.
My guess is ORDER BY [Dim_Safety_Check_Date_Wkey] is not deterministic.
answered Nov 19 '18 at 15:08
paparazzo
37.5k1673136
add a comment |
In case you use my comment as the answer.
My guess is ORDER BY [Dim_Safety_Check_Date_Wkey] is not deterministic.
answered Nov 19 '18 at 15:08
paparazzo
37.5k1673136
In case you use my comment as the answer.
My guess is ORDER BY [Dim_Safety_Check_Date_Wkey] is not deterministic.
answered Nov 19 '18 at 15:08
paparazzo
37.5k1673136
answered Nov 19 '18 at 15:08
paparazzo
37.5k1673136
answered Nov 19 '18 at 15:08
paparazzo
37.5k1673136
answered Nov 19 '18 at 15:08
paparazzo
37.5k1673136
37.5k1673136
add a comment |
add a comment |
In the CTE's you are finding the [Fact_Unit_Safety_Checks_Wkey] that's associated with the most resent row for any given [Dim_Unit_Wkey], [Dim_Safety_Check_Type_Wkey] combination... With no regard for weather or not [Safety_Check_Certificate_No] is equal to 'GP/KB11007'.
Then, in the outer query, you are filtering results based on [Safety_Check_Certificate_No] = 'GP/KB11007'.
So, unless the most recent [Fact_Unit_Safety_Checks_Wkey] happens to have [Safety_Check_Certificate_No] = 'GP/KB11007', the data is going to be filtered out.
answered Nov 19 '18 at 15:17
Jason A. Long
3,7701412
If the most recent row is repeatable then the count should repeat.
– paparazzo
Nov 19 '18 at 15:44
@paparazzo - Yes, assuming that there are no changes to the underlying data, the query should yield repeatable results. But, I don't see anything in the OP that would indicate that is the case.
– Jason A. Long
Nov 19 '18 at 16:20
See my answer. If the OP states results should be the same then I trust they are not changing data.
– paparazzo
Nov 19 '18 at 16:22
I saw your answer. A unique set of date values will always sort in the same order. That said, it's entirely possible that that the same date key is exists on multiple rows, essentially creating ties and those ties could be getting broken differently with each execution. Assuming that's the case, my answer still applies. As far as trusting the what the OP is or isn't doing... I don't take anything for granted.
– Jason A. Long
Nov 19 '18 at 16:42
Not the same. OP did comment I was correct. Good day. I not not vote you down.
– paparazzo
Nov 19 '18 at 16:45
|
show 1 more comment
In the CTE's you are finding the [Fact_Unit_Safety_Checks_Wkey] that's associated with the most resent row for any given [Dim_Unit_Wkey], [Dim_Safety_Check_Type_Wkey] combination... With no regard for weather or not [Safety_Check_Certificate_No] is equal to 'GP/KB11007'.
Then, in the outer query, you are filtering results based on [Safety_Check_Certificate_No] = 'GP/KB11007'.
So, unless the most recent [Fact_Unit_Safety_Checks_Wkey] happens to have [Safety_Check_Certificate_No] = 'GP/KB11007', the data is going to be filtered out.
answered Nov 19 '18 at 15:17
Jason A. Long
3,7701412
If the most recent row is repeatable then the count should repeat.
– paparazzo
Nov 19 '18 at 15:44
@paparazzo - Yes, assuming that there are no changes to the underlying data, the query should yield repeatable results. But, I don't see anything in the OP that would indicate that is the case.
– Jason A. Long
Nov 19 '18 at 16:20
See my answer. If the OP states results should be the same then I trust they are not changing data.
– paparazzo
Nov 19 '18 at 16:22
I saw your answer. A unique set of date values will always sort in the same order. That said, it's entirely possible that that the same date key is exists on multiple rows, essentially creating ties and those ties could be getting broken differently with each execution. Assuming that's the case, my answer still applies. As far as trusting the what the OP is or isn't doing... I don't take anything for granted.
– Jason A. Long
Nov 19 '18 at 16:42
Not the same. OP did comment I was correct. Good day. I not not vote you down.
– paparazzo
Nov 19 '18 at 16:45
|
show 1 more comment
In the CTE's you are finding the [Fact_Unit_Safety_Checks_Wkey] that's associated with the most resent row for any given [Dim_Unit_Wkey], [Dim_Safety_Check_Type_Wkey] combination... With no regard for weather or not [Safety_Check_Certificate_No] is equal to 'GP/KB11007'.
Then, in the outer query, you are filtering results based on [Safety_Check_Certificate_No] = 'GP/KB11007'.
So, unless the most recent [Fact_Unit_Safety_Checks_Wkey] happens to have [Safety_Check_Certificate_No] = 'GP/KB11007', the data is going to be filtered out.
answered Nov 19 '18 at 15:17
Jason A. Long
3,7701412
In the CTE's you are finding the [Fact_Unit_Safety_Checks_Wkey] that's associated with the most resent row for any given [Dim_Unit_Wkey], [Dim_Safety_Check_Type_Wkey] combination... With no regard for weather or not [Safety_Check_Certificate_No] is equal to 'GP/KB11007'.
Then, in the outer query, you are filtering results based on [Safety_Check_Certificate_No] = 'GP/KB11007'.
So, unless the most recent [Fact_Unit_Safety_Checks_Wkey] happens to have [Safety_Check_Certificate_No] = 'GP/KB11007', the data is going to be filtered out.
answered Nov 19 '18 at 15:17
Jason A. Long
3,7701412
answered Nov 19 '18 at 15:17
Jason A. Long
3,7701412
answered Nov 19 '18 at 15:17
Jason A. Long
3,7701412
answered Nov 19 '18 at 15:17
Jason A. Long
3,7701412
3,7701412
If the most recent row is repeatable then the count should repeat.
– paparazzo
Nov 19 '18 at 15:44
@paparazzo - Yes, assuming that there are no changes to the underlying data, the query should yield repeatable results. But, I don't see anything in the OP that would indicate that is the case.
– Jason A. Long
Nov 19 '18 at 16:20
See my answer. If the OP states results should be the same then I trust they are not changing data.
– paparazzo
Nov 19 '18 at 16:22
I saw your answer. A unique set of date values will always sort in the same order. That said, it's entirely possible that that the same date key is exists on multiple rows, essentially creating ties and those ties could be getting broken differently with each execution. Assuming that's the case, my answer still applies. As far as trusting the what the OP is or isn't doing... I don't take anything for granted.
– Jason A. Long
Nov 19 '18 at 16:42
Not the same. OP did comment I was correct. Good day. I not not vote you down.
– paparazzo
Nov 19 '18 at 16:45
|
show 1 more comment
If the most recent row is repeatable then the count should repeat.
– paparazzo
Nov 19 '18 at 15:44
@paparazzo - Yes, assuming that there are no changes to the underlying data, the query should yield repeatable results. But, I don't see anything in the OP that would indicate that is the case.
– Jason A. Long
Nov 19 '18 at 16:20
See my answer. If the OP states results should be the same then I trust they are not changing data.
– paparazzo
Nov 19 '18 at 16:22
I saw your answer. A unique set of date values will always sort in the same order. That said, it's entirely possible that that the same date key is exists on multiple rows, essentially creating ties and those ties could be getting broken differently with each execution. Assuming that's the case, my answer still applies. As far as trusting the what the OP is or isn't doing... I don't take anything for granted.
– Jason A. Long
Nov 19 '18 at 16:42
Not the same. OP did comment I was correct. Good day. I not not vote you down.
– paparazzo
Nov 19 '18 at 16:45
If the most recent row is repeatable then the count should repeat.
– paparazzo
Nov 19 '18 at 15:44
If the most recent row is repeatable then the count should repeat.
– paparazzo
Nov 19 '18 at 15:44
@paparazzo - Yes, assuming that there are no changes to the underlying data, the query should yield repeatable results. But, I don't see anything in the OP that would indicate that is the case.
– Jason A. Long
Nov 19 '18 at 16:20
@paparazzo - Yes, assuming that there are no changes to the underlying data, the query should yield repeatable results. But, I don't see anything in the OP that would indicate that is the case.
– Jason A. Long
Nov 19 '18 at 16:20
See my answer. If the OP states results should be the same then I trust they are not changing data.
– paparazzo
Nov 19 '18 at 16:22
See my answer. If the OP states results should be the same then I trust they are not changing data.
– paparazzo
Nov 19 '18 at 16:22
I saw your answer. A unique set of date values will always sort in the same order. That said, it's entirely possible that that the same date key is exists on multiple rows, essentially creating ties and those ties could be getting broken differently with each execution. Assuming that's the case, my answer still applies. As far as trusting the what the OP is or isn't doing... I don't take anything for granted.
– Jason A. Long
Nov 19 '18 at 16:42
I saw your answer. A unique set of date values will always sort in the same order. That said, it's entirely possible that that the same date key is exists on multiple rows, essentially creating ties and those ties could be getting broken differently with each execution. Assuming that's the case, my answer still applies. As far as trusting the what the OP is or isn't doing... I don't take anything for granted.
– Jason A. Long
Nov 19 '18 at 16:42
Not the same. OP did comment I was correct. Good day. I not not vote you down.
– paparazzo
Nov 19 '18 at 16:45
Not the same. OP did comment I was correct. Good day. I not not vote you down.
– paparazzo
Nov 19 '18 at 16:45
|
show 1 more comment
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53376677%2fmissing-rows-when-running-select-in-sql-server%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Please edit the question and specify the SQL Server version + Service Packs installed. Run
select @@VERSIONif needed. And see here for an (old) very similar issue regarding MAXDOP: sqlteam.com/forums/topic.asp?TOPIC_ID=156173– Peter B
Nov 19 '18 at 14:35
2
My guess is ORDER BY [Dim_Safety_Check_Date_Wkey] is not deterministic.
– paparazzo
Nov 19 '18 at 14:47
Microsoft SQL Server 2016 (SP2-CU3) (KB4458871) - 13.0.5216.0 (X64) Sep 13 2018 22:16:01 Copyright (c) Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2012 R2 Standard 6.3 <X64> (Build 9600: ) (Hypervisor)
– Mike Fleming
Nov 19 '18 at 14:53
Hi Paparazzo, that looks correct. Thanks
– Mike Fleming
Nov 19 '18 at 15:04