SQLServerCentral.com Community Update for {date}

Problems displaying this newsletter? View online.

Featured Contents

Question of the Day

The Upgraded Stored Procedure

Featured Script

Join Csv files using SQL

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 wish 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 Workbench, pgAdmin, 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

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 […]

PostgreSQL 101: A guide to PostgreSQL documentation & useful resources

Additional Articles from Redgate

It can be a daunting task for new users to get to grips with PostgreSQL documentation, particularly if you are not from a developer background.

If you are not sure where to start, we are here to help break down the different resources available so you can make the most of them.

In the latest webinar of our PostgreSQL 101 series, join Ryan Booz, PostgreSQL Advocate at Redgate, and Grant Fritchey, Microsoft Data Platform MVP, and discover:

- How to navigate PostgreSQL documentation
- The essential things you need to know about PostgreSQL documentation
- Other useful learning resources to help make your journey to using PostgreSQL easier

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.

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.

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...

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()

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

Public Role Permissions concern - All, I've been double-checking security for one of my servers and I found the below permissions on the public role. I'm trying to decide if those are default / system-granted permissions that are necessary for the functioning of SQL Server or if these might have been granted by someone some time ago. Does anyone know? […]

SQL Server 2016 - Administration

SSRS on Alwayson Availability Group - Hi, Installed SSRS feature in Primary replica and configured ReportServer and ReportTEMPDB by using SQL listener name. ReportServer and ReportTEMPDB brought into Availability Database and two databases are synchronized between two primary and secondary replica. SSRS reports rdl files kept it Node1 and able access reports http://Node1/Reports/browse/ working as expected and tested AG failover between […]

SQL Server 2016 - Development and T-SQL

How to get total sum across the table and sum grouped by date ranges - Here is sample code and what I tried to achieve, but not getting the output, any advice? select k.charges, sum(case when k.range>='101' then charges else 0 end) as '101charges', sum(case when k.range>='201' then charges else 0 end) as '201charge' from (select charges,substring(daterange,1,3) as range from dbo.charges, )k CREATE TABLE charges ( charges money […]

Converting file name MMDDYYYY.txt to a Date Field - Hello, Converting file name MMDDYYYY.txt to a Date Field but CONVERT or CAST do not seem to work perhaps because I also have a REPLACE? Can someone help me understand how to do this if it is possible? See sample code below and many thanks in advance! CREATE TABLE #t (getFileName varchar(100)) INSERT INTO #T […]

SQL Server 2019 - Development

extracting data from JSON with TSQL - Hi, I need help extracting data from the below JSON. What I need is the data from the "frequencyData" array. on the first row of the resultset I would like the first column to contain the first data sample of the "frequency"child array, the second column should contain the first data sample of the "df" […]

Value does not fall within the expected range. (Microsoft.SqlServer.ManagedDTS) - Good morning experts, Over the past few releases, my team and I have been running into this issue where when we deploy an SSIS Project from one SQL Server to another we get this error on the "Changing Protection Level part: Value does not fall within the expected range. (Microsoft.SqlServer.ManagedDTS) We have about 15 projects […]

Script for deleting data from a table and corresponding FKTables - Hi, Looking for a script to delete data from a table and also from the FK tables of data exists. I have seen some online using procedures and function but just looking for a base script

SQL Server 2008 - General

P2V os sql vm to new host - I have used P2V to replicate my sql vm to a new host alongside all its disks storage, the problem is that these disks are part of a failover cluster and when I try to bring them online on the new host the following error occurs: The disk must be in cluster maintenance mode and […]

SSDT

Partial Model Project With Same DB Reference - Hello, I am trying to create an SSDT project (in VS2022) for a database whose objects are primarily owned by a vendor application. This application dynamically creates views that we interface with from 'under-the-hood' tables that have non human-readable names. We have some custom integration code including some procedures, views, and functions, which are in-house […]

Analysis Services

How to connect SSAS TABULER with mysql data source - Hello I have a mysql data source and I want to use it as data source to my analysis service… I could connect it through ssis and ssrs using odbc data source but I face a hard time finding odbc or ado.net data source in my ssas… can anyone help me with steps and photos […]

SQL Server 2022 - Administration

Kubernetes Hands-On Courses - Hello, All I appreciate it may not be directly relevant to SQL Server, but I will be grateful if someone can recommend me (a company I work for are ready to chip in) a good Kubernetes course with practical element in it, please. There are plenty of free courses over the internet or event on […]

SQL Job on server set to UTC. Are jobs that perform GETDATE using UTC? - This is a scenario I haven't had to consider. We have a UTC time set SQL Server running jobs. We have set the jobs to run in based on UTC time converted from the timezone we're in (+10 hours) eg 6:45pm UTC (4:45am AEST) However one of the jobs calls a view that uses a […]

SQL Server 2022 - Development

Both values between Start and End date - good afternoon, I'm trying to determine if both values (101 and 102) both occur within a 'Shift' (startdate/enddate). It originally just needed 101, but our facility added 102 and for the 'goal' to be compliant (1), they need to be both present within a shift. I'm having a bit of trouble working this out within […]

Creating a subquery which conflicts with the where clause... - Hi All, I have created a table named issues where incidents are being tracked. Every record has a master indicator column ("Y" or "N")which denotes that its the master incident. If the same incident type is created by another user, then the Master Indicator will be set to N and the master_issue column will be […]

Database Query Optimization: How to Improve Query Performance in SQL - I have a SQL database that stores a large amount of data, and I'm experiencing slow query performance when retrieving information. What are some strategies and best practices for optimizing database queries in SQL to achieve better performance? Here's an example query I'm working with: SELECT first_name, last_name, email FROM users WHERE registration_date >= '2023-01-01' […]

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.