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

Using the Lightweight Tools

Today's editorial was originally published on Dec 19, 2019. It is being re-published as Steve is out of town.

I'm torn on Azure Data Studio (ADS). On one hand, I love the quick start and lightweight nature of the tool. On the other hand, the limited GUI and unfamiliar way of working with a database platform like SQL Server bother me a bit. Lacking some of the thick client features has me mostly using it for small things, like PostgreSQL access or notebooks. Even then, I struggle to leave SSMS. Maybe I've just had too many years of SSMS (and Enterprise Manager before it), to easily change.

While the ADS/SSMS relationship seems to lean towards the latter, that I just have too much history with SSMS to change. I keep hearing the VS Code is way more popular and useful than Visual Studio from many people. I'm not sure that this means most people that use Visual Studio have abandoned it for VS Code, but certainly VS Code usage has grown quite a bit. Even Facebook has made it the default development environment. I'd have thought this wasn't as popular in non-Microsoft stack places, but VS Code appears to compete well with Sublime Text, Atom, Notepad++, Vim, and others.

This week, I wonder if many of you feel that these new lightweight editors are better than the heavyweight thick clients. Do you prefer ADS or SSMS? VS Code or VS? Are there any limitations or features that might sway or change your mind? I certainly like VS Code, and have even moved PoSh work there instead of the ISE, one place where lightweight tooling wins for me.

I work for Redgate, and we are doing some work to build tools for ADS. There is a market there, but it seems very small for now. Perhaps that is changing, but I'm not sure that usage will dramatically grow in the next year. If you feel differently, or wish things would change, let me know.

Steve Jones - SSC Editor

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

 
 Featured Contents
SQLServerCentral Article

T-SQL Scripts to generate the HTML-formatted database storage report

Nisarg Upadhyay from SQLServerCentral

This article will show how to generate an HTML formatted report of your disk space.

External Article

Flyway for SQL Server Data Tools Users

Additional Articles from Redgate

If you are using SSDT for authoring, building, debugging, and publishing a database project, how do you change to, or preferably migrate towards, a Flyway-based database development? Flyway doesn't need to replace any code part of SSDT, but if allowed to manage every release candidate, it does allow for much cleaner branching, merging, and deployments.

External Article

Multiple Relationships on the same Table in a Power BI Data Model

Additional Articles from MSSQLTips.com

This article will cover how to create multiple relationships between the same tables in Power BI to build the data model you need.

Blog Post

From the SQL Server Central Blogs - Build announcement: Microsoft Fabric

James Serra from James Serra's Blog

The HUGE announcement at Microsoft Build yesterday was Microsoft Fabric (see Introducing Microsoft Fabric: Data analytics for the era of AI), now available in public preview. I have been...

Blog Post

From the SQL Server Central Blogs - SQL Server Backup Encryption and Compression

Matthew McGiffen from Matthew McGiffen DBA

In SQL Server you can also compress your encrypted backups. Unlike TDE this has been possible with Backup Encryption since the feature was first made available, and there have...

Practical Database Auditing for Microsoft SQL Server and Azure SQL

Practical Database Auditing for Microsoft SQL Server and Azure SQL: Troubleshooting, Regulatory Compliance, and Governance

Site Owners from SQLServerCentral

Know how to track changes and key events in your SQL Server databases in support of application troubleshooting, regulatory compliance, and governance. This book shows how to use key features in SQL Server ,such as SQL Server Audit and Extended Events, to track schema changes, permission changes, and changes to your data. You’ll even learn how to track queries run against specific tables in a database. ss

 

 Question of the Day

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

 

Getting the Winners

I get a JSON string that looks like this: 
DECLARE @JSON NVARCHAR(MAX) = N'{
"WinnerIDs": [11,14,15,16,45],
"Race": "Furry Scurry",
"RaceStartDate": "2022-05-20T08:00:00",
"RaceEndDate": "2022-05-20T10:00:00",
"Entries": 100
}';
I want to parse out the values of the winners to show each on a separate line of the result set. I try a couple of things. First, this: 
SELECT 
 oj.Race, oj.WinnerIDs
FROM
  OPENJSON (@JSON)
  WITH
    (Race NVARCHAR(100) '$.Race'
   , WinnerIDs NVARCHAR(MAX) '$.WinnerIDs' AS JSON) oj
Second, this: 
SELECT 
 oj.Race, oj2.WinnerIDs
FROM
  OPENJSON (@JSON)
  WITH
    (Race NVARCHAR(100) '$.Race'
   , WinnerIDs NVARCHAR(MAX) '$.WinnerIDs' AS JSON) oj
 INNER JOIN 
  OPENJSON (@JSON)
  WITH
    (Race NVARCHAR(100) '$.Race'
   , WinnerIDs NVARCHAR(MAX) '$.WinnerIDs' AS JSON) oj2
   ON oj.WinnerIDs = oj2.WinnerIDs
Third this: 
SELECT 
 oj.Race,
 oj.[value] AS WinnerID
FROM
  OPENJSON (JSON_QUERY(@JSON, '$.WinnerIDs')) AS oj
Fourth and lastly,  this: 
SELECT 
 oj.Race, oj2.[value] AS WinnerIDs
FROM
  OPENJSON (@JSON)
  WITH
    (Race NVARCHAR(100) '$.Race'
   , WinnerIDs NVARCHAR(MAX) '$.WinnerIDs' AS JSON) oj
     CROSS APPLY OPENJSON(oj.WinnerIDs) AS oj2
Which of these returns these results? 
RaceWinnerIDs
Furry Scurry11
Furry Scurry14
Furry Scurry15
Furry Scurry16
Furry Scurry45

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)

Secure Enclave Attestation

What does secure enclave attestation do in Always Encrypted?

Answer: Allows the client to establish trust with the secure enclave

Explanation: From Docs: Enclave attestation allows a client application to establish trust with the secure enclave for the database, the application is connected to, before the app uses the enclave for processing sensitive data. Ref: Secure enclave attestation - https://learn.microsoft.com/en-us/sql/relational-databases/security/encryption/always-encrypted-enclaves?view=sql-server-ver16#secure-enclave-attestation

Discuss this question and answer on the forums

 

 

 

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
Vulnerability Assessment removed? - In Brent Ozar's list of links today, I learned that Microsoft has removed the Vulnerability Assessment tool from SSMS as of v19.1. I recently started using it in my SQL Server environment and found it a great tool -- I was able to demonstrate to management why some of their security shortcuts were a bad […]
SQL Server 2017 - Development
Need help with a Query logic - I have a table which tracks of activity when users from one specific app login and logout. But I have seen some cases where let's say a user logins at 11:40 on March 24th and then logins again at 11:55 on the same day without logging out. The application doesn't allow that but I am […]
SQL Server 2016 - Administration
Database copy issue through - COPY DB WIZARD - I have a production server with SQL server 2016 version (13.0.5026.0) and a database with 25 GB of Data and 525 tables in it. Now, I want to transfer this production DB to my development environment with SQL server 2016 version (13.0.5026.0). I am using DB copy wizard for DB copy to the new instance […]
SQL Server 2016 - Development and T-SQL
Help with a SQL Unpivot - I am trying to use unpivot to extract some data, however, the results are getting messed up on at least one record. By this I mean it is out of order which it should be CLNUM: MODEL: CLSTATUS: CLSTATDATE: CLSTOP: Here is the query any help or advice would be appreciated. WITH ClientsWithOpenMattersCTE (ClientNo, HowMany) […]
SQL help with partition - With the following data, I need to create a column that will display a counter for each VID, Acc where PrgFld is not 'XXX' I am trying to do this with partition, but it's increasing the counter when PrgFld is XXX. I would prefer to not increase the counter or show 0 for XXX. Is […]
NOT LIKE Alternatives in WHERE clause - Hello, I need help to improve an existing (hence I cannot change table's structure nor the content's logic) WHERE clause, please. The table consists of returned products with their respective reason(s). In case there is more than one reason, they are concatenated. The request as shown in the mock-up below, has to retrieve a list […]
Administration - SQL Server 2014
Database backups going to device - Hi Friends, How can I know the exact device where my backups are going on? I only see {67BAB2D0-A1DC-44F8-BF73-EBAFD5AE3220}16 as one device. However, I do not find out where are my backup files located. Some databases are going to a particular drive, but others are using that expression, Thank you, Best Regards,
SQL Server 2019 - Administration
Upgrading from 2016 to 2019 with Encryption - Hi, I have a few AWS EC2 instances currently running SQL Server 2016. I'd now like to upgrade these to SQL Server 2019 but the problem is that some of the data has been encrypted at the column level. I understand that, after SQL 2016, the encryption algorithm changed from SHA1 to SHA2. Would this […]
SQL Server 2019 - Development
how to de escalte a isolation level, and are there any implications. - hi, I have one nested transaction and want to deescalate to outer  transaction's isolation/ or lower isolation. is it the right way? SET TRANSACTION ISOLATION LEVEL read committed; BEGIN TRY BEGIN TRAN DBCC USEROPTIONS --want to do some job in read commited SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; BEGIN TRY BEGIN TRAN DBCC USEROPTIONS --want to […]
Advanced SQL query help - Hi, I am looking for some help with two queries using the following table/data. It would be great to see the most efficient way to achieve these. Queries 1: Show all homes with rent above the average rent value for their Company 2: Show the number of homes registered 0-3 months, 4-6 months, 6-12 months, […]
MSSQL Service Broker DSQL operation on another database causing Broker failure - I have established a Service Broker configuration based on Eitan Blumin's excellent example. Advanced Service Broker Sample: Asynchronous Triggers I have crafted my own Stored Procedure. I can get the example to work when I'm performing activities within the same database. If I try to operate outside the current DB I get errors. I'm new […]
Extracting multiple fields from one column -     Hello, I have a situation. I am trying to show each  distinct event category, event action and event label from 'Hits' column and show the number of times each event occurred and the month that the event occurred– for labels containing “GNAV”. I am not sure why but something is wrong. Do you have […]
Analysis Services
How can I show the last opening period for each project in my project dimension - I have two attributes hierarchies in my dimension [Dim Project]: Project Code Opening date Project A with 3 opening date : 2023-01-01 , 2023-01-02 and 2023-01-03 Project B with 2 opening date : 2023-01-02 , 2023-01-04 MDX query : Select [Dim Project].[Project Code] .[Project Code].Allmembers * [Dim Project].[Opening date].[Opening date].Allmembers on 0 [Measures].[Measure1] on 1 […]
SQL Server 2022 - Administration
Allow only encrypted connections. - Hi In SQL22, Is there a way to allow only / force encrypted connections? I have 'force encryption' on and have cert installed:   But when I connect via SSMS I can unselect ' Encrypt Connection' and it connects. My understanding is that means that it is allowing non-encrypted connections: How do I make my […]
SQL Server 2022 - Development
SQL Query - Hi, i have a sample table: table ID    flag    name 1        0     test1 1        0     test2 1        1      test3 2       0     test4 2       0      test5 3       1     test6 4    […]
 

 

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

 

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