SQLServerCentral.com Community Update for August 6, 2020

Problems displaying this newsletter? View online.

Featured Contents

Question of the Day

Writing Dirty Pages

The Voice of the DBA

Daily Coping Tip

Try a new recipe or food

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.

Security for 2FA

I got a new mobile device recently and despite the restore from backup, I still had to perform quite a few setup chores. Reconnecting various apps to services by logging in was time consuming, but simple. A password manager was useful here, since I have different passwords everywhere. I set that application up first and then proceeded from there.

The only trouble I really had was with Outlook and mail. I have an IMAP provider for one account and Outlook doesn't make it easy to figure out where to change the settings for this. The second issue was with my corporate mail, which is secured with a 2FA connection through Duo.

This is a technology that doesn't use text, but has an application on devices and pushes a notification to them. I get a push when I log onto corporate assets from my PC, and I have to approve a login from my mobile. This prevents sum of the sim hijacking that can occur, where someone might have access to my SMS messages. It also prevents a lazy guy with Google Messages from getting a code on the same device rather than walking to the other room to get my phone an ensure I have it.

I had to get help from our admins with the new device, despite having the old device connected and able to approve the connection. I had thought this would work, but there was some issue with Duo. On one hand, maybe this is good that it isn't simple and I can't quickly approve a new device, in that it prevents someone from stealing my identity on a cloned device. On the other, in an organization of any size, where mobiles might change constantly, this feels like a time sink.

This might not be an attack vector to be worried about, but I think it's better to be safer here than we might need. I do think that good security is worth some hassle. I don't really complain about having to periodically re-authenicate, the requirement for strong passwords, and I love having 2FA enabled on many services. I hope we continue to find more and stronger methods of protecting individual and corporate data, along with more respect for the rights of humans whose data is being captured and used by most organizations. I also hope that we continue to improve the security options in SQL Server, including adding 2FA.

Steve Jones - SSC Editor

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

Featured Contents

Basic Git for DBAs: What's a Pull Request?

Steve Jones - SSC Editor from SQLServerCentral

Steve continues his series on Git for DBAs by looking at code review and pull requests.

Hands-On with Columnstore Indexes: Part 3 Maintenance and Additional Options

Additional Articles from SimpleTalk

In the final article of this series, Edward Pollack demonstrates the maintenance of columnstore indexes. He also takes a look at nonclustered columnstore and memory-optimized columnstore indexes.

What specific deployment data is valuable to you or your organization?

Additional Articles from Redgate

Redgate have assembled a small development team to research and build a capability that not only records your deployments over time, but provides a window into past trends and insights. To achieve the best results we need to understand from you what deployment value should be recorded and displayed. Please help us by completing our short two-page survey.

From the SQL Server Central Blogs - Database Fundamentals #28: Creating a Primary Key Using T-SQL

Grant Fritchey from The Scary DBA

There are actually a couple of ways to create a primary key with T-SQL. You can use the ALTER TABLE script to add a primary key to an existing...

From the SQL Server Central Blogs - Sending a SQL Agent Job results overview to a Microsoft Teams Channel

mrrobsewell from SQL DBA With A Beard

Microsoft Teams is fantastic for collaboration. It enables groups of people, teams if you like to be able to communicate, collaborate on documents, hold meetings and much much more....

Question of the Day

Today's question (by Steve Jones - SSC Editor):
	Writing Dirty Pages
How are dirty pages written to disk in SQL Server?
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)

Filtering an R Dataframe

I have a dataframe in R, called sales. In this dataframe, I have a column called Revenue. I am looking for the high values in this dataframe and want to filter this to just those results that have a value > 15000 for the revenue. What expression gives me this?

Answer: subset(sales, Revenue>15000)

Explanation: The subset function will do this. If you want to use the sales[] notation, you need a comma after the parameter in R to get the filter to work. Ref: filtering data - https://cran.r-project.org/web/packages/crunch/vignettes/filters.html

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

Solutions for real-time replication of data from one SQL instance to another - Hi, I'm hoping for some opinions on the best options for how to replicate, in real time, data from a production SQL Server database onto another SQL instance. This second instance would be for vendors who need to access some of the tables from the production database, but we would prefer that they not directly […]

Faster Integrity check - We are upgrading from SQL 2014 to SQL 2017. Noticed that integrity checks are running 6 times faster than SQL 2014. Is that a big enhancement in SQL 2017? Do you agree with me?

SQL Server 2017 - Development

Insert missing records for each month/year with the previous month's value - I have a sample data, see below. Sample data The base rules are If the same CustomerID and Account# for the month that don't exist based on the previous month, then insert it; If the same CustomerID and Account# for the month that do exist based on the previous month, then keep it. For example, […]

SQL Server 2016 - Administration

Import Extended Events XEL File ? - I have a 300 Meg .XEL file from an Entended Event session that I used recently. I stopped the session, and want to query the data. What's the best way to get the file into a SQL table ? In general, what's the best way to read the data, and look for stored procedure calls […]

Insert all columns except for Identity ID - Two tables A and B have the same structure and the same number of columns. First column in both tables is IDENTITY ID. The script below cause an error because ID. INSERT INTO B SELECT * FROM A How to use "Except" to not select column "ID" from table A?

SQL Server 2016 - Development and T-SQL

Retrieve a row with non-unique cluster keys - I noticed that for those tables with non-unique cluster key columns, SQL adds an internal unique identifier. Is there a way to retrieve a single row using this internal unique identifier which seems to be an Int32 number.

SQL Server 2012 - T-SQL

Entered by mistake - Entered by mistake

File import - Hi, I first have to say that I am not that knowledgeable on file imports, but this is really beyond me. My manager has a file that has adnominal spaces in it, I think some are tabbed and some just space, but my manager wants me to import the file into a table and keep […]

SQL Server 2019 - Administration

Application SP Response time differs on user login - I have multiple parameterized SP for our Application. When I Executes those SP individual it will returns output within 1 Sec. But when we are running Test Load with multiple user around 100 or 200 users same time , Same SP Response Time is Over 3-4 Sec. If i Execute same SP with same Parameter […]

SQL Server 2019 - Development

How to Identify Slow Code - In a specific Query, how can you easily identify how long each piece of code is taking? My query is taking over 20 minutes so I can't just comment out each line and see what improves it, so I need a better method.

For XML Clause is slow - Hello everyone, I am finding issue while running this query . WITH XMLNAMESPACES (DEFAULT 'http://example.Schema.Example.data') Select(Select * from [dbo].[udfReturnexampleRecords] () for xml path('Test'),root('example') ) as XMLTEXT Here udfReturnexampleRecords is a table valued function and while running complete query , it runs very slow for 20 lakh records. Any advice please . Thank you

Reporting Services

Moving sub-columns in a tablix - I have a report which looks like this: These are all grouped by manager, with each of the center cells displaying the value for the particular manager and then the picture at the top and at the bottom right the sales and profit for each year. This repeats for each manager. I want to move […]

Integration Services

Ignore failure works in Visual Studio but not in Integration Catalog - Hi I have a control package which calls a number of packges as part of an ETL process. In the RAW layer (from source to DB) I have a file which has failed due to a metadata change. This caused the whole ETL to blow up. I modified the control package by updating the […]

.dtsx packages disappearing!!! - SQL Server Data Tools 15.1.62002.01090 Microsoft Visual Studio Community 2017 Version 15.9.21 I am loosing dtsx packages! It happens sometimes when i am closing a solution and sometimes when i build the project. The packages are actually deleted from file and are no longer avaialble. They are just deleted. What is happening? Has anyone else […]

General

Joining 2 Tables (Sales with current stock on hand) displayed - Dear all, I am trying to join 2 tables (Dailysale table and Storerng table) Dailysale contains daily sales transaction while Storerng contains the stock on hand I am using the below query select DS.DTMTRADEDATE as [DATE], DS.INTSALEID as RECEIPT, IT.STRXREFCODE as ARTICLE, (sum(case when strsaletype='I' then convert (decimal(5,0),ds.DBLQTYSOLD) else null end)) as [QTY SOLD], (sum(case […]

This email has been sent to newsletter@newslettercollector.com. 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.