Rules in PostgreSQL (2020-12-03)

 SQLServerCentral Newsletter for December 3, 2020 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [Rules in PostgreSQL]( - [Why you should include the database in your 2021 IT strategy]( - [10 DevOps strategies for working with legacy databases]( - [From the SQL Server Central Blogs - Why Aren’t You Automating Database Deployments?]( - [From the SQL Server Central Blogs - Speaking at DPS 2020]( Question of the Day - [Ordering with NULL]( The Voice of the DBA  Daily Coping Tip Join a friend doing their hobby and find out why they love it 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. Catching Minor Issues This seems like something out of a thriller that Hollywood made for a spy. someone switches two cables in a rocket and then it crashes. However, it's what happened recently, not from a spy, but an [employee somewhere connected two cables to the wrong connectors](, resulting in reverse commands being sent to the rocket. That sounds crazy. Who would plug cables in backwards, and how is this not caught? Surely we wouldn't run into this in software would we? With all the unit testing? Well, we did once [have an English->metric error](. Actually, I've seen this in software. I've seen someone pass parameters in backwards to a method or stored procedure. I think I've done this before as well. While I hate typing out named parameters, I do appreciate [SQL Prompt]( for ensuring I connect the right value to the right parameter by spelling them out. Do you type out parameter names, as in EXEC myProc @Param1 = @value1, @param2 = @value2? It's a good habit to get into to prevent simple mistakes. In fact, this is where some simple, quick tests can catch errors. Not only will this catch some weird changes, this can ensure that your procedures are backwards compatible. That's often a concern in refactoring, where someone might not think a parameter is being used, or wants to replace it and then breaks other code. Testing is something that has improved in software dramatically over the years, though not as much in databases as I'd like. I urge you to use more procedures, but also add more tests and ensure that you don't make simple errors that should be caught. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [Rules in PostgreSQL]( sabyda from SQLServerCentral We sometimes need to perform a different/alternative actions during database queries. We need it for data protection or data abstraction. The PostgreSQL rule system allows to define an alternative action on insert, update or delete. A rule generates an extra query. As a result, rule execution impacts the performance of the system. Creating Rules A […] [Why you should include the database in your 2021 IT strategy]( Additional Articles from Redgate As 2020 draws to a close, managers in many organizations are considering what the next 12 months IT strategy should look like, and how this underpins business objectives. Given the current business climate where the pandemic has prompted many companies to change the way they work, it’s even more important right now – and it’s an opportunity to get ahead. [10 DevOps strategies for working with legacy databases]( Additional Articles from SimpleTalk The database is often left behind as organisations embrace DevOps. In this article, Robert Sheldon explains how to successfully bring databases into DevOps, especially when dealing with legacy databases. From the SQL Server Central Blogs - [Why Aren’t You Automating Database Deployments?]( Grant Fritchey from The Scary DBA Building out processes and mechanisms for automated code deployments and testing can be quite a lot of work and isn’t easy. Now, try the same thing with data, and... From the SQL Server Central Blogs - [Speaking at DPS 2020]( Steve Jones - SSC Editor from The Voice of the DBA I was lucky enough to attend the Data Platform Summit a few years ago. One of my favorite speaking photos was from the event.Me on a massive stage, massive...   Question of the Day Today's question (by Steve Jones - SSC Editor):  Ordering with NULL I have a bit of data: CREATE TABLE BeerCount ( BeerName VARCHAR(20) , BottleCount INT) GO INSERT dbo.BeerCount (BeerName, BottleCount) VALUES ('Becks', 5), ('Fat Tire', 1), ('Mac n Jacks', 2), ('Alaskan Amber', 4), (null, 7), ('Corona', 2), ('Tsing Tao', 4) GO If I run this query, where is the NULL value in the result set? SELECT beername, bottlecount FROM dbo.BeerCount AS bc ORDER BY bc.BeerName 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) WHILE in R Is this a valid WHILE loop in the R language? i <- 2 while (i<= 25) { print(paste0("Christmas Day ", i)) i <- i + 1 } Answer: yes Explanation: This is a valid WHILE loop. Ref: WHILE - [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 [alias SQL multisubnet failover = true]( - Good morning all , I have an always on instance to configure with a listener in mutlisubnet mode on the application side I am unable to add multisubnetfailover = true is it possible to create an sql alias with this parameter DESKTOP-95KKMMH\DISS; multisubnet failover = true thanks [HA Availability Group Listener]( - I was looking for a specific definition for the Active Directory / DNS team as to what I need set up for an HA Group Listener; does it just need to be a DNS name, IP address, and Directory Object in AD? If so, what is the object called, a cluster object? I have the […] [RegisterAllProvidersIP = 0 and timeout in failover]( - Good morning all , I have an alwayson instance with a multisubnet listener I configured RegisterAllProvidersIP = 0 in order to register a single DNS entry HostRecordTTL is configured at 1200 seconds I would like if with this configuration I will have timoeout during the failover if so how much time is needed for the […] SQL Server 2016 - Administration [SQL server night cpu usage]( - Hi using MS SQL maintenance plan to update statistic with full scan. The plan running over 2 days but still running. Try to restart DB server but the job auto resume. How can I kill all statistic update or estimate the complete time ? [Error connecting to sql using listener name only]( -  Hello I've a problem when i try to connect to an instance with Always On When i use a domain account, i can connect using listener_name\instance name or only listener_name When i use a sql account, i can only connect using listener_name\instance_name but not using only listener_name (error 18456) Is there a solution to […] [SQL permissions based on multiple AD Groups]( - Hi I'd like some input on the best way to handle setting permissions in SQL Server 2016 using 2 Active Directory Groups. I have 2 AD Groups - SQLDevs and SecurityCleared. I have created database roles for DEVS, CLEARED and DEV_CLEARED. I would like to apply permissions based on these Groups as follows... PublicSchema - […] Administration - SQL Server 2014 [sp_refreshview on indexed views]( - Hi I have to increase the size of a field in a table. I've listed all the views that use this table so I can run sp_refreshview on these. I'm left with a couple of schema bound views that use the underlying table, but not the changed field otherwise it wouldn't have allowed me to […] SQL 2012 - General [How to use While Loop to Insert Book details where status is not null?]( - I work on SQL SERVER 2012 . I face issue I can't use While loop to insert Books details that status is not NULL to table #BookHaveGeneralStatus . Meaning if any book have only one status not null on Books Details then insert it on table #BookHaveGeneralStatus using While loop . but if all Status […] [Get Particular Date from a Month]( - I would like to get a Thursday in a month which is followed with 2nd Tuesday. If the output is greater than today, Then I need to show the last month Thursday which is followed with 2nd Tuesday. For Instance, CASE 1 - If Today is 30 Nov 2020 - 2nd Tue is 10 Nov […] [Tlog migration]( - Hi Expert, In tlog migration, from 1 server to another where there is no connectivity I am doing following steps1 creating database replica on new server Running job's, user cript on new server In cutoverCoping the . Bak and log files from one server to another Creating ag group and assigning server to it Any […] [Tempdb increasing crazily]( - These days, the Tempdb of my one's SQL server increases crazily, sometimes only abount 5 mintues the MDF file increases 70GB, but lof file of tempdb is not big(Maybe about 2-3GB), I have some questions regarding Tempdb, thanks! as shown in the picture, there are many temp table in the tempdb, normally, when the temp […] SQL Server 2019 - Development [Find matched records between two tables]( - Hi, I haven't been able to find anywhere how to do or which transformation to use. I've seen Lookup but it only has one input, merge join but I could only get it to append... I'm using SSIS 2019 Community in VS with Windows 10. I've brought in two tables using flat file source nodes […] SQL Server 2008 - General [sys.dm_os_performance_counters Resource Pool Stats returns zero]( - I am using SqlServer 2008 R2 Express edition. When i execute below query i will get all 0 . select * from sys.dm_os_performance_counters where object_name = 'SQLServer:Resource Pool Stats' By referring this Link - In express edition I can't use this query to get resource statistics. But is there any other method to get the […] SSRS 2016 [Enabling date Input parameter to allow mmdd input but display as dd/mm/yyyy]( - Hi there I have an SSRS Report with various input parameters Now one of these is a date field which we want to format to mmdd format but as the user tabs out of it, it will then snap back into dd/mm/yyyy format. For example , 1130 would be entered by the user but then […] General [Costing per database]( - Hi, I am just checking to see if anyone has ever did a costing per database (based on it size). I am trying to figure out what the cost is for a database throughout its lifecycle. I am looking for mow much disk space will each DB use, how much memory does it require, how […]   [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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -