SQLServerCentral.com Community Update for {date}

Problems displaying this newsletter? View online.

Featured Contents

Question of the Day

Importing CSV with Numpy

The Voice of the DBA

Predictions for PASS

I'm flying home this morning from the PASS Summit. I won't actually see the keynote until later, and I've been busy with various other work and personal tasks, so I'm not sure what they'll talk about, but I have a few thoughts that I think will be covered. We'll see if I'm right in a few hours.

First, I think SQL Server 2019 will RTM today, both at the PASS Summit and at the Ignite conference taking place in Orlando. That's if it hasn't happened already. Ignite has Keynotes Monday and Tuesday, and it's entirely possible those announcements will come there first, with details at PASS. I'm writing this the week before, in advance of the event, but I'm fairly confident we'll see SQL Server launched, though I'm not necessarily confident all the SQL Server 2019 features will release.

There will be a continued push for Managed Instances, and other IaaS, lift and shift movement of more instances to either the Azure cloud, or some sort of hybrid cloud. I'm waiting for Azure Stack to start including the ability to provision, manage, and migrate instances from on-premises data center to the cloud, and I am guessing that's a bit of tech we may see this year.

In line with that, the modernization of your platform, with a push to try and get companies to leave 2008/R2 and move to 2017 (at least) or 2019 will be a theme. Lots of instances are on this old tech, and while the more modern versions are more powerful and stable, this is also a place where MS can make a lot of revenue, both from SQL and Windows. Most of those older versions likely need an OS upgrade as well. Perhaps there will be some discounts and bundling to convince organizations to move.

The last theme that I expect to see highlighted is one that's both a consolidation and an expansion one. Containers and Polybase, the latter underpinning Big Data Clusters are going to be big. I also think containers are the future, and SQL Server 2019 is where Microsoft starts to really push them, not just for big data, but also for regular OLTP stuff. Containers give me a contained instance I can provision pre-configured, which is something that dramatically lowers the effort of infrastructure work. Polybase lets me query data outside of my own instance, which makes a lot of sense if each instance is smaller inside a container.

Hopefully the keynote is interesting and the demos exciting. I'll be watching later tonight to try and see if anything I guessed came true.

Steve Jones - SSC Editor

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

Featured Contents

Idempotent DDL Scripts That Always Achieve The Same Result - Making Changes Only Once: Stairway to Exploring Database Metadata Level 6

Phil Factor from SQLServerCentral.com

Idempotence is the property of certain operations in mathematics and computer science, that can be applied multiple times without changing the result beyond the first time they are run. When you are making changes to a database to deploy a new version, you need to be sure that certain changes aren’t made twice, or in the wrong order.

Recover SQL Server Resource Database

Additional Articles from MSSQLTips.com

SQL Server Post Update Failure | Troubleshooting - In this tip we look at the steps to recover missing SQL Server mssqlsystemresource database files that cause SQL Server not to startup. This can occur when an update to SQL Server does not complete successfully therefore making these files absent from the folder where they need to reside.

Free eBook: SQL Server Internals: In-Memory OLTP

Press Release from Redgate

In this free eBook, Kalen Delaney explains how Microsoft's 2016 In-memory OLTP engine works. In her book, learn how to use lock- and latch-free data structures to allow non-blocking data processing, and find out how to migrate existing tables to Hekaton.

From the SQL Server Central Blogs - Getting table row counts with sp_Tablecount

david.fowler 42596 from SQL Undercover

Sometimes you just want to get a quick row count for a specific table – other times you may want to see a list of tables order by size...

From the SQL Server Central Blogs - Build a SQL Cluster Lab Part 1

Ryan Adams from Ryan Adams

This article is Part 1 in a series of articles showing how to build a SQL Cluster Lab. It covers building a Windows Cluster in Hyper-V that supports both...

Question of the Day

Today's question (by Steve Jones - SSC Editor):
	Importing CSV with Numpy
I have this data in a text file: Steve,DEN,LHR,5 Kendra,PDX,LHR,4 Grant,BOS,LHR,7 Steve,DEN,SYD,2 I have imported the numpy module as np and now want to read this data into an array using that module. What method should I use?
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)

The Computed Column Results

I have this computed column definition:

ALTER TABLE dbo.Activities
ADD ProdOneCount AS CASE
                        WHEN ProductID = 1 THEN
                            CallCount
                    END;
GO

I then have added this row to my table:

INSERT dbo.Activities
(
    UserID,
    TimeID,
    CallCount,
    ActivityTypeID,
    ProductID
)
VALUES
(
    N'12', 20, 30, 30,30
)
GO

That's the only row for a ProductID <> 1 in the table. When I issue this query, what happens?

SELECT top 10
 *
 FROM dbo.Activities AS a
 WHERE a.ProductID > 1

Answer: I get my row back with a NULL for the ProdOneCountThreshold column

Explanation: The computed column definition will return a NULL value for this row, and that is what is returned in the SELECT. Ref: Alter Computed Column Definition - https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-table-computed-column-definition-transact-sql?view=sql-server-2017

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

Maintenance Plans, Agent Jobs, and logging - Open an agent job that's been created by your maintenance plan Open the step Click Advanced Checkmark "Include step output in history" Click OK until you've closed out of the job Open the maintenance plan. Do anything or nothing at all and click save Go back to the job and look at the checkbox Does […]

SSAS remote connectivity issue - Hi , I installed SQL 2017 Deloper edition in my server. Installed with ABCDomian\Administrator. I am able to connect SSAS with windows credentials. Requirement is one user wants to connect SSAS remotely from his SQL management studio..He signin his PC with ABCDomain\xyz username. using his windows authentication not able to connect ssis. I noticed there […]

SSIS Connectivity issue - Hi, I installed SQL 2017 Developer edition. trying to connect all installed SSAS,SSRS,SSIS & DB engine. Iam able to connect all services except SSIS. When i checked the services in sql configuration manager all services are running. in services.msc i see SQL Server Integration services 14.0 also and it is running.. I installed SQL Server […]

SQL Server 2017 - Development

Try/Catch Not Working - I have a log table that gets an insert right at the beginning in order to show the process started. Then it should do the merge/insert and either mark it as failed or succeeded. The initial 'in progress' insert is not happening and the failure is not being logged either. I have purposely tried inserting […]

Convert European format values to US Format - Hi: Is there a function or some code to convert a number that's European (4.100,00 - dots in place of comma for thousands and comma for decimal point) into US (4,100.00)? -278.999 should be -278,999 621.562,61 should be 621,562.61 Any help is appreciated. Thanks !

SQL Server 2016 - Administration

Cannot connect to DB after Reboot but can after SQL Server engine restart - After a reboot of the computer, we see in the SQL Error Log problems to connect to one of the database STORESQL. Our software using this database cannot connect obviously. But after a simple restart of the SQL engine, the connection to the database is possible. Do you have an idea why? The logs […]

Add multiple databases to availability group - Hello together, We have about 50 databases that are not in full recovery mode and not in always on included. Do you know what the fastest way is to add these databases into an Availability group and also Change to full recovery mode without SQL Mgmt Studio Wizard. Maybe someone has a script suggestion for […]

Enable "force encryption" on SQL server - My production DB server is running without SSL since day one (force encryption is off at configuration manager) Someone is asking to use SSL connection from application server to DB server. If i issue a certificate and enable force encryption, should all existing clients be changed to encrypted connections too? in this case, we have […]

SQL Server 2016 - Development and T-SQL

How to get previous value(last matched records results) in SQL query result - Hi Every one, I need your help to achieve results. Can you please help me any one? My requirement is get last matched records results for unmatched records. I mean when matching the Name column with "ABC" then display corresponding records results value(988777) in expected result. else display previous result value. llly, when matching name […]

Administration - SQL Server 2014

Why Index column is NULL for DMV - sys.dm_db_index_physical_stats - I am running the DMV (sys.dm_db_index_physical_stats) to get the fragmentation for a database, however when I get the results the Index column is NULL for 3 tables even though the table in question has 2 x indexes on it and the avg_frag percentage is >80%. Why is this? I've tried DBCC INDEXDEFRAG for these 3 […]

Development - SQL Server 2014

Report runs forever.` - Not sure why this report runs continously... Select distinct H.Customs_Entry_Num as [Entry Num] ,H.Entry_Summary_Date ,L.Part_Num ,L.HTS_Num ,l.HTS_Value ,L.Line_Item_Duty AS Duty ,z.HTS_NUMBER ,z. PTNR_ID ,Z.ELIGIBILITY ,z.Created_Date ,z.COMPOSITE_PART FROM ADHOC.ATS_ESH H INNER JOIN adhoc.ATS_ESL L ON h.TRANS_SK = l.TRANS_SK LEFT JOIN [TSI].[ZATS_BROKER_FEED] Z ON L.Part_Num = Z.COMPOSITE_PART AND Z.CREATED_DATE = ( SELECT max(Z.CREATED_DATE) FROM [TSI].[ZATS_BROKER_FEED] Z WHERE […]

SQL Server 15 - Administration

Polybase on windows auth - Hello, im trying to config polybase to take data from my other sql and struggling a bit here. Does anyone finished such task successfully? The thing is to use windows authentication here. So i already have database master key. With that im going to create database scoped credentials: CREATE CREDENTIAL [AppCredWind] WITH IDENTITY = N'domain\user', […]

SQL Server 2008 - General

passing parameter to stored procedure in bcp command - Hi, I have to take output of stp using bcp command. the stp contains one parameter. I want to know how to pass parameter to stp and get the output. the batch contains below bcp command set DBName=DBLive set DBServerName=ServerLive set FileName=ClosePrice_N.csv bcp "EXEC "%DBName%..stp " " queryout %FileName% -Uadmin -Padmin -S%DBServerName% -t, -w How […]

SQL Azure - Administration

OPENJSON support for SQL Azure Encryption - Does OPENJSON supported for a Azure SQL DB encrypted with AE. especially for insert, update and delete.

Reporting Services

Maps - What is the best way to add location maps to SSRS - Hello, If I wanted to add a map of our employees and their work sites to an SSRS report. What is the best way to go about this. What is the best way to integrate maps? Thank you,

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.