Using pre-commit to prevent simple mistakes (2023-01-13)

SQLServerCentral Newsletter for January 13, 2023 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [Using pre-commit to prevent simple mistakes]( - [Database Sharding to help Improve Performance and Maintenance Tasks]( - [How to Create a Box and Whisker Plot in Power BI Desktop]( - [From the SQL Server Central Blogs - Installing Flyway Community on Windows]( - [From the SQL Server Central Blogs - How Secure is TDE?]( Question of the Day - [The ANSI PADDING Table]( The Voice of the DBA  Daily Coping Tip Take five minutes to sit still and breathe 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. The Programming Languages We Use Many of you reading this probably work primarily in SQL. Even if you are a developer whose main language is something else, you write a lot of SQL. Even if you have an ORM writing the SQL that goes into production, I bet a lot of you are writing queries against a database to check that the data coming back in your application is correct. As for me, I mostly work in SQL, with PowerShell and Python being second and third. I tried R for a while, but I think Python does everything R can do and it's much cleaner. I find R very cumbersome. I rarely write C# or experiment with anything else, but that's the nature of my job. PowerShell is important, as I do a bunch of DevOps and PoSh is a good choice to work with on the command line for gluing processes together. There was a set of [the top articles on programming languages from 2022](that I saw recently. I found it interesting to see what was popular. The top one was about Python being the most popular, but it shouldn't be. This one feels like clickbait, and I find many of the conclusions not making an argument against python in a meaningful way. There are some other links on the "hotness" of various languages. I think these are clicked on as many developers are just curious about what others are doing, and what they might experiment with. While I like curiosity and experimentation, I do think that many of our important systems in organizations need to be built with mainstream technologies. Support and staffing are a challenge, and while Golang might be great, finding people to read and code in it is hard. I don't know how to balance the growth of new tech with the safety of old tech, but I wouldn't stray too far from the mainstream for anything important. I do find it interesting that COBOL makes the list. I know there are still lots of COBOL systems, and while there aren't a ton of jobs, there are jobs and little competition. If I were 10-15 years younger, this would be tempting. Of course, I'd have to be willing to adapt to the jobs, but it is tempting. I know a few people making well into the six figures because of COBOL jobs. It's nice to see SQL is one of the top 10 languages in use, according to this survey.. It was #9 in 2021 and #8 in 2022. I don't know it grew in popularity so much as assembly declined compared to other skills. I certainly can't see SQL going away, but it's not as popular, clickbait-y, or exciting as other languages. Instead, it's a core, required skill for any serious software development. Whether you use relational or NoSQL databases, likely you need some SQL skills. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [SQLServerCentral Article]( [Using pre-commit to prevent simple mistakes]( David.Poole from SQLServerCentral Git hooks are a useful way of triggering code quality scripts. The pre-commit tool provides us with a way of doing this and tapping into the huge library of scripts already in existence to ensure our code complies with general linting and formatting good practice. [External Article]( [Database Sharding to help Improve Performance and Maintenance Tasks]( Additional Articles from MSSQLTips.com In this article, we will review the benefits and drawbacks of using database partitioning (sharding) to help improve performance and maintenance tasks. [External Article]( [How to Create a Box and Whisker Plot in Power BI Desktop]( Additional Articles from MSSQLTips.com In this article, we look at how to build a box and whisker plot chart using Power BI Desktop with a step-by-step walkthrough. [Blog Post]( From the SQL Server Central Blogs - [Installing Flyway Community on Windows]( Steve Jones - SSC Editor from The Voice of the DBA One of the things I’ve been trying to do is dig in more deeply to the Flyway command line (CLI) as part of my work with Redgate. While Flyway... [Blog Post]( From the SQL Server Central Blogs - [How Secure is TDE?]( Matthew McGiffen from Matthew McGiffen DBA When we consider how secure a form of encryption is there are two things we want to consider. Let’s discuss each of these in turn. What are We Protected...   Question of the Day Today's question (by Steve Jones - SSC Editor):  The ANSI PADDING Table I run this code to create a table and insert rows. What are the results that I see from the query? SET ANSI_PADDING ON GO CREATE TABLE dbo.ANSIPADDINGON(charcol CHAR(20), varcharcol VARCHAR(20)) go INSERT dbo.ANSIPADDINGON (charcol, varcharcol) VALUES ('c test','v test ') GO SELECT '[' + a.charcol + ']', '[' + a.varcharcol + ']' FROM dbo.ANSIPADDINGON AS a GO 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) Replacing text What datatype should I use to replace the text datatype in a database upgraded from SQL Server 2008 on SQL Server 2022? Answer: VARCHAR(MAX) Explanation: The text datatype can store up to 2GB, which makes it larger than a varchar(8000). A varchar(max) would be the type to replace this. Ref: ntext, text, and image - [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 [Always on AG]( - I have availability group configured with multiple secondary replica's. Looking to additional secondary replica to Azure. Is that possible? SQL Server 2016 - Administration [Can log backups cause Operating system error 32?]( - Hello experts, I am seeing sporadic (every other day or every few days) errors like this one for a backup job: Operating system error 32(The process cannot access the file because it is being used by another process.). I have read that the usual suspect is antivirus or similar scanning. But the timing of the […] SQL Server 2016 - Development and T-SQL [Invoke a Tableau dataset refresh from a SQL Agent Job]( - Hello, we have a nightly SQL Agent Job that refreshes our database on SQL Server 2016 Server. We also have a tablue timer that begins refreshing a Tableau dataset but when the Tableau starts its dataset refresh before our SQL Agent job completes it slows down our nightly SQL Agent job. Is there a way […] [Return 80 % match]( - I have data with a column (VenSN) that stores Y, N, Null, (N with serial number ie 'N 23423545'). I want to return all the records for each vendor that has 80% or more of the total records for that vendor containing 'Y' in VenSN column. How do I do this in a query?  […] Development - SQL Server 2014 [Starting a new FileTable]( - I have a database with two versions - a production version and a development version. As I fix and upgrade, I regularly copy data from the production version into the development version, so that I have current data to experiment on, and when it's time for a new rollout, I then delete the production verion […] SQL Server 2019 - Administration [Always on SQL Managed Instance]( -  is Always on support for the SQL managed instance. Distributed Queries are supported for SQL managed instances. SQL Server 2019 - Development [ISNULL in case statement]( - Hi, I have this SQL fragment SELECT COL1, CASE WHEN Table1.COL2 = 0 then 'ZERO' WHEN Table1.COL2 = 1 then 'ONE' ELSE '' END AS MyResult FROM Table1 Which is the best way to treat Table1.COL2 NULL values as if they were 1 ? Is this the best way ? WHEN ISNULL(Table.COL2,1) then 'ONE' Thank […] [EncryptByKey KeyGUID string truncate]( - Hi Folks EncryptByKey (Key_GUID('Name_SymKey'), @Dex) @Dex has 9000 char length error: Msg 8152, Level 16, State 10, Line 360 String or binary data would be truncated. The statement has been terminated. it happens when we are inserting encrypted value to a varbinary(max) column in a table any idea ?  appreciated SQL Azure - Development [Azure Pricing, SQL Server seems pretty high?]( - Hi, I'm really new to this and I think I've got this wrong, wondering if you can help me.  I've got an SQL database I'm wanting to put in Azure. It's 32gb and would be accessed by maybe thirty staff avg 20 times a day during normal working hours by a locally installed program. […] Reporting Services [How to use SQL on 2015 MacBook?]( - Hello I am trying to start learning SQL, but have run into some problems. When I went to download mySQL on their website it said it couldn’t on my computer, I’m assuming because it’s a 2015 MacBook Air running on 10.12.6. Should I install and older version or do something else? I downloaded dbeaver and […] PostgreSQL [Upgrade from RDS Postgres 11 to 14]( - Hi, Would like to know if there are any code changes needed to upgrade RDS postgres 11 to 14. SQL Server 2022 - Administration [Ola Hallengren Script for SQL Server 2022]( - Hello, is it necessary to install the latest version of Ola Hallengren maintenance solution to use this on the sql server 2022? Or can is use a version from 2020? I think we got some work to do if we had to use the newest version. I can't find any release notes from the latest […] [Always on SQL Managed Instance]( - Hi,  i need to do POC on the SQL managed instance in my office Is Always on support for the SQL managed instance. Distributed Queries are supported for SQL managed instances.  Please help me asap on this. SQL Server 2022 - Development [Error using python in SQL2022: DLL load failed on import: pylink and px_call]( - I'm getting an error trying to execute python scripts inside SQL Server 2022, and would appreciate any suggestions. After following this guide (for Python only): I get the error message below any time I try to execute a python script in Sql2022, for example: EXEC sp_execute_external_script @script=N'import sys;print(sys.version)',@language=N'Python';  The error message is: Msg […] [SQL Converting Columns to Rows based on specific column value]( - Hallo, I have a problem to fill a new column from a previous column where the values are calculate. Current output: Required output: As you can see when there is a accordance beetween previous year/month in column1 (weample 0, 1 etc.) it shold dispay in the row in column2. When we have in current year […]   [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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -