November 16, 2015 at 12:32 pm
I've never seen SQL behave this way before, so I need some help in pointing me where to look next.
A co-worker created a SQL script to help me learn our database as I get up to speed. The script that she created uses views that were built a long time ago. I looked at how the views were created so that I could begin to get a better idea of where the data lives and how it connects. Here is one of the queries used to create one of the views:
Create View dbo.ViewAllOpenCases
As
Select TC.Cas_ID
From c_prd.TCASE TC
Inner Join c_prd.TCAS_HISTORY TCH
On TC.Cas_ID = TCH.Cas_ID
Where TCH.CaseCloseDate Is Null
This is a basic query, but it threw me off when I first saw it. The reason it threw me off is all tables and views belong to the schema dbo, and this query is making reference to the c_prd schema. I tracked that back to the Synonyms section, and this is what I found:
CREATE SYNONYM [c_prd].[TCASE] FOR [<DatabaseNameNotGiven].[dbo].[TCASE]
I found the same thing for c_prd.TCas_History. Running JUST the Select statement from the Create View script with c_prd I pull up 9,816 records. When I change c_prd to dbo I receive 9,792 records. The ONLY thing I'm changing is the schema I'm referencing, and the synonym proves that it is still pointing at the dbo table. What gives? This variance shouldn't be there unless one of two things has happened:
1. MS SQL Server and T-SQL in general are broken
OR
2. There is something about the way SQL Server handles the view to synonym to raw table relationship that I'm missing.
Now I know you all think of me as super genius extraordinaire, and I thank you for that boost of confidence. That being said, while I'm good I'm not good enough to have discovered a fundamental flaw in T-SQL that has been missed by all of you up to this point. That means the issue has to be that I'm missing something.
Thoughts? Ideas? Suggestions?
[font="Arial"]“Any fool can know. The point is to understand.”
- Albert Einstein
"DOH!"
- Homer Simpson[/font]
November 16, 2015 at 12:57 pm
jarid.lawson (11/16/2015)
I've never seen SQL behave this way before, so I need some help in pointing me where to look next.A co-worker created a SQL script to help me learn our database as I get up to speed. The script that she created uses views that were built a long time ago. I looked at how the views were created so that I could begin to get a better idea of where the data lives and how it connects. Here is one of the queries used to create one of the views:
Create View dbo.ViewAllOpenCasesAs
Select TC.Cas_ID
From c_prd.TCASE TC
Inner Join c_prd.TCAS_HISTORY TCH
On TC.Cas_ID = TCH.Cas_ID
Where TCH.CaseCloseDate Is Null
This is a basic query, but it threw me off when I first saw it. The reason it threw me off is all tables and views belong to the schema dbo, and this query is making reference to the c_prd schema. I tracked that back to the Synonyms section, and this is what I found:
CREATE SYNONYM [c_prd].[TCASE] FOR [<DatabaseNameNotGiven].[dbo].[TCASE]I found the same thing for c_prd.TCas_History. Running JUST the Select statement from the Create View script with c_prd I pull up 9,816 records. When I change c_prd to dbo I receive 9,792 records. The ONLY thing I'm changing is the schema I'm referencing, and the synonym proves that it is still pointing at the dbo table. What gives? This variance shouldn't be there unless one of two things has happened:
1. MS SQL Server and T-SQL in general are broken
OR
2. There is something about the way SQL Server handles the view to synonym to raw table relationship that I'm missing.
Now I know you all think of me as super genius extraordinaire, and I thank you for that boost of confidence. That being said, while I'm good I'm not good enough to have discovered a fundamental flaw in T-SQL that has been missed by all of you up to this point. That means the issue has to be that I'm missing something.
Thoughts? Ideas? Suggestions?
What you don't say is if the database name in the synonym is the same database the view and tables are in or if it is a different database. From what you posted, we have no idea. You could be pulling data from different databases for all we know. We can't see what you see from here.
November 16, 2015 at 1:08 pm
Lynn Pettis (11/16/2015)
What you don't say is if the database name in the synonym is the same database the view and tables are in or if it is a different database. From what you posted, we have no idea. You could be pulling data from different databases for all we know. We can't see what you see from here.
Sorry, yes the database names are the same.
[font="Arial"]“Any fool can know. The point is to understand.”
- Albert Einstein
"DOH!"
- Homer Simpson[/font]
November 16, 2015 at 1:37 pm
jarid.lawson (11/16/2015)
Lynn Pettis (11/16/2015)
What you don't say is if the database name in the synonym is the same database the view and tables are in or if it is a different database. From what you posted, we have no idea. You could be pulling data from different databases for all we know. We can't see what you see from here.Sorry, yes the database names are the same.
Please post versions of the code you are running.
November 16, 2015 at 1:47 pm
Select TC.Cas_ID
From c_prd.TCASE TC
Inner Join c_prd.TCAS_HISTORY TCH
On TC.Cas_ID = TCH.Cas_ID
Where TCH.CaseCloseDate Is Null
Returns 9,816 records
Select TC.Cas_ID
From dbo.TCASE TC
Inner Join dbo.TCAS_HISTORY TCH
On TC.Cas_ID = TCH.Cas_ID
Where TCH.CaseCloseDate Is Null
Returns 9,792 records
[font="Arial"]“Any fool can know. The point is to understand.”
- Albert Einstein
"DOH!"
- Homer Simpson[/font]
November 16, 2015 at 1:51 pm
jarid.lawson (11/16/2015)
Select TC.Cas_IDFrom c_prd.TCASE TC
Inner Join c_prd.TCAS_HISTORY TCH
On TC.Cas_ID = TCH.Cas_ID
Where TCH.CaseCloseDate Is Null
Returns 9,816 records
Select TC.Cas_IDFrom dbo.TCASE TC
Inner Join dbo.TCAS_HISTORY TCH
On TC.Cas_ID = TCH.Cas_ID
Where TCH.CaseCloseDate Is Null
Returns 9,792 records
Where does this synonym point? c_prd.TCAS_HISTORY
November 16, 2015 at 1:57 pm
CREATE SYNONYM [c_prd].[TCAS_HISTORY] FOR [DatabaseNameNotGiven].[dbo].[TCAS_HISTORY]
This synonym is found in the same database listed for the DatabaseNameNotGiven value. I had SQL Server itself generate the code via right click > Script Synonym As > Create To > New Query Editor Window
[font="Arial"]“Any fool can know. The point is to understand.”
- Albert Einstein
"DOH!"
- Homer Simpson[/font]
November 16, 2015 at 2:01 pm
I have used aliases in this manner for years, never had a problem with a variance in data returned.
Break down the query into individual queries of each table and see if you get any differences there. Since the WHERE clause only affects the data returned from the history table run two queries each there, one with the WHERE clause and one without.
November 16, 2015 at 2:16 pm
The DBAs just sent out an email alerting us to a corruption in today's refresh (reporting database is 1 day behind production transactional database). They completed repairs and I am not getting the variance anymore. So that is good news at least.
Thank you for all the help.
[font="Arial"]“Any fool can know. The point is to understand.”
- Albert Einstein
"DOH!"
- Homer Simpson[/font]
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply