January 18, 2008 at 12:30 pm
DonaldW (1/18/2008)
"Basically agree with you DonaldW.One experience I had, though, makes me put at least this warning on that idea.
I had a query that joined a table, through a many-to-many table, to itself, seven times. Query worked great for the report it was driving, and gave the managers exactly what they needed."
Holy @#$&! It never occurred to me that anyone could open a .sql file in Access. Yikes. Yes, a cautionary tale, indeed.
They had Access as a front end for some internal use of the database. It's forms and reports are a very rapid development platform, on top of an SQL Server database. In there is the option for editing tables, queries, procs, functions, etc., inside of Access. Where it can, it fires up a GUI for the query design, with a diagram of the table relations, etc. Works just fine for simple queries (it's how I learned to write T-SQL at first), but it has some serious problems with complex queries (as outlined in my prior post).
Any changes made in Access on the underlying database take place directly in the production database. No source control, no code review, just "save changes Y/N?" and bang!
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 18, 2008 at 12:46 pm
GSquared (1/18/2008)
They had Access as a front end for some internal use of the database. It's forms and reports are a very rapid development platform, on top of an SQL Server database. In there is the option for editing tables, queries, procs, functions, etc., inside of Access. Where it can, it fires up a GUI for the query design, with a diagram of the table relations, etc. Works just fine for simple queries (it's how I learned to write T-SQL at first), but it has some serious problems with complex queries (as outlined in my prior post).Any changes made in Access on the underlying database take place directly in the production database. No source control, no code review, just "save changes Y/N?" and bang!
Oh, I see what you mean. I had to do something similar early in my SQL career. Fortunately, the Access piece was middleware. Believe it or not we were using Excel as the front end so the Access database was relatively safe. In fact, it was copied down to the user machine on demand and deleted when they were done. :sick: The design was NOT my idea, I hasten to add, but at least it prevented the kind of thing you describe. Of course, it didn't stop me from doing it to myself on occasion during development. :hehe:
My inital thought was to use Access query wizards to see how to structure the SQL itself then use that to write the query in SQL Server, particularly for things like unmatched records. Useful if you are just learning T-SQL. As Matt pointed out, the Query Designer in SSMS will do the same thing.
January 18, 2008 at 12:46 pm
Here is a list of books I have (I am amazed at all the books I have, so it took a little longer than I expected to find all my SQL Server 2005 books).
Professional SQL Server 2005 Integration Services Wrox
Pro SQL Server 2005 Service Broker APress
Introducing Microsoft SQL Server 2005 for Developers Microsoft Press
Inside Microsoft SQL Server 2005: T-SQL Querying Microsoft Press
Inside Microsoft SQL Server 2005: The Storage Engine Microsoft Press
Inside Microsoft SQL Server 2005: T-SQL Programming Microsoft Press
Microsoft SQL Server 2005: Applied Techniques Step by Step Microsoft Press
Microsoft SQL Server 2005: Analysis Services Step by Step Microsoft Press
Microsoft SQL Server 2005: Database Essentials Step by Step Microsoft Press
Programming Microsoft SQL Server 2005 Microsoft Press
A Develop's Guide to SQL Server 2005 Addison Wesley
😎
January 18, 2008 at 1:04 pm
As a result of these posts, I have already switched from Access to SSMS to create queries. It doesn't crash when I use aliases, allows me to indent, doesn't re-write my code, and the graphical interface provides exactly what I need and nothing more. So, far a huge impact on my day. Many thanks.
I found the SQL Cookbook, and the Rozenshtein set-based section at the end has forced me to re-think much of what I have already written, and pointed out so many possibilities for code optimization.
The more I immerse myself in all this, the more I like it. Although my current position is stressful, I am excited for what I will be able to provide this company. I have already done some data analysis and pointed out overlooked ways the company could minimize costs and leverage more revenue. One investigation I performed could have an impact of over $300k this year. That is really exciting. I never had that kind of direct business impact when I was running cable, configuring routers, and setting up user profiles. (And, yes, I doubt that the $300k will mean I could actually get a budget, and yes, I do know how to negotiate and leverage myself in a company 🙂 ).
So much to accomplish, but first, I have to figure out these tricky RIGHT OUTER joins ..... 😉
@Damon - is there a performance issue with mail? I am working with a single server.
I am also getting mixed messages about SSRS from the forum. I AM only working with a single server, and I'm not sure how to evaluate the performance impact of employing SSRS. I think it could provide a lot of value here. The production database is still quite small. How do I know if I could get away with it?
January 18, 2008 at 1:12 pm
jschroeder (1/18/2008)
As a result of these posts, I have already switched from Access to SSMS to create queries. It doesn't crash when I use aliases, allows me to indent, doesn't re-write my code, and the graphical interface provides exactly what I need and nothing more. So, far a huge impact on my day. Many thanks.I found the SQL Cookbook, and the Rozenshtein set-based section at the end has forced me to re-think much of what I have already written, and pointed out so many possibilities for code optimization.
The more I immerse myself in all this, the more I like it. Although my current position is stressful, I am excited for what I will be able to provide this company. I have already done some data analysis and pointed out overlooked ways the company could minimize costs and leverage more revenue. One investigation I performed could have an impact of over $300k this year. That is really exciting. I never had that kind of direct business impact when I was running cable, configuring routers, and setting up user profiles. (And, yes, I doubt that the $300k will mean I could actually get a budget, and yes, I do know how to negotiate and leverage myself in a company 🙂 ).
So much to accomplish, but first, I have to figure out these tricky RIGHT OUTER joins ..... 😉
@Damon - is there a performance issue with mail? I am working with a single server.
I am also getting mixed messages about SSRS from the forum. I AM only working with a single server, and I'm not sure how to evaluate the performance impact of employing SSRS. I think it could provide a lot of value here. The production database is still quite small. How do I know if I could get away with it?
Keep on plugging away at it, my friend, and ask questions when you have them.
Regarding Outer Joins (Left, Right, or Full) just remember that a Left Outer Join returns all the records from the left table even when there is no match in the right table (the one it's joined to), a right outer join does the reverse and a full outer join brings all records from both tables, merging the ones that have common data. It gets tricky when you start filtering and get unexpected results. My feet are riddled with bullet holes from where I've shot them in this kind of situation (and I expect many more to come).
January 21, 2008 at 12:28 pm
"So much to accomplish, but first, I have to figure out these tricky RIGHT OUTER joins ..... "
Table1
ID
1
2
3
Table2
ID
1
4
5
Inner Join:
select *
from Table1
inner join Table2
on Table1.ID = Table2.ID
Result
1 1 -- (This is the only data that appears in both tables)
Left Outer Join:
select *
from Table1
left outer join Table2
on Table1.ID = Table2.ID
Result
1 1 -- (This is the only data that appears in both tables)
2 null -- (Selects these two because they are in the LEFT table)
3 null
Right Outer Join:
select *
from Table1
right outer join Table2
on Table1.ID = Table2.ID
Result
1 1 -- (This is the only data that appears in both tables)
null 4 -- (Selects these two because they are in the RIGHT table)
null 5
Does that help clarify it?
You can add things like "Where Table2.ID is null" to the Left Outer Join example, and that will return all the rows in Table1 which are NOT in Table2.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 21, 2008 at 3:17 pm
jschroeder
@Damon - is there a performance issue with mail? I am working with a single server.
There is a negligible of a performance hit when enabling Database Mail. You can find an overview and a set up walk through here:
E-Mail Functionality in SQL Server 2005
http://www.sql-server-performance.com/articles/dba/email_functionality_p1.aspx
*Note*: You may also come across references to SQL Mail.
SQL Mail = SQL Server 2000 and SQL Server 2005
Database Mail = SQL Server 2005 only
I am also getting mixed messages about SSRS from the forum. I AM only working with a single server, and I'm not sure how to evaluate the performance impact of employing SSRS. I think it could provide a lot of value here. The production database is still quite small. How do I know if I could get away with it?
My perspective:
SSRS is a way to deliver data sets to your viewing audience. I come from a long background using Crystal Reports, which is another way to display data. A "perk" of SSRS is that it is included in the licensing fee of SQL Server 2005, as is Analysis Services (SSAS). Crystal Reports, for example has to be purchased separately. Heck, if you had to, you could write your reports in T-SQL using SSMS, then use the "results to file", execute your T-SQL and save the file to be used as an Excel (XLS) spreadsheet as your reporting solution.
If your end users will consume your reporting data "quick and dirty", go the Excel route. On the other hand, if then want the company logo, date ranges, and disclaimers at the bottom of every page, pitch both SSRS and Crystal Reports along with the price of each, and SSRS will look *much* more attractive to management.
My 2 cents
"Key"
MCITP: DBA, MCSE, MCTS: SQL 2005, OCP
Viewing 9 posts - 31 through 38 (of 38 total)
You must be logged in to reply to this topic. Login to reply