Breaking Down DENSE_RANK(): A Step-by-Step Guide for SQL Enthusiasts (2024-04-12)

SQLServerCentral Newsletter for April 12, 2024 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [Breaking Down DENSE_RANK(): A Step-by-Step Guide for SQL Enthusiasts]( - [Department of Redundancy Department]( - [From the SQL Server Central Blogs - The London Redgate Summit Comes on Apr 17]( - [From the SQL Server Central Blogs - What is data classification, and why is it important?]( - [Practical Database Auditing for Microsoft SQL Server and Azure SQL: Troubleshooting, Regulatory Compliance, and Governance]( Question of the Day - [Default Objects in Clones]( The Voice of the DBA  When Are Your Breaks? As I work through 2024, I found myself doing a little more vacation planning this year than in previous ones. In 2022 I traveled quite a bit, but my wife went with me often. We went to Europe 5 times that year and added quite a few vacation days around my work trips. My wife thought that was a great year. Last year, 2023, was different. I traveled more (36 trips), with most of them being short. When I traveled that much, I wanted to end trips quickly and get back home. I learned that was too many, and also too disruptive for life. I got behind on things I needed to do at home, my wife went with me less because many trips were all work, and I lacked energy from the pace of moving all over the world. As a result, my boss and I are more closely watching my travel schedule, and I'm consciously working to ensure I take some breaks between trips. Part of that is doing some planning. So far this has me reducing the number of trips (10 in H1), but also including 3 good-sized vacations away from work during that time. Note: don't feel too sorry for my travel load. I'll get a holiday each in the US, Europe, and Australia. I know a lot of people like to schedule their vacations at similar times each year. The end-of-year holidays are often a time when many people travel, but I know some people who take time every June, others every August, often corresponding with school breaks for children, family reunions, or some other event. When do you like to take your vacation? Are you a many-long-weekends-through-the-year person? Do you go on one long trip a year? Love holidays to get away or stay home and work because work is quiet? I don't know if it matters, but I'm always interested in what others do and why. I prefer more, shorter vacations, but convince me why your long trip is better. Tell me the amazing things/places you've done/seen. Maybe I'll get some ideas for a future trip. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [SQLServerCentral Article]( [Breaking Down DENSE_RANK(): A Step-by-Step Guide for SQL Enthusiasts]( Johnny_35 from SQLServerCentral Learn how it works and how to use the DENSE_RANK() function in your code. [External Article]( [Department of Redundancy Department]( Additional Articles from SimpleTalk Ever wonder all the reasons that we use databases instead of file systems? While we don’t think of it too much anymore, the first reason that databases came into existence was to remove redundancies. [Blog Post]( From the SQL Server Central Blogs - [The London Redgate Summit Comes on Apr 17]( Steve Jones - SSC Editor from The Voice of the DBA I’m heading to the UK in a few weeks for the London Redgate Summit. This starts a wild period of travel for me, but I’m excited. This is the... [Blog Post]( From the SQL Server Central Blogs - [What is data classification, and why is it important?]( Chris Yates from The SQL Professor The benefits of data classification and the features of a tool like Microsoft Purview, a unified data governance service. Data classification organizes data into categories based on its type,... [Practical Database Auditing for Microsoft SQL Server and Azure SQL]( [Practical Database Auditing for Microsoft SQL Server and Azure SQL: Troubleshooting, Regulatory Compliance, and Governance]( Site Owners from SQLServerCentral Know how to track changes and key events in your SQL Server databases in support of application troubleshooting, regulatory compliance, and governance. This book shows how to use key features in SQL Server ,such as SQL Server Audit and Extended Events, to track schema changes, permission changes, and changes to your data. You’ll even learn how to track queries run against specific tables in a database. ss   Question of the Day Today's question (by Steve Jones - SSC Editor):  Default Objects in Clones On SQL Server 2022, I do this: USE model GO CREATE TABLE dbo.DBALog (logdate DATETIME2(3), logmsg VARCHAR(2000)) GO I then create a new database: CREATE DATABASE INVENTORY GO I install a new database application in here with multiple tables, views, etc. I do not run any queries. I then decide to run this code: DBCC CLONEDATABASE(INVENTORY, Inventory_clone); GO What happens? 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) Tempdb Pages If I create an object in tempdb, what is the minimum number of pages used? Answer: 9 pages Explanation: There is a minimum of 9 pages. One IAM page and an extent (8 pages). Ref: tempdb - [ [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 [Downgrade from Enterprise to Standard (incl. SSAS, SSIS, SSRS)]( - Hi there, thanks for listening! We are preparing to downgrade one or our Instance from Enterprise to Standard. Thankfully, this is not a production server, so some downtime is not an issue. We are planning to make a snapshot of the VM so that we can rollback in case something goes wrong. I want to […] SQL Server 2017 - Development [Query help please]( - I have a history table that tracks changes to client records, im having trouble writing a query that will capture BOTH of these records based on (Where changedcolumns = 'Paid'). I need the OLD and NEW history in the result. [SQL agent help]( - Hi Guys, I have an SQL Agent job. It is called an SSIS Package and is stored in SSIS DB. I can see the SSIS Package completed without any problem. Job has only one step. However, the SQL Agent job never ends. Just for my testing, I created an SQL Agent job and used SQL […] SQL Server 2016 - Development and T-SQL [Tracking Database Growth - Script Automation & Reporting]( - Hello,  I found this script that helps me in populate a table with information about my database; specifically sizes. I am a looking for a way to automate the process wherein the data can be extracted to an excel spreadsheet for us to do reporting against to track our database size growth overtime. Your […] Development - SQL Server 2014 [Outer join table where there is 2 foreign keys to join]( - I am asked to provide a date in a query from a table that may or may not have a record. Thus the outer join. The issue is the joined table may have many records for the ID (foreign key) being joined, and the query is returning the first result not the last.  There […] SQL Server 2019 - Administration [Optimizing Always On failover alert]( - Hello, We are trying to optimize the no. of alerts generating while Availability Group fails over. Event ID: 1480: Database Replica Role Changed Event ID: 19406: Availability Group Replica Role changed. As per my understanding Event 1480 can never happen without 19406. Hence it is ok to disable alert for 1480. Please let me know […] SQL Server 2019 - Development [how to do this in SQL?]( - Hi everyone I am working on a query and I am totally stumped. I don't even know where to begin. I will give test data, expected outcome, and explanation of logic. Test Data: create table #test_table ( company char(4), trade_date date, type char(1), level int, price decimal(6,2) ) insert into #test_table values ('ABC','2024-03-21','L','100','23.45'), ('ABC','2024-03-21','L','200','33.12'), ('ABC','2024-03-21','L','300','21.05'), […] [Need Help: Polybase external table on Parquet file stored in Azure StorageV2]( - My goal is to create a Polybase external table from a Parquet file located in an Azure storage container. SQL Server 2019 (RTM-CU22-GDR) (KB5029378) - 15.0.4326.1 (X64) StorageV2 (general purpose v2) (LRS) File was exported from SQL Server using: parquet-cpp-arrow version 4.0.0 I know that the SAS token I'm using is probably good in terms […] [Help with reading from JSON array]( - Hi all, I am trying to read the following JSON code in SQL Server: declare @json nvarchar(max) set @json = ' { "WorkId":121, "Code":"UK_AAT", "UpdateAttributes": { "TriggerDateMapping": { "3N":"N/A", "10N": "N/A" }, "OverrideRules": [ {"Busket Rule":"A1", "Exit_Window": 15, "Strategy": "STE Triggered", "Start_Date": "Month 2" }, {"Busket Rule":"C2", "Exit_Window": 44, "Strategy": "STE", "Start_Date": "Month 2" }, […] [test sql servers]( - Hi we run 2019 standard. I'm torn between asking my dept head for 3 test sql servers for use by everybody so that we can test 3 projects at once vs enough horsepower on my local to run 3 instances.  I'm leaning toward one that is shared and whatever it takes to run 3 instances […] [error using #temp tables]( - Hi I am getting this error message when I try to re-use a temp table. The code is a bit long so I won't post it here. Here is a template on how the temp tables are being used. What am I doing wrong? Code: DROP TABLE IF EXISTS #TEMP1 DROP TABLE IF EXISTS #TEMP2 […] SQL Azure - Administration [Database Snapshot Alternative in SQL MI]( - Database snapshots are not supported in SQL Managed Instance, Is there a workaround for Database snapshot feature. Restore from point in time backups will take a lot of time for us. I want to create a readonly copy of a database from a specific point, how can this be achieved in SQL MI? Analysis Services [Dates are rendered in multiple formats in Excel against Cube]( - We upgraded a SQL2014 environment to a sql2016 one recently with a customer. This customer still uses the traditional SSAS Multidimensional cubes. After the upgrade the dates no longer present themselves in the singular same format within Excel reports. A value of 14 march 2024 is represented as '2024-03-14' correctly , but 4 march 2024 […] SQL Server 2022 - Administration [Post Upgrade Access issues - Databases in 120 Compatibility and Read_Only Status]( - I recently did an in place upgrade from 2014 to 2022, and couldn't connect to any of the databases that were in Read_Only state AND still in 120 compatibility mode, with the following error: "Cannot open database 'DBName' version 782. Upgrade the database to the latest version." After setting all the databases to 160 mode […] [SQL2022 CU12 Question]( - Hi. I installed SQL2022 CU12 and in the notes it says:  2937584 Fixes an issue in which the sp_server_diagnostics stored procedure doesn't respond to the Always On availability group (AG) resource DLL within the HealthCheckTimeout when the I/O takes a long time, which causes unnecessary restart and failover. For example, when the sp_server_diagnostics stored procedure is waiting for the PREEMPTIVE_OS_GETFINALFILEPATHBYHANDLE wait type. […]   [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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -