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

Using the Lightweight Tools

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

Finding Non Current Cumulative Updates

Steve Jones - SSC Editor from SQLServerCentral

A short article to help you quickly find a CU that isn't the latest one.

Redgate acknowledged as a Representative Vendor in Gartner’s 2019 Market Guide for Data Masking

Additional Articles from Redgate

Data Masking is key to providing realistic test data for DevOps teams while ensuring compliance with GDPR, CCPA, HIPAA and other regulations. Gartner recently published their 2019 Market Guide for Data Masking covering their analysis, recommendations, and Representative Vendors – including Redgate. Get the Complimentary Report.

Memory-Optimized TempDB Metadata in SQL Server 2019

Additional Articles from MSSQLTips.com

In this article we look at a new feature in SQL Server 2019 that can help improve TempDB performance.

From the SQL Server Central Blogs - New Power BI Report Design Pre-Con in 2020

Meagan Longoria from Data Savvy

I’m excited to announce that I will be offering a full-day pre-con about Power BI report design in the coming year called Bookmarks, brain pixels, and bar charts: creating...

From the SQL Server Central Blogs - Better, faster, cheaper: SQL Server on Azure IaaS

kleegeek from Technobabble by Klee from @kleegeek

GigaOm, an independent research firm that I highly respect, released a study today comparing performance and price between a SQL Server VM on Microsoft’s Azure platform and a SQL...

 

 Question of the Day

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

 

Changing Algorithms

I create a symmetric key in a SQL Server 2016 database. I use this code: 
CREATE SYMMETRIC KEY SalaryKey
WITH ALGORITHM = AES_256
, IDENTITY_VALUE = 'Salary Protection'
, KEY_SOURCE = 'Protect this from hackers'
ENCRYPTION BY PASSWORD = 'SomeReallyStr0ngP@ssword';
GO
I then use this to encrypt some data in a table: 
OPEN SYMMETRIC KEY SalaryKey DECRYPTION BY PASSWORD  = 'SomeReallyStr0ngP@ssword'

UPDATE dbo.Employees
 SET EncryptedSalary = ENCRYPTBYKEY(KEY_GUID('SalaryKey'), CAST(Salary AS VARCHAR(50)))
GO
I now transfer this data to a SQL Server 2017 database, into the same structure table. Now I run this: 
CREATE SYMMETRIC KEY SalaryKey
WITH ALGORITHM = AES_256
, IDENTITY_VALUE = 'Salary Protection'
, KEY_SOURCE = 'Protect this from hackers'
ENCRYPTION BY PASSWORD = 'SomeReallyStr0ngP@ssword';
GO
OPEN SYMMETRIC KEY SalaryKey DECRYPTION BY PASSWORD  = 'SomeReallyStr0ngP@ssword'
SELECT top 10
 e.EmpID
, e.EmpSSN
, e.Salary
, CAST(DECRYPTBYKEY(e.EncryptedSalary) AS VARCHAR(50)) AS DecryptedSalary
, e.EncryptedSalary
 FROM dbo.Employees AS e
GO
What is returned in the DecryptedSalary column?

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)

Renaming Columns

I have imported the numpy and pandas modules as np and pd, respectively. I have this data frame in my REPL.

>>> df
  BillMonth  BillingAmount
0       May            300
1      June            450
2      July            600
3    August            900

I want to rename BillingAmount to InvoiceAmount. How can I do this?

Answer: Either 1 or 3 above

Explanation: You can use the df.columns or df.rename lines to do this. If you rename, give the current name of the column and the new name. Ref:

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
Always on in standard and enterprise edition - Hello everyone! I would glad to know, what is the difference in Always on between Standard and Enterprise edition? And also there is something that change in 2012 and above? Thanks!
Migrating DB encrypted by master key - I'm migrating DBs from a 2008 R2 instance onto a 2017 instance.  I have one DB that is encrypted by the master key, how do I migrate this successfully across to a new instance?
Log Shipping - What has changed - We currently have a situation with a vendor where they will only give us access to our data via 5 minute log ship files that we import into our read only db. this is fine for most queries, but we need a way to get details about what has changed in each period in the […]
SQL Server 2017 - Development
Insert in batches using select coalesce option - Hi, I am trying to insert multiple tables data into one table using Insert in batches and with select coalesce. I am getting Ambiguos column (id) error. Can some one please help me to resolve the issue. Thank You. ERROR MESSAGE: Msg 209, Level 16, State 1, Line 26 Ambiguous column name 'id'. Line:26 - […]
Fastest Way to Calculate Total Number of records inserted per day - Hi, I need to show a number of records per day in the dashboard of the web application if the user reload the page the count will automatically be updated. Initially, we don't have any problem as the records grow to the millions of records the query execution takes slow. The following is the query […]
Fastest way to query Millions of records with Pagination and Total Record Count - HI, We had a table that contains a huge volume of data inserted per day it contains millions of rows. Currently, we had 2 million records in staging but once we hit the production the data will be easily more than 10 - 20 million rows Previously we had a pagination query that took more […]
SQL Server 2016 - Administration
changing db owner with always on replication (to fix DB properties window) - SQL Server 2016 standard edition, with primary databases and one other server set up as secondary failover with always on availability group. There were some issues over the weekend with an application that accesses the databases on this server, unbeknownst to me, they decided just to reboot the database server, which seemed to clear things […]
What\'s the best way to keep a record of all records inserted via an SP call? - Hey guys, so I may be overthinking this but basically, I have a bunch of stored procedures which I want to begin keeping historical track of.  I want to know which records were updated by which SP & when, and I'm just looking for the best way to do this.  Is there any built in […]
Impact value about missing indexes - is there relation between Impact from sys.dm_db_missing_index_group_stats and Impact from cache plan? Thanks for all.  
I can't Uninstall SQL Server 2016 - Hi, I have SQL Server 2016 installed in my machine but I can't uninstall it, it doesn't appear on my Control Panel, I looked again using CMD wmic, product get name, it also doesn't appear. But I can see it, SQL Server on the services and SQL Server Configuration Management. I also tried using CMD […]
SQL Server 2016 - Development and T-SQL
BCP utility - Hi All I am trying to export some data from a SQL server database to a CSV file. As I need to do this for around hundreds of records, I am using a cursor to loop through and creating dynamic file names to write individual files. This seems to be working ok. Some of the […]
Administration - SQL Server 2014
Trouble connecting in single user mode - I have an SQL server which I need to get admin access to. Logged on as administrator to the server I attempt to start in single user (-m) mode. However in this case, instead of dropping into single user (as on other servers in this project),  I get a screen full of continuous login failures […]
Development - SQL Server 2014
How to get information about an AG group using powershell - Hi Experts, Does anyone has a powershell script which displays below information: for a given AG listener name or AG group name, I need to get Listnener name, AG name, replicas , replica role desc , Can we get this information using pure powershell which reads registry values or do we need to execute a […]
SQL 2012 - General
SQL Monitoring - Hi All, I have SQL 2012 instance on a vm server  which I am testing with Red Gate Monitoring Tool installed on my machine and my machine machine is set a base monitor for testing purpose. Problem I am facing is that  it only monitor while I am logged on to the machine. If  I […]
Strategies and Ideas
Tracking History in Fact Table - Hi, I was hoping somebody would be able to offer advice in relation to tracking history in a fact table, in particular whether #2 below would be a viable option? I would have thought that #1 would be bad for performance given the volume of records created through history so maybe not best practice?. If […]
 

 

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

 

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