Using Data Science And Machine Learning In Identifying Fraudulent Activities (2021-04-14)

 SQLServerCentral Newsletter for April 14, 2021 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [Using Data Science And Machine Learning In Identifying Fraudulent Activities]( - [Stairway to Advanced T-SQL Level 4: Record Level Processing Using Transact-SQL Cursors]( - [Oracle sequences: The basics]( - [Digital Event: Innovate Today with Azure SQL]( - [From the SQL Server Central Blogs - Modern Data Warehouse & Reverse ETL]( - [From the SQL Server Central Blogs - Running Scripts in Parallel with SQL Multi Script]( Question of the Day - [Sorting in R]( The Voice of the DBA  Daily Coping Tip Turn a regular activity into a playful game today 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. Bug Management All of our software has bugs. Some bugs are just bad code, some are incorrect specs, and some are things the client thinks should work differently than the way the application is built. In any case, we know there are plenty of bugs in software. How companies handle bugs is interesting. Some feed these back to the current developer team. Some use junior developers to do bug work. Some have support teams that focus on bugs and coordinate with developers. What if you get parts of your software from another source? More and more developers are using open source libraries, packages, frameworks, and more. How do you deal with bugs in those situations? As someone that has worked in this manner, tackling those bugs yourself is problematic and introduces other issues with your software development pipeline when the upstream source is updated. Google is taking a different approach, actually assigning developers to projects that they need. One example is in [this piece](, about a couple developers assigned to the security of Linux. While the headline is that they keep finding lots of bugs, the more interesting part to me is that Google internally builds the OSS they use from source, add in their patches, and then use pull requests to get them accepted into the main project. I don't know that many of us can afford to sponsor a developer to contribute to a project full time, but I do like the idea of using a fork to build your software from source. This might not prevent bugs or malware, but if you are hit in this manner, you know that lots of others will be as well. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [Using Data Science And Machine Learning In Identifying Fraudulent Activities]( siddharthray123 from SQLServerCentral A look at one way to detect fraud using R and a machine learning model. [Stairway to Advanced T-SQL]( [Stairway to Advanced T-SQL Level 4: Record Level Processing Using Transact-SQL Cursors]( Greg Larsen from SQLServerCentral.com Using a CURSOR is not normally the best way to process through a set of records. Yet when a seasoned programmer moves to writing TSQL for the first time they frequently look for ways to process a sets of records one row at a time. They do this because they are not used to thinking about processing records as a set. In order to process through a TSQL record set a row at a time you can use a cursor. A cursor is a record set that is defined with the DECLARE CURSOR statement. Cursors can be defined as either read-only or updatable. In this article I will introduce you to using cursors to do record level processing one row at a time. [Oracle sequences: The basics]( Additional Articles from SimpleTalk Oracle sequences can be used to create artificial IDs for a table. In this article, Jonathan Lewis explains how they work under-the-hood. [Digital Event: Innovate Today with Azure SQL]( Additional Articles from Redgate Join Microsoft's Rohan Kumar, and a full team of Azure SQL experts, for Innovate Today with Azure SQL. This free digital event from Microsoft lets you choose the sessions and technical demos that are most interesting to you and focus on the capabilities and possibilities that you want to learn more about. From the SQL Server Central Blogs - [Modern Data Warehouse & Reverse ETL]( James Serra from James Serra's Blog An extension to the Modern Data Warehouse (MDW) that I have heard a bit about lately is called “Reverse ETL”. Before I describe what that is, first I wanted... From the SQL Server Central Blogs - [Running Scripts in Parallel with SQL Multi Script]( Steve Jones - SSC Editor from The Voice of the DBA One of the lesser known tools in the Redgate Toolbelt is SQL Multi Script. While few customers used it, those that did loved it, as it solves a problem...   Question of the Day Today's question (by Steve Jones - SSC Editor):  Sorting in R There is a sort() method in R that you can use on various objects. What type of sort should I use with a vector of large integer values for the best performance? 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) Preventing Trigger Execution In SQL Server 2019, I need to alter some data in a table. However, there is a trigger that I do not want to fire for this data modification but I need it to fire for future changes. What is the easiest way to accomplish this? Answer: Use DISABLE TRIGGER Explanation: The easiest way to do this is to run DISABLE TRIGGER, make the change, and then ENABLE TRIGGER, all in the same batch. Ref: DISABLE TRIGGER - [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 [SQL Query issues - different conditions in the SELECT list]( - If this is the wrong forum, please let me know.  In SQL Server 2017, I have several tables that I need to join together to create one query that looks like this: Country EmailCount RecordCount PercentRecords USA 50000 5000 10.00 Brazil 40000 7500 18.75 Germany 30000 25000 83.33 Tables and applicable columns MailboxSummary Contains […] SQL Server 2017 - Development [Splitting a group up into multiple groups using a score....]( - Splitting a group up into multiple groups using a score for how many times an item should appear in a sub group This is related to betting, and whilst I do alright by myself I want to remove human bias from my main income which is making money from football accumulators. I have a table […] SQL Server 2016 - Administration [T logs increasing]( - Hi In one of our servers the t-logs of one of the database which is in full recovery mode and with hourly log backups has increased im not able to find the exact error/cause for this from the event viewer or logs can anyone let me know where else do i have to check Thanks […] [Kick starting Extended Events]( - We have no prior experience of using XE. Yes, our DBA group uses server side traces, sp_whoisactive collections, and more. Yet none of us yet have dived deep enough into XE to make momentous decisions. What would you advice we start with? what Sessions/templates/ should be the first one we launch for starters? and should […] [Questions about Always On]( - Dear all, I have the following questions about SQL Server Always On: How does SQL Server copy data changes from the primary node to the secondary nodes? Through log shipping (backup & restore transaction log)? Or bitwise copy (copy all the changes in bit level)? It seemed Microsoft hasn't tell us. Many people ask me […] [What is the best way to see Deadlocks info and take advantage of analyzing it?]( - There was a 5 minute periods of time the other day when the lasting blocking (chains) caused by 1 spid culprit were really numerous, And then suddenly on minute 6 everything went through as if it was a clogged sewerage pile, and the CPU usage dropped and all, within seconds. And I am wondering how […] SQL Server 2016 - Development and T-SQL [How to map out multi-level Supervisor/Employee relationship]( - No doubt this has already been answered and probably detailed many ways so I just need to know the right key words to find the post or a link if anyone has it. I'm trying to write a query that will show all levels of Supervisor to Employee relations ships where the relationships can go […] [RunningTotal column not working]( - Hello, I need a little help with my query because they don´t work properly when i try to do a runningTotal column.  This is my query: DECLARE @ContaI VARCHAR(15) DECLARE @ContaF VARCHAR(15) DECLARE @DataI DATETIME DECLARE @dataF DATETIME SET @ContaI = '6221' SET @ContaF = '6229999999' SET @DataI = '20210401' SET @DataF = '20210531' […] SQL 2012 - General [A really silly question]( - Hello. Sometimes when working on SQL server, i accidentally press something on my keyboard, after which if i put my mouse cursor in a middle of a string, press any letter or number, and that letter or number will replace a letter that was already in that string, instead of inserting it, and "pushing" the […] [incorrect syntax near as when make pivot so How to solve it ?]( - i work on sql server 2012 when implement pivot i get error incorrect synatx near as but i don't know what is issue this is my code : DECLARE @result NVARCHAR(MAX) DECLARE @col NVARCHAR(MAX) DECLARE @sqldata NVARCHAR(MAX) ----------------------- set @result = ( SELECT SUBSTRING((Select ',['+FeatureName +']' AS [text()] FROM extractreports.dbo.allfeatures with(nolock) GROUP BY FeatureName,displayorder ORDER […] [How to solve issue of Invalid length parameter passed to the LEFT or SUBSTRING f]( - I work on sql server 2014 I face issue error as Invalid length parameter passed to the LEFT or SUBSTRING function when run script below ? Microsoft SQL Server 2014 - 12.0.2269.0 (X64) Enterprise Edition (64-bit) on Windows NT 6.3 I create table student with rows values insert as below : CREATE TABLE [dbo].[Student]( [Sno] […] SQL Server 2019 - Administration [how to really patch a 24/7 always on]( - hi guys, i have a 2 node AlwaysOn. i need some confirmation on the step on how to keep the service still up while u go about patching it. all backup are up to dates. switch from automatic failover to manual verify sync is ok patch secondary replica reboot secondary replica switch AOGP to secondary […] [HKCKP files \ sys.dm_db_xtp_checkpoint_files]( - Hi all, does anybody know how can I get rid of the old files in the folder: \$HKv2 under my data folder? I understand that they are InMem checkpoint files which I can see in this DM view: sys.dm_db_xtp_checkpoint_files under: relative_file_path for example : $HKv2\{85D9A12C-F870-4DCA-842A-FF2390683680}.hkckp I see files from the last 24 hours which consume […] SQL Server 2019 - Development [How to troubleshoot PK Constraint violation]( - I'm hoping someone has seen this before because I've been doing this for years and never had this problem. When trying to add new records to synchronize a table I keep getting a violation of the primary key constraint. The primary key is on ContactID and using the query below I cannot understand how there […] Integration Services [Whether to leave a clustered index on a staging table]( - Hi Using dataflows to load data from one SQL table to another (seperate databases; not an exact copy) Historically, best practice suggests loading data to a table that has no (nonclustered) indexes on it then adding the indexes post insert So, disable and enable When following the truncate destination and load method, what's the best […]   [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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -