SQLServerCentral.com Community Update for {date}

Problems displaying this newsletter? View online.

Featured Contents

Question of the Day

Checking the Check Constraint

The Voice of the DBA

Daily Coping Tip

Set a goal that links to your sense of purpose in life

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.

The Forever Technology War

This editorial was originally published on Nov 1, 2016. It is being republished as Steve is on holiday.

The Forever War is a classic science fiction novel. I'd highly recommend it if you enjoy that genre, but even if you aren't a science fiction fan, this is a story that can really make you think. The gist of the story is that as various parts of our military travel faster than light, we might encounter foes (or other units of our own military) that are substantially ahead of, or behind us, in technology. All because of the nature of time travel. Recently I ran across a few situations that reminded me of this story.

The first item was at home, where my son's computer acted up. He likes Windows 7 and has stuck by it. However an update caused boot corruption and he wasn't able to repair the system. In the process of trying to get repair things, we dropped into the command line to run chkdsk and bootrec. He wasn't sure how those worked, which makes sense. He's 17 and most of his life has been spent in GUI OS's. However, once we realized he needed to reinstall, I told him to copy things off his boot drive to another drive. I showed him a bit of moving around the command line, making directories, listing contents, copying files. As I was walking away, he asked, "how do I make a folder?"

A folder. Not a directory. We've had not only a technology shift, but also a nomenclature change in computing. It had never occurred to me that my son wouldn't know basic command line calls. As a side note, he greatly appreciated the /? parameter to learn more about commands.

The second situation was at a conference, where I was talking about continuous integration and deployment. A person asked about SSIS packages, and how would you move these with a tool like TeamCity or VSTS. I asked how they'd do this manually, and said that we would just program that. This person asked how they'd program the steps they take in SSDT. My answer was to use the command line, but this person had no idea there was a command line. Their whole career had been spent in GUI systems and they assumed that was the only way. Hint, pretty much everything can be run from a command line.

Those situations showed me that we have a technology shift in place. There are things that some of us have grown up with, used as a base to learn more, and we can assume that others have similar paths. After all, wouldn't you need to know about x to learn about y?

Not necessarily. The longer I am in this business, the more I see that the experiences and knowledge of different people can be vastly different. A person that knows how some technology, say T-SQL and SQL Server security might have no idea how replication works. Or they might know AlwaysOn well, but couldn't implement an Extended Event session. There are gaps and holes in the knowledge that each of us has, and assuming that others have similar understanding of any particular item can cause us problems when we try to work together.

Understanding, transparency, sharing knowledge, and appreciation of diverse experiences can help us all learn, and work together better. If a situation seems strange or a person is confused, perhaps a few questions on their frame of reference of the situation is a better response than assuming they are incompetent or telling them to RTM.

Steve Jones - SSC Editor

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

Featured Contents

Automate MSBI deployments Using PowerShell

Kunal Rathi from SQLServerCentral.com

There are various ways we can deploy MSBI project deliverable. This article will give you an idea to automate MSBI deployments using Windows PowerShell.

Git Anatomy

Additional Articles from SimpleTalk

In this article, Dino Esposito gives an overview of git explaining repository, commit, and branch.

Format your own SQL code using this free online formatter

Additional Articles from Redgate

Redgate have launched their very own Online SQL Formatter powered by SQL Prompt. Visit the page here to try formatting your SQL code with a few pre-defined styles, or find out how to create and share your own styles with SQL Prompt.

From the SQL Server Central Blogs - Quick Start to Using Influxdb on Macos

Sheldon Hull from Sheldon Hull

Intro
OSS 2.0 is a release candidate at this time, so this may change once it’s released.
It wasn’t quite clear to me how to get up and running quickly with...

From the SQL Server Central Blogs - Error: 8623 - The query processor ran out of internal resources and could not produce a query plan

@DBA_ANDY from Nebraska SQL from @DBA_ANDY

Unfortunately a common error in many of our client environments is this:
--

Error: 8623, Severity: 16, State: 1. The query processor ran out of internal resources and could not produce...

Question of the Day

Today's question (by Steve Jones - SSC Editor):
	Checking the Check Constraint
What happens when I run this code? CREATE TABLE MyTest ( myid INT , myval INT CHECK myval < 10 ); GO DROP TABLE MyTest;
Think you know the answer? Click here, and find out if you are right.

Yesterday's Question of the Day (by BTylerWhite)

Enumerate the Python List

I have the following code in Python:

animals = ["cat", "dog", "elephant", "fox", "horse"]

for index, animal in enumerate(animals, start=1):
    print(animal)

print(index)

What will be the result of printing the index variable after the loop completes?

Answer: 5

Explanation: In Python, we can use the built-in enumerate function to return an enumerable object from a sequence. Enumerate will return a tuple result including the count of the values of iterating over the sequence. In the example provided in the question, the start argument was provided with 1, indicating to not start at 0 by default. The "animals" list variable was provided as the iterable sequence. This function is particularly useful to avoid the requirement of specifying a "counter" variable and adding to it throughout a traditional loop. This could be achieved in a similar way using a counter variable, like so:

animals = ["cat", "dog", "elephant", "fox", "horse"]

index = 0

for animal in animals:
    print(animal)
    index += 1

print(index)

References: https://docs.python.org/3/library/functions.html#enumerate

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

Distributed Availability Group Failover test without affecting Primary site - Hi I have created a test environment with Distributed Availability Group (SQL 2016) for DR Testing and I can fail over to DR. Now I need to test the DR site databases with application. To avoid the test data sync back to Production site, i have to drop the Distributed AG. Is there any way […]

Locking issue - All, I would appreciate some advice on identifying an issue. The error I received is: "The instance of the SQL Server Database Engine cannot obtain a LOCK resource at this time" I was monitoring sys.dm_tran_locks and I saw the query is taking a lot of page locks. I'm therefore thinking that the issue is either: […]

SQL Server 2017 - Development

Visual Studio Database Project Reference to a Non-Project Database - I have a database solution in Visual Studio Enterprise 2019 which consists of four database projects. My databases live on a shared SQL Server instance that hosts multiple solutions. My database projects take advantage of objects on that instance in another database that I do not control, and is not managed in a VS solution […]

SQL Server 2016 - Administration

Availability Group Latency Check - How can I check if there is latency between primary and secondary on SQL Server 2016. Are there any scripts available?. What in specific you look for in the availability dashboard. For example, I see all the db's in green ( healthy) however found out that we still had 40 secs latency. Do you […]

Recovering SQL databases - Hi I'm looking into recovery plan for individual SQL databases. The scenario I'm planning for, is if a single SQL database on an instance with multiple databases, for multiple applications, becomes unavailable. My thought is to have a cold standby server we can restore the database to, then use CLIconfig on the application servers to […]

SQL Server 2016 - Development and T-SQL

Ugly code fix - CASE WHEN Phone IS NOT NULL OR MobilPhone IS NOT NULL THEN CONCAT(CONCAT(LEFT(COALESCE(Phone,MobilPhone),3),'-'), CONCAT(CONCT(RIGHT(LEFT(COALESCE(Phone,MobilPhone),7),3),'-'), RIGHT(LEFT(COALESCE(Phone,MobilPhone),12),4))) ELSE '' END As PhoneNumber I inherited this god awful code. I comment this variable out and the query executes in < 30 seconds against a sizable number of records. (~ 1MM). I add this little tidbit of insanity back […]

Administration - SQL Server 2014

How to find all Linked Servers pointing to our instance? - Hi all. We are preparing for migration, and scripting all our Linked Servers. But how to find all other Linked Servers, that originate on different instances but leading to ours? Thanks

Development - SQL Server 2014

TRY_CONVERT missing? - Hi gurus! I am trying to use the try_convert function on a SQL Server 2014 Standard Edition and a SQL Server 2016 Developer Edition (my staging) and I am getting this: Msg 195, Level 15, State 10, Line 11 'TRY_CONVERT' is not a recognized built-in function name. Am I missing something? As usual, any help […]

location address without file name - If my column contains these values. C:\AABCD\EDFG\RFG\MyDB123.bak D:\RFG\MyDB1123.bak E:\AABCD\EDFG\MyDB1223.bak How do I select only these in my output C:\AABCD\EDFG\RFG D:\RFG E:\AABCD\EDFG Thanks

string selection - If my column contains these values. A:\AABCD\EDFG\RFG\MyDB123.bak A:\RFG\MyDB1123.bak A:\AABCD\EDFG\MyDB1223.bak How do I select only these in my output A:\AABCD\EDFG\RFG A:\RFG A:\AABCD\EDFG Thanks

SQL Server 2019 - Administration

A read operation on a large object failed - I got an alert every day recently at different time on SQL server box DESCRIPTION: A read operation on a large object failed while sending data to the client. A common cause for this is if the application is running in READ UNCOMMITTED isolation level. This connection will be terminated. This is hard for me […]

Numerous IF and Else clauses & its execution time in sys.dm_exec_procedure_stat - Hey SSC, Please if we have a stored procedure in which at a time only a single code segment of IF is executed out of many IFs in the procedure. Every IF code segment has a different query and execution time. So on cumulative what info would be sys.dm_exec_procedure_stat will bring to us? and how […]

SQL Server 2019 - Development

Hello I need help to improove the following query with many joins - Hello I have this query SELECT ISNULL(sta5.AttributeValue, rdd5.Value) as 'Manufacturer Code', ( SELECT TOP 1 ID2.Content FROM ItemMaster IM join ItemMasterDetail ID1 on IM.Id = ID1.ItemMasterId and ID1.ItemMasterAttributeId = 14 join ItemMasterDetail ID2 on IM.Id = ID2.ItemMasterId and ID2.ItemMasterAttributeId = 15 WHERE ID1.content = ISNULL(sta5.AttributeValue, rdd5.Value) and ID1.Sequence = ID2.Sequence order by ID2.[Sequence] desc […]

SQL Server 2008 - General

Select query is slow for Non clustered index - I am using SQL Server 2008 R2. Select query is slow when non cluster index is used in the table compared to table without index. Query used: select * from table order by customer_name It is too slow (more than 1 minute for 40000 rows) Error was due to non-clustered index , without index query […]

SQLServerCentral.com Website Issues

Can post please be stored in nvarchar? - It actually surprises me this should even be a request. Post data, however, appears to be stored as a varchar, not an nvarchar which is causing malformed post, including today's QOTD. Obviously this data has not been lost, but this should be fixed, as it can't be assumed that post will only contain characters from […]

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.