Scalar Functions with Query Plans: Not all is obvious! (SQLServerCentral 6/4/2018)

[SQLServerCentral - www.sqlservercentral.com]( A community of more than 1,600,000 database professionals and growing Featured Contents - [Scalar Functions with Query Plans: Not all is obvious!]( - [Building reusable table build scripts using SQL Prompt]( - [Deploying Azure Container Instances]( (From the SQLServerCentral Blogs) - [DBCC Clonedatabase and Query Store]( (From the SQLServerCentral Blogs) Featured Scripts - [SQL Server Backup, Integrity Check, Index and Statistics Maintenance]( - [SQL Server All View Refresh]( The Voice of the DBA Will Coding Be Less Important? How many of you can read Greek? I can decipher the alphabet and pronounce words, but that's about it. I thought about this after running across [an essay]( that says that coding will eventually be as important to most people as ancient Greek, which is to say, hardly important at all. The author seems to suggest that eventually we'll tell systems what we want and they'll build algorithms. I guess that's what we do in code, but the impression I have from the piece is that we'll use natural language to inform some AI system that will actually produce the code. Possibly, but let's set that aside for a minute. Assume that we could have an AI that removed the need for us to code the corporate CRUD systems or most of the websites we have. Could such a system be adapted to manage data? Possibly, and maybe more consistently than we can. Certainly plugging in RTO/RPO values and HA needs might allow an AI like system to schedule backups, build up or tear down new nodes in a cluster or Availability Group, and certainly respond to DBCC errors, reindex systems, and more. I certainly could see that we could request a system to do X and lots of the background details would be handled. There are some [other thoughts about this piece]( that I tend to agree with. The ability to easily specify the way in which a system can work, for the average or even above average user, isn't coming soon, if ever. The thing is, I'll take solace in something that I truly believe. Writing the code isn't the hard part. Sure, syntax can be challenging, and working out the mechanics of how modules interact, and building MAKE-like scripts, and deploying changes are difficult. We forget about the differences in environments and configuration, and I bet better static analysis and AI might help here. The thing that's really hard, that is likely to still exist even if we specify the way something works is that it's really, really hard to be specific. We struggle to express the exact way that systems should work. We struggle sometimes to communicate what "a discount for more than 10 pieces" means. Is that a discount at 10? At 11? what if there are 20 ordered and 10 returned? Do we remove the discount or pro-rate the return? We can solve the issue, but so often, we don't know there's an issue until the system gets into production. Changes in rules/regulations? We don't often know about them, much less know how to express those changes in plain language. Even if we could get by without writing code, the expression of "how" something works likely requires a lot of detail, a meticulous person, and endless patience to get the machine to understand. Something many developers do now. I suspect the same thing would be necessary for database development. Even if we could specify the result set, would we really trust the machine to query the source and not come up with multiple ways to get this result set, which might not be valid for another? Call my cynical, but coding isn't going away, even if the syntax changes to English (or some other spoken tongue). Steve Jones from [SQLServerCentral.com]( Join the debate, and [respond to today's editorial on the forums]( --------------------------------------------------------------- The Voice of the DBA Podcast Listen to the [MP3 Audio]( ( 5.0MB) podcast or subscribe to the feed at [iTunes]( and [Libsyn](. [feed]( The Voice of the DBA podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music. ADVERTISEMENT [SQL Source Control]( How to track every change to your SQL Server database See who’s changing your database, alongside affected objects, date, time, and reason for the change with SQL Source Control. Get a full change history in your source control system. [Learn more]( [SQL In The City]( SQL in the City Streamed – Compliant DevOps: June 20 2018 Join Redgate’s livestream on June 20 and learn from Microsoft Data Platform MVPs, Steve Jones, Grant Fritchey and Kathi Kellenberger. The schedule will include sessions to help you learn how to demonstrate compliance with regulations such as the GDPR, HIPAA and SOX. [Find out more and register for free]( Featured Contents  [] [Scalar Functions with Query Plans: Not all is obvious!]( Mike Byrd from [SQLServerCentral.com]() Scalar functions in Query Plans just don't always show the whole story ---------------------------------------------------------------  [] [Building reusable table build scripts using SQL Prompt]( You’ve been working on a query, function or procedure and you want to store the results of executing it in a table; maybe a table variable, or a temporary table. These all require column definitions, and such things can be tiresome to do. Phil Factor demonstrates how to use SQL Prompt to do most of the mindless tasks that you’d otherwise be obliged to undertake by hand.[More »]( ---------------------------------------------------------------  [] From the SQLServerCentral Blogs - [Deploying Azure Container Instances]( Andrew Pruski from [SQLServerCentral Blogs]( In a previous post I went through how to Push an image to the Azure Container Registry Now let’s look at...[More »]( ---------------------------------------------------------------  [] From the SQLServerCentral Blogs - [DBCC Clonedatabase and Query Store]( SQLRUs from [SQLServerCentral Blogs]( Cloning things is all the rage these days…..even Storm Troopers. Anyway, by now, you probably have seen documentation on Query Store...[More »]( Question of the Day Today's Question (by Steve Jones): What is returned by this code? DECLARE @x VARCHAR = '777'; SELECT @x; Think you know the answer? [Click here](, and find out if you are right. --------------------------------------------------------------- We keep track of your score to give you bragging rights against your peers. This question is worth 1 point in this category: T-SQL. We'd love to give you credit for your own question and answer. To submit a QOTD, simply log in to the [Contribution Center](. ADVERTISEMENT comprehensive and authoritative guide will teach you the DAX language for business intelligence, data modeling, and analytics. Leading Microsoft BI consultants Marco Russo and Alberto Ferrari help you master everything from table functions through advanced code and model optimization Pick up your copy of this great book today at [Amazon]( today. Yesterday's Question of the Day Yesterday's Question (by Steve Jones): I am creating a new Service Broker queue. I want to ensure that all messages are retained until the conversation is ended. What setting should I change? Answer: RETENTION = ON Explanation: The parameter to keep messages is RETENTION. When set to on, all messages are kept until the conversation is ended. Ref: CREATE QUEUE - [click here]( --------------------------------------------------------------- [» Discuss this question and answer on the forums]( Featured Script [SQL Server Backup, Integrity Check, Index and Statistics Maintenance]( Ola Hallengren from [SQLServerCentral.com]() Award-Winning SQL Server Maintenance Solution for running backups, integrity checks, and index and statistics maintenance on all editions of Microsoft SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012, SQL Server 2014, SQL Server 2016, and SQL Server 2017. - Backup: [click here]( - Integrity Check: [click here]( - Index and Statistics Maintenance: [click here]( The SQL Server Maintenance Solution is [free](. Ola Hallengren [click here]( [More »]( Featured Script [SQL Server All View Refresh]( Yusuf Kahveci from [SQLServerCentral.com]() run the script I prepared to update all the views in the database. Yusuf KAHVECI yusufkahveci@sqlturkiye.com www.sqlturkiye.com Thanks [More »]( 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]( : [SQL Server 2017 - Administration]( [??????????????????Q/?501146313??????????UR??????????University of Reading]( - ???????QQ??501146313?????,????,??????,?????,?????,???,??,??,??,???,???,??,?????????????????:????????????????????????? ??????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????/?????????????????????/????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????? [???????????????????????????Q/?501146313??????????????????????/????Adelaide University]( - ???????????????????????????Q/?501146313??????????????????????/????Adelaide University???????????????????????????Q/?501146313??????????????????????/????Adelaide University???????????????????????????Q/?501146313??????????????????????/????Adelaide University???????????????????????????Q/?501146313??????????????????????/????Adelaide University ???????QQ??501146313?????,????,??????,?????,?????, [???????????????????????????Q/?501146313??????????????????????/????Adelaide University]( - ???????QQ??501146313?????,????,??????,?????,?????,???,??,??,??,???,???,??,?????????????????:????????????????????????? ??????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????/?????????????????????/????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????? [??????????????????????Q/?501146313??????????????????California Institute of Technology]( - ??????????????????????Q/?501146313??????????????????California Institute of Technology??????????????????????Q/?501146313??????????????????California Institute of Technology??????????????????????Q/?501146313??????????????????California Institute of Technology??????????????????????Q/?501146313??????????????????California Institute of Technology ???????QQ??501146313?????,????,??????,??? [???????????????????????????Q/?501146313?????????????????????/????TheUniversity of Melbourne]( - ???????QQ??501146313?????,????,??????,?????,?????,???,??,??,??,???,???,??,?????????????????:????????????????????????? ??????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????/?????????????????????/????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????? [???????????????????????????Q/?501146313?????????????????????/????TheUniversity of Melbourne]( - ???????QQ??501146313?????,????,??????,?????,?????,???,??,??,??,???,???,??,?????????????????:????????????????????????? ??????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????/?????????????????????/????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????? [??????????????????Q/?501146313??????????SMU??????????Saint Mary's University]( - ???????QQ??501146313?????,????,??????,?????,?????,???,??,??,??,???,???,??,?????????????????:????????????????????????? ??????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????/?????????????????????/????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????? [??????????????????????Q/?501146313?PITT??????????????University of Pittsburgh]( - ??????????????????????Q/?501146313?PITT??????????????University of Pittsburgh??????????????????????Q/?501146313?PITT??????????????University of Pittsburgh??????????????????????Q/?501146313?PITT??????????????University of Pittsburgh??????????????????????Q/?501146313?PITT??????????????University of Pittsburgh ???????QQ??501146313?????,????,??????,?????,?????,???,??,??,??,???,???,??,??? [??????????????????????Q/?501146313?PITT??????????????University of Pittsburgh]( - ???????QQ??501146313?????,????,??????,?????,?????,???,??,??,??,???,???,??,?????????????????:????????????????????????? ??????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????/?????????????????????/????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????? [DB Maintenance]( - Hi Guys, Part of the Database Maintenance routines (REbuild Index and Update Stats)...which is the best sequence to maintain? I always... --------------------------------------------------------------- [SQL Server 2016]( : [SQL Server 2016 - Administration]( [Upgrading from SQL Server 2016 SP1 to SP2]( - Hi, I'm running SQL Server 2016 SP1 (specifically SP1-CU4) Developer on my development machine. I had downloaded the full SQL Server... [SQL Server 2016 - Tran log growing huge]( - Forum, We recently deployed a new server SQL 2016 (first)and built an Availability group, now the problem is the TRN log... --------------------------------------------------------------- [SQL Server 2016]( : [SQL Server 2016 - Development and T-SQL]( [CTE to returning WAC price]( - Hello community, I have build a CTE to calculate my WAC price like that: CREATETABLE stock_table   (document_type VARCHAR(2), document_date datetime, product_id --------------------------------------------------------------- [SQL Server 2014]( : [Development - SQL Server 2014]( [create table from select]( - What's the correct syntax? CREATE TABLE Title2 AS SELECT * FROM Title Msg 156, Level 15, State 1, Line 2 Incorrect syntax near... --------------------------------------------------------------- [SQL Server 2012]( : [SQL Server 2012 - T-SQL]( [How do we eliminate the pairs and only display the single rows ? - SQL HELP]( - My question is listed within the SQL code. IF OBJECT_ID('tempdb..#RX') IS NOT NULL DROP TABLE #RX; create table #RX(EmployeeNbr VARCHAR(20) , Claim_Number... --------------------------------------------------------------- [SQL Server 2008]( : [SQL Server 2008 - General]( [Query]( - Hi  I have below Data . [Same query, two users, different performance]( - Hey Gurus, I have a query that performs differently depending upon the user executing it. This was first brought to my... --------------------------------------------------------------- [SQL Server 2008]( : [SQL Server Newbies]( [detect if previous record is higher or lower ?]( - In it's simplest form if I had a table that stored a persons name, date that the record was entered,... --------------------------------------------------------------- [Cloud Computing]( : [SQL Azure - Development]( [Add Azure SQL Database to Azure Private Network]( - Does anyone know if this is possible? I can find no resource, Microsoft or third-party which give advice how this... --------------------------------------------------------------- [Data Warehousing]( : [Integration Services]( [Importing a Flat File Containing Header and Detail Rows]( - Hello All, I'm not quite sure how to explain my situation without confusing the living hell out of everyone but i'll... 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](. Feel free to forward this to any colleagues that you think might be interested. If you have received this email from a colleague, you can register to receive it [here](. --------------------------------------------------------------- This transmission is ©2018 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved. Contact: webmaster@sqlservercentral.com