A Brief History of Data Analysis (SQLServerCentral 2019-06-06)

 SQLServerCentral Newsletter for June 6, 2019 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [A Brief History of Data Analysis]( - [SQL Server function to convert integer date to datetime format]( - [Bloor scores SQL Provision 4.5 out of 5 for test data provisioning]( - [From the SQL Server Central Blogs - Bad Idea Cowboy Hat: Using a foreign key to prevent updates]( - [From the SQL Server Central Blogs - Quick Tip – How to fix SQL Server 2017 install error on Ubuntu 18.04: E: Could not get lock /var/lib/dpkg/lock-frontend – open (11: Resource temporarily unavailable)]( Question of the Day - [Changing Database Names]( The Voice of the DBA  Cloud Pace Recently, a user responded on SQLServerCentral to something I'd written on Azure, asking how do I keep up with all the changes. I wrote back: "Between the complexity and pace of change, I’m always learning something." To be clear, I don't really keep up. Even just trying to read all the [Azure Announcements]( can be overwhelming, especially if you want to set up a quick demo on any of them. Even trying to get through the (free) e-book, [The Developer's Guide to Azure](, was a chore. There were so many things I hadn't heard of or barely knew about that I had to stop myself from logging in and trying something to get to the end. The Cloud isn't really anything new. Not very new. All the IaaS stuff has been around for a long time, where you could rent a machine from some data center owner. The PaaS stuff is a little new in its implementation, but the idea is what many developers have dealt with in organizations where they didn't have any administrative privileges. The database was just a database, no server involved. The biggest change I see with the "cloud" is the pace of change. Companies lose some of the overhead of dealing with different departments and getting resources provisioned. Because cloud vendors are working at scale, they've built tools that allow customers to spin up new VMs, databases, even clustered systems in minutes. Much of the tedious complexity has been removed, which allows users to just move forward with building systems. The move towards DevOps, with a focus on automation, means that vendors also build new platforms, capabilities, and options that we can use at a furious pace. The choices we have can be overwhelming at times, with no one person really able to keep up with the myriad of possibilities. To me, this means two things for your company. First, you always need to have some experiments running. No proof of concepts that will be turned into applications, but true developer or operational experiments where people try out an elastic pool or machine learning or a K8s replica set of Hello, World containers. Your staff needs to be learning and sharing with each other in small ways. The second thing is that you need to limit the technologies in use. While there are many amazing and useful new services, you can't bounce between them and support an unlimited number of choices. Limit the number of technologies (languages, frameworks, platforms, services, whatever) to the few that get the job done, with a process to add new ones if a rational case can be made. The bottom line is that it is important to keep learning, evolving and using better technology over time, but we have to be sure that most of our time is spent working on the software we're building, using the knowledge we already have. We're paid to get work done and while learning is a part of that, it's a small part. Mostly we need to be doing the other parts of our jobs. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](  [Redgate SQL Provision](  Featured Contents [A Brief History of Data Analysis]( RevOX11 from SQLServerCentral.com Take a look at Data Analysis from ancient times to current days [SQL Server function to convert integer date to datetime format]( Additional Articles from MSSQLTips.com In this tip we look at code you can use to convert an integer date into a datetime format. This is an issue you will face when working the history data for SQL Server Agent Jobs. [Bloor scores SQL Provision 4.5 out of 5 for test data provisioning]( Additional Articles from Redgate In this independent review of SQL Provision, the Bloor analyst finds that SQL Provision’s combination of database cloning and data masking stands out with “clear advantages over competing approaches, such as data subsetting or synthetic data generation.” From the SQL Server Central Blogs - [Bad Idea Cowboy Hat: Using a foreign key to prevent updates]( Kenneth.Fisher from SQLServerCentral When I decided to rip off of Brent Ozar’s (b/t) Bad Idea Jeans series (yes I asked) I decided that ... Continue reading From the SQL Server Central Blogs - [Quick Tip – How to fix SQL Server 2017 install error on Ubuntu 18.04: E: Could not get lock /var/lib/dpkg/lock-frontend – open (11: Resource temporarily unavailable)]( Basit Farooq from SQLServerCentral Problem Recently, when I tried to install SQL Server 2017 (mssql-server package) in Ubuntu 18.04, I encountered the error below: The command that I used to install the mssql-server...   Question of the Day Today's question (by Steve Jones - SSC Editor):  Changing Database Names In SQL Server 2017, how do we programmatically change the name of a database? I want to change database YYY to XXX in a way that will be supported in the future. Think you know the answer? [Click here]( and find out if you are right.  [Redgate University](    Yesterday's Question of the Day (by Steve Jones - SSC Editor) Properly loading the data frame I've got a CSV file that looks like this: "ProcName","cputime","reads" "GetCustomers","27784","312165" "GetCustomerList","43170","627272" "CalculateMonthlyTotals","48868","2705414" "CalculateMonthlyTotals","25868","2922859" I want to import this in RStudio, and run this: procs <- read.csv2("C:\\Users\\Steve\\Documents\\SQL Server Management Studio\\procexecutions.csv", header=TRUE) I then see this: > head(procs) ProcName.cputime.reads 1 GetCustomers,27784,312165 2 GetCustomerList,43170,627272 3 CalculateMonthlyTotals,48868,2705414 4 CalculateMonthlyTotals,25868,2922859 5 CalculateMonthlyTotals,38206,862347 6 GetCustomers,49426,3449650 Why do I see all the data in one column? Answer: read.csv2 defaults to a semicolon as the delimeter Explanation: The defaults for read.csv2 are different than read.csv. The function for read.csv2 is designed for non English locales where the comma is used as the decimal separator and the semicolon is used as a column separator. Ref: read.table() - [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 [SSIS Scale out deployment in a high availability cluster]( - Good day all, Just wondering if it will be possible to have a scale-out master and scale-out worker on primary and secondary in a high availability setup, were in the case of a failover the master on the secondary server would be able to utilize the worker on the primary server and the secondary server, […] SQL Server 2017 - Development [Merge/Split/Switch partition's affect on indexes?]( - Hi All, Currently I have a partitioned table that is partitioned by date. On this table, I have 3 indexes that are not partitioned, each in their own filegroup. I'm now wanting to archive earlier portions of this partitioned data into another table of similar structure; when executing a Merge/Split or a Switch style archive […] SQL Server 2016 - Administration [Buffer Pool calculation]( - Hello Colleagues. Could you tell me about how MS SQL Server 2016 SE determines memory size for Buffer Pool? May be there are a formula ? Thanks in advance. [BPSORT + CXCONSUMER Waits - DEV vs QA server]( - I've got a process which does a lot of ETL type queries which runs fine on our DEV server but not on QA. SQL 2016 developer edition (prod is on Standard, haven't tested there yet) DEV = 4CPU, 16GB sql instance, maxdop = 2 QA = 8CPU, 32GB sql instance, maxdop = 4 On DEV, […] SQL Server 2016 - Development and T-SQL [insert and update]( - Hi, need some advice. I have more than 20 columns and more than 50k rows. I would like to do insert and update. I did the merge in SQL from this example: I am having issue where it takes too long. More than 20 minutes. Is it because of the total rows? Any other […] [Determining the length of a data type]( - On this website in at the beginning of the 3rd paragraph, we have: "Length for a numeric data type is the number of bytes that are used to store the number." When I run this code, the answer is 2. DECLARE @n int SELECT @n = 25 SELECT LEN(@n) The int data type has 4 […] Administration - SQL Server 2014 [Reporting Server]( - I have reporting server which gets replicated from other OLTP server.However, the reporting server has low PLE and hig disk queue which make sense having low PLE could lead to high disk queue length and this trigger alerts. However, adding more memory on reporting server would help but it's reporting server so it would take […] [FlushCache Error Message]( - While analyzing an issue, I stumbled on to a "FlushCache" error message (error log). There were many (both AM + PM). And this had been going on for quite a while. Had not seen this message before. First instinct was to check other production SQL Servers (VMs) to see if this was happening else where. […] [StartDate & End Date parameters]( - Hi, I've created a view to populate data from existing tables In my report project I built using SSDT 2014, I created Dataset to read form this view. I also set a Startdate and Endate parameter with default calander values. The expectation is for the dates to filter the dates by above parameters. The Report […] SQL Server 2012 - T-SQL [Need help with query where i need to aggregate an aggregate]( - Hi all, I have an inherited system which I can't do much with so (for now) i'm stuck with what i've got. Data is collected and inserted into a "storage" table at various times throughout the month which shows customers and how much storage they are consuming. I need to report on the TOTAL maximum […] SQL Server 2008 - General [How to map the table data while inserting into another table in sql]( -  table1 id  value 2  20 3  30 4  40 5  50 6  60 7  null the above table has to be inserted into another table2 with following fashion i,e: it has to insert the value corresponding to 2 into col1 when id =2 ,col2 when id=3 and so […] [How to map the table data while inserting into another table in sql]( - table1 id  value 2  20 3  30 4  40 5  50 6  60 7  null the above table has to be inserted into another table2 with following fashion i,e: it has to insert the value corresponding to 2 into col1 when id =2 ,col2 when id=3 and so on. […] [How to map the table data while inserting into another table in sql]( - table1 id  value 2  20 3  30 4  40 5  50 6  60 7  null the above table has to be inserted into another table2 with following fashion i,e: it has to insert the value corresponding to 2 into col1 when id =2 ,col2 when id=3 and so on. […] Powershell [run stored procedure from PS script]( - anyone have a script that will run a PS script to run a SQL SP and catch if it fails along with capture messages from the SP? Thanks Integration Services [Import data from Word Custom Controls]( - I'm just wondering how hard this would be in SSIS... I got it to work in Access using Word automation/VBA (dirty word, I know!) I can loop through the Word file's ContentControls collection and map the names to fields in my table. Has anybody done this in SSIS? The lack of branching in Access is […]   [RSS Feed]([Twitter]( 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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -