SQL Server Management Studio



  • 'nuff said



  • What about it? I personally find it better than Enterprise Manager/Query Analyzer combo


  • Considered Harmful

    No, I don't think enough was said.  I find SQL Server Management Studio to be the best database IDE I've used, easily beating out Enterprise Manager + Query Analyzer before it.



  • Maybe I've got an f*'ed up install, but it is basically a steaming pile of dung from my viewpoint.  Takes a long time to open, randomly closes and is basically a major annoyance.  I go out of my way to avoid using it, using query analyzer as much as possible.



  • @campkev said:

    Maybe I've got an f*'ed up install, but it is basically a steaming pile of dung from my viewpoint.  Takes a long time to open, randomly closes and is basically a major annoyance.  I go out of my way to avoid using it, using query analyzer as much as possible.

    I found that Aqua Data Studio is quite useful and works quite well ([url]www.aquafold.com[/url]).  The older version is free for personal use, I'm not really sure about the newest version (30 day trial, I think?).  It's written in Java, so it'll work for you Linux peoples too ;)  It works with somewhere around 15 different backend databases, including MSSQL and Oracle.



  • @campkev said:

    Maybe I've got an f*'ed up install, but it is basically a steaming pile of dung from my viewpoint.  Takes a long time to open, randomly closes and is basically a major annoyance.  I go out of my way to avoid using it, using query analyzer as much as possible.

    You've got a fucked-** install. It's always been quite fast for me. It has a few UI problems, but I think it's generally very good.



  • @bobday said:

    @campkev said:

    Maybe I've got an f*'ed up install, but it is basically a steaming pile of dung from my viewpoint.  Takes a long time to open, randomly closes and is basically a major annoyance.  I go out of my way to avoid using it, using query analyzer as much as possible.

    You've got a fucked-** install. It's always been quite fast for me. It has a few UI problems, but I think it's generally very good.

     Same. I refuse to go back to the EM/QA combo that used to drive me crazy.

    Its either your install or your DBs (or both)



  • @campkev said:

    Maybe I've got an f*'ed up install, but it is basically a steaming pile of dung from my viewpoint.  Takes a long time to open, randomly closes and is basically a major annoyance.  I go out of my way to avoid using it, using query analyzer as much as possible.

    You have a fucked up installation.

    My copy is bliss compared to the previous EM-QA combo. Foremostly, It has configurable fonts for the displays. which is YAY GOOD because an Open Table's Tahoma 11px at 1600*1200 is nigh impossible to read. I fancy that Vista mono font, Consolas, at Much Bigger than the defaults.

    My tiny gripe with it is that it feels like it was clicked together (though proficiently) by a drag&drop UI creator. But part of that is due to the fact that I increased my overall font size in Windows -- not all apps deal with that perfectly. And it's weird that New Query asks for a connection, instead of using the one for the DB currently selected. So instead I right-click the DB and New Query from there.

    I'm not DBA enough to judge its actual functionality in-depth. I doubt it's far more powerful than the ole EM-QA combo. But combining the two programs was a master move.

    Tip:
    if you switch between a table view and a query, the interface will jitter while it hides one toolbar and shows the other. Solution: drag the toolbars on the same line. Huzza.



  • @Heron said:

    @campkev said:

    Maybe I've got an f*'ed up install, but it is basically a steaming pile of dung from my viewpoint.  Takes a long time to open, randomly closes and is basically a major annoyance.  I go out of my way to avoid using it, using query analyzer as much as possible.

    I found that Aqua Data Studio is quite useful and works quite well ([url]www.aquafold.com[/url]).  The older version is free for personal use, I'm not really sure about the newest version (30 day trial, I think?).  It's written in Java, so it'll work for you Linux peoples too ;)  It works with somewhere around 15 different backend databases, including MSSQL and Oracle.

    Aqua Data Studio is not bad, and yes, it definitely works well on Linux. On the other hand, Oracle's SQL Developer is free and in some places more pleasant to use. And yes, it also connects to MS SQL Server and MySQL. (Though it lacks the DBA features ADS has to offer)



  • @dhromed said:

    And it's weird that New Query asks for a connection, instead of using the one for the DB currently selected. So instead I right-click the DB and New Query from there.

    They fixed that (same problem happened with Open).  Just install the service pack.<hints id="hah_hints"></hints>



  • @campkev said:

    'nuff said

    I think the only WTF that can possibly be that short is if you're talking about Excel.



  • @campkev said:

    'nuff said

     

    The only thing that I can think of is that there are still a few dialogs that aren't resizable, so I'm looking at a 400x300 popup on a 1600x1200 screen, and all my object names are cut off.

     

    But then EntMan and QA had far more. So...move in the right direction, what with using those crazy resizable forms that came out in 1984 (or whatever) 



  • @batasrki said:

    What about it? I personally find it better than Enterprise Manager/Query Analyzer combo

    Let's see, things off the top of my head...

    1) Click on a NULL field to edit it.  It actually leaves the text literal 'NULL' in the field and you need to backspace over it.  I can't count the number of times I edit a field, look up, and it says 'NULLJohn'.

    2) When editing a boolean field, you actually need to type in the full freaking word 'true' or 'false' instead of 1 or 0.  Gone are the days of typing 1 DOWN 0 DOWN 1 DOWNN 0 DOWN 0 to quickly edit five records.

    3) Open Table -> Query is gone.  Instead we have 'Open Table' with NO option to put in a where clause before it starts returning every record in the entire table so you have to quickly hit the 'Stop Retrieving Data' button.   You can do Script Table As -> Select To -> New Query Editor Window (side-scrolling menus THREE layers deep!  Lovely!) but query editor window data is not editable.

    4) If you copy data and paste into Excel*, it doesn't take the field names with it (Enterprise Manager did) and also keeps the NULL literal text for all null fields (Enterprise Manager didn't).

    5)  Whenever you open Management Studio, you get that utterly useless 'Summary' window that tells you nothing important.

    6)  In Enterprise Manger, you could press 'G' and it would jump to the tables starting with 'G'.  That doesn't work in the tree view in Management Studio.

    7)  If you want to set a column as identity in Enterprise Manger, you double-click on the Identity row in the Columns section and it cycles through yes, no, and not for replication.  If you want to set it in Management Studio, you need to scroll down the column properties, click the + to expand out 'Identity Specification' (double-clicking on it doesn't do anything) then double-click on 'Is Identity'.  Three mouse clicks instead of one.

    8) It's pretty much the same story with defining FK relationships -- many more mouse clicks, popup windows, and steps than before.  Also, in Enterprise Manager you could select the PK table and FK table from dropdowns so you could define a relationship by either modifying the PK table or FK table.  In Management Studio the FK table is greyed out and unchangable so you MUST define relationships by modifying the FK table only.

    * Shaddup.  I have clients that want dumps of data for mail merges, financial analysis, etc. and Excel is the only common denominator we have.
     



  • I do find it a bit annoying and QA was a lot faster for many things, but overall SSMS is a better, more integrated product.  Good tip about fixing the New Query bug where it asks for a server...

    With #4 above, you can change the options for SSMS to control this I believe.  Same with #5 - you can hide that summary window (makes it start faster too!). 

    I find that when I am using the keyboard to navigate through the tables & their columns on the left it can be annoying cause in QA, being single threaded, you could key ahead down, across, down, etc and get through the table name, "columns", etc.  In SSMS you cannot do this as (I think) the loading of columns, etc is done in a background thread so the keyboard strokes skip over what i want.   Will get used to it though :)



  • And #6 - this works, but you have to type the schema name as well, just as it's listed in the treeview.  So to go to myTable in the dbo schema (which every table should be in unless you're getting really fancy and prolly causing WTFs everywhere) just type dbo.myTable and you're there.



  • @campkev said:

    Maybe I've got an f*'ed up install, but it is basically a steaming pile of dung from my viewpoint.  Takes a long time to open, randomly closes and is basically a major annoyance.  I go out of my way to avoid using it, using query analyzer as much as possible.


    Works perfectly for me,..
    Maybe your installation just doesn't like you.
    It happens.

    CAPTCHA: consciousness software



  • When I switched from Entreprise Manager to SQL Server Management Studio, like a lot of people, I was angered by that new piece of crap :

    -It's slow (it takes several second to display the message and a memory hog.

    -It feels buggy. Never had a trouble with entreprise manager but with server management studio I had several 'oops I fucked up here is my stack trace' message box. (It seems to happen a lot when you try to use stuff you don't have access to).

     -Each time you create a new procedure/function you must remove all the useless auto-generated stub.

    -You have to click WAY more than Entreprise manager. It's so damn annoying to click all the time to open that damn tree!

    -Couldn't they find an even longer and meaningless name? I mean : 'SQL Server Management Studio Express'. Come on!

     

    With some time that feeling vanishes and you start getting used to it. If I had the choice of the tool i'd probably keep the new server management studio 

    Here is what's nice with the new tool :

    -mdi/tabs windows. I don't need to keep open multiple instances of entreprise manager. (Except for the relationship/index/contraints screens which are really fucked up.

    -editing of text(stored procedure, table definition) is much nicer (search/replace with regex!)

    -I can access to the database at a lower level : I can look at the create table statements, I can look at the system function/procedures,...

    -Using the design table option does not lock the table!

    -It's packed with a bunch of really cool features. It's really worth it to spend some time to discover some of the cool features

    -I feel more at home thanks to the similarity with visual studio.

     

    If you give it some time you'll start to like it more and more :).



  • @MrYates said:

    And #6 - this works, but you have to type the schema name as well, just as it's listed in the treeview.  So to go to myTable in the dbo schema (which every table should be in unless you're getting really fancy and prolly causing WTFs everywhere) just type dbo.myTable and you're there.

    That would be a solution, if that weren't the exact problem

    The very point is that it's highly impractical to type:

    d

    b

    o

    .

    [table first char]

    EVERY TIME.
    There is no need for "dbo." to be display before every object -- and I really don't care about that information by default. It's noise, not information. Maybe put it in parens after the object name. But typing "dbo." all the time gets boring real quicklike.

    This problem is generic to UI's, by the way, and applies to any list view in any application that supports find-by-type, such as Windows Explorer. I hope application designers and name-convention inventors start addressing this in the foreseeable future. It's easy to solve for STDEx, as the app can just look for the typed letter after the period.

     

    @pjabbott said:

    1) Click on a NULL field to edit it.  It actually leaves the text literal 'NULL' in the field and you need to backspace over it.  I can't count the number of times I edit a field, look up, and it says 'NULLJohn'.

    Click on any field to edit it, and the field's text is selected. Whatever you type will replace the original contents. The only way I can replicate a value of "NULLJohn" is by clicking the field and then explicitly aiming for the cursor position after the L and clicking again. Perhaps you double-click?

     



  • hmm, I agree with your #5, as well as with the one of the above posters when it comes hitting the 'New Query' button (why DOES it ask for a connection again). Actually, also when you open existing SQL files, it would do the same thing. This would get annoying when I needed to open 20 files (Yes, my previous company has a fucked-up DB versioning system).

    I guess I never had a need to edit table data manually and I always knew what table I needed to look at, so I didn't click much in it. I like the fact that it has combined QA and EM into one interface, no matter how idiotic that interface is. 



  • @pjabbott said:

    3) ...but query editor window data is not editable.

    I agree with most of your points except this one, which is only half-right. If you right-click in window and choose 'Pane > SQL', you can see and edit the SQL statement to add a WHERE clause or whatever you gotta do. Two annoyances though: you have to do this every time you open a table, it doesn't remember that you had the pane visible last time, and F5 doesnt work to re-run the query. You have to right-click and choose "Execute SQL"

     

    But all seriousness aside, what the hell is up with that Summary window? Have you ever seen anything as useless?
     



  • What is "fucked-**"?



  • Irony.



  • @bobday said:

    F5 doesnt work to re-run the query [in a table view]. You have to right-click and choose "Execute SQL"

    Ctrl+R for Table Views, F5 for Query Windows.

    That says something about the attention with which the merger was executed.

    In fact, Ctrl+R in a Query Window will toggle the result set, and F5 in a Table View will do absolutely nothing. 



  • My biggest beef with EM that they have thankfully removed in SSMS is EM's unwillingness to keep a whole table open (or even the top N rows) for longer than, like, 15 minutes because it wants to free resources. So, walk away for coffee, get sidetracked by a coworker, or god forbid spend some time replying to e-mails - boom, half the windows on my screen are now empty and I've lost track of what I needed them for. Thanks!

     



  • @akatherder said:

    What is "fucked-**"?

    Sorry, I meant ******-up

    @dhromed: Yeah, I didn't bother to research into any alternate key combinations available. Of course, it still stands as an example of annoyingly bad/inconsistent UI design.



  • I'm a SQL Server DBA who has been using both tools for a while now and I cannot understand how anyone would choose Management Studio over Enterprise Manager.  Here are a few of my beefs:

     1.) MS takes much longer to load, even when you configure it not to connect on startup.

     2.) I totally agree with another post I saw on here.  For nearly any task, it takes significantly more clicks to get it done, and there are longer delays in between clicks.

    3.) EM allowed external tools to be configured and EM would pass parameters for the current server and database.  MS does not have these parameters available for external tools.  It does provide a bunch of useless parameters such as the current project directory.  This is of no value to me.

    4.) The new online help is harder to use.  Searches return more irrelevant info than I've ever seen.  It also takes a long time to load and all the increased search capabilities clutter the screen.  SQL 2000 Books Online was faster and easier to use.  I don't use 2005 Help at all since Google is superior.

    5.) Error messages often give misleading information and some don't even use correct English.

    6.) When starting a job, the menu says "Start Job At Step", leading you to believe you can select a step.  It always starts at step 1 regardless.

    7.) When displaying database file properties, it doesn't show space used as the Taskpad did in 2000.  Yeah, you can use the new reports but that takes longer to get to.

    8.) I have no faith in the new reports anyway.  I deliberately created a blocking situation to see what the blocking report would display and after refreshing several times, it never even detected blocking.

    9.) When drilling down on an object, you get far too many windows.  I don't like all the extra clutter on my desktop.

    10.) No easy way to show database role permissions as there was in EM.

     I only use SSMS on our SQL 2005 servers because I have to.  I far prefer the older tools and still use them on our SQL 2000 servers.



  • So I guess we can agree that SMSS is a bit of a sloppy job.

    >  1.) MS takes much longer to load, even when you configure it not to connect on startup.

    4 seconds versus 5 seconds on my 3+ year old Dell. Can you quantify "much longer"?

    Photoshop CS2 takes much longer than CS, as in, twice as long plus 10 second aftermath of odd activity plus weird (video card?) bug that slows down display inside image when Info palette is active. EM vs SMSS -- not much difference, performance-wise.



  • Your question prompted my curiosity to see exactly how much slower it is.  I did the following timing tests on my workstation (Dell Optiplex GX280, Win XP SP2, P4 2.8 Ghz, 512 MB RAM):

    • Reboot
    • Start Enterprise Manager: 5 seconds
    • Stop EM
    • Start Management Studio: 12 seconds
    • Stop SSMS
    • Start Enterprise Manager: 2 seconds
    • Stop EM
    • Start Management Studio: 2 seconds
    • Reboot
    • Start Management Studio: 13 seconds
    • Stop SSMS
    • Start Enterprise Manager: 5 seconds
    • Stop EM
    • Start Management Studio: 2 seconds
    • Stop SSMS
    • Start Enterprise Manager: 2 seconds
    • Reboot
    • Start Enterprise Manager: 5 seconds
    • Stop EM
    • Start Management Studio: 12 seconds
    • Stop SSMS
    • Start Enterprise Manager: 2 seconds
    • Stop EM
    • Start Management Studio: 2 seconds

    So we can see that on FIRST startup only, Management Studio takes longer.  After that, it comes up equally as quick.  Granted, this first startup time is a small irritation, but it's in addition to all the others.

     



  • @pjabbott said:

    @batasrki said:

    What about it? I personally find it better than Enterprise Manager/Query Analyzer combo

    Let's see, things off the top of my head...

    1) Click on a NULL field to edit it.  It actually leaves the text literal 'NULL' in the field and you need to backspace over it.  I can't count the number of times I edit a field, look up, and it says 'NULLJohn'.

    Use CTRL-0 to set the value to null.

    2) When editing a boolean field, you actually need to type in the full freaking word 'true' or 'false' instead of 1 or 0.  Gone are the days of typing 1 DOWN 0 DOWN 1 DOWNN 0 DOWN 0 to quickly edit five records.

    Agreed.  I hate this too.

    3) Open Table -> Query is gone.  Instead we have 'Open Table' with NO option to put in a where clause before it starts returning every record in the entire table so you have to quickly hit the 'Stop Retrieving Data' button.   You can do Script Table As -> Select To -> New Query Editor Window (side-scrolling menus THREE layers deep!  Lovely!) but query editor window data is not editable.

    The "Query Designer" toolbar has a button that allows you to modify the sql statement, and you must click the ! button to re-execute.

    4) If you copy data and paste into Excel*, it doesn't take the field names with it (Enterprise Manager did) and also keeps the NULL literal text for all null fields (Enterprise Manager didn't).

    No idea about this one.

    5)  Whenever you open Management Studio, you get that utterly useless 'Summary' window that tells you nothing important.

    The summary window provides some neat reports about your database or server (depending on what is highlighted in object explorer).  You can configure SSMS to start with an empty environment if you prefer.  (Tools -> Options -> Environment -> "At startup:" )

    6)  In Enterprise Manger, you could press 'G' and it would jump to the tables starting with 'G'.  That doesn't work in the tree view in Management Studio.

    As someone already posted, type the schema name first.

    7)  If you want to set a column as identity in Enterprise Manger, you double-click on the Identity row in the Columns section and it cycles through yes, no, and not for replication.  If you want to set it in Management Studio, you need to scroll down the column properties, click the + to expand out 'Identity Specification' (double-clicking on it doesn't do anything) then double-click on 'Is Identity'.  Three mouse clicks instead of one.

    I mostly do this stuff using TSQL scripts, so I can't speak to this.

    8) It's pretty much the same story with defining FK relationships -- many more mouse clicks, popup windows, and steps than before.  Also, in Enterprise Manager you could select the PK table and FK table from dropdowns so you could define a relationship by either modifying the PK table or FK table.  In Management Studio the FK table is greyed out and unchangable so you MUST define relationships by modifying the FK table only.

    See above...

    * Shaddup.  I have clients that want dumps of data for mail merges, financial analysis, etc. and Excel is the only common denominator we have.

    Use CSV or XML.  They are more common than Excel. 



  • Summary of an earlier post of mine.

    As someone already posted, type the schema name first.


    This is the truth, but it's hardly practical. It's extremely, extremely annoying to the point where I prefer to drag the scrollbar.

    1) Click on a NULL field to edit it.  It actually leaves the text literal 'NULL' in the field and you need to backspace over it.  I can't count the number of times I edit a field, look up, and it says 'NULLJohn'.

    Use CTRL-0 to set the value to null.


    1) his issue is with modifying null values, not nulling modified values.
    2) I personally cannot reproduce the issue. Clicking a field will select everything in the field. A value of "NULLJohn" has to be inserted deliberately and cannot happen by accident.

    [..]and you must click the ! button to re-execute.


    Button-clicking is 4 n00bs. :) F5, baby.


Log in to reply