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

Daily Coping Tip

Take a photo of something that brings you joy and share it.

I also have a thread at SQLServerCentral dealing with coping mechanisms and resources. Feel free to participate.

 

For many of you out there working in a new way, I'm including a thought of the day on how to cope in this challenging time from The Action for Happiness Coping Calendar. My items will be on my blog, feel free to share yours.

Other IDEs

Most of us use SSMS (SQL Server Management Studio) to manage our SQL Server instances or to write database code. However, Microsoft does give us database options with Visual Studio, VS Code, and Azure Data Studio. It does seem as though Microsoft would prefer more of us move to ADS, but I'm not sure I'm ready, and I wonder if you are.

I saw a list recently of some other tools, and I wonder if anyone uses these in their work. Most of these are cross platform, and support a number of different database platforms. Datagrip might be the most popular, but there are others. SQL Manager, Adminer, DBVis, SQLPro, Flyspeed, and ss are ones I've heard of in the past. I've used DBArtisan at one company, and loved it as I could work disconnected. At the time, Query Analyzer didn't support offline work.

Having an IDE that you are comfortable using is important to working efficiently. Many of us become quite used to the flow of our tool, we develop habits, muscle memory, and shortcuts that work for us. We get used to where things appear on the screen and what icons to click. Changing IDEs can be very disruptive and frustrating while you get used to a new interface. I still remember going from Enterprise Manager to SSMS and it was an annoying time period as I got used to SSMS.

These days I find myself trying ADS often, but usually falling back to SSMS. I think that visually the basic HTML-like display is annoying. I have much preferred the thick client look at SSMS. It doesn't help that some of the tools I often use don't quite work the same in ADS. If we get a bit more of SQL Prompt in there, I might change my mind, but using the Command Palette rather than keyboard shortcuts is somewhat unfamiliar to me. What's strange is that I like VS Code overall, and use it for PoSh, but for some reason it's not the same with databases.

I am glad we have choice in tooling, though I don't know that any large set of users actually use anything more than what is installed with SQL Server. I don't know if other tools will allow anyone to produce more, or better, code, but I do know that I have found that some tools do help. It can be worth spending a bit of time experimenting with some other IDE to see if your work improves.

Steve Jones - SSC Editor

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

 
 Featured Contents
Stairway to DAX and Power BI

Stairway to DAX and Power BI - Level 12: Function / Iterator Function Pairs: The DAX CountA() and CountAX() Functions

Bill Pearson from SQLServerCentral.com

Business Intelligence Architect, Analysis Services Maestro, eight-year Microsoft Data Platform MVP and author Bill Pearson introduces the DAX CountA() and CountAX() functions, discussing the syntax, uses and operation of each. He then provides hands-on exposure to CountA() and CountAX(), in counting non-empty cells in a column, and in counting nonblank results when evaluating the result of an expression over a table, respectively.

Take our short readers’ survey and win a $50 Amazon voucher

Additional Articles from Redgate

Could you spare 3 minutes to help us out at SQL Server Central? Take this short survey, tell us about who you are and why you come to SQL Server Central, and you could win a $50 Amazon voucher.

Monitor Deadlocks in SQL Server with system_health Extended Events

Additional Articles from MSSQLTips.com

In this article we look at how to find SQL Server deadlocks using the system health Extended Events session.

Free eBook: Troubleshooting SQL Server: A Guide for the Accidental DBA

Press Release from Redgate

Three SQL Server MVPs (Jonathan Kehayias, Ted Krueger and Gail Shaw) provide fascinating insight into the most common SQL Server problems, why they occur, and how they can be diagnosed using tools such as Performance Monitor, Dynamic Management Views and server-side tracing. The focus is on practical solutions for removing root causes of these problems, rather than "papering over the cracks".

From the SQL Server Central Blogs - Getting Started on AWS RDS

Grant Fritchey from The Scary DBA

I’m expanding my skill set into AWS in a big way. So, one of the things I do when I’m learning a technology is to write blog posts about...

From the SQL Server Central Blogs - Stairway to Microsoft Azure SQL Database Part 4 : Azure SQL server , an overview

nelsonaloor from PracticalSQLDba

In the last few posts, we were discussing about database deployment model and purchase model. Out of the three deployment models, managed instance works almost like on-premises SQL server...

 

 Question of the Day

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

 

Getting the Group Counts

I have this dataframe in Python: 
sales.head()
DateDayMonthYearCustomer_AgeAge_GroupCustomer_GenderCountryStateProduct_CategorySub_CategoryProductOrder_QuantityUnit_CostUnit_PriceProfitCostRevenue
02013-11-2626November201319Youth (<25)MCanadaBritish ColumbiaAccessoriesBike RacksHitch Rack - 4-Bike845120590360950
12015-11-2626November201519Youth (<25)MCanadaBritish ColumbiaAccessoriesBike RacksHitch Rack - 4-Bike845120590360950
22014-03-2323March201449Adults (35-64)MAustraliaNew South WalesAccessoriesBike RacksHitch Rack - 4-Bike2345120136610352401
32016-03-2323March201649Adults (35-64)MAustraliaNew South WalesAccessoriesBike RacksHitch Rack - 4-Bike204512011889002088
42014-05-1515May201447Adults (35-64)FAustraliaNew South WalesAccessoriesBike RacksHitch Rack - 4-Bike445120238180418
Which of these will give me count of distributions for each of the values in Age_Group?

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)

The Last Sequence

I create a new sequence object.

CREATE SEQUENCE dbo.DecrmentByOne AS SMALLINT INCREMENT BY-1;

What does this code return?

SELECT NEXT VALUE FOR dbo.DecrmentByOne

Answer: 32,767

Explanation: The default value for a sequence object is the min or max of the datatype. If the object decrements, then the max value is chosen. Ref: CREATE SEQUENCE - https://docs.microsoft.com/en-us/sql/t-sql/statements/create-sequence-transact-sql?view=sql-server-ver15

Discuss this question and answer on the forums

 

Featured Script

How to register SPN for SQL service account

s.charaya from SQLServerCentral

Check if the SPN is already registered: setspn -l domain\xxxxx If not, run below commands: setspn -A MSSQLSvc/abc.xx.companyname.com:1433 domain\xxxxx setspn -A MSSQLSvc/abc.xx.companyname.com domain\xxxxx setspn -A MSSQLSvc/abc:1433 domain\xxxxx setspn -A MSSQLSvc/ abc domain\xxxxx   Verify again, setspn -l domain\xxxxx

setspn -A MSSQLSvc/abc.xx.companyname.com:1433 domainxxxxx
setspn -A MSSQLSvc/abc.xx.companyname.com domainxxxxx
setspn -A MSSQLSvc/abc:1433 domainxxxxx
setspn -A MSSQLSvc/ abc domainxxxxx

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
Performance Tuning a SQL Server Database in Amazon RDS - Hi Team, Thanks in advance for simplifying the the people tasks by your valuable suggestions. I am being assigned a new task where in I have to analyze a SQL Server DB in Amazon RDS. I am struck with below issues. How to performance tune the Database. Can I just a take a backup and […]
SQL Server 2017 - Development
PIVOT query - Hi I have this query using PIVOT SELECT * FROM ( SELECT a.Mun, l.Name, a.NumberOfRooms FROM Acco as a INNER JOIN look as l ON a.AccoTypeId = l.id INNER JOIN reg as r ON r.AccoId = a.Id WHERE r.RegDate IS NOT NULL AND r.RegDate <> '' and a.Mun like 'CB%' ) as t PIVOT( SUM(NumberOfRooms) […]
Using a PARTITION BY JOIN to Fill In The Gaps in Sparse Data - Hello Trying to convert an Oracle SQL which uses "partition" in Left outer join into SQL server as shown below. select v.table_name , v.program_no , v.column_name , a.creation_timestamp , a.record_key , a.before_value , a.after_value from audit_010_v v left outer join audit_data a partition by (table_name, creation_timestamp, record_key) on a.table_name = v.table_name and a.column_name = v.column_name […]
SQL Server 2016 - Administration
SSRS "My Subscriptions" Fails to Load if TLSv1.0 Is Disabled on the Server - We have noticed in the past that if we disable TLSv1.0 in HKey_Local_Machine\System\CurrentControlSet\Control\SecurityProviders \SCHANNEL\Protocols\TLS 1.0\Server\Enabled (set to 0), most functionality still works in SSRS, but "My Subscriptions" fails to load, giving the not-very-descriptive error: "An error has occured. Something went wrong. Please try again later." Upon changing the setting to 1, "My Subsriptions" works as […]
SQL Server authentication - I see my sql server is NTLM authentication enabled. I am looking to connect using my mac pro laptop now and I am not able to connect to sql servers as they use windows authentication without kerberos so I can't connect from non windows machines. I am thinking to use sql account instead of enabling […]
adding a Select statement to an existing table - I have this table abc. when I script it out it contains Create table and Alter procedure commands. Now I want to add a Select statement to this table, so when I script out the next time it should consist of Create table, Alter procedure and the new Select statement Is there a way to […]
Is there anything in SQL Server that is analogous to Autonomous DB in Oracle? - Or is anything like that is planned to roll out in the SQL Server world?
SQL Server 2016 - Development and T-SQL
In general how do you know which fields to join on between 2 tables? - I know this is a broad question. I have 2 tables (no primary/foreign keys).  In general, with 2 tables, how do you know which fields to join on and who many fields do you use? From what I understand, the more fields you join on the more narrow you are making your data. But do […]
Comparing 2 large tables row by row and date comparision - We have a functionality where in we need to process a large file containing around 1.5 million rows. We insert the data from the file in a secondary table and once this insert is complete, we compare it with our parent table and only insert rows which have changed. The parent table contains around 2.5 […]
Project EVERYTHING is assigned to me. Can I measure Everything via T-SQL? How? - Is it reasonable to roll-up ALL metrics from Query Store to measure 'everything'? and then compare 1 sp  (input param, SP name) metrics with 'Everything else' on server (or should it be by db?)  one stored proc watch is easy via Query Store. But aggregating ALL THE REST ('Everything') for even just one day is […]
SQL Server 2019 - Development
Extracting a Date Properly from a String. - If i have a string of the following format master_20200613220000.bak Then How do i display the Date as 2020-06-13 22:00:00.000 Please Help.  
Hi all, just registered with SQLServerCentral. - Hi all, I am looking for some one to join me in learning and developing a small project.  I have some background on MS Sql Server 2012. If there is some one there with a project or we could find one and learn together,  please let. I  believe the best way to learn quickly is […]
Two Strings compare using t-sql - Declare @Intstr1 varchar(100) Declare @Intstr2 varchar(100) Set @Intstr1 = '1,2,3,4,5,6,7,8,9'   -- This will NOT contain duplicates Set @Intstr2 = '4,6,6,7,7'  -- This string may have duplicates but surely exists in str1 Need a t-sql for finding numbers from Str1 which are NOT in Str2 Please help. Expected output.. @output = '1,2,3,5,8,9'    
General
ISNULL clause with a condition - Hi, I've got the following code to select the period where I have active data: { FIXED : MAX(IF NOT (ISNULL([Qty in tn])) THEN [Period] END)} The problem I have is that the Qty in tn is only null when another column [Data Type] = ACT. How do I modify the above code to take […]
COVID-19 Pandemic
Daily Coping 15 Jun 2020 - Today’s tip is to find a joyful way of being physically active (indoors or out). http://voiceofthedba.com/2020/06/15/daily-coping-15-june-2020/
 

 

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

 

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