Stairway to Advanced T-SQL Level 2: Using the APPLY Operator (2024-03-13)

SQLServerCentral Newsletter for March 13, 2024 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [Stairway to Advanced T-SQL Level 2: Using the APPLY Operator]( - [SQL Server to PostgreSQL Database Schema Conversion]( - [From the SQL Server Central Blogs - Don't Trust the Defaults]( - [From the SQL Server Central Blogs - How Can Data Empower Leaders]( - [Big Data Analytics with Spark: A Practitioner's Guide to Using Spark for Large Scale Data Analysis]( Question of the Day - [Converting to Bits]( The Voice of the DBA  Navigating the Database Landscape The title of our keynote session at the [Redgate Summit in Atlanta]( is Navigating the Database Landscape, and I'll be delivering part of the talk, along with Grant Fritchey and Kathi Kellenberger today, Mar 13. This is based on the [State of Database Landscape Survey results]( as well as our experience working with customers and implementing DevOps solutions over the last decade. The talk was mostly written by others, but as I rehearsed the session, I found myself wondering about how I'd approach my job if we returned to being a DBA or developer. When working in technology today, there are many challenges outside of actually learning about any of the particular products, languages, platforms, etc. We have the politics of working with others, ongoing work, emergency requests outside of channels, random questions asked by others, code reviews, and probably a few other things I'm forgetting, all outside of learning any new skills. While I consider myself a lifelong learner, I know that finding time (and energy) to acquire the basics of any new technology is challenging. At the same time, while working in any size estate, it seems that someone always wants to add a new tool, platform, language, service, or database to the environment. It's great we have choices, but it seems like sometimes every technologist wants to just use something new rather than work within the areas we have experience. Early in my career, it was rare to find more than 1 or 2 database platforms in a company. Now we have lots, often seemingly just added because one person watched a talk or video and thinks it would solve all our problems in this particular instance. Working in an enterprise of any size likely means there are multiple database platforms in use. While you might only be in charge of 1 or 2 today, who knows when someone will call you as the "database expert" and expect you to configure [Redis]( or troubleshoot [ElasticSearch](. I've had friends in this position, and I've had people come ask me to figure out MySQL, DB2, [BTrieve]( Lucerne, and more in my career. It's a challenge, and it's also stressful because I want to do a good job, even if I don't know what I'm doing. Fortunately Internet search, contacts among friends, and more have helped me usually solve an issue. The modern database landscape is likely to be more complex than ever, and with the advent of cloud services, we find there are lots of options that anyone in an organization might choose to use in production, and then ask you to support them. Our jobs are increasingly complex, both from the depth of things we need to know about a database to the breadth of different products and services that might enter our realm of responsibility. Navigating all this is a challenge, but if you rise to the occasion, there can be a lot of rewards. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [Stairway to Advanced T-SQL]( [Stairway to Advanced T-SQL Level 2: Using the APPLY Operator]( Greg Larsen from SQLServerCentral.com The APPLY operator allows you to join a record set with a function, and apply the function to every qualifying row of the table (or view). The APPLY operator takes on two formats: CROSS APPLY, or OUTER APPLY. This article will explain the differences between these two formats, and show you examples of how each of these formats work. [External Article]( [SQL Server to PostgreSQL Database Schema Conversion]( Additional Articles from MSSQLTips.com In this tip, I will demonstrate how to migrate an existing database schema from SQL Server to PostgreSQL. From the SQL Server Central Blogs - [Don't Trust the Defaults]( Rayis Imayev from Data Adventures (2024-Mar-08) Software installation typically involves several steps. After accepting the license agreement, you may have an express option to streamline the installation process by accepting all defaults, including the installation... [Blog Post]( From the SQL Server Central Blogs - [How Can Data Empower Leaders]( Chris Yates from The SQL Professor Using data effectively, leaders can make better decisions, drive innovation, and inspire trust. What is leadership through data? Leadership that utilizes data is the ability to use data as... [Big Data Analytics cover]( [Big Data Analytics with Spark: A Practitioner's Guide to Using Spark for Large Scale Data Analysis]( Site Owners from SQLServerCentral Big Data Analytics with Spark is a step-by-step guide for learning Spark, which is an open-source fast and general-purpose cluster computing framework for large-scale data analysis. You will learn how to use Spark for different types of big data analytics projects, including batch, interactive, graph, and stream data analysis as well as machine learning. In addition, this book will help you become a much sought-after Spark expert.   Question of the Day Today's question (by Steve Jones - SSC Editor):  Converting to Bits I have this code: CREATE TABLE BitTest( insertdate DATETIME DEFAULT GETDATE(), a INT) GO INSERT dbo.BitTest (a) VALUES (0), (1), (0), (2), (5), (0) GO ALTER TABLE dbo.BitTest ALTER COLUMN a BIT GO What happens when I run this? 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) Join Hints What does the REMOTE join hint do? Answer: This causes the join to occur on a remote site if the right table is remote Explanation: The REMOTE hint is used to process a join on a remote site if the right table in the join is remote (or both are remote). If both are local, the join is processed locally. Ref: JOIN Hints - [ [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 [Opening an SSIS package created in 2016 with a later version of Visual Studio]( - I have SSIS packages created in SS2016(i.e. 130). If I use a visual studio version newer than the SSIS package, will it convert the package to a later version? I would think if that's the case then i will have a problem running this upgraded SSIS Package because I'm still running SS2016. I would think […] Administration - SQL Server 2014 [SQL server disk block size formatted as 4KB runs slow]( - Hello All, One of my SQL server 2014 all the disk are formatted as 4KB. I see Microsoft recommends to have 64KB. I feel the query running slow and most of the query waits shows as two waits 1. PAGEIOLATCH_SH 2.CXPACKET and overall server wait also same. Please share your experience. SQL Server 2019 - Administration [Failover Cluster configuration in windows 2019 for 2 subnets]( - Today I am in critical situation for Production server build with failover cluster for 2 different sub nets with 3 nodes cluster. SQL1.domain.com xx.aaa.bb.cc1 SQL2.domain.com xx.aaa.bb.cc2 DR.domain.com xx.aa1.bb.zzz While validating a Failover Cluster over two member servers I get the following two errors: Connectivity to a writable domain controller from node SQL2.domain.com could not be […] [Server_principals sid changed compared to database_principals sid for AD-group]( - Yesterday I configured some new databases and granted a couple of AD-groups auth to perform their stuff. Today, the admin-group could not create objects in their database. Ad-group membership was OK Account was not locked out. Only thing I did after this initial setup is upgrade the SQL Server 2019 to CU25, upgrade the Windows […] SQL Server 2019 - Development [find the number of values less than the current row]( - Hi everyone I am working on a query where I need to find the number of values that are less than the current row's value going back X days. For this example, let use X =5 but it can be any number. Sample data: Explanation: Start at Day 11. Day 11 value is 42. I […] [over clause]( - Posting Error please ignore [how to speed this query up?]( - hi everyone I have a query I use at work. I use percent_rank to rank sales by company for the last 50 days. For example, if I run the query today then I want to look at the last 50 days of data (including today) and then rank the sales for those 50 days. So […] [Pre-defined filters for stored procedures]( - Hello all, I have defined a set of filters and assigned each set a FilterID. I want to pass this FilterID to a stored procedure and filter the results accordingly. My working example inserts the broadest set of data into a temp table and runs conditional deletes to get the appropriate results. Before I delve […] Reporting Services [Tablix shows only the first entry when several entries exists with the same time]( - Hi @all, I'm using a tablix in SQL Server 2019 Reporting Service to show some entries from a SQL database. It works fine and query is very simple. Select [TimeStamp], [Data1], [Data2], [Data3] From Table Where [TimeStamp] between @Start and @End  But there is an issue when the TimeStamp value is identical for some […] Integration Services [DB Native versus Win Authentication with SSIS Packages and Deployment]( - Hi there, I have done a migration of Informatica PowerCenter ETL project to MS SSIS project using Visual Studio 22 Everything is now working fine when I run the packages one by one within the VStudio on my laptop. The next step would be to deploy my project to the Sql Server SSIS Catalog. I […] SQL Server 2022 - Administration [facing deadlocks issue from last week]( - I am facing few deadlock issue recently and when i tried to debug the issue using the XML file generated from the Extended event, i was not able to figure out the exact issue. The text seems very generic to me. Below the content of the XML file […] [Alway on Availability group cluster less]( - Hi for dr purposes i need to setup an on prem db in replica with a db on azure on a iaas vm  i'll use sql always on availaility group in clusterless ag mode , if i need to test the secondary node do i have to do a failover? what about the fallback? […] SQL Server 2022 - Development [Table partitioning questions]( - We have state of the art latest and greatest managed SQL Azure MI Business critical. Table has 84 M rows, transactions table with data from 2019 to 2024 and beyond, I was asked to implement partitioning to improve performance and management , I read around and will use the GUI and implement partitioning by month […] [Analysis services tabular models - how to detect tables which have had errors]( - I am using a query (using TMSCHEMA) to derive the tabular tables to be processed. The aim is to process as much as possible in an iterative way and skip the ones which have an error i nthe processing run. Running the query and execute the processing works with a stored procedure. The query works […] [SQL Server Count Query]( - Hi All, I have a data in column A in where in need to achieve the count which I have mentioned in column B. Please find the attached excel dataset for reference. Thanks   [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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -