SQLServerCentral.com Community Update for {date}

Problems displaying this newsletter? View online.

Featured Contents

Question of the Day

SQL Server Container Editions

The Voice of the DBA

The Aware DBA

A long time ago, at least, a long time in the eyes of my kids, I worked for a large organization as a production DBA. We had a generic monitoring tool, but it wasn't SQL Server specific. I ended up writing my own enhancements for the tool that used its data, as well as gathering other SQL Server specific items into a local database on each instance. From there, I had this data copied to a central location each night and a report generated. This helped us with ISO certification (originally) and later, Sarbanes-Oxley compliance.

I was reminded of that when I read Pamela Mooney's post on Things You Should Know About Your Server. She follows the simple method of ensuring each server monitors itself and rolls up that data. This works well, even if I'd never want to do this again. The time I spent building a monitoring system wasn't really time well spent. These days I'd buy some monitoring software and then query that data, bother the vendor for enhancements and have either a notebook or a smaller set of processes that gathered specific data I might need. Likely I'd use XE and some stored query data, but if I monitor infrastructure, I don't want to have to worry about building and maintaining monitoring software.

The main point from Pamela's post, however, is that she seeks awareness of how the systems work. That's the key for a successful sysadmin of any system. You should not, and do not want to, check each system every day, but you do want to know how to find out what is "normal" quickly and be aware of changes. We want to quickly narrow down potential and possible problems because of our experience and history with some server/instance/application/etc.

Most of us get calls on a few systems regularly. We may know those well, but often we're fire fighting and normal can be an elusive definition. There are often many systems that we rarely get called to examine, but most good DBAs I know have some idea of how almost every system performs. Having a little data available quickly allows us to diagnose whether a problem is real or imaginary, chronic or transient, as well as how to proceed to resolve any issue.

Awareness is an important skill when dealing with anything on a regular basis in your life. Certainly in parts of life outside of work, but even at work, paying attention and having some idea how how your environment should work will help you catch, diagnose, and solve issues. The best DBAs might do this before the client even calls.

Steve Jones - SSC Editor

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

Featured Contents

Making Good Use of SQL Server Auditing

SQLBlimp from SQLServerCentral.com

SQL Server Auditing is a powerful out-of-the box toolset that captures auditing information and writes to a file or the Event Log. This article examines the little things that might snag you up.

An Intro to SQL Window Functions

Additional Articles from SQLServerCentral

Learn about some window function basics in T-SQL

How SQL Server monitoring benefits your whole organization.

Additional Articles from Redgate

The 2019 State of SQL Server Monitoring report showed how instrumental monitoring is to managing server estates. In this guide, we go further, demonstrating how monitoring can benefit your entire organization. From development to IT management, and from finance to the C-suite. Download your free copy of the guide today

From the SQL Server Central Blogs - Off-Topic Origem HS-3 Headphones Review

Steve Jones - SSC Editor from The Voice of the DBA

I’m a big fan of music when I’m moving through life, and I use a lot of headphones. Between the gym, travel, and downtime between volleyball matches, I often...

From the SQL Server Central Blogs - Understanding Columnstore Indexes in SQL Server Part 3

SQLEspresso from SQLEspresso

My last two blogs were on understanding columnstore and creating columnstore indexes. In this post, I will touch on one last topic, when to use columnstore indexes. As noted...

From the SQL Server Central Blogs - Finding Data Gaps

SQLRNNR from SQL RNNR

Quality data is a goal we all strive to attain. Nobody wants to have bad data. Yet, despite our best efforts, the problem of bad data has its way...

Question of the Day

Today's question (by Steve Jones - SSC Editor):
	SQL Server Container Editions
I want to run a Docker container of SQL Server 2017. I know I can to specify an edition as a parameter. The main editions of SQL Server 2017 are: Evaluation Developer Express Web Standard Enterprise What can I specify for the MSSQL_PID environment variable?
Think you know the answer? Click here, and find out if you are right.

Yesterday's Question of the Day (by Owen White)

SQL Update Statement Awareness

I am tasked with updating some values in one of my data fields. I find that there are three unique values in the table: 1, 2, and 3. I only want to update the values of 1 and 2 to the values of 10 and 20 respectively. I want to use the least amount of code so I use the following CASE statement to update the values:

UPDATE my_table SET my_field = CASE WHEN 1 THEN 10 WHEN 2 THEN 20 END;

If I run the following SQL statement, what will the list of unique values in the output show?

SELECT DISTINCT my_field FROM my_table;

Answer: 10, 20 and NULL

Explanation: The result will show 10, 20 and NULL because the UPDATE statement did not specify how to handle values that did not match the criteria so it set the value of 3 to NULL. When using a CASE statement, if you do not specify an ELSE clause the results will return NULL. The output will return the first matching WHEN clause that results in a TRUE boolean result of the comparison. If the ELSE portion is not included the output will return NULL. This is explained in the RETURN TYPE - Return Values in the Microsoft documentation for the CASE (Transact-SQL): https://docs.microsoft.com/en-us/sql/t-sql/language-elements/case-transact-sql?view=sql-server-2017 To preserve values that are not in the comparison, use the following:

UPDATE my_table SET my_field = CASE WHEN 1 THEN 10 WHEN 2 THEN 20 ELSE my_field END;

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

Suspend-ClusterNode -Drain && move-clusterGroup - Hello everyone , if I proceed to do a suspended from the main node of my cluster Suspend-ClusterNode -Drain Are all resources automatically transferred to the second node or I have to do Move-ClusterGroup thanks

File Integrity Monitoring system and Database Server - Looking to find if it is a feasible idea to install FIM system on a SQL Server. It is a sensitive and highly transnational system and I dont feel comfortable. For people who do not know what FIM is please read the below link. https://en.wikipedia.org/wiki/File_integrity_monitoring It may end up messing stuff. […]

SQL Server 2017 - Development

Data quality improvement - Hi I am exploring ideas of improving data quality in new organisation i joined. The issue is it is not straight forward as to re-write things and improve as there are 100s of dataset we process every weeks. I checked few tables for basic checks and found about 40 tables with duplicates. They are actual […]

SQL Server 2016 - Administration

From SSMS, how to see the deleted records in output window - Hi I'm deleting rows from a table and I want to see them in output window. But I'm just getting like (2834 row(s) affected). How to see the actual records it is deleted? Is this possible from SSMS?

SQL Server 2016 - Development and T-SQL

trying to get all data to line up - Hello, first, thank you all who helped or took the time to look at my last issue, but now i got 1 more, and its a tough this time for me so my 2 tables are: declare @Employees TAble ( employeeID int, employeename varchar(256), Hoursworked decimal(18,2), PayrollDate date ) insert into @Employees(employeeID,employeename,Hoursworked,PayrollDate) values(1,'bob',3.0,'05/01/2019') insert […]

Administration - SQL Server 2014

How to track an activity - Hi, A few days ago, I had a selected group of triggers disappear. I say selected because in a giving table only the same group of triggers disappeared and not others. Anyway, I know the date that this happened, is there any way that I can see if a script(procedure) was run and by who […]

Development - SQL Server 2014

something wrong with the left join i think - i should get a few thousand rows back, but when i try pull in Duty Rates, i get hundreds of thousands. Thoughts? ;WITH cte_DutyRates AS ( SELECT DISTINCT ROW_NUMBER() OVER (PARTITION BY Year, HTS8 ORDER BY Duty_Rate DESC) AS RowNum ,Year ,HTS8 ,Duty_Rate FROM NAFTAXXXX.ITC_DUTY_RATES where Year is not null ) select top 200 […]

SQL 2012 - General

Deny Permissions to Schema Owner (assign read only) and deny to Linked Servers - We are running SQL Server 2012 SP4 EE on Windows Server 2008 R2 Standard. We had a developer who moved to another department. We would like to give the developer 'read only' access to the database before eventually disabling his account. We added the developer to the db_datareader database role. I want to make sure […]

data file .mdf datetime stamp on windows file system when running DBCC CheckDB - I'm trying to find out what causes the timestamp to change on the data file when running DBCC CheckDB. I have confirmed that auto grow, auto shrink, open, close or a restart did not occur. What exactly would DBCC CheckDB be doing that it would need to change the date timestamp on the data file?

Understanding the use of a CTE with MERGE - I'm familiar with CTE's which I have used a lot and I am somewhat familiar with the MERGE statement but I've never worked with these 2 together and now that we have recently (finally) upgraded to more recent builds of SQL Server I'm looking at something I need some help understanding. With the understanding that […]

T-SQL (SS2K8)

Convert Rows into Columns - Hello All, I'm trying to convert rows in table to columns as per the example i.e create one long string of data for every occurrence of ID column. Could anyone please help in achieving it in best way. /** Build up a Table to work with. **/ DECLARE @T TABLE ( ID INT NOT NULL […]

Reporting Services

Grouping - Hi, I'm trying to create a report (using a Dynamics 365 Fetch) of all completed bookings grouped by week number, day and account. So far, I've got this: What I want is to group by week number, day name and account so that you expand the week number to reveal a list of accounts with […]

Analysis Services

I have a query mdx that where it doesn't filter - I can't find the reason because a query that has in 'with member' an attribute of a dimensio that is key does not filter with where, if I put another attribute that is not key if it works ?. Dimension FE6 FE6 is key (int) FE6b (int) Descripción (character) WITH SET [FILAS] AS EXCEPT([FE6].[FE6].[FE6].members, […]

Article Discussions by Author

Making Good Use of SQL Server Auditing - Comments posted to this topic are about the item Making Good Use of SQL Server Auditing.

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.