10-04-13, 10:53 #1Registered User
- Join Date
- Jul 2010
Unanswered: SQLTreeO, Addins, Plugins and best practices questions
I am currently surfing around the world wide web for additional tools to help in productivity and ease the pain to develop inside the ManagementStudio.
I've searched after best practices and found some useful information and some not.
1Wondering now if any of you guys are using tools, plugins, addIns that you find useful, commercial or free, does not matter, that actually helps a lot in the way you work in Sql Management studio. The tools used shouldnt be on the format "A large diagramme, showing amount of tables created last 30 days", which to me sounds useless... :P But more on the format of SQLTreeO (which I am currently downloading and testing. It enables folders within the view of the object explorer). Or a tool that if you run a query against a table (a select statement only), it is attached to the table automatically, so right-clicking the table shows "Edit top 2500 rows, Select top 2500 rows, <run last select statement>, <edit through running last select statement>"
Why the need of such things? Well, navigating through 300 stored procedures, 30 databases, where some database also contains 300-350 tables, which also are prefixed with "dbo.", without a search function, without proper navigation (I've also tested Object explorer details, still not satisfied).
2 "Best practices" do you guys use? Using views and stored procedures everywhere, easily call-able functions from any application, and easily maintainable (changing securities for allow an app to insert is easily done). Packing things in a .dll, all queries, or just the procedure calls (businuess logic) that is shared among applications? And "best practices" can be individual too, things like "I have registered a hotkey which does...". Can also be things like 'SET @i = (select top(1) EmployeeID from employee)' vs 'SELECT TOP(1) @i = EmployeeID FROM Employee'
Take this for example:
Looking for tips/suggestions/ideas for the simplest (and cleanest, shortest way (time, cost), and close to no-maintainance) queries for this little scenario. Would you create procedures for insert,update,delete and select? Would you create functions? Views? Would you create the queries in a .dll file, with LINQ? Using ORM systems?
3 Within management studio, anyone using the "New > Project" and have found a good use for the Solution Explorer? I am always within Object Explorer (and the defalut solution opened, never created a new project/solution), and just looking at the solution explorer, the few hours it takes to learn (and probably master), is not really worth it? Seems stupid.
Note not looking for queries, but thoughts, ideas,..
Where I am?
Oh, not landed on any decisions yet, currently doing a bit of everything, which is the reason why I post, due to now it gives me a headache! :P
Small statements such as "select name from employee" are done "there and then", inside the application (C#/C++/php/Lua/VBS).
Larger procedures are stored within the database and called from the application, both from a library file (dll) and not.
And even a few applications are using various ORM systems.
Using SQL 2008 R2.
Googled a bit and SQL 2012 does not seem to help on these matters.
Actually had these questions throughout the last few years, but never had enough time to ask/had a colleague to ask...
Last edited by ManyTimes; 10-04-13 at 11:54.
10-04-13, 12:59 #2Resident Curmudgeon
Provided Answers: 54
- Join Date
- Feb 2004
- In front of the computer
Keep in mind that the authors of these tools are friends of mine, so I can't claim that this is 100% unbiased opinion even though I used their tools before I got to know them.
In terms of SSMS (SQL Server Management Studio) add-ins, the only one that I've ever used (or even considered) was SSMS Tools Pack which is written and maintained by Mladen Prajdić.
Another tool that runs outside of SSMS, but is probably more useful to me personally is SQL Spec. This tool will document an entire SQL Server from logins and linked servers down to columns, datatypes, and samples of the data! It will also document SSIS package, Access Databases, DB2, Oracle, and just about anything else that you need to understand when doing a database project. I can't say enough good things about Jesse and the only bad thing that I can say is that he doesn't come here often enough!
A great tool to have when doing server onboarding (when you are taking responsibility for managing a SQL Server) is sp_blitz by Brent Ozar and others. This will give you a quick and easy to follow list of potential pitfalls, pre-sorted by their "threat level" to your getting to sleep.
I'm a huge PowerShell fan, and I highly recommend Idera's SQL Server Tools because they'll make your life much easier once you learn how to use them. If you don't know PowerShell now:
- Stop reading this
- Go learn Powershell (I recommend Pluralsight - Hardcore Dev and IT Training but the PowerShell.com tools are free and very good.
- Come back when you're done.
- Send me accolades and cash for the advice if you are so moved!
-PatPIn theory, theory and practice are identical. In practice, theory and practice are unrelated.