SQLServerCentral.com Community Update for {date}

Problems displaying this newsletter? View online.

Featured Contents

Question of the Day

Protecting the DMK

Featured Script

Unused Indexes

The Voice of the DBA

Daily Coping Tip

Connect with nature. Breathe and notice life continuing.

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.

Server Side SSMS

SSMS is a large monolithic application built on the Visual Studio shell. It's slow to start, heavyweight, resource intensive, and can be a resource hog. It's also indispensable for many SQL Server DBAs.

I grew up using various SQL Server tools. We had isql for Windows early on and then Enteprise Manager before SQL Server Management Studio (SSMS) was built. Now we also have Azure Data Studio(ADS), though arguably for many administrators and DBAs, ADS doesn't have enough capabilities to supplant SSMS. That may change, but for now I find myself sticking with SSMS for most of the code writing I want to do.

Many people install SMSS on the database server itself, and many other people refuse to do so. Most experts agree that running SSMS on the server itself is a bad idea, though that doesn't stop some people from doing so on a regular basis. While I agree with Andy Mallon that we should install SQL Server on a server, I also agree that we shouldn't use it.

That seems counter intuitive doesn't it? Install it but not use it? Why install it in the first place?

When there is a crisis, you will want this tool. It's familiar, it has all the wizards, dialog boxes, and tools that many solutions will describe when you frantically search for a solution to a problem. It's also the best way to get something done if you have to connect from a remote location. Even if your laptop connects, sometimes networking issues, long query times, and more mean you will want local execution of queries, not remote calls from SSMS on a workstation. After all, if something happens to your client, you don't want your query to rollback.

Andy has a good list of reasons, as well as a few tips to avoid running SSMS on the server. For me, I'd recommend a dedicated jump box, server or workstation, where users can RDP to this dedicated box and run SSMS from there. Why? It's becoming more common as organizations try to implement better security. They often do this by limiting access to production from most workstations. In fact, for some clients, that's the only way they can connect to production. I expect that to become more prevalent than not in the future, so I'd suggest you get comfortable with another solution now.

Steve Jones - SSC Editor

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

Featured Contents

Create Custom Functions in Power Query

Archana from SQLServerCentral

Introduction Often at times, you may come across situations where you need to calculate a column repeatedly multiple times in the same Power BI report or across multiple reports. Although you can use the calculated columns to some extent, these are not robust and not reusable. In order to reuse the same piece of code, […]

Tracking Underlying Object Changes in Views

Additional Articles from SQLServerCentral

Views in SQL Server are used to simplify writing queries and managing security, but’s it’s easy for views to eventually get out of sync with the underlying tables. In this article, Edward Pollack shows how to overcome this problem.

From the SQL Server Central Blogs - SQL Server and CPU Hyper-Threading in Virtual Environments

kleegeek from Technobabble by Klee from @kleegeek

I recommend leaving the hyper-threaded logical cores enabled in the host BIOS, but not depending on them for performance gains. Hyperthreaded CPU cores, or logical cores, should not be...

From the SQL Server Central Blogs - Switching between different Azure Data Factory environments

Koen Verbeeck from Koen Verbeeck

When working with Azure Data Factory, it’s possible you have multiple ADF environments. For example, you can have one for dev, one for test and one for production. Unfortunately,...

Question of the Day

Today's question (by Steve Jones - SSC Editor):
	Protecting the DMK
I run this code in a SQL Server 2017 user database to create a Database Master Key (DMK). CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'AR#@llyStrongP@ssword!' Is this DMK still protected by the Service Master Key (SMK)?
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)

A Strange View

I have a table that is defined like this:

CREATE TABLE [dbo].[OrderLineItem]
(
[OrderLineItemKey] [int] NOT NULL,
[OrderID] [int] NOT NULL,
[OrderLinenumber] [smallint] NULL,
[qty] [int] NULL,
[unitcost] [money] NULL,
[linetotal] AS ([qty]*[unitcost])
) ON [PRIMARY]
GO

I want to create a view of just a few of these columns, and I type this:

CREATE VIEW dbo.MyView
AS
SELECT OrderLineItemKey
     , OrderID
     , OrderLinenumber
     , qty
     , unitcost 
 linetotal
 FROM dbo.OrderLineItem AS oli
GO

What happens with this view?

Answer: The view is created with 5 columns

Explanation: This view actually has five columns. The last column, linetotal, is treated as an alias and is used for the header for the unitcost column. View results Ref: CREATE VIEW - https://docs.microsoft.com/en-us/sql/t-sql/statements/create-view-transact-sql?view=sql-server-ver15

Discuss this question and answer on the forums

Featured Script

Unused Indexes

dawaller from SQLServerCentral

This script will provide a list of indexes that are not used in a database. Indexes are overhead and any indexes not being used but are being updated cause additional load on a database. This looks at the following items: User seeks, scans and lookups. If all three of these are zero and the User […]

-- Unused Index Script
-- Original Author: David Waller
-- Date: 4/2020
SELECT TOP 25
o.name AS ObjectName
, i.name AS IndexName
, i.index_id AS IndexID
, dm_ius.user_seeks AS UserSeek
, dm_ius.user_scans AS UserScans
, dm_ius.user_lookups AS UserLookups
, dm_ius.user_updates AS UserUpdates
, p.TableRows
, 'DROP INDEX ' + QUOTENAME(i.name)
+ ' ON ' + QUOTENAME(s.name) + '.'
+ QUOTENAME(OBJECT_NAME(dm_ius.OBJECT_ID)) AS 'drop statement'
FROM sys.dm_db_index_usage_stats dm_ius
INNER JOIN sys.indexes i ON i.index_id = dm_ius.index_id
AND dm_ius.OBJECT_ID = i.OBJECT_ID
INNER JOIN sys.objects o ON dm_ius.OBJECT_ID = o.OBJECT_ID
INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
INNER JOIN (SELECT SUM(p.rows) TableRows, p.index_id, p.OBJECT_ID
FROM sys.partitions p GROUP BY p.index_id, p.OBJECT_ID) p
ON p.index_id = dm_ius.index_id AND dm_ius.OBJECT_ID = p.OBJECT_ID
WHERE OBJECTPROPERTY(dm_ius.OBJECT_ID,'IsUserTable') = 1
AND dm_ius.database_id = DB_ID()
AND i.type_desc = 'nonclustered'
AND i.is_primary_key = 0
AND i.is_unique_constraint = 0
and dm_ius.user_seeks = 0
and dm_ius.user_scans = 0
and dm_ius.user_lookups = 0
ORDER BY dm_ius.user_updates desc
GO

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

Replicatio DB issue - Hi Team , We have Prod publisher database and subscription database using the transaction replication .Last week one of prod application were completely slow due to bad query .So we recreate the query and tested in publisher , it was taking 14 sec meanwhile our developer has executed same query on Subscription database , […]

SQL Server 2017 - Development

Column total using group by rollup - I'm trying to total all the rows but one in a column using group by rollup in SQL Server. I'm getting the below which is correct for all the columns but I'd like to removed the RTF Total-224549.33. Is there a way to have a case statement in the group by rollup or what other […]

Integration service and OLE DB provider Microsoft.ace.oledb.16.0 is not register - Hi to all. I'm in trouble. I want to create an Integration Service project in Visual Studio 2017. I've already installed Microsoft Access Database Engine 2016 Redistributable x64. So when I add a new Excel source type with an OLEDB connector Visual Studio tells me "OLE DB provider Microsoft.ace.oledb.16.0 is not registered". I tried to […]

How create a report based on column value change - I have been asked to create a report based on when a column gets updated with the row and date it was updated. what is the best way to create the report. Table has about 3 million rows and the column report is created on is INT. Thanks in advance.

SQL Server 2016 - Administration

TempDB full - Hello, When the temp db is full ( due to a temp tbl) my learning has been you can drop the temp tbl by going to the tempdb - temp tbls - right click - dropNow, this was done to resolve the issue by dropping the Temp table. I was wondering if dropping the temp […]

SQL Server 2016 - Development and T-SQL

Question regarding decimal field - Dear All I have a question regarding decimal field, i have set field to DECIMAL (18,2) and it looks like this, is there a way to separate thousands with dot(.), so that number looks like 9.090,00 in column. It is a pretty big table and when i got the results after SUM i get unclear […]

Administration - SQL Server 2014

Tracking Connections To a Database - I'm trying to come up with a way to track the history of connections made to one database without setting up a trace or having a job run every minute. So far I've been focusing on the sys.dm_exec_sessions and sys.dm_exec_connections DMVs and each offers some of what I'm looking for (ID, host name, time) but […]

SQL Server 2019 - Administration

How will one setup a SQL FCI on WSFC with Windows Core ? - Multi Subnet WSFC of 4 Nodes 2 Nodes in primary DC and other 2 in DR Center. Idea is to set up failover cluster instance here in Primary DC and another at DR Center . and Setup an AOAG in async mode. Please criticize it if there are any gotchas. Please guide me here. Even […]

rows Issu - Hello Team, I have 2 Tables A and Tables B.In Table A i have 3 rows(1,2,3) and Table B i have 3 rows(4,5,6).I need to get all the rows but i need to have single cloumn.Please advise Thanks in advance RK

How do I use cascade delete with SQL Server? - I have 2 tables: T1 and T2, they are existing tables with data. We have a one to many relationship between T1 and T2. How do I alter the table definitions to perform cascading delete in SQL Server when a record from T1 is deleted, all associated records in T2 also deleted. The foreign constraint […]

SQL Server 2019 - Development

sum of sales 2 dates - if object_id( 'tempdb..#anchorj') is not null begin drop table #anchorj end create table #anchorj ( ID varchar(10),anchor sales date date) User ID Sales Date S12 3/12/2015 S13 4/5/2017 S14 2/15/2016 S15 1/1/2017 S16 5/4/2014 S17 3/24/2017 S18 2/6/2015 S19 4/18/2017 S20 6/2/2015 S21 7/5/2016 S22 8/2/2015 S23 8/2/2018 Select Distinct User ID, sum ( sales_amt) […]

Sync data between Two databases - Hi All, This is sampath i am new to sqlservercentral community,i am begginer in sql related queries please help me to achieve. I have two databases in same server assume Realtime database and Backup database these two databases contains 25+ tables. i have done insert/delete/update in Realtime database automatically Backup database should be updated. Is […]

SSRS 2016

Font renders correctly online, but not when exported to PDF or Excel - Hello, I'm having a problem with Wingdings/Wingdings 2 in SSRS reports exported to PDF or Excel (haven't tried other export formats). In my report I set the cell value and FontFamily from the dataset, i.e.; Value FontFamily * Wingdings P Wingdings 2 and it renders correctly online (*/Wingdings = envelope, P/Wingdings 2 = check mark); […]

Analysis Services

The 'Tabular View' feature is not included in the '64 Bit Standard' edition of - Hi , I am getting the error below when i want to process the cube.Please help me if anyone came across this before. "Errors related to feature availability and configuration: The 'Tabular View' feature is not included in the '64 Bit Standard' edition of Analysis Services" I am running sql 2014 standard and visual studio […]

COVID-19 Pandemic

Daily Coping 16 Apr 2020 - Today's tip is to put your worries into perspective and try to let them go. No easy to do, but here are my thoughts: http://voiceofthedba.com/2020/04/16/daily-coping-16-apr-2020/

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.