January 17, 2008 at 9:20 am
Management Studio is what I use for typing all of my queries. Access is very awkward for that.
Some tricks that come in useful:
If you select part of the query and hit F5 on the keyboard, it will only run that part
Ctrl+R clear/displays the results panel
Ctrl+N opens a new connection
You can drag-and-drop table names, proc names, etc., from the object explorer to the connection window (where you type your queries)
There are more sophisticated programs for this, produced by Redgate, ApexSQL, etc. They also cost more (though not expensive). I prefer the simplicity of Management Studio (it's one step removed from being a simple text editor), but the more sophisticated tools often come in really handy when you're learning T-SQL.
Whichever tool you use, become very familiar with how it displays execution plans, and learn to read the important parts of them early on. It doesn't do you much good to finally figure out a way to write a query in T-SQL, have the query compile and run, and then find out that it will take 10 hours to run and will lock your whole server while it does it. Reading query execution plans will make writing queries much easier for you, since the server will tell you where you need to look to improve performance, results, etc.
- 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 17, 2008 at 9:35 am
I also use UltraEdit at times, especially if I am working with large files. I find it easier to format my SQL code, and it has syntax highlighting. It currently does not have a word file for SQL Server 2005, but so far that hasn't been an issue.
😎
January 17, 2008 at 11:47 am
@Lynn - you had some book suggestions for me. I would love to hear what you are thinking. The bookstores I have access to do not have the same selection as the Borders/B&N but I can plan on traveling to one. It would be helpful to get some recommendations so I can look at mail-order.
Oh, and thanks for the "how to post code" link. I'll be posting a SQL question later.
January 17, 2008 at 11:56 am
I'm still working on the list. Between taking kids to church last night and a production issue on one of our servers, I got side tracked. I will try to have it completed and posted by tomorrow morning.
Only reason I suggested checking out Borders/Barnes & Noble is so you could actually look at the books, read a little in each to make an informed decision. Where you actually buy them is another story....
Thanks,
Lynn
January 18, 2008 at 5:38 am
GSquared (1/17/2008)
Management Studio is what I use for typing all of my queries. Access is very awkward for that....
I agree. However Access has one good feature for people learning T-SQL - you can design the query visually then look at the T-SQL that Access wrote for it. This is really helpful, as long as one keeps in mind that there may be some differences in the T-SQL between the two products. (There were in Access 97 and SQL Server 7, at any rate.) I used this method to learn the basics of using Left joins to find unmatched records, many moons ago, and as a teaching tool it's not a bad one.
Of course, the key is to use this as a springboard into writing one's own stuff, not as waterwings for dog-paddling along.
January 18, 2008 at 8:36 am
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.
Then, one day, someone opened the query in Access 2003, just to see how it worked, and closed it. And clicked Yes on Save Changes. And blew it up completely.
Access rewrote all the joins, so instead of:
Table as T1
inner join MMTable MMT1
on T1.ID = MMT1.ID1
inner join Table T2
on MMT1.ID2 = T2.ID
inner join MMTable MMT2
on T2.ID = MMT2.ID1
inner join Table T3
on MMT2.ID2 = T3.ID
... and so on, a chain join
It became:
Table as T1
inner join MMTable MMT1
on T1.ID = MMT1.ID1
inner join Table T2
on T1.ID = T2.ID
inner join MMTable MMT2
on T1.ID = MMT2.ID1
inner join Table T3
on T1.ID = T3.ID
... and so on, everything joined to the ID of the first instance of the primary table
Note, the user didn't tell it to do this, Access just got confused by all the joins and decided on its own to rewrite them.
I rebuilt the query, and copied it to a test database, opened Access on the test database, and it did exactly the same thing.
Also tested it with Enterprise Manager's query builder (since I think that uses the same engine as Access), and it did the same thing.
So, yes, Access can help with learning how to write queries, but it can also FUBAR your production database if someone opens the wrong thing and clicks the wrong "yes"!
What if, instead of a view that populated a report that's run once a week or so, it had instead been, oh, maybe an update proc run every few milliseconds? Anything at all that had to do with an e-commerce page? Something that calculated pricing or inventory levels?
There are also lessons in this about who has what access and uses what programs on production databases, but the main point is, be careful about how Access treats complex queries.
- 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 8:40 am
"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.
January 18, 2008 at 8:47 am
I am going to give my standard spiel here that you should find the budget to get a professional to come in for a few days to give your entire sql server system a performance, security and disaster recovery review. I can guarantee you that there are things that should be done or settings to be manipulated or indexes to be placed or queries to be modifed to ensure the database system is optimized and secure and can recover from a major outage.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
January 18, 2008 at 8:55 am
I can't find the response, but Jeff put one out a while back that was perfect for a newbie. I'll try and summarize.
You have already started doing what you need to become a better DBA.
Start at the least with downloading the Free version of SQL Server (limited to 2G), but other than that fully functional. Make sure to download the Management Studio too.
Then use this site. It is a great place to learn. And learn by doing. Try to answer all the questions. Expectially QOD's. And don't just guess. Research them. Most of them are found word for word in the BOL. (Books on-Line).
Even the veterans often find new things doing just that.
Good luck.
January 18, 2008 at 9:09 am
Late to the thread, but first congrats on the job and on the job you are doing. Sounds like you really are an asset to the company.
I have to plug my company, End to End training, here for some learning. I'm a part owner and we have a couple great classes in Orlando. There's a One on One Mentoring and a Performance Tuning class. I'd really recommend that you continue to ask for training and a one-time budget to build some skills. After six months, you've proven yourself. I'd recommend some training, a consultant for a few days, a class with a private provider (not Microsoft curriculum), something. If this is valuable and important, they need to spend some $$.
Second, any book will do, just get one, start learning. Read the T-SQL threads here and see what people do to code better.
Third, ask lots of questions. Make sure you have a test system somewhere, try things, see what works and what doesn't, and then show your code and ask if people think there's a better way.
Last, have fun. enjoy your job and the challenges.
January 18, 2008 at 9:09 am
IMHO one of the better single books on T-SQL and a couple of other flavors is :
"SQL CookBook" by Anthony Moliaro and published by O'Reilly.
It has good real world examples of queries that you may be faced with on a day to day basis. Its not a beginners tome, but BOL will help out there.
January 18, 2008 at 9:12 am
I come from a shop that used Access for their production apps. I tasked myself with the job of converting as much data/queries/reports to SQL Server. I converted all queries to stored procedures and BANG, guess what?.......... No problem with people changing the queries. I moved the tables to SQL and no more users changing fields/data types. Then I put the reports on SSRS and the users loved getting their data without having to login to the Access database. Certainly Access has it's place (it's easy, especially for a beginner) but security and speed are what you get when converting that info to SQL Server. You come out smelling like a rose and your boss should see some performance improvements plus your users won't be able to play around with the objects quite as much.
January 18, 2008 at 9:35 am
DonaldW (1/18/2008)
GSquared (1/17/2008)
Management Studio is what I use for typing all of my queries. Access is very awkward for that....
I agree. However Access has one good feature for people learning T-SQL - you can design the query visually then look at the T-SQL that Access wrote for it. This is really helpful, as long as one keeps in mind that there may be some differences in the T-SQL between the two products. (There were in Access 97 and SQL Server 7, at any rate.) I used this method to learn the basics of using Left joins to find unmatched records, many moons ago, and as a teaching tool it's not a bad one.
Of course, the key is to use this as a springboard into writing one's own stuff, not as waterwings for dog-paddling along.
Donald -
for what it's worth - there IS a graphical editor in SSMS. Once you open a new query window (the text one), just right-click and pick "Design Query in Editor". Not quite as nice as the Access one, but 90% there.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
January 18, 2008 at 9:36 am
jschroeder,
It is always nice to see someone that has been thrust the crown of thorns that is the role of DBA/Developer, and you have accepted it with open arms and a smile!
There are lot of folk on this site that are very passionate about SQL Server, SQL Server Central is a community of people that I have found to be very generous of both their time and knowledge.
I would like to build upon some of the recommendations made by other posters on this thread.
Books and online learning:
For purchasing books on a budget (I am a book person), Amazon.com has their network of booksellers of new and used titles. When you click on a link for any title on Amazon.com, there are the words "xx used and new available from $xx.xx", just to the right of the image of the book's cover. Use your best judgement when buying the used books, and be mindful of the ratings for each seller. Personally I have received only one book that was not in the condition specified by the seller.
These books are my suggestions on where to start.
--
Database Design for Mere Mortals(R): A Hands-On Guide to Relational Database Design (2nd Edition) (For Mere Mortals) (Paperback)
by Michael J. Hernandez (Author)
ISBN-10: 0201752840
ISBN-13: 978-0201752847
--
SQL Queries for Mere Mortals(R): A Hands-On Guide to Data Manipulation in SQL (2nd Edition) (For Mere Mortals) (Paperback)
by John L. Viescas (Author), Michael J. Hernandez (Author)
ISBN-10: 0321444434
ISBN-13: 978-0321444431
Both "Mere Mortals" books are fantastic learning tools and reference books. Database Design will give you a solid foundation on what/how a database is properly designed. SQL Queries starts with the basics of SQL and each lesson and chapter builds upon the last. From the introduction: "Everything you read in this book is based on the current American Standards Institute (ANSI) Standard for the SQL database language ... The SQL you learn here is *not* specific to any particular software product." page XXIV.
--
Publisher Wrox has a series of technical books for SQL Server 2005. On the back of any SQL 2005 related book there is a little "learning tree" of which book you can read next to take you on your learning path. (Amazon.com *MAY* have the back cover image scanned, so you can see one).
Beginning SQL Server 2005 Administration (Paperback)
by Dan Wood (Author), Chris Leiter (Author), Paul Turley (Author)
ISBN-10: 0470047046
ISBN-13: 978-0470047040
This will help you in making sure that you have all of your database administration related bases covered.
--
Beginning Transact-SQL With SQL Server 2000 and 2005 (Paperback)
by Paul Turley (Author)
ISBN-10: 076457955X
ISBN-13: 978-0764579554
A great starting place for T-SQL specific syntax.
--
There are literally thousands of articles, tutorials, white papers and journals all over the Internet. A couple of starting places.
SQLServerCentral.com (of course!)
http://www.sqlservercentral.com/Articles/ ... browse by topic
http://www.sqlservercentral.com/Forums/ ... learn from others, give your own advice
http://www.sqlservercentral.com/Scripts/ ... database scripts, more than I can shake a stick at, by topic
MSDN (Microsoft Developer Network) http://msdn2.microsoft.com/en-us/default.aspx
I would recommend signing up for a free WindowsLive account. There is a list of Development Centers on the left side of the start page, dig around the SQL Server area ... have fun, and start with the basics, it is easy to get overwhelmed with content.
Backups, Scheduled Jobs and Database Mail:
Make sure that your backups/backup jobs are running (and not failing). Look into getting Database Mail setup. You can configure SQL Server 2005 to email you when things do or don't happen, specifically when a (bakup) job fails. See Server Books OnLine (BOL) Topic: Database Mail Configuration Wizard. Note that you will need to install the SMTP Service on the database server, which is *NOT* installed by default, and you will need Admnistrator level access to install the service on the server. (I am assuming that you are running Windows Server 2003.)
How can I install the SMTP service under Windows Server 2003?
http://www.windowsitpro.com/Article/ArticleID/40810/40810.html
Restores:
Backups are useless (in my opinion, as is the opinion of lots of others on this site) unless the backup is restored as a verification. Trust me in the fact that you don't want to have to restore a database with the CIO standing over your shoulder and the backup is corrupted. See if you can get a second workstation that you can use as a development/restore system.
Minimum system requirements for SQL Server 2005 are here:
http://technet.microsoft.com/en-us/library/ms143506.aspx
I have a Windows XP Professional SP2 machine with a PIII 1.8GHZ processor, 1 GM RAM, several GB free disk space, running SQL Server 2005 Developer Edition at home. SQL Server 2005 Developer Edition is installed. It runs a little slow, but I am the only user on the system, it is my little sandbox.
The idea here is to see if you can scrounge together a workstation that you can put SQL Server Developer Edition on. Test your backups with and give you a "playground", without having to learn on your production database server. Odds are that if you can pitch the idea to management that you have come up with a solution that shows that you are taking steps to ensure that the database backups are being verified, "they" might just be able to squeeze $50 from somewhere for SQL Server 2005 Developer Edition.
Hopefully this will give you some ideas of where to go from here ...
I look forward to your future postings.
Happy T-SQLing
"Key"
MCITP: DBA, MCSE, MCTS: SQL 2005, OCP
January 18, 2008 at 9:39 am
Matt Miller (1/18/2008)[/Donald -
for what it's worth - there IS a graphical editor in SSMS. Once you open a new query window (the text one), just right-click and pick "Design Query in Editor". Not quite as nice as the Access one, but 90% there.
Son of a gun, I didn't realize that. I haven't used a graphical design method in years and I didn't even think to look for one. Thanks for pointing that out. My bad.
Viewing 15 posts - 16 through 30 (of 38 total)
You must be logged in to reply to this topic. Login to reply