Window Function Basics - PARTITION BY (2023-12-08)

SQLServerCentral Newsletter for December 8, 2023 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [Window Function Basics - PARTITION BY]( - [How using a database monitoring tool helps DBAs create value for the whole organization]( - [Learn Spark SQL Date and Time Functions]( - [From the SQL Server Central Blogs - A SQL Prompt AI Experiment with Window Clauses]( - [From the SQL Server Central Blogs - Understanding I/O Freeze in SQL Server 2022's TSQL Snapshot Backups]( - [Microsoft Power Platform Up and Running: Learn to Analyze Data, Create Solutions, Automate Processes, and Develop Virtual Agents with Low Code Programming]( Question of the Day - [Backing Up Master]( The Voice of the DBA  Focus on Growth in Your Career I find there to be a lot of value in actively managing your career. This is part of a series of editorials based on advice I've read from Raylene Yung (Facebook and Stripe) on your engineering career. I write a lot about growing your career. I think it's important to find a good career, but also learn to be good at whatever you choose to do for employment. While I do think that learning is a skill and each of us ought to sharpen our saws, I sometimes find people chasing new things all the time. In [this interview]( I saw a great quote: "... while it’s important to be informed about the latest frameworks and best practices, learning this at the cost of developing fundamental skills is a bad tradeoff." That's something I imagine Jeff Moden saying about all the new technologies that get incorporated into database work. Often I find there are a lot of DBAs and database developers that want to implement PowerShell or MDX or Python because they can and not because it's a good choice. Or perhaps more often because they aren't as skilled in SQL as they should be. I am a fan of experimenting with different methods and determining if there is a better way to solve a problem. However, I am only a fan of using new techniques when they are better. Either they have to perform better (less resources or a lot less time to run), simplify the situation, or they save a ton of developer time. And not just your time, but for all other developers. One of the things noted in the interview above, as well as something I've seen, is that many younger people want to constantly try something new, without developing deep skills in any area. They often move in new directions because they can. I also see intermediate or senior engineers getting bored with their work or excited by something they read about and looking to shift everyone to a new platform/language/framework/etc. New can be better, but isn't necessarily better. Instead, I might try to answer these questions from the article: - how can I make every code change great? - how can I be as good at X as someone on my team - For SQL, can you be as good as Jeff Moden or Itzik Ben-Gan? Or maybe closer to their level? The goal is to become better at your craft. Like the woodworker who improves their skills in shaping wood, or the chef becoming better at creating more tasty recipes, or maybe the weekend race car driver who learns to shave seconds off their lap times with improved driving technique. If it isn't important to you to become the best in your team, maybe this will help. The cloud, AI, and competition from younger people will ensure that if you don't improve your skills at all, you'll slowly fall behind and your current manager, or the one that takes their place, might find a way to replace you with someone that does the job better, faster, and cheaper. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [SQLServerCentral Article]( [Window Function Basics - PARTITION BY]( Emmitt Albright from SQLServerCentral This article looks at the basics of the OVER() clause and how the PARTITION section works. [External Article]( [How using a database monitoring tool helps DBAs create value for the whole organization]( Additional Articles from SimpleTalk The rise of cross platform databases which are both on-premises and in the cloud, alongside decreasing customer satisfaction and trust in digital channels, has increased the importance - and value - of enhanced database monitoring. Find out how using a database monitoring tool creates value for the whole organization. [External Article]( [Learn Spark SQL Date and Time Functions]( Additional Articles from MSSQLTips.com This article covers how to use the different date and time functions when working with Spark SQL. [Blog Post]( From the SQL Server Central Blogs - [A SQL Prompt AI Experiment with Window Clauses]( Steve Jones - SSC Editor from The Voice of the DBA SQL Prompt has an EAP with some AI capabilities. I was asked to do some testing, and while I’ve done relatively little, I did find some time to play... [Blog Post]( From the SQL Server Central Blogs - [Understanding I/O Freeze in SQL Server 2022's TSQL Snapshot Backups]( aen from Anthony Nocentino Blog SQL Server 2022 introduces a new feature to enable application-consistent snapshot backups. TSQL Snapshot Backups enable the SQL Server to control the database quiesce without external tools. Using TSQL... [Microsoft Power Platform Up and Running: Learn to Analyze Data, Create Solutions, Automate Processes, and Develop Virtual Agents with Low Code Programming]( Site Owners from SQLServerCentral The book begins with the basics, explaining what low-code and no-code are and showing how to maximize efficiency in creating business applications for one's organization. Next, the book describes the Microsoft Power Platform's foundation, as well as all its components and services. Readers will begin with practical exercises right away, beginning with provisioning a Power Platform environment. Next, the book delves deeper into the Power Platform components such as Power Apps, Power Automate, Power BI, and Power Virtual Agents. Towards the end, the book explains practical exercises for each feature or service where you will gradually build a small business solution for a fictitious organization, Project Wizards, Inc.   Question of the Day Today's question (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 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) A Partial Count I run this code in a SQL Server 2022 version of AdventureWorks: SELECT COUNT(*) FROM Sales.Customer I get 19280 as the result. What happens when I run this? SELECT COUNT(*) FROM Sales.Customer TABLESAMPLE SYSTEM(10 PERCENT); Answer: I get results that range from the hundreds to over 3,000 Explanation: The TABLESAMPLE clause will cause SQL Server to return approximately the number of rows as a percentage of the table, but the actual number that comes back can vary dramatically. The reason is that this gets a sample of data pages, not rows, and since there can be varying numbers of rows on pages, you can get various results. In testing, I have seen results below 500 and over 3500. Ref: FROM - [ [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 [Small Transactional Table with Frequent Updates]( - Hi All We have a 12 row on our platform that gets updated for every transaction and stores Identity values used by each transaction. Every time a new transaction is received, the appropriate transaction row is incremented by 1. The trouble we're seeing more and more lately is contention through that table during busy periods, […] SQL Server 2017 - Development [Slicing status based on conditions]( - Hi, I have a table ShipperStatusHistory that tracks orders, Shipping status and relay points (for customers where to collect packages) . I want to flag the package That weren't collected by customers for each a relay point. To identify a left package (not collected by customer) the status need to be ("Dropoff" or "preparation") ***OR […] SQL Server 2016 - Administration [broker error]( - Hello, I install a broker under sql server 2016 and I have a message that I never recognized. In transmission_status : "24(The program issued an incorrect length command.)" The message of 25490 characters long has not been sent. Thank you in advance for any help. Administration - SQL Server 2014 [Suggestion on HA DR solution for transactional replication published database]( - Hi All, I am planning for HA DR solution for transactional replication published database. (Not for distribution DB). It is in SQL 2014 standard edition and having 4 subscriber for only one database, I am planning to have either log shipping or DB mirroring into another server. Can anyone suggest which is best to for […] SQL Server 2019 - Administration [Sporadic error during restore job]( - Hello experts, I see this error in a SQL Agent job. It happens during a step that restores a database, and it is supposed to temporarily enabled xp_cmdshell to read some backup files, then disable xp_cmdshell. Does anyone know why this error happens, and why it happens only sometimes? Thanks for any help. Configuration option […] SQL Server 2019 - Development [Migrating DB to new DB schema]( - Hello everyone, I am rather new to SQL-Server and got a problem to which I cannot find a solution. But first to my project. I am developing a software with C# ef core and SQL-Server with the code first approach. Bothe get hosted by me via docker containers. My current problem is that I want […] [AI Tools]( - Not quite sure where to post this or how quite to ask as its early stage I am looking for two type of AI tool A tool to go through data and check accuracy/ confidence levels very simple example( millions of records ) a customer table with Company name , addresses, postcodes etc a separete […] [Openrowset & removing unwanted characters...]( - Folks I'm wondering can you help me here. I have inherited a system that populates a table from Excel using the OPENROWSET command. The table in question is called: tbl_raw_data and then the loaded data is moved on elsewhere... The excel speasheet can and does change column names and number of rows over time. The […] SQL Server 2008 - General [Reduction of installation time of sql server 2008 R2 Express]( - The installation of SQL Server 2008 R2 Express on a virtual machine takes around 12-15 minutes, whereas on a regular PC, it takes around 5-6 minutes. Are there any options to reduce the installation time ? The following approaches have been attempted: Via command line: SQLEXPR_x64_ENU /ACTION=Install /IACCEPTSQLSERVERLICENSETERMS /QS /FEATURES=SQLENGINE /INSTANCENAME=PMP /SQLSVCACCOUNT="NT AUTHORITY\SYSTEM" /AddCurrentUserAsSQLAdmin /SQLSYSADMINACCOUNTS="BUILTIN\ADMINISTRATORS" […] Strategies and Ideas [data security for a data warehouse]( - I'm working with someone whose client says "Yes, we have a data warehouse. But NO you can't be granted access to it." Technically, can you not create one or more schemas and add views to the schema ( and the other necessary required objects ) so that the outsiders are granted some but not full […] Integration Services [PowerQuery in SSIS... is it only in 2023?]( - First off, I'm terrible at SSIS... I'm trying to create a PowerQuery data source, and it's flat out not there in VS 2022 (yes, I have the rest of SSIS installed). is it only in 2023? is there a good rank beginner article on getting started with it somewhere? I saw one, but I wanted […] General [Review of DB Activity]( - Hi Folks,  I have been tasked with reviewing all the activity on a SQL Server 2000 - Version 8.00.2666 (SP4) I have monitored DB's before and via execution of Master DB stored procs, watching activity and so on, however never really when in to this depth of attempting to find out all the activity […] SQL Server 2022 - Development [Average price calculation]( - Hello! I need to make a query that will calculate my average price for each item. CREATE TABLE [dbo].[Table_1]( [id] [int] IDENTITY(1,1) NOT NULL, [idProduct] [int] NOT NULL, [Product] [nvarchar](50) NOT NULL, [Quantity] [decimal](18, 2) NOT NULL, [Price] [decimal](18, 2) NOT NULL,  CONSTRAINT [PK_Table_1] PRIMARY KEY CLUSTERED ( [id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE […] [Need help on query - DATEDIFF]( - Hello, I want to have value Month ago Year ago So far, I've this SELECT CASE WHEN DATEDIFF(SECOND, crtDte, GETDATE()) < 60 THEN ' Just Posted' WHEN DATEDIFF(MINUTE, crtDte, GETDATE()) < 60 THEN CAST(DATEDIFF(MINUTE, crtDte, GETDATE()) AS VARCHAR(10)) + ' Minutes' WHEN DATEDIFF(MINUTE, crtDte, GETDATE()) < 24 * 60 THEN CAST(FLOOR(DATEDIFF(MINUTE, crtDte, GETDATE())/60) AS VARCHAR(10)) […] [sp_dropserver - Bug/behavior change - Leaves transaction open after TRY...CATCH]( - Verified on latest CU 10 for SQL 2022. Confirmed works correctly on SQL 2019 (i.e. no open transaction). When it cannot drop server because it is used in replication, the transaction count goes from 0 to 1 after the CATCH. This causes stored procedures with this code to fail, since the transaction count is unexpected. […]   [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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -