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

Daily Coping Tip

Make a list of new things you want to do in the next month.

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.

Building Bad Software Faster

I saw a talk about building software recently with an interesting quote: "if Henry Ford had asked customers what they wanted, they'd have asked for a faster horse." This was with regards to talking to customers, getting feedback, and having this drive your product direction.

It is important to talk to customers, and understand what their needs and issues are, but you have to remember that customers have a very limited experience. They often think about changing, or evolving what they already see in software. They don't think about very different experiences.

True innovation involves feedback from customers, but it also involves a lot of trying. A lot of experiments. And a lot of failure.

That can be hard for many developers. We don't like to fail. I think the same thing can be true of database developers. We are used to writing queries that work and meet some specification. We are used to some success when we build software.

When we build the database foundation on which lots of software is built, we need for our foundation to be solid and work. However, we also need to support experimentation and changes. In some sense, we should learn to model and run experiments in way that allows us to continue them or shut them down. Continuing means evolving the schema in a way that will support growth and scale over time. Shutting things down means getting rid of the schema additions and potentially archiving data.

Moving to DevOps does mean moving faster, but we shouldn't be building worse software. If anything, we should be learning quicker where we've written bad code, designed a poor model, or deployed poorly written code. We ought to then be able to fix it just as quickly.

Steve Jones - SSC Editor

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

 
 Featured Contents

Finding the Free Space Difference Between Availability Groups Replicas

Cláudio Silva from SQLServerCentral

When was last time you have checked your Always On Availability Groups' replicas storage distribution?

SSRS Dynamic Row-Level Security with Recursive Hierarchy Group

Additional Articles from MSSQLTips.com

In this article we walk through how to build a SQL Server Reporting Services report based on database level security settings by user to show different results to each user that runs the report.

Meet Redgate at PASS Virtual Summit

Additional Articles from Redgate

Redgate are Gold sponsors of PASS Virtual Summit, which this year is available to all online. It's time to accelerate your data career by joining Redgate's Advocates who will be presenting a variety of sessions, as well as demo solutions throughout the week of 11 - 13 November. See what's happening and register today.

From the SQL Server Central Blogs - Protect your SQL Server from Ransomware: Backup Your Service Master Key and More

Will Assaf from SQL Tact

Not many disaster recovery or SQL migration/upgrade scenarios require the SQL Server instance service master key to be restored. 
Some do.
Recently, by far the most frequent and common disaster recovery...

From the SQL Server Central Blogs - Database Fundamentals #29: Create Foreign Keys With Table Designer

Grant Fritchey from The Scary DBA

The purpose of a foreign key is to ensure data integrity by making sure that data added to a child table actually exists in the parent table and preventing...

 

 Question of the Day

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

 

Storing the Timezone

What datatype(s) are used to store a date and time with the time zone information?

Think you know the answer? Click here, and find out if you are right.

 

 

 Yesterday's Question of the Day (by sknox)

What Resets @@ROWCOUNT?

Which of the following statements (AFTER any SELECT statement) will set @@ROWCOUNT to 0?

Assume the statements are fully completed and reference existing objects/transactions. (you do need to choose MORE THAN ONE answer)

Answer: CREATE TABLE, DROP TABLE, SET NOCOUNT ON, COMMIT TRANSACTION, RETURN

Explanation: @@ROWCOUNT returns the count of rows affected by the last statement. All of the examples are statements, so they will set @@ROWCOUNT -- BUT setting a variable sets @@ROWCOUNT to 1, not 0. Reference for @@ROWCOUNT: https://docs.microsoft.com/en-us/sql/t-sql/functions/rowcount-transact-sql

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
Could database with compat level 100 support columnstore index? - Hi everyone. I asked this question previously on other forum, but didn't get satisfied answer. I hope I could find the answer here. So, I have an instance which runs on SQL Server 2017 Developer Edition instance. I restored database ContosoRetailDW which had compatibility level 100. After restore operation, I run the following query: CREATE […]
Changing datatype in a column - Do I have to rebuild the Clustered columnstore index when we modify datatype in a column?   Thanks!
High buffer size for a table could mean either it has incorrect indexes or ? - or simply that the table is USED A LOT, right? -- Breaks down buffers used by current database by object (table, index) in the buffer cache (Query 51) SELECT OBJECT_NAME(p.[object_id]) AS [ObjectName], p.index_id, COUNT(*)/128 AS [Buffer size(MB)], COUNT(*) AS [BufferCount], p.data_compression_desc AS [CompressionType] FROM sys.allocation_units AS a WITH (NOLOCK) INNER JOIN sys.dm_os_buffer_descriptors AS b WITH […]
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 […]
SQL Server 2016 - Administration
Page Verify = Checksum - Hi I'm belated changing the page verification option for a database from NONE to CHECKSUM.  I'm aware this only affects future writes to the database, and existing pages won't have that checksum calculated until they are updated or the relevant tables or indexes are rebuilt. My question is: would an ALTER INDEX ... REORGANIZE also […]
Audit connection which are connecting to a server using AG listener. - I am in a middle of removing a server which is part of AG and someone has asked me to see if there are users, connecting to that specific server using a listener name so I used a DMV to capture all that information. The script is below SELECT es.login_name ,es.program_name ,login_time ,ec.client_net_address ,ec.client_tcp_port ,agl.dns_name […]
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 check database deadlock and blocked process occurred before? - 1. if there is no database monitoring software, and if there is any approach to check the database deadlock and blocked process occurred before? and how to know which statement caused the dead lock and which statment was blocked? 2. if use the third part monitoring software, which one is the best to do that […]
Development - SQL Server 2014
How would I get the last record of a self-referential table? - This summer I authored a report that referenced nine tables in a query. This is against an older database. This is for a pharmacy application that someone back when wrote originally in a Microsoft Access application. Along the way somebody upgraded the database from the original .MDB to a SQL 2012 database, where it currently […]
SQL Server 2019 - Development
SQL Full text query too slow - I have some Full Text Catalogs and since some time ago, the query time really slow down, and the only solution I found until now is to rebuild the catalogs, sometimes more than once a day with command: ALTER FULLTEXT CATALOG TABLE1 REBUILD Why is necessary to rebuild the catalogs? There are other solutions for […]
SQL Azure - Administration
Databricks Job Listing - Not really sure if this is the correct place to post this, but here goes I'm fairly new to databricks, but I have a list of about 40 jobs that run at different times during the day. Is it possible to get a listing of those jobs that are currently executing, using a sql or […]
Powershell
Scanning to find sql instances/servers - We use MAPS tool to scan through our domain and then list out our sql servers/instances. How can this be achieved using powershell ? Thanks
SSDT
CDC Source tool in SSIS - varchar data mapping issue - I'm trying to introduce myself to the change data capture items in the SSIS Toolbox and have hit a problem with the CDC Source tool. I've been using this tutorial as a guide: https://www.mssqltips.com/sqlservertip/5815/sql-server-integration-services-ssis-cdc-tasks-for-incremental-data-loading/ So I have a source table with varchar data types in SQL Server, but in the CDC Source Advanced Editor it […]
SSIS performance issue: Data load from sqlserver2016 (EC2) - Mysql8.0 (RDS) - Hi, I am trying to Full Load  from Table on sqlserver(2016) to a table on Aurora My Sql database (RDS).The table has 1M records and taking 3 hrs to load it when I run it as a job on server. I am using ODBC 32 bit driver  and ADO.net Provider on the ETL side. The […]
Integration Services
Find MAX value from multiple datetime columns - This may have an easy resolution but I'm having difficulty finding it.  I'm creating a package that needs to take records that are new or updated since the last time the job run.  I have 2 DateTime columns Insert_date and Last_Updated.  I need records for newly inserted records and modified/last_updated records.  How do I accomplish […]
 

 

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

 

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