Creating and using inline table-valued functions (2020-06-19)

 SQLServerCentral Newsletter for June 19, 2020 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [Creating and using inline table-valued functions]( - [Can I Offload DBCC CHECKDB To Another Server?]( - [What is the impact of remote working on estate management?]( - [From the SQL Server Central Blogs - Use Chocolatey to Install Multiple SQL Server 2019 Instances for Testing]( - [From the SQL Server Central Blogs - Creating a New Sequence–#SQLNewBlogger]( Question of the Day - [Adding Extended Properties to Tables]( Featured Script - [Stack multiple tables using UNION ALL]( The Voice of the DBA  Daily Coping Tip Make a plan with friends to do something fun together I also have [a thread at SQLServerCentral]( dealing with coping mechanisms and resources. Feel free to participate.  For many of you out there working in a new way, I'm including a thought of the day on how to cope in this challenging time from [The Action for Happiness Coping Calendar](. My items will be on [my blog](, feel free to share yours. Evaluating Life at Work One of the more interesting things that we do at Redgate is regularly ask workers to evaluate their environment. We use a company, called Thymometrics, and periodically employers as asked to rate various factors, both in terms of how important are they to us, and how satisfied are we. I [tweeted the general dashboard](, but essentially there are a series of sliders we use to rate things, between a max and min. No scale, just a visual choice of are things more or less important. Am I more of less satisfied. The range is factors are here: - Work Environment - Wellbeing - Salary - Benefits - Culture and Values - Giving Something Back - Leadership - My Manager - Personal Growth - Recognition - Redgate's Purpose - My Team - My Role When I log in, I can see how I last set things. I can also reset everything to the middle and re-evaluate how I view the world. Usually I look through and see if I want to change my satisfaction. Rarely do I think that some part of my job situation has changed in importance. However, at least once a year I do reset things and start over. While I know plenty of people see this as a pain in the sitting part of the body to go through, I appreciate it. This lets me evaluate how I feel about my job situation, and about my company. I get to think for a few minutes about whether I am truly satisfied with my position and how I am treated by this organization. This is a somewhat formalized process. I checked with the team that looks at this and they get anonymized rollup of data, unless someone puts in a comment and asks for a reply. Even then, they don't send the raw data to managers, but attempt to mediate if there are issues. It's a good idea, and really, this is what I do and recommend for others. At least once a year I take a serious look at my employment situation, in conjunction with how my personal life is going and think about what I like and don't. I think about my other options, and discuss this with my wife. Is this the best way to live my life? So far it has been every year, since I think I have one of the best jobs out there. That doesn't mean I should assume there aren't other options or ways that I might want to change my job. I think you should do the same thing yourself, at least once a year. Honestly evaluate your situation, ask others about theirs, and make the best decision for you. After all, you only get one chance to go through this life. If it's not working, plan to make a change. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [Creating and using inline table-valued functions]( kimani_m from SQLServerCentral.com Inline Table Valued Functions (iTVFs) are one type of user defined function that is available to implement in SQL Server since SQL Server 2000. iTVFs remain a very useful tool in our SQL armoury, so let's quickly revisit them and the different ways we can use them in our code. [Can I Offload DBCC CHECKDB To Another Server?]( Additional Articles from Brent Ozar Unlimited Blog You want to check for corruption, but you don’t want to slow down your primary production server. In this post, I’m specifically talking about offloading the corruption checking process. I’m not talking about doing corruption checking on both the primary and other servers – that’s wonderful, and if you’re doing that, you should hug yourself. You’re doing a good job. Who’s a good dog? You are! Good dog. [What is the impact of remote working on estate management?]( Additional Articles from Redgate Distributed workforces have very quickly become the norm. The 2020 State of Database Monitoring survey explores the impact of remote working on estate management and how organisations are responding. You can read the full findings in the free report, here. From the SQL Server Central Blogs - [Use Chocolatey to Install Multiple SQL Server 2019 Instances for Testing]( Chad Crawford from LittleKendra I’m working on a project where it’s useful to automate environment setup and teardown for testing some devops deployment scenarios for databases using transactional replication. To make this easier,... From the SQL Server Central Blogs - [Creating a New Sequence–#SQLNewBlogger]( Steve Jones - SSC Editor from The Voice of the DBA I wrote about sequences in an editorial recently and decided to start using them. Creating one turned out to be surprisingly easy. I had a table I’d been logging...   Question of the Day Today's question (by Steve Jones - SSC Editor):  Adding Extended Properties to Tables I want to add an extended property to the dbo.Adverts. What parameters do I need to specify to sp_addextendedproperty? 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) CTE in Tempdb When does a CTE use tempdb? Answer: When a spool operator is in the execution plan Explanation: Tempdb is used for CTEs when a spool operator saves intermediate results. Ref: Capacity planning in tempdb - v=sql.105)?redirectedfrom=MSDN [Discuss this question and answer on the forums](  Featured Script [Stack multiple tables using UNION ALL]( T::Libs from SQLServerCentral Avoid conversion errors when using UNION to stack rows from multiple tables. --CREATE EXAMPLE TABLES create table [dbo].[table1] ([name1] varchar(100), [name2] varchar(100), [name3] varchar(100)) create table [dbo].[table2] ([name1] varchar(100), [name2] varchar(100), [name3] varchar(100), [name5] varchar(100), [name6] varchar(100)) create table [dbo].[table3] ([name1] varchar(100), [name3] varchar(100), [name5] varchar(100), [name7] varchar(100)) create table [dbo].[table4] ([name4] varchar(100)) --THEN INSERT DATA insert into [dbo].[table1] ([name1], [name2], [name3]) values('T1N1','T1N2','T1N3') insert into [dbo].[table2] ([name1], [name2], [name3], [name5], [name6]) values('T2N1','T2N2','T2N3','T2N5','T2N6') insert into [dbo].[table2] ([name1], [name2], [name3], [name5], [name6]) values('T2N1','T2N2','T2N3','T2N5','T2N6') insert into [dbo].[table3] ([name1], [name3], [name5], [name7]) values('T3N1','T3N3','T3N5','T3N7') insert into [dbo].[table4] ([name4]) values('T4N4') --get the table names, exclude the "UNION ALL" command on the first table. this is the first step in preparing the result script. ;with [tables] as ( select case when tid>1 then 'union all' else '' end as union_all ,table_name from ( select table_schema + '.' + table_name as table_name, row_number() over (order by table_name) as tid from information_schema.tables where table_name like 'table_' ) AS A ) --Then get to cooresponding columns from the same set of tables , [columns] as ( select table_schema + '.' + table_name as table_name, column_name from information_schema.columns where table_name like 'table_' ) ,combinations as ( select distinct [tables].table_name, [columns].column_name from [tables],[columns] ) --then find where the place holders need to be. , [placeholders] as ( select tc.table_name, tc.column_name, iif(b.column_name is null, ''''' as [' + tc.column_name + ']', b.column_name) as mapper ,row_number() over (partition by tc.table_name order by tc.column_name) as colide from combinations tc left join [columns] as b on tc.table_name = b.table_name and tc.column_name = b.column_name ) --keep the values in [placeholder]; store them in a temp table select * into #placeholders from [placeholders] --for the columns that don't exist in any of the tables, the mapper column shows '' as [] --now we just turn this table into a script using STUFF FOR XML(). --we'll use the same CTE as before, though this could be saves as a table, temp or otherwise. declare @script nvarchar(4000) = N'' ;with [tables] as ( select case when tid>1 then 'union all' else '' end as union_all ,table_name from ( select table_schema + '.' + table_name as table_name, row_number() over (order by table_name) as tid from information_schema.tables where table_name like 'table_' ) AS A ) select @script = @script + union_all + char(10) + 'select ''' + t.table_name + ''' as [table], ' + stuff((select ', ' + m.mapper from #placeholders m where m.table_name = t.table_name for xml path('')),1,1,'') + ' from ' + table_name + char(10) + char(13) from [tables] t print @script exec sp_executesql @script --done! [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 - Administration [SSMS: How to save multiple resultsets from same query into 1 result or 1 file?]( - I am submitting an official request for DBA of certain servers to run a script on those servers and give me back the resultset. However the script (the actual script is 3 times larger than the attached and returns 20 more results) returns multiple results, so a DBA will not be copy/pasting each resultset into […] [Availability Groups for failover AND mirror]( - I've got a 2-node failover AG setup. I want to add a 3rd node to be used as a reporting server. That could be read-only, but has to be available all the time. For reasons beyond my control, I can't use replication. The database has no primary keys indexes (3rd-party software design). So, it all […] SQL Server 2017 - Development [SQL Server Fragmentation- Defragment]( - The table consist of around 2 billion record on an average there are 1000K inserts happening daily , we see some slowness in recent days , so while checking the table we saw some fragmentation information. Above is the query i used against my dataware database and observed some fragmentation in the index for some […] SQL Server 2016 - Administration [Creating DB diagram]( - Hi, I was planning to create a DB diagram of a user DB to better understand the relationship however when I right-click on database diagram (please expand the user DB to see this option) I see an alert window saying 'This database does not have one or more of the support objects required to use […] [SQL server 2016 SP2 error]( - Hi All,  am trying to update my 2016 SQL server instance to the SP2. the current version is Microsoft SQL Server 2016 (SP1-GDR) (KB4505219) - 13.0.4259.0 (X64) Jun 15 2019 19:20:12 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows 10 Enterprise 6.3 (Build 18363: ) SP2 setup file : SQLServer2016-KB4549825-x64.exe for some […] [SQL Trace Not Capturing Database Events....EXEC, INSERT, UPDATE...]( - I want to see what Stored Procedures are run when users enter data in a 3rd party product we use. I would also like to see INSERTS & UPDATES to tables. I set up a trace that I have used elsewhere with success, but in this case, it's not capturing any of the events I […] SQL Server 2016 - Development and T-SQL [Help with T-SQL]( - I have a web interface for managing tool rentals for our chapter members. I have a display page that will list all of the tools we have and to flag those tools that are currently being rented out to include the expected return date. The important thing is to know which are available and which […] [How to optimise the table performanace (90 lacs records)]( - Hi, I have a table with 5000 users along with 6 years of attendance data. If I want to retrieve user data from the table it takes time to load. Table Structure Usercode, Rosterdate, Shift code, AttendanceCode Case 1 If i want to retrieve the shift information from 1-jun-2020 to 10-jun-2020 for 5000 users, would […] [Help in Date spans for continuoes and regular dates spans]( - Hello all, I would like to take input scripts and generate output data as given below. output is expected as if member is continuously enrolled without any gap in enrollment get min and max and if there is enrollment gap leave as is. for any given member there are no multiple spans just leave as […] Administration - SQL Server 2014 [OPENQUERY giving wrong result on linked server]( - I have setup linked servers on my SQL Server and choose Oracle as a linked server. SOMETIMES, When I run the query below under the SA username, I get different result. However when I run under my own username I get correct result. I Can't understand this anomaly. The below query returns only one row. The difference occurs in […] [Sum of all Memory Clerks and Total Server Memory (KB)]( - I am trying to understand the memory management of SQL Server. I have a Windows Server 2019 on virtual environment with 64 GB of RAM. I also have SQL Server 2019 installed with Max Server Memory 59776 MB. By using the following query I found the list of all memory clerks: SELECT [type] AS [ClerkType], […] SQL Server 2012 - T-SQL [? ON Parsing an XML Field]( - Hi, I know I've asked this before and got a good link , but can't figure out how to parse this XML field. I would usually parse something like Data.Value('Data/.../..) But not sure how to parse out say "Case" from the field below(SqlParameters)? SQL Server 2019 - Administration [How do I count the objects (tables,views,indexes) for a database?]( - Is there any simple script which I can use to count the user objects in a database? Reason is, I'm planning to do an upgrade from SQL 2008R2 to SQL 2019 & my plan is to take a count of the objects on the old SQL and 2019 so I can compare and see if […] Anything that is NOT about SQL! [SSC Traffic Declining ?]( - Are there fewer posts & replies on SSC these days ? It seems like it. Or are they just spread over more sub forums of the various SQL versions ? COVID-19 Pandemic [Daily Coping 16 Jun 2020]( - Today’s tip is to find the joy in music today: sing, play, dance, or listen.   [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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -