SQL Window Functions Series: NTILE() (2024-01-03)

SQLServerCentral Newsletter for January 3, 2024 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [SQL Window Functions Series: NTILE()]( - [Kickstart the year with our free Livestream! Navigating the database landscape in 2024]( - [Using Spark Jobs for Multiple Lakehouse Maintenance in Microsoft Fabric]( - [From the SQL Server Central Blogs - Kusto (KQL) for Azure SQL Audit]( - [From the SQL Server Central Blogs - Create failed for Availability Group]( - [Expert Performance Indexing in Azure SQL and SQL Server 2022]( Question of the Day - [Default Statistics Sampling]( The Voice of the DBA  Fixing Poor Programming Practices I see a lot of poor programming practices in real-world code. I'm sure I've written a few myself. I'm also sure that many of you see the same thing and sometimes wonder how that code got deployed. One of the things I told myself over the last few years was that I had to accept the reality of situations. It's easy to complain and say that code should be written differently or entities should be modeled in another way, but I (or you) can't change that situation today. And it ignores the fact that we are stuck with this environment and we have to move forward from here, not try to rewind the clock and go back in time to design things better at the start. Today I'm wondering how you've adapted to poor practices and fixed them. Do you have stories to share that might help others deal with their situations? Post comments below, and to help, I've got a few stories from my past. Both of these deal with third-party software products that (poorly) use a SQL Server database. One company purchased a new accounting package with very poor security practices. The installation noted that the software needed to use the sa account. I couldn't think of a reason why an accounting package needed sa on an instance, especially as we were installing this on a production server with another database. I called the support line and we discussed what was happening. They said that for the accountants to add new users, they needed to add a login and thus needed sa. This was in an older version of SQL Server, but that still didn't make sense. It was lazy developers not understanding the security model and wanting sysadmin to make things easy. We showed them that we could add a login ourselves and a public user mapped in the database, which the software would pick up and allow finance people to assign rights. Poor practice mitigated. In another piece of software, we found a view being used that was querying other views and tables. As you might expect, performance was poor. We decided to "fix" the view with better code, but first, we created a new schema (with our company name) and added their view to this schema. That way we kept their code around. We rewrote their view to run more efficiently, and if we had a support call, we'd replace our better view with the original one from the previous schema. We even had a proc in our schema to do that for us. Of course, our view was kept in our VCS, as it should be. If you've got stories, let us know in the comments today. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [SQLServerCentral Article]( [SQL Window Functions Series: NTILE()]( Nikhil Bangad from SQLServerCentral Unlock the power of SQL's NTILE function with our latest deep-dive article. Learn to segment your data effectively into quantiles for more nuanced analysis, discover common pitfalls and their solutions, and optimize your queries for peak performance. [External Article]( [Kickstart the year with our free Livestream! Navigating the database landscape in 2024]( Press Release from Redgate The key finding from our annual ‘State Of’ survey is that there’s a need for skill diversification to keep up with the pace of technological advances in IT world. How will this skills gap affect you? Whether you’re just starting out in your career, you’re a seasoned data professional or you’re a senior IT leader wanting to stay ahead of business growth, join our free livestream on January 23rd. Redgaters Steve Jones, Ryan Booz and Beca Parker will introduce key findings from the survey and offer their thoughts on the big changes coming in 2024 and what you can do to thrive in this changing landscape. Register now [External Article]( [Using Spark Jobs for Multiple Lakehouse Maintenance in Microsoft Fabric]( Additional Articles from SimpleTalk I have published videos and articles before about Lakehouse maintenance. In this article I want to address a missing point for a lot of Fabric administrators: How to do maintenance on multiple lakehouses that are located in different workspaces. [Blog Post]( From the SQL Server Central Blogs - [Kusto (KQL) for Azure SQL Audit]( hellosqlkitty from SQLKitty According to Rod Trent, Kusto is named after Jacques Cousteau. He says, “We are exploring the depths of our data” by searching and querying our Azure log data. His... [Blog Post]( From the SQL Server Central Blogs - [Create failed for Availability Group]( Hemantgiri S. Goswami from SQL Server Citation - SQL Blog by Hemantgiri S. Goswami, SQL MVP Ever since the release of Always On Availability Group (AG or HADR) in SQL Server 2012 the adoption ratio is increasing. A lots of my clients too are adopting... [Expert Performance Indexing in Azure SQL and SQL Server 2022]( [Expert Performance Indexing in Azure SQL and SQL Server 2022]( Site Owners from SQLServerCentral Take a deep dive into perhaps the single most important facet of query performance―indexes―and how to best use them. Newly updated for SQL Server 2022 and Azure SQL, this fourth edition includes new guidance and features related to columnstore indexes, improved and consolidated content on Query Store, deeper content around Intelligent Query Processing, and other […]   Question of the Day Today's question (by Steve Jones - SSC Editor):  Default Statistics Sampling If I don't specify a percentage of sampling (or fullscan) with CREATE STATISTICS, what is the default percentage of the table sampled? 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) The First Place Seeing the New Year I want to find out which time zone gets to experience the new year first. Which of these queries will get me that time zone? -- 1 SELECT TOP 1 [name] , MIN (tzi.current_utc_offset) OVER (ORDER BY tzi.current_utc_offset) FROM sys.time_zone_info AS tzi; -- 2 SELECT TOP 1 [name] , tzi.current_utc_offset FROM sys.time_zone_info AS tzi ORDER BY tzi.current_utc_offset; -- 3 SELECT TOP 1 [name] , tzi.current_utc_offset FROM sys.time_zone_info AS tzi ORDER BY tzi.current_utc_offset DESC; -- 4 SELECT TOP 1 [name] , tzi.current_utc_offset FROM sys.time_zone_info AS tzi ORDER BY CAST (REPLACE (tzi.current_utc_offset, ':', '') AS INT) DESC; Answer: 4 Explanation: The time zones are stored as nvarchar, so to order them by time, they need to be changed to ints. However, an int cannot have a colon, so that must be removed. Also, the first time zone to experience the new year will be a plus + time zone ahead of UCT. Happy New Year! [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 - Development [How to establish a relationship in SQL Server]( - Good morning and Merry Christmas everyone! I'm not sure if anyone has posted something like this before, and if they have, I couldn't find it. And I apologize for the repeat. I'm looking to take the minimum value of a relationship between customers and accounts and apply it to all accounts, thereby establishing a relationship […] SQL Server 2016 - Development and T-SQL [How to validate existing data in a table]( - Hello Team, I have some application databases where i need to perform data validation. I am not sure what type of data is already stored in the tables. Does there are any set of pre-defined rules / constraints with which i am cross verify data ? Or i have to reply on the application team […] Administration - SQL Server 2014 [FTS Not enabled for DB - but it is working(?)]( - Hello. I have a couple of DBs that say that Full Text Search is not Enabled on the DB - yet it seems to work? FTS catalogs were created by upgrades to SW that use them to run Contains queries via the portal - this seems to work fine. Maybe the data is stale or […] SQL Server 2019 - Development [Temp Tables Used within Procedure giving error in one 2019 sql server instance]( - I have Three stored Procedures A,B,C C is a common procedure which derives data into a common table depending on parameters from respective procedures A or B and returns to a or b for further processing on one sql 2019 environment its working normally but on another if Proc A is executed first then later […] [Managed Instance: Resource alerts with db_mail for usage and notifications?]( - Hello SQLServerCentral community, I'm looking for guidance on setting up resource alerts for Managed Instance. Specifically, I want to monitor resource usage and receive email notifications when reaching storage or CPU limits. Can someone please provide me with detailed instructions or steps on how to configure this using db_mail? Thank you in advance for your […] [SQL2019 ODBC Connection from Linux setup help.]( - I can connect to the sql server 2019 from my linux machine with tsql but not with isql or with PHP odbc_connect. I can connect to older SQL Servers. I have tried a lot of different thing including FreeTDS. isql error is [ISQL]ERROR: Could not SQLConnect my odbcinst.ini [FreeTDS] Description=FreeTDS Driver Driver=/usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so Setup=/usr/lib/x86_64-linux-gnu/odbc/libtdsS.so [ODBC Driver […] Reporting Services [How to use pagination for huge reports]( - Hi all, I created a SSRS report that supposed to load about 600k records. In order to minimize loading time, I used pagination, by 50 records each page. However, when I refresh it in a Preview mode it throws "out of memory" exception. I expected that if I use pagination it will only load 1st […] [SSRS Unable to log in to web Portal]( - Hi guys, I have a new SSRS setup on a new computer, but the default web authentication won't go through no matter what. I've tried adding roles on SSMS, adding Firewall Ports and Authentication and changing my windows info but it all didnt works. Can anyone give me some tips on what to do? Thanks [Report is Blank in PDF only!?]( - Hi all, This one is stumping me and I can't tell if this is a bug or a user issue (meaning me). Hoping for some insight. My report consists of a main report with two sub-reports in a table, and my first sub report has its own subreport with a grouping applied. So something like […] General [Task solution help request]( - Couldy You help me with this task, please? You are working with the library books database. The Books table has the columns id, name, year. The library has new books whose information is stored in another table called "New", however they do not have a year column.  Write a query to select the books from both tables, Books and […] Article Discussions by Author [Always On Failover Availability Group Automation]( - Hi! I have successfully installed the always-on system from your current repository, and I want to thank you for that. However, while the primary database is inaccessible, I want to be able to write to and update the secondary database. In other words, how can I automatically make the secondary database the primary one in […] SQL Server 2022 - Administration [SQL Server 2022: AlwaysOn AG databases can not sync after applying CU6 or higher]( - The setup is: OS - Win Server 2022 & MSSQL - SQL Server 2022 AlwaysOn Availability Groups are enabled on two (primary + secondary) replicas and use WSFC. Several availability groups are created and a database in each of them. AGs are configured with Sync Commit and Automatic failover mode, also they use dedicated separate VLAN […] [Database space issue]( - Hi experts, I am facing issue with the database file space used. Here are some details: Server - Azure database Data file size - 350 GB All table combined izes - ~40 GB Space used within the data file- 340 GB I looked at sys.allocation units and there are several container_ids that are missing in […] SQL Server 2022 - Development [Return]( - Self taught novice learning SQL and would welcome some help with the following code problem In my data set I have one record per customer but would like my select query to return 12 rows for each customer record in the data set. e.g. If I have 2 customers I would end up with 24 […] [Last value in a row containing..]( - I've inherited code in which I need to designate each PATID as either Rel_Exempt or Med_Exempt, depending on whether it is the last column in the row (not counting the column with 'NONE'). Hoping it will turn out as below. Thanks in advance for any help.  create table #values ( PatID varchar(10) ,Name1 varchar(10) […]   [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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -