Problems displaying this newsletter? View online.
SQL Server Central
Featured Contents
Question of the Day
Featured Script
The Voice of the DBA
 

The Importance of Thick Clients

I've used a number of clients with SQL Server across the last 30 years. I worked early on with the command line and isql (now SQLCMD and soon Go-SQLCMD). That was handy with DOS and Windows 3.1. However, I soon moved to GUI tools and spent years in ,ISQL/W,  which was how I ran Query Analyzer. Enterprise Manager was the next evolution, though I used Rapid SQL for awhile to get offline query work while on a plane. This was handy for me in documenting and commenting code without a server. Amazing to think I used to work without a SQL instance on my laptop.

SSMS (Management Studio) has become the de facto way I've written queries for over a decade. Microsoft has tried to push people to

Azure Data Studio (ADS), but I don't see a lot of people moving to it. It's fast, but also, not attractive, at least not to me.

I saw a post recently from Chris Webb, asking if it was possible to do all one's development in the Power BI Web interface. He asks the question since lots of BI people have Macs and you can't run Power BI Desktop on MacOS. That's one reason why ADS was created, to provide a cross-platform tool. Chris says the web isn't quite up to snuff, though it's been improving. So for now, you need Windows.

I feel the same way. I do with SSMS was available cross-platform. When I had a MacBook, I constantly had a Windows VM running to get to SSMS and SQL Server. These days I could run a Linux version of SQL Server on MacOS (or a container), but not SSMS. I could run ADS, but I don't love it.

I'm not completely sure why ADS bothers me, but I think a big reason is the familiarity of a thick client and Windows GUI controls. I always felt many of the Linux GUI elements feel like cheap imitations of their Windows counterparts. The smoothness of the Win32 applications, the way that grid results come across, the rendering of text, smooth menus, etc. run is just better. Even when it's not great, it's better.

I like the thick Windows clients.

I do think there are some nice Electron-based apps that look and work well in Windows and Linux. In fact, Redgate's own direction has been Flyway Desktop rather than continuing to insist on SSMS, precisely because we find more and more customers aren't standardized on Windows. This is especially true for RDBMSs other than SQL Server. That UI looks pretty good, though admittedly, I'm not writing queries.

Especially in SSMS, the way a thick client works is much better than ADS. Especially with SQL Prompt. Intellisense in ADS leaves a lot to be desired and the constant need for me to use the command palette for things is annoying. Give me menu items and better shortcuts. I'm not sure why I don't think it works as well, but it doesn't. Not for me.

I wonder how many of you feel the same way. I know some people love DataGrip, but that is often because they work cross-platform or they are full-stack people that like Rider for their other work.

I do like VS Code, but I'm almost always just writing code, not looking at results or managing a server instance. When I look at other platform tools, MySQL WorkbenchpgAdmin, SQL Developer, I see why SQL Server and SSMS are preferred by many. Those tools are just poorly built compared to SSMS.

What do you think? I know many of us dislike things about SSMS, but still find it indispensable for daily work. Do you love/hate SSMS or have you found another tool that works well for you?

Steve Jones - SSC Editor

Join the debate, and respond to today's editorial on the forums

 
 Featured Contents
SQLServerCentral Article

GENERATE_SERIES() Breaks Minimal Logging

Jeff Moden from SQLServerCentral

tl;dr The title says it all. Prologue One of the keys to my personal learning is that, very early in my database career, I taught myself how to make lot’s of rows of Random Constrained Data in a comparatively short time. With the help of a few good folks over time, the method has been […]

External Article

Yet Another Reason to Not Use sp_ in your SQL Server Object Names

Additional Articles from SimpleTalk

In 2012, Aaron Bertrand said most everything I knew (and a bit more) about the issues with using the sp_ prefix. Procedures prefixed with sp_ have special powers when placed in the the master database in that it can be executed anywhere on the server after that.

External Article

SQL Server Execution Plan Issues when using TOP Clause and Various Parameters

Additional Articles from MSSQLTips.com

A query is sometimes super fast and sometimes very slow. One of the main reasons for this problem is Parameter Sniffing. There are several approaches to addressing the Parameter Sniffing issue and this article discusses effective methods for handling Parameter Sniffing in SELECT statements containing a TOP clause.

Blog Post

From the SQL Server Central Blogs - ADS Copilot Experiments with XML

Steve Jones - SSC Editor from The Voice of the DBA

I sent some code to a customer recently to help them decrypt some stored procedures. I sent a quick and dirty set of code, noting at the bottom that...

Blog Post

From the SQL Server Central Blogs - State of the Database Landscape Survey 2023

Grant Fritchey from The Scary DBA

As data professionals, of any stripe, we should, as much as we can, where we can, base our decisions on data. After all, in theory anyway, we’re the experts...

SQL Server 2022 Administration Inside Out

Steve Jones - SSC Editor from SQLServerCentral

Dive into SQL Server 2022 administration and grow your Microsoft SQL Server data platform skillset. This well-organized reference packs in timesaving solutions, tips, and workarounds, all you need to plan, implement, deploy, provision, manage, and secure SQL Server 2022 in any environment: on-premises, cloud, or hybrid, including detailed, dedicated chapters on Azure SQL Database and Azure SQL Managed Instance.

 

 Question of the Day

Today's question (by Steve Jones - SSC Editor):

 

The Upgraded Stored Procedure

I had a SQL Server 2014 database. In it, I created this function:
CREATE FUNCTION OPENJSON
(@json varchar(1000))
RETURNS TABLE
AS
RETURN
SELECT CASE
         WHEN SUBSTRING (@json, 1, 1) = '{' THEN
           1
         ELSE
           0
       END AS json_string;
This works, and this call:
SELECT * FROM OPENJSON('{ "key":1 }')
Returns this result:
json_string
--------------
1
Now I restore this database on a SQL Server 2019 instance and set the compatibility level to 150. I run this code:
SELECT * FROM OPENJSON('{ "key":1 }')
What happens?

Think you know the answer? Click here, and find out if you are right.

 

 

 Yesterday's Question of the Day (by Steve Jones - SSC Editor)

Tracking Backup to Nul

I have a SQL Server 2019 database and I run this command:

BACKUP DATABASE way0utwest TO disk = 'nul'

If I check msdb.dbo.backupset, what do I see for this backup?

Answer: The backup is listed with a filesize the same size as a full backup

Explanation: Whenever a backup is run, it is recorded in msdb. This includes backups to the nul device. The sizes listed are the same as a backup to a disk file. Ref: BACKUP - https://learn.microsoft.com/en-us/sql/t-sql/statements/backup-transact-sql?view=sql-server-ver16#arguments

Discuss this question and answer on the forums

 

Featured Script

Join Csv files using SQL

Girish Ganesamanian Tirunelveli from SQLServerCentral

Csv files must frequently be joined. It would be fantastic if we could connect CSV files using the power of SQL. This script accomplishes that.

#=====================================================================
# ExecuteSqlForCsv.py:
# Usage: python3 ExecuteSqlForCsv.py "SELECT emp_name, dept_name FROM employees.csv, departments.csv WHERE employees.csv.dept_id = departments.csv.dept_id"
#=====================================================================
import pandas as pd
import sys

def main():
strSql = "".join(sys.argv[1:])
strSelectClause = strSql.split("FROM")[0].replace("SELECT","").replace(" ","")
lstColumns = strSelectClause.split(",")
strFromClause = strSql.split("FROM")[1]
strFromClause = strFromClause.split("WHERE")[0]
strCsv1 = strFromClause.split(",")[0].strip()
strCsv2 = strFromClause.split(",")[1].strip()
strWhereClause = strSql.split("WHERE")[1]
strLeftOn = strWhereClause.split("=")[0].replace(strCsv1,"").replace(".","").strip()
strRightOn = strWhereClause.split("=")[1].replace(strCsv2,"").replace(".","").strip()
dfCsv1 = pd.read_csv(strCsv1)
dfCsv2 = pd.read_csv(strCsv2)
dfOutput = pd.merge(dfCsv1, dfCsv2, how="left", left_on=[strLeftOn], right_on = [strRightOn])
dfOutput = dfOutput[lstColumns]
print(dfOutput)
# End main()

if __name__ == "__main__":
main()

More »

 

Database Pros Who Need Your Help

Here's a few of the new posts today on the forums. To see more, visit the forums.


SQL Server 2017 - Administration
shrinking a large data file - Hi All, One of our drive got filled (E: drive) and we decided to shrink the data file from 1.6TB to 500GB. While trying to shrink the file with didnt work. USE [dbname] GO DBCC SHRINKFILE (N'db_name_dat' , 0, TRUNCATEONLY) GO it completed successfully but it didnt release any space to OS. --then, tried below. […]
unable to see job history for some jobs - Hi All, I am unable to see job history for some jobs. Usually, we run some maintenance jobs. for example) index maintenance and updating stats. Why it is not showing up? anything needs to be changed in SQL Agent Properties?   Regards, Sam
SQL Server 2016 - Administration
BCP with ODBC 18? - Hello all.  My org has identified ODBC driver version 17 as having security vulnerabilities.  It looks as  though 17.10.4.1 remedies these, but we're considering v18 anyways.  It looks as though bcp requires version 17.  Is this the case?  Surely not, though I don't see otherwise.
Administration - SQL Server 2014
Maintenance Task Failed - how to log the error - I've a Multi step Maintenance plan of 5 steps called "Reindex". This automatically created an Agent job called "Reindex..Subplan_1" There's an email alert on failure of the Agent job. Is there any place in maintenance task design to put an alert in or do I always have to use add the alert to Agent task […]
Development - SQL Server 2014
Replace bad characters - Hi all I need help to replace bad characters with space  in string. select col1, patindex('%[^ !-~]%' COLLATE Latin1_General_BIN,col1) as [Position], substring(col1,patindex('%[^ !-~]%' COLLATE Latin1_General_BIN,col1),1) as [InvalidCharacter], ascii(substring(col1,patindex('%[^ !-~]%' COLLATE Latin1_General_BIN,col1),1)) as [ASCIICode] from myTable where patindex('%[^ !-~]%' COLLATE Latin1_General_BIN,col1) >0 Here is the result: Col1                    […]
SQL Server 2019 - Development
MS Sync Framework Error - Failed to Execute the command BulkUpdateCommand - It turns out that the error is in a trigger on the Transfers table, so this can be ignored. Sorry if you've wasted your time reading this.   Hi, I've got two databases at distant locations that sync evey few minutes using the Ms Sync Framework 2.1. I'm getting this error occurring: ERROR , SyncBPO, […]
SQL Azure - Administration
Azure SQL database: How additional storage (beyond 250GB) being charged? - Hi All, I have a question on how additional storage (beyond 250GB) will be charged for Azure SQL databases. Is it charged based on Max storage limit (MAXSIZE) or is it based on Allocated space? Thanks.
Reporting Services
Tabs in SSRS Report - Hi, I have 2 tabs created in SSRS Report.There is calendar Day prompt in the report. By Selecting the Calendar Day Prompt the 2nd tab displayed in the report varies from 2nd Page and 3rd page. Is there any option to display the 2nd tab of the Report to display in 3rd page irrespective of […]
SSRS 2019 Custom Authentication: error occurred when invoking the authorization - Hi, I am upgrading SSRS 2014 to 2019 and custom form based authentication is implemented with SSRS 2014. I followed the steps https://github.com/microsoft/Reporting-Services/tree/master/CustomSecuritySample and implemented same for SSRS 2019. While Web Service URL works fine but while accessing Web Portal URL I am getting below error and the web page displays. The service is not available. An […]
Migrating Reports from SQL Server 2014 to SQL Server 2022 via Visual Studio 2022 - Migrating Reports from SQL Server 2014 to SQL Server 2022 via Visual Studio 2022 with Reporting Services extension installed. I am having trouble with parameters and the error message: "Must declare the scalar variable @TellMeWhyIdontlikeThisMonday". I'm also getting the error message: "Custom parameter layout was removed from the report. SQL Server 2014 Reporting Services and […]
SQL Server 2022 - Administration
issue with backup cleanup step - I have a maintenance plan for my nightly backups.  It consists of three steps:  Back up my databases to a folder on another server - in this case it's literally just a Windows server with a lot of drive space.  Run DBCC check db against my databases.  Cleanup - delete any backups older than five […]
SQL Server 2022 - Development
SQL2022 UPDATED FROM SQL2014 - PARALLELISM ISSUES - Hi We just updated from SQL2014 to SQL2022. Most everything went fairly well - except I have about 6 stored procs that move allot of data around that are suddenly not working. When looking at whoisactive they are generating CX waits. They stall all day with no results. They ran fine in SQL2014. I enabled […]
Need Assistance- 32-bit SSIS Package and Analyzing Shift-wise Data - I'm currently working with SQL Server 2022 - Development edition and I'm facing a couple of challenges while dealing with a 32-bit SSIS package. This package involves processing data that spans across different shifts, and I need to perform some analysis on it. My dataset includes values recorded across different shifts, and I need to […]
Running 32 bit SSIS package - This is my first post in this forum, so hello everyone! Would be most grateful for help with the following issue: a simple SSIS package (created in Visual Studio 2019) imports a table from an ODBC Source (MySQL Server 5.xxx) into a database in a SQL Server 2022. ODBC Source is 32-bit, the package runs […]
Searching in date field provides inconsistent results - I have been working with SQL Server for too many years to count and this is baffling; perhaps someone has a clue on the cause.  I am running this on SQL Server 2022.  I put together a simple function to return a primary key integer for a lookup table that has mileage rates that are […]
 

 

RSS FeedTwitter

This email has been sent to {email}. To be removed from this list, please click here. If you have any problems leaving the list, please contact the webmaster@sqlservercentral.com. This newsletter was sent to you because you signed up at SQLServerCentral.com.
©2019 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved.
webmaster@sqlservercentral.com

 

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -