Stairway to SQLCLR Level 8: Using Visual Studio to Work around SSDT (2022-06-01)

SQLServerCentral Newsletter for June 1, 2022 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [Stairway to SQLCLR Level 8: Using Visual Studio to Work around SSDT]( - [Managing Database Code Quality in a Flyway Development]( - [Combinations, permutations, and derangements]( - [From the SQL Server Central Blogs - Performance of Azure Shared Disk with Zone Redundant Storage (ZRS)]( - [From the SQL Server Central Blogs - How you can save up to 80% on Azure Data Factory pricing]( Question of the Day - [Dirty Pages]( The Voice of the DBA  Daily Coping Tip Remember an event in your life that was really meaningful 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. Production Subsets This editorial was originally published on Aug 22, 2014. It is being republished as Steve is traveling. Continuous delivery recommends developers never use production data. It's too big, too cumbersome, and slows the process too much. Developers should have enough data to determine if their solutions work as they build them. Testing should have enough to do some tuning, but unless you plan on full performance/load tests (which you should), then you don't need the full set of production data. It's an interesting idea, and overall I agree. A subset of data, hundreds of rows, can usually tell you if you're writing code that works if you profile the code and look for inefficiencies. Note that profiling code doesn't mean use Profiler. It means examining the resource used by your code in terms of CPU, I/O, memory, etc. There are tools to help you, and at some point in your development process, you should be using them. However it can be time consuming and cumbersome to build small development data sets. There are lots of choices in how you might do this, and I thought this would make an interesting poll. For those of you that deal with development, whether that's T-SQL, .NET, or something else, what do you think? Should we have a subset of production data, a custom data set, or perhaps deal with complete production data? Some of this depends on the size of your production data, and I hope, it's contents. I would not want any PII, PCI, medical, etc. in any development area. However if that's not the case, then what do you prefer? Whether you have  custom data set or a subset of production, it can be cumbersome to keep this up to date. Your data may evolve over time and there's overhead in maintaining some scripts that would produce the data you need. Perhaps that's the cost of writing good software, but I'm curious how many of you feel. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [Stairway to SQLCLR]( [Stairway to SQLCLR Level 8: Using Visual Studio to Work around SSDT]( Solomon Rutzky from SQLServerCentral.com In the final installment of this three-Level series, we use another automation-related feature to do what SSDT doesn't handle natively. [External Article]( [Managing Database Code Quality in a Flyway Development]( Additional Articles from Redgate In this article, I'll discuss the most important quality metrics for a database development, and then practical ways to ensure that a Flyway-managed database is designed and implemented to a high enough standard that it is reliable to use. [External Article]( [Combinations, permutations, and derangements]( Additional Articles from SimpleTalk Joe Celko explains how several mathematical concepts, combinations, permutations, and derangements, relate to databases. [Blog Post]( From the SQL Server Central Blogs - [Performance of Azure Shared Disk with Zone Redundant Storage (ZRS)]( david.bermingham from Clustering for Mere Mortals On September 9th, 2021, Microsoft announced the general availability of Zone-Redundant Storage (ZRS) for Azure Disk Storage, including Azure Shared Disk. What makes this interesting is that you can... [Blog Post]( From the SQL Server Central Blogs - [How you can save up to 80% on Azure Data Factory pricing]( Koen Verbeeck from Koen Verbeeck … with this one weird little trick Sorry for the clickbait, but you can actually save a lot on ADF pricing if you design your pipelines carefully. And...   Question of the Day Today's question (by Steve Jones - SSC Editor):  Dirty Pages What is a dirty page? 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) 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; Answer: Finds the date of Memorial Day Explanation: This code finds the date of the last Monday of May in the current year. This is Memorial Day in the US. Happy Memorial Day! [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 [AG backup preference behavior]( - Hi All, We have 3-node AG setup in our Production environment. Primary site (2 nodes), (DR site 1node). SQL Version is :Microsoft SQL Server 2017 (RTM-CU23) Enterprise Edition:(64-bit) We have 3 user databases as part of AG. We had some performance issues and decided to offload the backup load to secondary node of AG. For […] [Named instance connectivity]( - Hi We have a new named instance, I can able to connect using SSMS. However when I ping the instance name, it’s not working. It is enabled for mixed mode & enabled for the remote connections. TCP/IP port is enabled.  I added the 1434 udp port in the firewall. Also changed the dynamic port to […] SQL Server 2017 - Development [How to validate user if input formate no yyyy-MM-dd for created date ?]( - I work on sql server 2017 i need to validate user input of temp table if formate of created date not YYYY-MM-DD then reject by update status field of temp table to be Notvalid and if formate of created date is YYYY-MM-DD then update status by Valid CREATE TABLE #TempPC ( [ID] INT IDENTITY , […] [Adding leading zeroes after the decimal]( - Hello, I have the following requirement to add leading zeros after the decimal from numeric string, please advice the same. Input -> OutPut 100 -> 100.0000 1.1 -> 1.0001 1.10 -> 1.0010 1.100 -> 1.0100 200.00 -> 200.0000 SQL Server 2016 - Administration [SSRS Encryption Keys]( - We make a once per month backup of the ReportServer, ReportServerTempDB and the encryption keys from each location and store those backups at another site. My question is "Does the key change or can we use the same key that been there for a while. Please bear in mind that we periodically change the service […] Administration - SQL Server 2014 [StartDate & End Date parameters]( - Hi, I've created a view to populate data from existing tables In my report project I built using SSDT 2014, I created Dataset to read form this view. I also set a Startdate and Endate parameter with default calander values. The expectation is for the dates to filter the dates by above parameters. The Report […] Development - SQL Server 2014 [Pivot - Unpivot]( - Hi everybody, i have a table in SQl Server 2014 like the following: And i woul like to abtain an output like this: any help is appreciated. Thank you [Pivot Database Names]( - Hello, I have been trying and failing for days to create a query, or SSRS report, that makes a column for each database on the server, with each row representing a logon. The values of the database named columns would be the database role that logon has in the database. I have been trying with […] SQL 2012 - General [SQL Server 2012 Standard Media/ISO]( - Does anyone have a copy of SQL Server 2012 Standard handy? I just need the ISO or DVD contents to install on a new server. I have the key already from the old server, but we don't have the media any longer. I checked online and can't seem to find a good place to download […] SQL Server 2019 - Development [how to select lowest value of column and corresponding date column]( - I am trying to get the lowest cost for a part and the highest cost for a part. along with the dates that they occurred. I almost have what I want except that in my query I don't know how to associate the lowest cost with the correct date. I understand why (because that's what […] [Using RTRIM, LTRIM with a LEFT Function.]( - Is there a particular order for using the LTRIM, RTRIM with a LEFT function? Example - I only want the Left 35 from a field and want to trim any unnecessary off it. Is this the correct Syntax - LEFT(RTRIM(LTRIM(Guar.First)),35) or should I use the TRIM's then the LEFT? [Newbie Question:If else Sql Question]( - In this case i am expecting the else statement to execute. please correct dECLARE @BusinessEntityId INT SET @BusinessEntityId=1003 IF @BusinessEntityId<=(SELECT MAX(BusinessEntityId) AS BusinessEntityId FROM Person.Person) BEGIN SELECT * FROM Person.Person WHERE BusinessEntityID<=@BusinessEntityId END ELSE IF @BusinessEntityId>=1000 AND @BusinessEntityId<2000 SELECT * FROM Person.Person WHERE BusinessEntityID>=@BusinessEntityId AND BusinessEntityID<=@BusinessEntityId SQL Azure - Development [Troubleshooting a pipeline error]( - I am creating a pipeline in a data factory per this page's instructions, . I am a beginner with this and am trying to follow the instruction as closely as possible. The change, in my case, is that I am using a VM SQL installation, also in Azure, but not a managed instance. I have […] General [Is SQL Express enough?]( - I am assisting a company in upgrading their server. It is a small business with 9 computers. The mostly have a few shared documents and spreadsheet on the server, and the server hosts and MS SQL database for their accounting software. The accounting software has a maximum of 6 users at a time. The database […] Integration Services [how to pass previous week dates in stored proc parameters.]( - i am planning to create package with an execute sql task where it calls procedure with parameters (@fromdate, @todate) @FromDate DATETIME @ToDate DATETIME For example if i scheudle job to run this package every monday example (May9). the execute task should run for previous week dates May 2 to May8(Monday to Sunday) EXEC sp_getdetails '05-02-2022', […]   [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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -