The Basics of iTVFs (SQLServerCentral 1/10/2019)

[SQLServerCentral - www.sqlservercentral.com]( A community of more than 1,600,000 database professionals and growing Featured Contents - [The Basics of iTVFs]( - [SSRS Reporting Basics: When is SSRS the Right Tool?]( - [Introducing Azure SQL Database Hyperscale]( - [Automatic Tuning Monitoring and Diagnostics]( (From the SQLServerCentral Blogs) - [Visualizing Hash Match Join Internals And Understanding Their Implications]( (From the SQLServerCentral Blogs) The Voice of the DBA Badly Trained AI Most of us know that data is being used to make more and more decisions inside of all kinds of organizations from retail giants to banks to sports teams. We are constantly asked, or see reports, of data driven decisions. We often need to show some data that supports and explains the rationale for making some choice. As our populace becomes more data savvy, I expect this trend to continue. AI (Artificial Intelligence), and the related Machine Learning (ML), are becoming more and more widely used. From mobile phones to autos to trading systems, we regularly see new "AI capabilities" being added to products and services. No business or industry seems immune, and I'm sure many of you are seeing AI being incorporated or feeling pressure to start using some AI in your work. As you work with AI, or start to, you'll quickly realize the importance of data in your efforts. This is true for the cleanliness of data, but perhaps even more important in the tagging of data sets. As Amazon learned, building an AI or ML system, is hard. [They scrapped one system]( that was being used to rate resumes and help their recruiters sort through the volume of applications they received. Why? Because of bias. Apparently the system would downgrade women's resumes for various reasons. To me, this is a perfect example of a principle I've had throughout my career: garbage in garbage out. In this case it's not necessarily bad data that was the problem, but bad tagging of what was a good and bad resume, probably from the internal prejudices of a few people. There will be more dangers as we use ML and AI technologies in our work. It won't be enough that we clean the raw data for training, but also that we clean and properly manage the tagging of what data sets represent the results we are looking for. Like in much of our software, it's easy for us to only consider the happy path, to only tag those items we think are good results. That is useful, but we might also be unconsciously tagging other results as bad, which appears to have happened to Amazon. We can build systems that do a better, more rational job than most humans, but we need extraordinary care to ensure our training data lacks bias. Unfortunately, most people both think they're not biased and are unwilling to spend extra resources to deeply examine the data. Two things that worry me about the future of our AI/ML systems that will inform us. Steve Jones from [SQLServerCentral.com]( Join the debate, and [respond to today's editorial on the forums]( ADVERTISEMENT [RGUni]( Redgate University Self-paced online training courses with easy to follow classes on getting started, exploring advanced features, and making the most of Redgate products. Learn the fundamentals, best practices, and top tips from the experts - Redgate’s Microsoft Data Platform MVPs and engineers. [Start Learning]( [GDPR](www.red-gate.com/gdpr?utm_source=ssc&utm_medium=pubemailad&utm_content=gdpr-ready&utm_campaign=GDPR&utm_term=20180110-slot2) How to make your SQL Server development GDPR ready Redgate’s SQL Data Privacy Suite helps you to build a data catalog of your SQL Server estate, and put in processes to protect and monitor personal information. [Find out more about how our tools help with GDPR compliance](www.red-gate.com/gdpr?utm_source=ssc&utm_medium=pubemailad&utm_content=gdpr-ready&utm_campaign=GDPR&utm_term=20180110-slot2) Featured Contents  [] [The Basics of iTVFs]( Steve Jones from [SQLServerCentral.com]() Learn the basics of inline table valued functions in this short article.[More »]( ---------------------------------------------------------------  [] [SSRS Reporting Basics: When is SSRS the Right Tool?]( Additional Articles from [SimpleTalk]() SSRS has undergone a number of changes over the past few versions. It remains a very popular reporting tool in companies large and small. In this article, Eugene Meidinger recounts the history of SSRS and explains when it’s the best tool to use. This is the first article of a series on SSRS.[More »]( ---------------------------------------------------------------  [] [Introducing Azure SQL Database Hyperscale]( Additional Articles from [Microsoft MSDN]( With Azure SQL Database Hyperscale, databases can quickly auto-scale up to 100TB, eliminating the need to pre-provision storage resources, and significantly expand the potential for app growth without being limited by storage size.[More »]( ---------------------------------------------------------------  [] From the SQLServerCentral Blogs - [Automatic Tuning Monitoring and Diagnostics]( Jason Brimhall from [SQLServerCentral Blogs]( Cool new toys/tools have been made available to the data professional. Among these tools are query data store and automatic...[More »]( ---------------------------------------------------------------  [] From the SQLServerCentral Blogs - [Visualizing Hash Match Join Internals And Understanding Their Implications]( Bert Wagner from [SQLServerCentral Blogs]( This post is part 3 in a series about physical join operators (be sure to check out part 1 – nested...[More »]( Question of the Day Today's Question (by Steve Jones): On SQL Server 2017, I have this table: CREATE TABLE dbo.SalesOrderHeader ( OrderKey INT IDENTITY(1, 1) , CustomerName VARCHAR(30) ) GO I now insert some data in the table: INSERT dbo.SalesOrderHeader (CustomerName) VALUES ('Andy') INSERT dbo.SalesOrderHeader (CustomerName) VALUES ('Brian') INSERT dbo.SalesOrderHeader (CustomerName) VALUES ('Steve') If I now issue this command: DBCC CHECKIDENT(SalesOrderHeader, RESEED, 20) GO and then insert a new row: INSERT dbo.SalesOrderHeader (CustomerName) VALUES ('Azure') GO What is the value for OrderKey in the row with "Azure" as the CustomerName? 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: IDENTITY Property. 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 [Microsoft SQL Server 2017 on Linux]( [This comprehensive guide shows, step-by-step, how to set up, configure, and administer SQL Server 2017 on Linux for high performance and high availability. Written by a SQL Server expert and respected author, Microsoft SQL Server 2017 on Linux teaches valuable Linux skills to Windows-based SQL Server professionals. You will get clear coverage of both Linux and SQL Server and complete explanations of the latest features, tools, and techniques. Get your copy from Amazon today](. Yesterday's Question of the Day Yesterday's Question (by Steve Jones): I have this code in Python. What is returned? positions = "S", "OH", "MH", "OPP", "OH", "L" positions[:-2] Answer: ('S', 'OH', 'MH', 'OPP') Explanation: The negative value returns all items from the beginning of the tuple until the -2 second index, excluding that index. The negative indexes are 1 based, so the last value ("L") is -1, and the second "OH" is -2. All items before that are returned. Ref: Tuples - [click here]( --------------------------------------------------------------- [» 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]( : [SQL Server 2017 - Administration]( [Availability group listener is not listening]( - I've successfully created an availability group with 2 nodes that one node is physical and another is VM. Also running... --------------------------------------------------------------- [SQL Server 2017]( : [SQL Server 2017 - Development]( [substring with Charindex]( - Hello. I have a COLUMN that I'm trying to break apart into separate fields. This column is delimited by a... [SQL Quiz]( - Removed as per the wish of a user ! --------------------------------------------------------------- [SQL Server 2016]( : [SQL Server 2016 - Administration]( [Transaction log filling up issue.]( - Hi, I am seeing a bit of a strange issue on a lot of servers in the place I am currently... [Database in RECOVERY PENDING state - Access Denied]( - Hi I have s SQL2016 instance with 8 out of 10 databases in recovery pending state and I need assistance. I had... --------------------------------------------------------------- [SQL Server 2016]( : [SQL Server 2016 - Development and T-SQL]( [Writeback Values bigger than Int allows?]( - Ahoi, i have a write Table enabled and fully functionally working, the only issue is my users need to write back... [Storing a hash of a row]( - Hi Folks, First of all, happy new year to you all! TL;DR - I need to store a calculated hash value from a... --------------------------------------------------------------- [SQL Server 2014]( : [Administration - SQL Server 2014]( [Are cl and clst system indexs?]( - Has anybody seen these named indexs? I am doing the below join and these 2 indexs pop up and are... --------------------------------------------------------------- [SQL Server 2014]( : [Development - SQL Server 2014]( [Alternate Way Of Writing the Query]( - Hello Friends, We have been looking to optimize a query which at times run erratic and keeps changing its execution plan.... [SSIS: Changing target table without remapping all table columns]( - In developing an SSIS package that processes data and writes the output to a table with a 100+ columns, is... --------------------------------------------------------------- [SQL Server 2012]( : [SQL Server 2012 - T-SQL]( [Claculated field in Where clause]( - Hello,  am looking for the best, or most efficient way to implement a store procedure where I have several calculated fields... [CTE to determine Course Level]( - Okay, before someone asks, this is not homework... I just can't figure out how to do it. :-) I have two... --------------------------------------------------------------- [SQL Server vNext]( : [SQL Server 15 - Development]( [insert with output clause]( - I have an insert with an output clause where one of the outputted columns is not in the inserted list,... --------------------------------------------------------------- [SQL Server 2008]( : [SQL Server 2008 - General]( [Select convert to 2 decimal places, comma, right aligned]( - Hello The field below currently has an output that looks like this - select A. as from EQSA0345 A Replacement Cost 523.88000 60689.76000 48860.26000 77239.26000 132227.26000 65668.18000 71523.34000 I would... --------------------------------------------------------------- [Cloud Computing]( : [SQL Azure - Development]( [Renaming a primary key columns in the Azure DB]( - Summary: I have a DB that has combined keys as proimary keys and I have a task to rename one of... --------------------------------------------------------------- [Programming]( : [XML]( [Problem reading all Attributes into SQL table]( - Hello everyone. I'm writing a proc that imports several XML files and stores them in a SQL table.  Here's a sample... --------------------------------------------------------------- [SQL Server 2005]( : [Backups]( [Restore sql server backup]( - While restoring a database backup file taken in SQL 2005 Standard edition to new database in SQL 2014 Standard edition;... [Maintenance clean-up task completing, but giving Error: 18204,Severity: 16, State: 1]( - I have a maintenance plan that runs a cleanup task on the local server to delete all transaction files older... [Can you recover data from transaction log?]( - Hi, I have question regarding data recovery. Yesterday, our Navision consultants accidently deleted important business data from our database. We were... --------------------------------------------------------------- [Career]( : [Employers and Employees]( [Evolution of career from perm/consulting to freelancing?]( - Hi,  I am working as DBA for 17+years.  permanent -> consultant->  permanent -> consultant ...  at some point I start thinking about... 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