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

Giving Computers Ethics

This editorial was originally published on March 27, 2019. It is being re-run as Steve is on sabbatical.

I was reading a fascinating paper recently about autonomous cars. I'm actually excited about having a car that can drive itself, though I think this is likely quite a few years away, despite the hype. Ever since I read Red Thunder, I've thought that we would first get full time autonomous cars that would either be limited in where they were in use, or part-time autonomous cars that could only be self-driving in certain places. Dense inner cities, or maybe isolated highways might be good places to try this, in my mind.

While we want to do some programming of these cars, we also have a lot of AI/ML systems in place that run models trained to react in certain ways. They identify things that are moving and stationary, trying to determine how the car should navigate and react. The systems aren't quite as tightly programmed as many of us expect, with if this then that logic. Instead they have guidelines that are decided upon by the designers and then reactions to data inputs and analysis are a little more fuzzy.

What are the goals? Well, in most cases they are just moving the car safely down a road. In crisis situations, it's a little more murky. What happens when collisions are unavoidable? How should the car react? Humans often panic and do strange things, but we don't want erratic behavior from automated systems, so what should we set as goals? There's a bit of research that was done to ask humans what they would do when they can consider the situation a little more slowly.

In short, humans make different decisions in different cultures. There are clusters and tendencies in different parts of the world, which is interesting. While people are people and behave similarly in many cases, we tend to value different things, depending on our views of the world. That can be problematic when we start to expect computer systems to be more consistent or predictable. After all, we should decide how computers react and be able to trust our decisions are followed. It is up to humans to imprint our ethical desires as a society on computer systems.

This is an area where I feel AI and ML systems are moving faster than our ability to comprehend the implications. I would want to have a framework built for automated systems, certainly cars, and then expect all vendors of systems would implement that framework in their vehicles. However, this goes beyond cars, and in any places where we are using software, AI/ML based or not, we ought to publish a comprehensive outline of the way in which our system works.

Computers have the capability to improve our world and reduce chaos, but only if we agree on the way in which they work, and disclose in a transparent way the data they handle and the decisions they make based on that data. I hope that we start to get better about informing the world the goals and operation of our systems.

Steve Jones - SSC Editor

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

Redgate SQL Monitor
 
  Featured Contents

Introduction to Attribute Relationships in SSAS

aveek22 from SQLServerCentral

In this article, you will learn how you can set the attribute relationships between dimensions in SSAS.

New: 2020 State of Database DevOps report

Press Release from Redgate

Now in it’s 4th year, the State of Database DevOps report continues to deliver valuable insights. In the report, you will learn multiple tactics to improve the quality of database code deployment, whether it is by changing the type of environment used for development, enhancing your code review practices, or your change management/approval practices.

Storage 101: The Language of Storage

Additional Articles from SimpleTalk

Robert Sheldon continues his series on storage. This article covers some of the basics of performance metrics, HDDs, and SSDs.

PASS Summit 2020

Press Release from PASS

PASS Summit is the must-attend conference for Microsoft data professionals; delivering 3 full days of technical training on data architecture, management, and analytics. Get the skills you need with in-depth sessions created and led by industry experts. Network and problem-solve with peers and Microsoft engineers, and get ahead of the curve with spotlight sessions on today’s hot topics. Join Redgate and the PASS community in Houston on November 10 - 13 to future-proof your data career.

From the SQL Server Central Blogs - Allow users to start/stop Azure VMs

jsterrett from John Sterrett

Today I wanted to cover how you can grant the least privilege required to stop, start or restart an Azure VM. This is also a fun great example of...

From the SQL Server Central Blogs - The State of Database DevOps 2020

Grant Fritchey from The Scary DBA

I’ve been an advocate for database DevOps since before DevOps was a thing. When I first switched from being a developer full-time to being a DBA full-time, the first...

 

  Question of the Day

Today's question (by Kathi Kellenberger):

 

Calculate the Days Between Orders

Your SQL Server 2017 database has a Sales table with 100 million rows that contains CustomerID, OrderID, and OrderDate columns. You would like to return a list of orders (all columns). For each order, return the number of days until the next order is placed. For the last order, return a 0 instead of NULL.  Assuming a helpful index is in place, which query will give you that answer and also performs the best? Query #1
SELECT CustomerID, OrderID, OrderDate, 
   DATEDIFF(DAY,OrderDate, LEAD(OrderDate,1,OrderDate) 
   OVER(PARTITION BY CustomerID ORDER BY OrderID)) AS DaysUntilNextOrder
FROM Sales;
Query #2
SELECT CustomerID, OrderID, OrderDate, 
   DATEDIFF(DAY,OrderDate, LAG(OrderDate,-1,0) 
   OVER(PARTITION BY CustomerID ORDER BY OrderID)) AS DaysUntilNextOrder
FROM Sales;
Query #3
SELECT O.CustomerID, O.OrderID, O.OrderDate, 
   ISNULL(DATEDIFF(DAY,O.OrderDate,NextOrder.OrderDate),0) AS DaysUntilNextOrder
FROM Sales AS O
OUTER APPLY (
   SELECT TOP(1) OrderDate 
   FROM Sales AS I
   WHERE I.CustomerID = O.CustomerID 
      AND I.OrderID > O.OrderID
   ORDER BY I.OrderID) AS NextOrder;

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

 

 

  Yesterday's Question of the Day (by Kathi Kellenberger)

Using TOP with ROW_NUMBER

Which query will generate ten “random” sample rows from the Sales.SalesOrderDetail table (AdventureWorks2017) with new ID numbers (NewOrderID) that continue after the highest SalesOrderID in the table? In other words, if the highest SalesOrderID is 75123, then the NewOrderID in the rows returned will be between 75124 and 75133.

Query #1

SELECT TOP(10) SalesOrderID, ProductID, OrderQty, 
	ROW_NUMBER() OVER(ORDER BY SalesOrderID) 
        + MAX(SalesOrderID) OVER() AS NewOrderID
FROM Sales.SalesOrderDetail AS SOD
ORDER BY NEWID();

Query #2

WITH SalesID AS (
	SELECT TOP(10) SalesOrderID,  ProductID, OrderQty,  
            MAX(SalesOrderID) OVER() AS MaxID
	FROM Sales.SalesOrderDetail AS SOD 
	ORDER BY NEWID())
SELECT TOP(10) SalesOrderID, ProductID, OrderQty,  
        ROW_NUMBER() OVER(ORDER BY SalesOrderID) 
        + MaxID AS NewOrderID
FROM SalesID;

Query #3

SELECT SalesOrderID, ProductID, OrderQty, 
	ROW_NUMBER() OVER(ORDER BY SalesOrderID) 
        + MAX(SalesOrderID) OVER() AS NewOrderID 
FROM Sales.SalesOrderDetail 
WHERE ROW_NUMBER() OVER(ORDER BY SalesOrderID) <= 10;

 

Answer: Query #2

Explanation: By ordering by NEWID(), you will get a random-ish (enough for our purposes) set of rows each time you run the query to produce the sample. You can find the last SalesOrderID by using a window aggregate MAX(SalesOrderID) OVER(). Window aggregates allow you to use an aggregate function even though the query is not an aggregate query. To continue the sequence of IDs, it’s possible to use ROW_NUMBER which returns a list of integers beginning with 1. Because the row numbers are generated before TOP is applied in Query #1, the query will return sample rows, but the row numbers, and therefore NewOrderID numbers, returned will not be the ones you are looking for. Here is a sample: Query #2 just won’t run because you cannot filter the row numbers directly in the WHERE clause. Query #1 is the answer. By placing the query to return the ten rows in a common table expression (CTE), you can apply the row numbers afterwards. (You could also do this with a temp table, table variable, or derived table subquery instead of a CTE.) In this case, the sample rows have the NewOrderID numbers that you need. Ref: Expert T-SQL Window Functions in SQL Server 2019: The Hidden Secret to Fast Analytic and Reporting Queries 2nd ed.

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
After DDL trigger introduction query runs fast - I'm perplexed with the odd behavior on sql server. Scenario1: Query executes for 70sec which does schema and default data changes Scenatio2: DDL tracking (database-level triggers) were introduced in new db (same db as above) The same query runs in 60 sec. My understanding is that after DDL triggers were introduced it should take longer […]
Masking Tools - Any recommendations? I acquired ApexSQL Mask and it's pretty self-explanatory and seems like a decent enough tool if you have a well-formed, well-normalized database. Unfortunately, the application we run is 3rd party and the database structure defies most sensibilities. I also have a little bit of experience with Dataguise... also ineffective for ill-formed databases. The […]
SQL Server 2017 - Development
Denormalize an orders table - I want to take an order table and do something like an unpivot into a new table.  For each item row I want create a number of new rows equal to the quantity, but with the same data.  I think the goal is best explained by the script below.  This method does work, but I […]
SQL Server 2016 - Administration
Copy latest sql full backup file from one server to another - Hello All, Can someone provide a script or point to an already created script that will copy the latest sql full backup file in the format MyFullBackup_backup_2020_02_06_040730_7697934.bak from one server to another? I would like to include this in a job step for SQL Agent.  I've tried robocopy, powershell, and "entertained" using SSIS, but would […]
SQL Server 2016 - Development and T-SQL
Need to replace this inefficient CASE statement - What do you think is the best way to get rid of this attached CASE statement? Extend the Join ..?  Or a differently shaped WHERE clause? What will it look like? I kind of got stuck ... (see attached Select statement). ........... FROM Popup_Switches AS PS WITH (NOLOCK) JOIN #Temp_1        AS T […]
Error in SSIS Foreach Loop - Hi there, everyone. I'm struggling with an SSIS package I'm working on. Here's the scenario: I have to extract data from a SQL table to JSON format. They want it done on a level using three fields. I.e. SELECT * FROM TableA AS a JOIN TableB as b on a.field1=b.field1 and a.field2=b.field2 and a.field3=b.field3 TO […]
Development - SQL Server 2014
Getting only MM/DD from the Column of type varchar(50) - Hello All, I am trying to get the date column in the format 'MM/DD' from the data column. However, it is of type varchar(50) so I am converting it to DateTime and again converting it to 101 to retrieve the format I need. Here is the syntax I am using CONVERT(varchar(5), CONVERT(datetime, alertQueue.[Pickup_Date], 126), 101) […]
Exception when calling "ExecuteNonQuery" with "0" argument (s): "String or binar - I found this Sources from: https://solutioncenter.apexsql.com/multi-server-script-to-automatically-monitor-sql-server-availability/   I have this exception: ComputerName ComputerName MSSQLSERVER tempdb creation time is 4/2/2020 7:26:57 PM SQL connection to ComputerName Exception when calling "ExecuteNonQuery" with "0" argument (s): "String or binary data would be truncated. The statement has been terminated." On the line: 118 character: 1 + $ Command.ExecuteNonQuery () […]
Reset sequences at 00H - Hi i have a .net program that based on the partnumber it creates a sequence  (if a sequence exists and if not he creates one). he creates the sequence with the name "seq_partnumber" example: seq_345765ab , seq_45123trw i create a job to reset every sequence at midnight alter sequence Seq_345765ab restart with 1 alter sequence […]
SQL Server 2012 - T-SQL
DATEFROMPARTS and CONVERT failing but only when inside an SP - OK, I personally hate this question, as no matter what I've tried, I have been completely unable to repro this issue outside of the SP that is causing the problem. I really apologise for that. Anyway, the problem I'm having is that I have an SP, which also calls somefunctions, one of which uses DATEFROMPARTS. […]
SQL Server 2019 - Administration
Move SQL Server 2008 DB's to SQL Server 2019 - Hi, DBA gurus please give inputs regarding this project to move 100 DB's  , msdb jobs , users, linked servers etc from one server sqldev to sqldevnew. sqldev  is on lower version and has limited hardware and now we need to add 100 more DB's to make it scalabale and this is a VAL environment. […]
SQL Server 2019 - Development
Time worked on a ticket Query - I am trying to get a report on time spent working on a ticket. Basically I have multiple employees working on a single ticket and want to report on how much time they worked on a ticket. For example: Employee A has worked on a ticket from 12 PM till 5 PM and during this […]
Reporting Services
SSRS - One filter for every Fields - Hi SQL experts ! I am getting used to report developments under SSRS (2012, moving soon to 2016), but I have one insolvable problem. To make it quick, my job is to migrate reports from Hyperion Interactive Reporting (know as BRIO) to SSRS. That one functionnality I am in trouble with was existing in BRIO, […]
Integration Services
Why do Execute SQL Tasks create table names with an appended Hexadecimal value? - I am updating an SSIS package which has an Execute SQL task. This task creates a temp staging table for loading table with the following syntax: create table dbo.PushPortfolioData ( PartyURN varchar(255), FirstName varchar(255), LastName varchar(255), EmailAddress varchar(255), AddressLine1 varchar(255), AddressLine2 varchar(255), AddressLine3 varchar(255), AddressLine4 varchar(255), AddressLine5 varchar(255), PostCode varchar(255), PayrollRef varchar(255), NINumber varchar(255), NotificationMethod […]
Microsoft Access
Modify Data to Add a Letter Before Numbers - Hi I am using access and i have customers data (15k) records Now i want to modify a column 'Customers' in a table called 'MRF' In customers column i have ID Numbers of Customers I want to run a query to add a letter "X" before all ID numbers How can i do it thanks
 

 

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

 

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