SQLServerCentral.com Community Update for {date}

Problems displaying this newsletter? View online.

Featured Contents

Question of the Day

Starting with Full Text Search

Featured Script

Index fragmentation on all databases

The Voice of the DBA

Daily Coping Tip

Look for something positive in a difficult situation

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.

Enterprise Software

I'll go in the store; it's built for that.

A quote from Jessica Kerr, who is a developer. She has a great blog on software development, called The Enterprise Eats Software. It starts with a poor experience for an online order for Lowe's. Interestingly enough, as I read this, I was starting a home renovation project, but I didn't have a bad experience, I had a great one.

Short story. We had contracted for a bathroom project, but we forgot to get some of the fixtures. The contractor came a day early to talk about things, and we realized we needed a shower diverter valve and trim kit. We looked for the trim, which is what was important to my wife, and found a valve to fit it. While the contractor called two local supply houses, I looked at Amazon. The suppliers didn't, Amazon did, noting delivery in two days. I ordered it around 10:30 am that day. It arrived around 11:00 am the next day, a day early and before the existing shower had been demoed.

That was fantastic. It made me think why would I want to use any service that wasn't that amazing and quick. I got an email at my desk with a picture, telling me the package had been delivered and showed me where it was. I walked it upstairs thinking that calling multiple suppliers and then driving around town would have been a pain. Not to mention a time sink.

I don't often find a lot of large companies do a good job with their software. A few do, and apart from Amazon, I'll say build.com was incredible for us to get a tub and Wayfair got us a vanity very quickly (too quickly, actually). Both have built great systems that not only took the order but updated us and handled the complexities of shipping large items. Not to mention the incredible selection available.

Apart from the retail challenges, just the general experience from many enterprises leaves something to be desired. They just aren't good at being agile, flexible, and maybe more importantly, constantly improving. They get caught up in some of the hassles in the essay, like bureaucracy, power struggles, too many rules, etc. They don't know how to operate in a flexible, agile, constant change environment.

I'm reading Project to Product, which in many ways sees a lot of the same problems. Software is disconnected from the goals of the business, and too often there are individuals and processes that get in the way of becoming more effective across teams and partners. BMW is one of the success stories here, and I still think they're behind Tesla in their industry. They might catch up, and if they do, it's because they are learning to be a better software company, not a better manufacturer.

Steve Jones - SSC Editor

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

Featured Contents

An Introduction to Power BI

Sucharita Das from SQLServerCentral

Introduction Power BI is a collection of software services, apps, and connectors working together. It helps to connect different data sources and get visually immersive and interactive insights from the data. Power BI creates powerful reports to be shared with others, as required. Data may be connected from different sources including files, databases, Azure services, […]

Best of Summit 2020

Additional Articles from Redgate

The Best of Summit 2020 series is now live! This curated series features a selection of the most-viewed content from PASS Virtual Summit 2020 to help you find relevant data platform training related to this year’s conference tracks. Best of Summit 2020 includes sessions from some of the top speakers in the data industry including Erin Stellato, Andy Yun, Melissa Coates, Pinal Dave, Hamish Watson, and many others.

SQL Server Restore Database Options and Examples

Additional Articles from MSSQLTips.com

In this article we look at different SQL Server backup types and schedules and the steps to take to properly restore a database.

From the SQL Server Central Blogs - Using Readable Secondary in Azure SQLDB

SQLEspresso from SQLEspresso

When using a Geo Replicated Azure SQL Database Readable Secondary there are a few things to consider when it comes to performance tuning. Check out this episode of Data Exposed:...

From the SQL Server Central Blogs - Monitoring your Chatbot in Power Virtual Agents

Devin Knight from Devin Knight

Power Virtual Agents empowers subject matter experts to build intelligent conversational bots, using a guided, no-code graphical interface. In this video you will learn how

Question of the Day

Today's question (by Steve Jones - SSC Editor):
	Starting with Full Text Search
How do I enable Full Text Search in a SQL Server 2019 database?
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)

Data Flow Error Handling in ADF

I have an Azure Data Factory with a data flow activity in it. I am loading data from a source to an Azure SQL Database sink. If I encounter an error, what is the default error handling action?

Answer: Fail on first error

Explanation: The default action is to fail on the first error. Ref: Error Row handling -https://docs.microsoft.com/en-us/azure/data-factory/connector-azure-sql-database#error-row-handling

Discuss this question and answer on the forums

Featured Script

Index fragmentation on all databases

Evgeny Garaev from SQLServerCentral

The script returns all the fragmented indexes on all databases on the instance you run it. Can be run on multiple instances in the same time using central management capability.

create table #temp_if(
[DBName] nvarchar(128) NULL,
[SchemaName] nvarchar(128) NULL,
[ObjectName] nvarchar(128) NULL,
[IndexName] nvarchar(128) NULL,
[fragmentation] numeric(38,35) NULL,
[page_count] bigint NULL);
exec master.sys.sp_msforeachdb 'USE [?];
insert into #temp_if
select ''?'' as [DbName],
dbschemas.[name],
dbtables.[name],
dbindexes.[name],
indexstats.avg_fragmentation_in_percent,
indexstats.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]
INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
AND indexstats.index_id = dbindexes.index_id
WHERE indexstats.database_id = DB_ID() and indexstats.page_count > 1000
and indexstats.avg_fragmentation_in_percent > 5
and dbindexes.[name] IS NOT NULL;'
select * from #temp_if
order by page_count desc;
drop table #temp_if;

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

Removing a SQL node from a cluster - First and foremost - I AM NOT A SYSTEMS ADMIN I'm a DBA. My windows admin and I built a sql cluster to do availability groups. It was both of our 1st time building for AG. It was a total success. Then we pointed our 3rd party software at it and it couldn't deal with […]

SQL Server 2017 - Development

How to show the MIN Date Value in a query - After reading many posts, this is what I learned. SELECT MIN(StartDate) AS MinDate FROM Schedule WHERE WorkCenterID = 'LAY-W' AND ScheduleID = '5' GROUP BY StartDate It produces all three values. I just want the Minimum Date Value to be returned. Thanks!

SQL Server 2016 - Development and T-SQL

Excessive run time - Hi I have a number of queries that select data and put the data into a table. Data flow from one database and is loaded into another table in another database using a SSIS data flow task. I've noticed that on a good day, it works through them all fairly quickly (around 30-40 mins) […]

Development - SQL Server 2014

Using an Integer parameter in a WHERE clause - I have a simple query which has one parameter (@Amount); if I use it like the pasted code below, it works just fine: DECLARE @Amount int, @Client varchar(500) SET @Amount = '1170' SET @Client = 'Google' Select * from Invoices where Client like '%' + @Client + '%' and InvoiceAmount = @Amount But what if […]

SQL 2012 - General

Rows to Column - Hai all I have one table one column. I have 10 rows that table . I want output, segregate one column to three column rows accordingly Create table #test(Name varchar(20)) insert into #test values('one') insert into #test values('two') insert into #test values('three') insert into #test values('four') insert into #test values('five') insert into #test values('six') […]

SQL Server 2019 - Administration

Move mdf and ldf on separate disks - Hello Forum, Inside my database server I have 3 harddisks. Sadly there is no more free space to mount additional disks. So I set up: * Disk 1: OS, SQL-Server executables incl. master, model, msdb and tempdb * Disk 2: mdf for User DB * Disk 3: ldf for User DB For testing I installed […]

How to integrate TFS into SSMS? - Hi all, In our old version of SQL Server and its SSMS we used to have Team Foundation Server integrated into SSMS. The setup was done through Tools/Options/Source Control/Plug-in Selection. But now we are migrating to SQL Server 2019 and new SSMS is missing this option. Or I am missing something? Thanks

Switching from Server/CAL to Core license. Do we need the core install? - We are changing our licensing model and with 2019 there are different installs for Core or Server/Cal I asked the MS rep if this is simply a key change or does it require a new install. I couldn't get a concrete answer out of him. Seems like as long as we pay for the core […]

Error when attempting to do initial backup for log shipping - We have several databases that we log ship from our primary production SQL instance to a reporting instance. We've been doing this for years now and it's been working almost flawlessly most of the time. However, we've come across an issue. Over the weekend, we had a power outage in our DEV environment. The host […]

SQL Server 2019 - Development

Clustered vs Nonclustered Columnstore Update Performance - Hi Everyone, I'm experiencing horrendous update performance with Clustered Columnstore Indexes (CCI). Whilst our current Nonclustered Columnstore Index (NCCI) setup is not going to win any update-performance awards, it works sufficiently well. In my test setup, I can update 1 mil rows in about 90 seconds using NCCIs. Inserting 1 mil rows (both for CCI […]

Incremental/Delta Import into SQL - Hi Community, I need a solution for a problem. I have excel files which needs to be imported into a SQL Database. I tried the below method using SSMS. When SSMS is connected to the chosen instance of SQL Server, right-click on the desired database and navigate to Tasks > Import data option from the Tasks submenu: The problem is the […]

Import data from PostgreSQL to MS SQL Server - I am trying to import data from the PostgreSQL into the MS SQL Server database, using SQL Server Import and Export Wizard. During the column mapping I get the following error: The column attribute "COLUMN_SIZE" is not valid. The column attribute "DECIMAL_DIGITS" is not valid. The column attribute "COLUMN_SIZE" is not valid. I modified the […]

Comparing two sets of value sets - I have two columns that I need to check for a given set of values: SELECT * FROM test t WHERE t.col_a IN ('a','b','d','g','t','s') OR t.col_b IN ('a','b','d','g','t','s') What I'm wondering is if there is a shorthand method to determine if either col_a or col_b have a value in the set without repeating the set […]

SSRS 2016

SSRS MS Report Builder: How to repeat all item labels - I am working on creating a report like below where I would like there to be no empty spaces in rows - so a employee number should show on every row - even if it is repeating the same information. Here is my design view, I have not grouped the report by anything but still […]

Integration Services

SSIS not calling powershell script - I have an SSIS package that I've had to alter to call a powershell script to do SFTP. We have other SSIS packages that do the exact same thing and they work fine. But for some reason, when trying to run this package in the VS 2010 shell on the Windows 2012 R2 server, it […]

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.