3 ms·

I work with SQL Server. I really like general multi-db GUI tools like this, but also wonder if one is giving up a lot of power. SQL Server's free native tool i

by wenc 7y ago

I work with SQL Server. I really like general multi-db GUI tools like this, but also wonder if one is giving up a lot of power. SQL Server's free native tool is SSMS (SQL Server Management Studio), which is one of the most powerful SQL clients I've ever used. (despite my carryover reservations with MS products from the 1980s-90s, SQL Server is indisputably an innovative and solid piece of technology -- MS did something right with their database group). One can interact syntactically with the database via pure SQL, but SSMS lets you access the deep corners of the database quickly via a GUI (SQL is a great query language, but many admin/ops tasks are much more easily done via a GUI -- writing SQL every time you want make a simple change is tedious plus nobody remembers the syntax for infrequently used features). Some of the features I use regularly: row/column/covering index creation, linked servers, user management, live query plans, create/alter script generation from existing objects (stored proc, view, index, table, etc.). I've never seen these features exposed in any third party SQL GUI client. Postgres has a bunch of very powerful features too, and I've never seen these exposed in GUI tools. Jetbrains' DataGrip comes the closest, but because it needs to support lowest common denominator features across databases, it doesn't expose deep features either. I wonder if folks are giving up deep feature discovery by using an generic GUI SQL client. Exception: Oracle SQL Developer. It's native to Oracle but is quite unpleasant to use.

oefrha 7y ago
Edit: read the parent wrong, but leaving my comment here so it doesn’t look like backtracking some offensive content. > wonder if one is giving up a lot of power. That’s an irrational concern. GUI tools also come with SQL consoles where you can do anything that’s possible in the CLI. With the added benefit of results being presented in a nicer way.
- wenc 7y ago
  > That’s an irrational concern. Is it though? So for SQL Server, you can't really see the live query plan (real-time plan + statistics while the query is running) without the GUI. You cannot see the Activity Monitor without the GUI. There are elements that cannot be accessed via pure SQL. 3rd party GUIs generally don't support these features.
  - oefrha 7y ago
    Never mind, I read your comment wrong.
    - wenc 7y ago
      No worries, I appreciate it.
- yashap 7y ago
  I think you’ve missed wenc’s point - they aren’t arguing for a CLI, they’re arguing for a UI specialized to a single RDBMS, vs one trying to convert many RDBMSs.
- kyleee 7y ago
  If you've used SSMS extensively you'll know it's tailored to SQL server like a glove. You'd definitely be leaving usability on the table going with a generic DB client. The person you've responded to even mentioned that of course you can do all things via CLI, but SSMS builds powerful GUI layer on top of that
  - Nullabillity 7y ago
    > If you've used SSMS extensively you'll know it's tailored to SQL server like a glove. Sure, assuming that MSSQL is a foot.
aik 7y ago
How do you deal with the complete lack of searching for tables/SPs/functions in SSMS? There are a number of 3rd party extensions but they all seem slow and still just jump you to the entity in the explorer. The speed of searching for anything in DataGrip is just so beautiful and amazing for productivity. A big downside is we have run into situations where a SP is cached and is an old version and DataGrip doesn’t notify me that’s the case.
- wenc 7y ago
  > How do you deal with the complete lack of searching for tables/SPs/functions in SSMS? That is a weakness of SSMS, and I've no good answers. For stuff like SQL formatting though, the free ApexSQL Refactor [1] has been a godsend for me. I write a lot of complex analytic queries and having the ability to tidy up statements as I'm writing the query is amazing. [1] https://www.apexsql.com/sql-tools-refactor.aspx https://www.apexsql.com/sql-tools-refactor.aspx
- greggyb 7y ago
  GUI search is awful, but it does exist. Right clicking on an object type node (e.g. 'Tables' or 'Procedures' in the object explorer) and selecting the 'Filter' option gives you a limited search feature for objects in that node. It is clumsy and painful, and I will most often just query the sys schema to search for objects, but GUI search is there.
listenallyall 7y ago
>> I wonder if folks are giving up deep feature discovery by using an generic GUI SQL client. SSMS, unfortunately, is missing a ton of features that 3rd-party clients have mastered -- instant search/filter through query results, quick aggregating of numeric results (min max, sum, avg, sd, etc), sorting on any column, exporting to multiple formats, filtering of schema objects, charts from query results -- just off the top of my head. They also enable many tasks via GUI like creating/editing table schemas, importing bulk data, pre-query variable substitution, etc. SSMS is free, and should be installed whenever one needs to interface with SQL Server, but good 3rd party clients are generally better at 90+% of common tasks, while SSMS only needs to be used for a handful of specific features that it does very well.
- wenc 7y ago
  --- > SSMS, unfortunately, is missing a ton of features that 3rd-party clients have mastered -- instant search/filter through query results, quick aggregating of numeric results (min max, sum, avg, sd, etc), sorting on any column, exporting to multiple formats, filtering of schema objects, charts from query results -- just off the top of my head. They also enable many tasks via GUI like creating/editing table schemas, importing bulk data, pre-query variable substitution, etc. --- True, those are fair points. It does depend on one's use case. Instant search/filter/sort through query results is a useful one that seems to be an omission on SSMS's part. Quick aggregation and charting are nice to haves, but I would typically do these in Jupyter or Tableau (or Excel if it's a small dataset). These are not features most people would miss if they weren't in their SQL client. Pre-query variable substitution is a great idea, though in SSMS one can use DECLARE @var in many instances (not all) -- it's also not widely supported in 3rd party clients except for more advanced ones like DataGrip. SSMS does have a pretty comprehensive GUI for creates/edits of schemas. Bulk data ingest can be done in multiple ways (CSV import GUI, bacpacs/dacpacs, backup/restores or using the bcp CLI that comes with SSMS) -- also bulk ingest is not a common feature in 3rd party GUIs. Side note: for really large data ingests from Parquet etc. I write my own ETL in C# which uses the SqlBulkCopy class (or "turbodbc" in Python) for fast ETL. No GUI client I know can do this.
  - listenallyall 7y ago

by wenc 7y ago

oefrha 7y ago

Edit: read the parent wrong, but leaving my comment here so it doesn’t look like backtracking some offensive content. > wonder if one is giving up a lot of power. That’s an irrational concern. GUI tools also come with SQL consoles where you can do anything that’s possible in the CLI. With the added benefit of results being presented in a nicer way.

wenc 7y ago
> That’s an irrational concern. Is it though? So for SQL Server, you can't really see the live query plan (real-time plan + statistics while the query is running) without the GUI. You cannot see the Activity Monitor without the GUI. There are elements that cannot be accessed via pure SQL. 3rd party GUIs generally don't support these features.
- oefrha 7y ago
  Never mind, I read your comment wrong.
  - wenc 7y ago
    No worries, I appreciate it.
yashap 7y ago
I think you’ve missed wenc’s point - they aren’t arguing for a CLI, they’re arguing for a UI specialized to a single RDBMS, vs one trying to convert many RDBMSs.
kyleee 7y ago
If you've used SSMS extensively you'll know it's tailored to SQL server like a glove. You'd definitely be leaving usability on the table going with a generic DB client. The person you've responded to even mentioned that of course you can do all things via CLI, but SSMS builds powerful GUI layer on top of that
- Nullabillity 7y ago
  > If you've used SSMS extensively you'll know it's tailored to SQL server like a glove. Sure, assuming that MSSQL is a foot.

aik 7y ago

How do you deal with the complete lack of searching for tables/SPs/functions in SSMS? There are a number of 3rd party extensions but they all seem slow and still just jump you to the entity in the explorer. The speed of searching for anything in DataGrip is just so beautiful and amazing for productivity. A big downside is we have run into situations where a SP is cached and is an old version and DataGrip doesn’t notify me that’s the case.

wenc 7y ago
> How do you deal with the complete lack of searching for tables/SPs/functions in SSMS? That is a weakness of SSMS, and I've no good answers. For stuff like SQL formatting though, the free ApexSQL Refactor [1] has been a godsend for me. I write a lot of complex analytic queries and having the ability to tidy up statements as I'm writing the query is amazing. [1] https://www.apexsql.com/sql-tools-refactor.aspx https://www.apexsql.com/sql-tools-refactor.aspx
greggyb 7y ago
GUI search is awful, but it does exist. Right clicking on an object type node (e.g. 'Tables' or 'Procedures' in the object explorer) and selecting the 'Filter' option gives you a limited search feature for objects in that node. It is clumsy and painful, and I will most often just query the sys schema to search for objects, but GUI search is there.

listenallyall 7y ago

>> I wonder if folks are giving up deep feature discovery by using an generic GUI SQL client. SSMS, unfortunately, is missing a ton of features that 3rd-party clients have mastered -- instant search/filter through query results, quick aggregating of numeric results (min max, sum, avg, sd, etc), sorting on any column, exporting to multiple formats, filtering of schema objects, charts from query results -- just off the top of my head. They also enable many tasks via GUI like creating/editing table schemas, importing bulk data, pre-query variable substitution, etc. SSMS is free, and should be installed whenever one needs to interface with SQL Server, but good 3rd party clients are generally better at 90+% of common tasks, while SSMS only needs to be used for a handful of specific features that it does very well.

wenc 7y ago
--- > SSMS, unfortunately, is missing a ton of features that 3rd-party clients have mastered -- instant search/filter through query results, quick aggregating of numeric results (min max, sum, avg, sd, etc), sorting on any column, exporting to multiple formats, filtering of schema objects, charts from query results -- just off the top of my head. They also enable many tasks via GUI like creating/editing table schemas, importing bulk data, pre-query variable substitution, etc. --- True, those are fair points. It does depend on one's use case. Instant search/filter/sort through query results is a useful one that seems to be an omission on SSMS's part. Quick aggregation and charting are nice to haves, but I would typically do these in Jupyter or Tableau (or Excel if it's a small dataset). These are not features most people would miss if they weren't in their SQL client. Pre-query variable substitution is a great idea, though in SSMS one can use DECLARE @var in many instances (not all) -- it's also not widely supported in 3rd party clients except for more advanced ones like DataGrip. SSMS does have a pretty comprehensive GUI for creates/edits of schemas. Bulk data ingest can be done in multiple ways (CSV import GUI, bacpacs/dacpacs, backup/restores or using the bcp CLI that comes with SSMS) -- also bulk ingest is not a common feature in 3rd party GUIs. Side note: for really large data ingests from Parquet etc. I write my own ETL in C# which uses the SqlBulkCopy class (or "turbodbc" in Python) for fast ETL. No GUI client I know can do this.
- listenallyall 7y ago