Using tsqlt Tests to Practice Queries (2022-05-30)

SQLServerCentral Newsletter for May 30, 2022 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [Using tsqlt Tests to Practice Queries]( - [Getting Started with Flyway and any RDBMS]( - [Search all String Columns in all SQL Server Tables or Views]( - [From the SQL Server Central Blogs - T-SQL Tuesday 150: My first technical job]( - [From the SQL Server Central Blogs - TSQL Tuesday #150 – My first technical job]( Question of the Day - [Decoding the Code]( The Voice of the DBA  Daily Coping Tip Set aside a regular time for a month to pursue something you enjoy 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. Audit systems: a good idea or a mess to maintain? Today we have a guest editorial as Steve is out of the office. This editorial was originally published on Feb 27, 2018. I was recently involved with a design of a configuration system. Pretty early on in the design we realized it would be a good idea to have an audit system. The system would need to keep track of any changes that were made to the configuration tables and allow for the ability to back out to any of the previous changes. It also keeps track of who changed what and when they changed it. This new configuration application has its own schema. All database access is done through stored procedures. There is no inline SQL code in the application. The audit tables match the actual tables except for a few extra audit columns. In the stored procs that do the insert, update or delete, there are audit procedures that are called before the action is done and audit data that is saved to the audit tables. The configuration application has an admin screen where you can view the audit data and if you have elevated rights you can click a revert button that will restore one of the previous configurations that is selected. This allows the users of the system to maintain it without IT intervention. No more late night calls asking for some configuration to be restored because it was changed by mistake. There are a number of other ways that one can audit a system in a production environment. Some audit systems keep track of changes that have been made, but don’t really help you back out those changes. I have seen some audit systems that are trigger based, but they tend not to have a lot of visibility to the end user. I am sure there are a number of other options when it comes to audit systems. I know SQL Server 2017 has a server audit and a database audit. You can see some details here: I have never tried it, but it is another option to consider. If audit systems are beneficial, why don’t we use them all over the place, in every application? Well, there can be a lot of work that needs to be done anytime a change needs to be made. Something as simple as adding a column to a table now has to be done in at least two places. So, the work is effectively doubled. Also, if inline SQL is allowed, it can quickly become impossible to keep an audit system working without a lot of extra work to ensure the inline SQL isn’t breaking the audit or vice versa. What is your experience with an audit system? Have you found a certain audit pattern to work better than others? Share your experience and let us know if your audit system was a good idea or a mess to maintain? bkubicek [Join the debate, and respond to today's editorial on the forums](   Featured Contents [SQLServerCentral Article]( [Using tsqlt Tests to Practice Queries]( Steve Jones - SSC Editor from SQLServerCentral In this short article, see how a unit test can be easily written in tsqlt, and how this can help us refactor or build better code. [External Article]( [Getting Started with Flyway and any RDBMS]( Additional Articles from Redgate How to get started with Flyway, as simply as possible, using PowerShell. This article provides a practice set of Flyway migration scripts that will build the original pubs database on either SQL Server, PostgreSQL, MySQL, MariaDB or SQLite and then migrate it from version to version, making a series of improvements to its schema design. [External Article]( [Search all String Columns in all SQL Server Tables or Views]( Additional Articles from MSSQLTips.com In this article we look at T-SQL script you can use to search for a string in every table and view in every database or in a specific database. [Blog Post]( From the SQL Server Central Blogs - [T-SQL Tuesday 150: My first technical job]( Diligentdba 46159 from Mala's Data Blog I am glad to be contributing to the 150th blog party started by Adam Machanic and has helped so many get our blogs going. This month’s T-SQL Tuesday is... [Blog Post]( From the SQL Server Central Blogs - [TSQL Tuesday #150 – My first technical job]( Tracy Boggiano from Database Superhero’s Blog It’s that time of the month again, the blog party, woohoo! This time Kenneth Fisher (b | t) has invited us to blog about our first tech job. Oh...   Question of the Day Today's question (by Steve Jones - SSC Editor):  Decoding the Code What does this code do? SET DATEFIRST 7; WITH myTally (n) AS ( SELECT n = ROW_NUMBER () OVER (ORDER BY (SELECT NULL)) FROM ( VALUES (1) , (2) , (3) , (4) , (5) , (6) , (7) , (8) , (9) , (10)) a (n) CROSS JOIN ( VALUES (1) , (2) , (3) , (4) , (5) , (6) , (7) , (8) , (9) , (10)) b (n) CROSS JOIN ( VALUES (1) , (2)) c (n) ) , cteCurrYearDates (myDate) AS ( SELECT DATEADD (DAY, n, DATEADD (yy, DATEDIFF (yy, 0, GETDATE ()), 0)) FROM myTally) , cteMay (Mondays) AS ( SELECT cteCurrYearDates.myDate FROM cteCurrYearDates WHERE DATEPART (WEEKDAY, cteCurrYearDates.myDate) = 2 AND YEAR (cteCurrYearDates.myDate) = YEAR (GETDATE ())) SELECT TOP 1 Mondays FROM cteMay WHERE MONTH (cteMay.Mondays) = 5 ORDER BY cteMay.Mondays DESC; 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) Starting on Monday How do I ensure that the T-SQL code I run recognizes Monday as the first day of the week? Answer: Use SET DATEFIRST 1 Explanation: DATEFIRST will set the first day of the week. In US English installations, the first day of the week is Sunday, which is day 7. If you want to ensure Monday is the first day of the week, SET DATEFIRST 1. Ref: SET DATEFIRST - [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 [Migrating Always On cluster to new Domain and cluster]( - We are in the process of migrating to new Windows AD. What would be the best approach for this with minimal down time?  Essentially we have several databases in AG on different nodes and a single listener name. The idea is to move to new Windows AD/new servers and retain the listener name. Normally if […] SQL Server 2017 - Development [Rules based updates]( - I found an article that is very similar to how I would like to apply business rules to update my table. I will have 4 colname values I would like to use: Channel,ShipFrom,ProdLine,CustType Channel -- Will be only 2 values ShipFrom - Could be single entry, or ALL, or ALL except(xx,xx,xx) Prodline -- All(*), or […] [Missing abstrophe in dynamic SQL]( - Hello , I haven't been able since morning to find how to add the missing abstrophe in my procedure that uses dynamic SQL I added a print in my script to display the exec instruction and I have a missing apstrophe in the name of the proc Anyone have an idea please? USE [test] GO […] SQL Server 2016 - Administration ['Alert- Sev17 Error:Fatal error in resource limit exceed occurred' on server]( - Hello! I have setup SQL Audit and it is writing to the Security log in the Event viewer. It is working but getting this error message below few times a day. "SQL Server Audit could not write in security log". What can be done to resolve this alert?  Thanks.  [SQL Audit in Always On Availability Group]( - Hello! I have setup SQL Audit in Always on Availability group (2 node) for ddl and dml to write to Event viewer. I have configured on both nodes using the same GUID. It writes to Event viewer in the Primary node (1) or when I fail primary and  and make secondary primary, it works. But, […] SQL Server 2016 - Development and T-SQL [Filetred index]( - \hello Guys,  Mayyou help wuth the corect index filetred creation using modulo?  The query need to assist is select col1,col2,......... WHERE ISRECNUM % 20 = 1 and i wish to create a filtered index on ISRECNUM column which is INT   like WHERE ISRECNUM % 20 = 1  . Thanks, Best Regards, […] [Disabling backup Jobs]( - I've not done a lot with disabling jobs with script, I need to read the job names from a text a disable them or enable them on all servers within the environment? Is there any good examples for doing this? [Rows won't return using where with one of the columns]( - Select * from tablea where comt_dim_id = 3947804 -- returns no rows Select count(1) from tablea where comt_dim_id = 3947804 -- returns count = 32  Why? How?  There are no current locks in the database. The column datatype = INT. Selecting these 32 rows based on a different column value works and shows the comt_dim_id […] SQL Server 2019 - Administration [PBALP User in SQL Server]( - Hi Everyone, Currently looking at tidying up some SQL servers and i have a number of local users (PBALP) being one of them i would like to drop. Is there a way i can check to see what if anything is dependant on that user?   [Hi everyone!]( - I'm Johnson, and I've just joined these forums. I was looking on Google for SQL Server Forums and found forums.sqlservercentral.com. I just wanted to say hello to everyone to start. Once I feel like I belong, I will talk more on the forum. @SQL Server Forums Munchkin Cats [Monitor SQL Server Job Failures]( - Ahoi, i am usually working with SSIS and SSAS, but since there is no one else i am also "in charge" if adminsitration it seems. We have multiple SQL Servers + Instances. Now i am looking for a good/central way to monitor Job failures across the entire scope of servers/instances. Since the guy who was […] [Notify with backup file name]( - Hello, I have the tlog bkp job that runs in every 5 mins. There is another monitoring job which checks if the tlog backup job runs for more than 30 mins and if yes, it should notify the same to dba via agent alerts (notifying via email). Unable to find a method to notify the […] SQL Server 2019 - Development [DateDiff - Data Type conversion Issues]( - Hi Experts Not able to do DATEDIFF between two dates, but it works when these dates are part of ( a.ClosingDate, calc.maxDate ) CTE and in select I use DATEDIFF , My result is taking forever so I was trying to get rid of CTE so I encounter this issue. Any help please. I attempted […] Integration Services [Data Collection from restful web call]( - First off. Sorry for formatting - on a mobile device. —————— So a little back sorry - I know almost nothing about this topic, so forgive me if I use a phrase out of turn. I’m looking at being able to pull data from a training platform and dumping it into my local database so […] Anything that is NOT about SQL! [Building a front end for a database]( - Hello everyone! I'm a beginner with databases and I'm getting started with building a front end for my database so that end users can add/edit data. I've been trying to find information about this online but I guess I'm not searching the right words because I can't seem to get a hit. I'd really appreciate […]   [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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -