Parameter Pain (2019-11-11)

 SQLServerCentral Newsletter for November 11, 2019 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [Parameter pain]( - [Compare SQL Server Results of Two Queries]( - [Why you should include the database in your 2020 IT strategy]( - [From the SQL Server Central Blogs - PASS Summit 2019 – Day 1 Keynote]( - [From the SQL Server Central Blogs - Availability Groups: What to do without MultiSubNetFailover = True]( Question of the Day - [Finding Failed Logins]( Featured Script - [T-SQL script to purge all the tables including foreign key references]( The Voice of the DBA  True HA and DR For many years, SQL Server administrators have tried to ensure their systems run smoothly by executing DBCC commands against their production databases. As workloads have grown, this has become an issue with resource contention with production users. Backups have had the same issue, and the smart DBAs have offloaded this work to another server. With the more recent versions of SQL Server, we could move backups and DBCC checks to secondary systems. The problem with this has been licensing. Microsoft has required a secondary node (often referred to as passive, which [Allan Hirt notes is incorrect]() to be separately licensed if any activity is performed, which includes DBCC. We could have a secondary system receiving data as a part of [Software Assurance licensing]( but no activity on this system. A common misunderstanding, and a long and often raised complaint by customers. That has changed as of Nov 1, 2019. [Microsoft posted a blog]( that now lets us use those secondary systems for a number of purposes. We can now run DBCC, log and full backups, and monitor resource usage data on a secondary system without requiring licensing. What's more, we can now have multiple secondary systems that are not separately licensed for both HA and DR purposes. Perhaps best of all, this isn't limited to SQL Server 2019. This is in effect for all supported SQL Server systems. With [SQL Server 2012 in Extended Support]( I would assume this would apply there. The caveat is that you need Software Assurance (SA) on the licensed systems. You don't get this for free, but many organizations get SA to allow them to upgrade, and this will be a welcome addition to the value from paying for SA. I think this should have been the way that licensing worked forever, as I'd argue DBCC isn't a workload, but a verification that things are working appropriately. SQL Server should detect this stuff automatically to me, but since it doesn't, this isn't any active workload. Neither are backups. Finally, Microsoft agrees. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums]( [Redgate Data Masker](   Featured Contents [Parameter pain]( MVDBA (Mike Vessey) from SQLServerCentral A forum question came up a few days ago regarding an abnormally large procedural cache. In an attempt to help out, I threw together a few ideas (some wrong, some right) and started looking at the types of query that were in the cache. A lot of the queries were in the format select * […] [Compare SQL Server Results of Two Queries]( Additional Articles from MSSQLTips.com Code Tuning | Data Comparison | T-SQL Samples - In this tip we look at a simple way to compare the output from two different queries that are supposed to provide the same output to make sure the query results are exactly the same for both. [Why you should include the database in your 2020 IT strategy]( Additional Articles from Redgate With 2020 just around the corner, organizations of all shapes and sizes are considering what the next 12 months plans should look like. Here is what you need to know about including the database in your plans. From the SQL Server Central Blogs - [PASS Summit 2019 – Day 1 Keynote]( Tim Mitchell from Tim Mitchell Today is the first full day of the PASS Summit, and this morning we kicked things off with a 2-hour opening ceremony and keynote. Networking and Growth PASS president... From the SQL Server Central Blogs - [Availability Groups: What to do without MultiSubNetFailover = True]( Will Assaf from SQL Tact I received an email from a client who is having issues with third-party applications connecting to their three-subnet SQL Server Availability Group. After an exchange with Microsoft Support, they...   Question of the Day Today's question (by Steve Jones - SSC Editor):  Finding Failed Logins I have installed SQL Server 2017 and left the default security action for "Audit Failed Logins" in the instance security properties. Where can I view the failed logins? 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) The Derived Table What happens when I run this code? SELECT C.CustomerID FROM ( SELECT sh.CustomerID, OrderDate = MAX(sh.OrderDate) FROM dbo.SalesHeader AS sh GROUP BY sh.CustomerID ) AS C INNER JOIN C AS c2 ON C.CustomerID = c2.CustomerID; Answer: I get a syntax error Explanation: I get a syntax error from this. Why? The FROM clause is parsed at once and used to build an execution plan. However, when this happens, the c alias for the subquery hasn't been materialized, at least according to the parser. This means I get this error: Msg 208, Level 16, State 1, Line 1 Invalid object name 'C'. The error is actually for the C in the inner join line. Ref: Subqueries - [Discuss this question and answer on the forums](  Featured Script [T-SQL script to purge all the tables including foreign key references]( Brahmanand Shukla from SQLServerCentral T-SQL script to purge all the tables including foreign key references. The script has been made smart enough to use TRUNCATE wherever there is no foreign key reference and to use DELETE wherever there is foreign key reference. /* Author : Brahmanand Shukla Date : 28-Oct-2019 Purpose : T-SQL script to purge all the tables including foreign key references */ ; WITH cte_All_Tables_With_Foreign_Key -- Get all the tables having foreign key. Ignore the self-referencing. AS ( SELECT PAR_SCH.[name] AS [Parent_Schema_Name] , PAR_TAB.[name] AS [Parent_Table_Name] , REF_SCH.[name] AS [Referenced_Schema_Name] , REF_TAB.[name] AS [Referenced_Table_Name] , FK.[parent_object_id] AS [parent_object_id] , FK.[referenced_object_id] AS [referenced_object_id] FROM sys.foreign_keys FK INNER JOIN sys.tables PAR_TAB ON PAR_TAB.[object_id] = FK.[parent_object_id] INNER JOIN sys.schemas PAR_SCH ON PAR_SCH.[schema_id] = PAR_TAB.[schema_id] INNER JOIN sys.tables REF_TAB ON REF_TAB.[object_id] = FK.[referenced_object_id] INNER JOIN sys.schemas REF_SCH ON REF_SCH.[schema_id] = REF_TAB.[schema_id] WHERE FK.[type] = 'F' AND FK.[parent_object_id] <> [referenced_object_id] AND PAR_TAB.type = 'U' AND REF_TAB.type = 'U' ) , cte_Find_All_Referenced_Tables_In_Sequence /* Recursive CTE : Find the sequence of each referenced table. For e.g Table1 is referenced with Table2 and Table2 is referenced with Table3 then Table3 should be assigned Sequence as 1, Table2 should be assigned Sequence as 2 and Table1 should be assigned Sequence as 3 */ AS ( SELECT FK1.[Parent_Schema_Name] , FK1.[Parent_Table_Name] , FK1.[Referenced_Schema_Name] , FK1.[Referenced_Table_Name] , FK1.[parent_object_id] , FK1.[referenced_object_id] , 1 AS [Iteration_Sequence_No] FROM cte_All_Tables_With_Foreign_Key FK1 LEFT JOIN cte_All_Tables_With_Foreign_Key FK2 ON FK1.[parent_object_id] = FK2.[referenced_object_id] WHERE FK2.[parent_object_id] IS NULL UNION ALL SELECT FK.[Parent_Schema_Name] , FK.[Parent_Table_Name] , FK.[Referenced_Schema_Name] , FK.[Referenced_Table_Name] , FK.[parent_object_id] , FK.[referenced_object_id] , CTE.[Iteration_Sequence_No] + 1 AS [Iteration_Sequence_No] FROM cte_All_Tables_With_Foreign_Key FK INNER JOIN cte_Find_All_Referenced_Tables_In_Sequence CTE ON FK.[parent_object_id] = CTE.[referenced_object_id] WHERE FK.[referenced_object_id] <> CTE.[parent_object_id] ) /* Get the distinct parent tables with their Iteration Sequence No */ , cte_Unique_Parent_Tables_With_References AS ( SELECT DISTINCT [Parent_Schema_Name] , [Parent_Table_Name] , [parent_object_id] , [Iteration_Sequence_No] FROM cte_Find_All_Referenced_Tables_In_Sequence ) , cte_All_Tables /* Merge all tables (such as Tables with Foreign Key and Tables without Foreign Key). */ AS ( SELECT SCH.[name] AS [TABLE_SCHEMA] , TAB.[name] AS [TABLE_NAME] , ISNULL(STGB.[Iteration_Sequence_No], (ISNULL(MITRN.[Max_Iteration_Sequence_No], 0) + 1)) AS [ITERATION_SEQUENCE_NO] , CASE WHEN STGB.[parent_object_id] IS NOT NULL THEN 1 ELSE 0 END AS [TABLE_HAS_REFERENCE] FROM sys.tables TAB INNER JOIN sys.schemas SCH ON SCH.[schema_id] = TAB.[schema_id] LEFT JOIN cte_Unique_Parent_Tables_With_References STGB ON STGB.[parent_object_id] = TAB.[object_id] OUTER APPLY ( SELECT MAX([Iteration_Sequence_No]) AS [Max_Iteration_Sequence_No] FROM cte_Unique_Parent_Tables_With_References ) MITRN WHERE TAB.[type] = 'U' AND TAB.[name] NOT LIKE 'sys%' ) /* Output : Table Schema, Table Name and T-SQL script to purge the table data. TRUNCATE is being used whereever there is no foreign key reference or else DELETE is used. */ SELECT TBL_SEQ.[TABLE_SCHEMA] AS [TABLE_SCHEMA] , TBL_SEQ.[TABLE_NAME] AS [TABLE_NAME] , TBL_SEQ.[Iteration_Sequence_No] , (CASE WHEN ROW_NUMBER() OVER (ORDER BY TBL_SEQ.[Iteration_Sequence_No] ASC) = 1 THEN 'SET NOCOUNT ON;' ELSE '' END) + CHAR(13) + CHAR(10) + (CASE WHEN TBL_SEQ.[TABLE_HAS_REFERENCE] = 0 THEN 'TRUNCATE TABLE ' + QUOTENAME(TBL_SEQ.[TABLE_SCHEMA]) + '.' + QUOTENAME(TBL_SEQ.[TABLE_NAME]) + ';' ELSE 'DELETE FROM ' + QUOTENAME(TBL_SEQ.[TABLE_SCHEMA]) + '.' + QUOTENAME(TBL_SEQ.[TABLE_NAME]) + ';' END + CHAR(13) + CHAR(10) + 'GO') AS [TSQL_SCRIPT] FROM cte_All_Tables TBL_SEQ ORDER BY TBL_SEQ.[Iteration_Sequence_No] ASC, TBL_SEQ.[TABLE_SCHEMA] ASC, TBL_SEQ.[TABLE_NAME] ASC OPTION (MAXRECURSION 0) [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 [DB access monitoring]( - Hi All, I need to monitor a application (service user) action on SQL server DB e.g when application is querying predefined tables and columns.I do not want to user SQL Server DB audit as log file size is increasing and getting bigger in size in a day. Is there a way to get the info […] SQL Server 2017 - Development [Date Dimension Table and Query]( - I have a date dimension with the following data: WarrantyId   QuarterName   StartDate 889840      Y1Q1          2019-07-01 00:00:00.000 889840      Y1Q2          2019-10-01 00:00:00.000 889840      Y1Q3 […] SQL Server 2016 - Administration [SQL Copy Database Wizard Broken]( - I have been using the SQL Copy Database Wizard (right click on the DB and Copy Database option) through the GUI (SMS) to copy a DB up until SQL 2016. Every installation I have where 2016 or greater has been involved the feature is broken and always gives the same error. It doesn't matter if […] [Is there a SQL to show how many total select statements hit a particular table?]( - Since the last server restart or any point of time. Without any auditing or anything like that set up. Can DMVs be used to create such query? Thanks. [Email alert]( - Can extended events send out an email alert ? ex: I have a windows or sql account created on a server . I can track that the account has been created but can I configure and have an email alert sent to me when the account creation happens thanks [Account alert]( - I have a windows service account(AAAA) which gives access to either other windows accounts or creates other sql accounts) in a SQL database server. How can I identify any other accounts which are given access or created in that database server but not created/given access through this windows service account(AAAA). I need to be immediately […] SQL Server 2016 - Development and T-SQL [how to merge 3 different temp tables data into one]( - Is there any better way to do this?  create table #student (StudentName varchar(20) null, StdId int not null, Stdgrade varchar(10) null) insert into #student (StudentName , StdId , Stdgrade ) select std.Name ,std.StdID ,dp.stdgrade from dbo.student std join dbo.department dp on std.stdid = dp.id where dp.isactive = 1 insert into #student (StudentName , StdId […] [Using CASE Statement to SET variable when null value]( - Hi , I have a SP , that does an update to add comments to the table. However when there is a Null value it errors out. Im trying to use a Case when to put in a default value if there comment passed are null. Ive put this together but not sure if I […] Development - SQL Server 2014 [SELECT processed before WHERE clause]( - I have a SELECT CAST(Column to int) that has been working OK, on the assumption that the WHERE has already filtered out any rows that would cause the CAST to error. Then I changed the filtering (still excluding any problem rows) - and it failed. I finally realised that the execution plan must have changed, […] SQL 2012 - General [How to enhance this query for good performance ?]( - I work on sql server 2012 and i need to enhance or make this query have good performance this stored procedure work success but i need to know when make drop to temp table and cte plus how to write it with best practice for performance  create Proc ImporterQueue_RunModified As WITH CTE AS ( […] SQL Server 2012 - T-SQL [Get some row depending of Date of nexts rows]( - Hi, Sorry for my bad english. I need to select some rows of a table like that : Id Object  |       Start      |       End A      | 06/11/2019 09:00 | 06/11/2019 09:15 A    […] Reporting Services [Hosting PowerBI in SSRS]( - Overview I have been tasked with the question is there a tool that can host PowerBI reports other than PowerBi Reports Engine? The problem I believe is licensing. We have license for SQL Server Enterprise 2017 that includes SSRS & SSAS. Question Can we host PowerBI Reports in SSRS? I believe I got the answer […] XML [XML Help.. adding a root to the root?]( - I have a XML output that gets output into a file for BizTalk to send to another company, and I'm trying to format the Root clause the way they want but its a bit bizzare and I'm having trouble This is how they want it formatted: ... ... […] TFS/Data Dude/DBPro [TFS change tracking and details.]( - Hi, I had a quick question and I am using TFS 2015. How do I see what is the changeset number of an SP which is deployed just now? How do I find out at what time that Stored procedure or object was checked in? Thanks!  SQL Server 2005 Integration Services [SSIS 2016 ROW counts to a Flat File destination]( - Hi, I have 3 flat file sources. Now I want to count the number of records in each flat file and I want to write the counts to a flat file in the below output. FileName Count can anyone suggest me step by step on how exactly I achieve this ? Many Thanks     [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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -