SQL Window Functions Series: LAG() and LEAD() (2023-12-11)

SQLServerCentral Newsletter for December 11, 2023 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [SQL Window Functions Series: LAG() and LEAD()]( - [What is subsetting, what are the advantages, and how does it make test data management easier?]( - [Identify log write limits on Azure SQL Managed Instance using QPI library]( - [From the SQL Server Central Blogs - Job Description of a DBA]( - [From the SQL Server Central Blogs - Exploring the Query Store on Secondary Replicas in SQL Server 2022]( - [SQL Server 2022 Administration Inside Out]( Question of the Day - [Attaching Databases]( The Voice of the DBA  Breaking Biometrics In the movies, when someone needs to bypass some sort of fingerprint biometric system, they use tape to lift a fingerprint, or in the more gory films, cut off a body part. In any case, there have been a lot of attempts to replicate this in the real world. Sometimes [these work]( though the consensus is modern hardware isn't as susceptible as older hardware. I don't know if that's true, but I did see an interesting look at the main three sensors used in most laptops. All the laptops had their authentication bypassed, though not with any fake biometric device, but rather with attacking the actual communications between the sensor and the OS. There's [a great report]( from two people who were asked by Microsoft to evaluate the security of the top three sensors. It's a fascinating look at how the hardware and software of the OS are set up to provide secure authentication and how both can be fooled. Some of the problems are users not enabling features and some are that manufacturers aren't understanding or implementing the secure protocols from Microsoft. I both get how this happens and I feel it's not acceptable. Certainly someone might read a spec and not completely understand it, even across a team of people. However, for vendors, this can't be something they don't have researchers, pen testers, or some security professional examine. Hardware vendors spending money designing these devices ought to include the money for a few people to try and break the security before they are sold. At least for security features. I like the enhancements made with multi-factor authentication, and I appreciate biometrics as a convenient way to access things. At the same time, I want to be able to trust them. The efforts made by the researchers are pretty high, not something that someone in a coffee shop can do in 5 minutes while you go to the bathroom. At least, apart from the Surface. It appears that one can be quick. At the same time, if you lose your laptop, within hours someone can break past the biometrics with a man-in-the-middle attack. Even if you've encrypted the disk, your data can be accessed since this attack gets the OS to authenticate them. Security is tricky, but I don't plan on disabling my fingerprint scanner. However, if I were carrying around data that was more important than the work I do now or data worth a lot of money, I don't know I'd ever enable biometrics. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [SQLServerCentral Article]( [SQL Window Functions Series: LAG() and LEAD()]( Nikhil Bangad from SQLServerCentral Dive deep into the powerful SQL window functions, LAG() and LEAD(). Explore their intricacies, discover real-world examples, and avoid common pitfalls. [External Article]( [What is subsetting, what are the advantages, and how does it make test data management easier?]( Additional Articles from Redgate As data grows and databases become larger and more complicated, data subsetting provides a method of working with a smaller, lighter copy of a database to make development and testing faster and easier. In this article, James Hemson poses the questions; what exactly is data subsetting, how and why are developers using it – or not using it, and what’s prompting conversations about it? [External Article]( [Identify log write limits on Azure SQL Managed Instance using QPI library]( Additional Articles from Microsoft MSDN Azure SQL Managed Instance is fully managed SQL Server instance hosted in Azure cloud. Managed Instance introduces some limits such as max log write throughput that can slow down your workload. In this post you will see how to identify write log throughput issue on Managed Instance. [Blog Post]( From the SQL Server Central Blogs - [Job Description of a DBA]( Hemantgiri S. Goswami from SQL Server Citation - SQL Blog by Hemantgiri S. Goswami, SQL MVP I have curated the list of activities of a DBA or one can say Job Description of a DBA. DBA needs to adapt and keep learning new technologies The... [Blog Post]( From the SQL Server Central Blogs - [Exploring the Query Store on Secondary Replicas in SQL Server 2022]( Klaus Aschenbrenner from Klaus Aschenbrenner SQL Server 2022 introduces an exciting feature: the ability to access the Query Store from a secondary replica in an Availability Group. This enhancement offers a significant advantage, allowing... [SQL Server 2022 Administration Inside Out]( Site Owners from SQLServerCentral Dive into SQL Server 2022 administration and grow your Microsoft SQL Server data platform skillset. This well-organized reference packs in timesaving solutions, tips, and workarounds, all you need to plan, implement, deploy, provision, manage, and secure SQL Server 2022 in any environment: on-premises, cloud, or hybrid, including detailed, dedicated chapters on Azure SQL Database and Azure SQL Managed Instance.   Question of the Day Today's question (by Steve Jones - SSC Editor):  Attaching Databases With SQL Server 2022, what is the recommended code to attach a database? 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) Backing Up Master Can I run this in SQL Server 2022? ALTER DATABASE master SET RECOVERY Full If yes, can I then run this? BACKUP DATABASE master TO DISK = 'master_20231121_1045.bak' go CREATE LOGIN TestDev WITH PASSWORD = 'DemoUserP@ssw0rd' GO BACKUP LOG master TO DISK = 'master_20231121_1045.trn' GO Answer: Yes and No, I don't know Explanation: You can run the first code, and the master database will show a recovery model of Full. However, when you try to run the second line, you get this error: Msg 4212, Level 16, State 1, Line 11 Cannot back up the log of the master database. Use BACKUP DATABASE instead. Msg 3013, Level 16, State 1, Line 11 BACKUP LOG is terminating abnormally. Therefore, the correct answers are yes, you can change the recovery model, and then no, you cannot run a log backup. Ref: Backup and restore: system databases - [ [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 2016 - Development and T-SQL [STUFF Command Not Working]( - I am trying to use the STUFF command, however, the column using the STUFF just seems to just include everything, instead of the ones specific to the user. I am hoping someone can notice what I am doing incorrectly in my SQL Statement. Below is the code I am using. SELECT DISTINCT G.[Period], G.[Sector], G.RID, […] [WHY Follow a LEFT OUTER JOIN with another LEFT OUTER JOIN]( - NOTE: The Topic Title probably isn't very good but I wasn't sure how to title this with a limited amount of text. I thought I had previously asked about this but according to my history I haven't. I have run across some code that I've never dealt with before and I'm curious if you guys […] Development - SQL Server 2014 [Not enough disk space error]( - Hi,  I keep getting this error message saying that I don't have enough disk space whenever I run a query. based on suggestions online I tried freeing up some disk space by cleaning up temporary files. This however is a temporary fix  I noticed that my C drive quickly goes from, say, 9 […] SQL Server 2019 - Administration [Drop or delete a existing partition]( - Hello, so I was reading on partitioning a large table, Which i successfully did, using the clustered index and partition function/schema... example the table is a simple table with 4 columns CREATE TABLE Orders ( OrderID INT PRIMARY KEY, CustomerID INT, ItemID INT, OrderDate DATETIME );  CREATE CLUSTERED INDEX Order_Date_Added_Partition ON dbo.Orders (OrderID) WITH […] [Compare all tables row counts for 2 server instances?]( - USE [DBName] SELECT QUOTENAME(SCHEMA_NAME(sOBJ.schema_id)) + '.' + QUOTENAME(sOBJ.name) AS [TableName] , SUM(sPTN.Rows) AS [RowCount] FROM sys.objects AS sOBJ INNER JOIN sys.partitions AS sPTN ON sOBJ.object_id = sPTN.object_id WHERE sOBJ.type = 'U' AND sOBJ.is_ms_shipped = 0x0 AND index_id < 2 -- 0:Heap, 1:Clustered GROUP BY sOBJ.schema_id , sOBJ.name ORDER BY [TableName] I have the code above […] [Ola hallengren backup not deleting old backup files for SQL 2016 Clusters]( - Ola hallengren stopped deleting older files for SQL Server 2016 which are on cluster. Files can be deleted manually but not with SQL script which had been working. [Replication Monitoring with Always On Availability Group]( - Hey Folks, So I have SQL Transactional Replication set up on a database that's part of an AOAG. Everything is working fine except for one thing. After a failover, the Replication Monitor is not able to show any info since the primary is where the replication was configured and now the secondary is the new […] [SQL AOAG with listener ( without windows clustering)]( - Hello, I am deploying SQL 2019 Enterprise edition with AOAG ( without windows clustering). Steps done : 1. SQL installation done on 2 nodes 2. Service account is added as sysadmin on both servers 3. Enabled AOAG on both nodes 4. Database created & backup taken 5. Configuration of AG is done. Database added. Listener […] SQL Server 2019 - Development [converting date]( - cast(DATEADD(DAY, case when j1 is null then 0 else j1 end, j_date) as date) as 'DateEnd', I get results as 12/1/2023 12:00:00 AM when data gets imported to my txt file, what can be done to remove time, I need result: 12/1/2023 Thank you [Powershell, how to convert file to txt pipe]( - Hello, how can I change this code to txt pipe format? $DataSet.Tables[0] | Export-csv -Path "C:\Test.txt" -NoTypeInformation SQL Azure - Administration [SQL Replication - Disconnected from Azure Storage with OS result code: 0]( - Hello, We had a few errors with replication for SQL Managed Instance stopped working with "Disconnected from Azure Storage "\\StorageLocation" with OS result code: 0. Can restart it again with no issues but not sure why it has happened now a few times. Any suggestions on how to troubleshoot?  Thanks,  Daniel Reporting Services [Summing different fields on a group by]( - HI I have a data set with student info; Name, Type there would be multiple lines for each student Soemthing like StudentA, Type1 StudentA, Type2 StudentB, Type1 StudentC, Type2  I want a matrix where its grouped by student then have two columns, Type1, Type2 Output to be: STudent   Type1   Type2 StudentA  […] Powershell [Create a new SQL DB then copy tables and data from MS Access DB]( - Hi, I'm looking for some advice and to find out if the following is possible. I have an Access DB that get's updated each week, In order to improve our reporting, I'd like to create a new SQL DB each week, with a copy of the Access DB tables and it's data. I want to […] PostgreSQL [PostgreSQL authenticated against Microsoft Active Directory]( - Can I authenticate with Active Directory and get into PostgreSQL? Version: PostgreSQL 16.1 on Windows Server 2022. I'm running DBeaver Community (portable version) on Windows 11 and successfully connect using database user postgres. I found this doc that suggests using GSSAPI to authenticate against Microsoft Active Directory. Confused now if I should be using LDAP […] SQL Server 2022 - Administration [Provider not found in SSMS]( - I am trying to setup a linked server and per the documentation it needs to use MSODBCSQL18 - and so we've installed that. It's installed because if I go into the ODBC Data Sources in Windows, I can see it under Drivers and create a connection with it there. However, in SSMS I still only […]   [RSS Feed]( 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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -