How to join the free PASS Data Community Summit Livestream from around the world (2023-11-15)

SQLServerCentral Newsletter for November 15, 2023 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [How to join the free PASS Data Community Summit Livestream from around the world]( - [SQL Window Functions Series: RANK() and DENSE_RANK()]( - [SQL Server Table Design Guidelines - Data Types, Designing for Deletes and Primary Keys]( - [From the SQL Server Central Blogs - Adding a Foreign Key in the CREATE TABLE statement–#SQLNewBlogger]( - [From the SQL Server Central Blogs - Common Mistakes in SQL Server – Final]( - [The Definitive Guide to Azure Data Engineering: Modern ELT, DevOps, and Analytics on the Azure Cloud Platform]( Question of the Day - [Checking the Servicves]( The Voice of the DBA  Can Data Save the World? This editorial was originally published on Dec 5, 2017. It is being re-run as Steve is away at the [Data Community Summit](. Years ago I read one of the [Freakonomics]( books. It was an interesting look at how we might examine our world in unexpected ways, using lots of data. While I didn't always agree with the conclusions of the authors in different areas, I did find the idea of using data to probe and examine for patterns in our world to be fascinating. Recently I caught [a podcast with the authors]( and the opening question caught my eye. It was "can data save the world?", and I was hooked, needing to stop and listen to the episode. That's because I think data is the most important asset in the world today, and data is what really powers all the software we have. We need data, and we need to ensure our data has a high level of quality and integrity in order to glean information from all those bits and bytes to get value from software. Certainly software matters, but data, to me, is more important. The authors' position is interesting in that they see data as an important part of analyzing data. The type of data, industry, organization, etc. doesn't matter, but the data also isn't necessarily enough to change someone's mind or convince them of an argument. There needs to be a story as well, which requires a different skill from that needed for analyzing data. I do think that often the way in which we present an analysis can be just as important as the data. Perhaps even more so. What I do find interesting in the podcast is that younger companies (and people) are willing to embrace more data driven approaches. I see that often. There have been no shortage of clients in my career that were sure they knew the answer to some question about their business job without referring to any data analysis. They trusted their experience. Even if there was data that might show their conclusions were slightly erroneous, they often didn't want to change their decision or conclusion. Humans are creatures of habit. Even those of us that embrace some change will find that we like change in some parts of our lives, but not others. If someone has had success in their career without using data to support or alter their opinions, it can be hard to get them to change. I wish I had a good method for convincing people to get started using more data, but really I think that the best idea is to find a different person to convince. If you can do so, and produce real results, then you may be able to go back to the first person and show them some evidence. The one part of the podcast that I wish turned out different is at the end, where the authors note there isn't a good way to teach someone how to analyze data and tell a story. They thought about creating their own curriculum, but gave up. It's too much work, and apparently they weren't sure they could do a good job. They imply this is harder than teaching someone to program, which is disappointing as we have lots of programmers that need help as well. If we can't teach programmers at any scale, what does that mean for data scientists? The one part I would agree with them on is that data science is a great area to move towards your career if you have the talent. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [SQLServerCentral Article]( [How to join the free PASS Data Community Summit Livestream from around the world]( Press Release from SQLServerCentral Learn how you can watch the sessions livestreamed from the PASS Data Community Summit 2023. [SQLServerCentral Article]( [SQL Window Functions Series: RANK() and DENSE_RANK()]( Nikhil Bangad from SQLServerCentral Welcome to the fascinating world of SQL window functions! Today, we'll explore in detail: RANK() and DENSE_RANK(). [External Article]( [SQL Server Table Design Guidelines - Data Types, Designing for Deletes and Primary Keys]( Additional Articles from MSSQLTips.com In this article, we look at three table design guidelines when building an SQL table including data types, designing for deletes, and primary keys [Blog Post]( From the SQL Server Central Blogs - [Adding a Foreign Key in the CREATE TABLE statement–#SQLNewBlogger]( Steve Jones - SSC Editor from The Voice of the DBA I had someone ask this question recently and had to double check the syntax myself, so I thought this would make a nice SQL New Blogger post. Another post... [Blog Post]( From the SQL Server Central Blogs - [Common Mistakes in SQL Server – Final]( Hemantgiri S. Goswami from SQL Server Citation - SQL Blog by Hemantgiri S. Goswami, SQL MVP In this final and summary post on the blog series of Common Mistakes in SQL Server I have pen down about auto growth, use of null values, implicit conversion... [The Definitive Guide to Azure Data Engineering: Modern ELT, DevOps, and Analytics on the Azure Cloud Platform]( [The Definitive Guide to Azure Data Engineering: Modern ELT, DevOps, and Analytics on the Azure Cloud Platform]( Site Owners from SQLServerCentral Build efficient and scalable batch and real-time data ingestion pipelines, DevOps continuous integration and deployment pipelines, and advanced analytics solutions on the Azure Data Platform. This book teaches you to design and implement robust data engineering solutions using Data Factory, Databricks, Synapse Analytics, Snowflake, Azure SQL database, Stream Analytics, Cosmos database, and Data Lake Storage Gen2.   Question of the Day Today's question (by Steve Jones - SSC Editor):  Checking the Servicves I am connected to a SQL Server 2022 instance. I want to check on the service account running SQL Agent. How can I do this from T-SQL? 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) High Deadlock Priority Deadlock priorities range from -10 to 10. If I run this code, what is my deadlock priority? SET DEADLOCK_PRIORITY HIGH Answer: 5 Explanation: The setting when using high is 5. This means your session will be the victim if another session has a priority higher than 5. Ref: SET DEADLOCK_PRIORITY - [ [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 [SQL2017 DBMirroring Query store not working on mirrored databases]( - I have noticed on our SQL2017 mirrored databases Query store is not working. ( Moving to AGs is not an option. ) Settings in use: ALTER DATABASE [MyMirroredDb] SET QUERY_STORE = ON GO ALTER DATABASE [MyMirroredDb] SET QUERY_STORE (OPERATION_MODE = READ_WRITE, CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30), DATA_FLUSH_INTERVAL_SECONDS = 900, INTERVAL_LENGTH_MINUTES = 60, MAX_STORAGE_SIZE_MB = 100, […] SQL Server 2016 - Administration [SQL Server Transactional Replication-Snapshot Initialization for existing REPL]( - Hi,  This is Syed JB, I have an existing Transactional Replication. Unfortunately, for some reasons Replication has been removed for some time. But, now, we have to rebuild the Transactional Replication. Here is the problem, Subscriber has the historical data and for this I can’t push the snapshot and without the initialization, subscriber subscription […] SQL 2012 - General [SQL Scripts automatically invoked]( - When detecting the processes in the SQL Server 2012, I found there was a SQL scrit executed automatically automatically many times, the following as attachment, do you know when the script will be executed and when it will be executed? thanks a lot! SQL Server 2019 - Administration [Log shipping time_since_last_backup]( - I am using SQL server 2019, I have a database setup transaction log shipping on Server A, and secondary on Server B. I have a need to setup another database to also use transaction log shipping, also primary db is on ServerA and secondary is on Server B. When I setup the Alert monitor instance […] [SQL replication error.]( - Hi I´m having two SQL server cluster ( lets say A and B. ) with a number of instances ( lets say A1, A2,.. , B1,B2...) in two different sites. I want to replicate A\A1 database Test to server B\B1 database Test and server B\B1 database test2 to server A\A1 database Test2 ( hope […] [Sorry posted in admin section]( - Sorry posted in admin section. SQL Server 2019 - Development [Find a word from string]( - Hi All I would like to get rows wherever it has "restricted" . If I write condition with like '%restricted%' it is showing me all. create table #tbl_datafile_list (Autogrow varchar(500)) go insert into #tbl_datafile_list values ('64.0 MB, unrestricted growth'), ('By 10 percent, restricted growth to 500000.0 MB'), ('By 10 percent, unrestricted growth'), ('1.0 MB, unrestricted […] [Has SQL Server Introduced a way for Converting HTML to non-HTML stored as VARCHA]( - If a table stores in a column of type VARCHAR the textual equivalent of an html page, does SQL Server have some but in function or process for converting that into non-html text, just regular text with carriage returns and the like? I've seen a few custom solutions for this but noting native to SQL […] SQL Azure - Administration [IX fragmentation and Updating Stats]( - Though its Azure MI Business critical , I see fragmentation, Any script to identify which IX's need to be rebuilt ,as Frag is over 50%. I will add REBUILD WITH (ONLINE=ON) as we cannot have downtime , Its a 24 x 7 DB. I see failed inserts on dynatrace every now and then, possibly as […] [Good Azure cost calculator]( - Azure calculator doesn't give a good estimate as it cannot differentiate between HA/ DR and primary replica. I have looked around , I couldn't find a good way to come up with an estimate, wondering how others have done this before? [shrink data file with low priority]( - so total size of DB on Azure is 1 TB, but when I add all tables ( Data file ) and log file the total size is 100 GB. So I need to reclaim the 900 GB ( release storage to the OS ) . MS recommends running DBCC shrinkfile with wait at low priority […] Reporting Services [HSTS - need to enable in SSRS 2022]( - Hi, My sys admin says I must enable HSTS in SSRS to pass penetration tests. Using this as guide: Enabling HSTS and selecting most secure ciphers and protocols for HTTPS for ConfigMgr I change the custom header property and restarted the ssrs service. Problem is my sysadmin is saying that is still not passing the HSTS […] Analysis Services [Product Hierarchy in a Matrix with 8+ measures on Values-- Perfomance]( - I'm not new to PowerBI, but I'm new to this problem. I'm working for a company that's creating a PowerBI solution on Azure for a retail company. They have a proper star schema (Sales fact in the middle, and then these dimensions: Product (hierarchy, 8 levels), Date, Store Hierarchy. And then they have like 8 […] SQL Server 2022 - Administration [None of the IP addresses configured for the availability group listener]( - Hi , Receiving below error when trying to add listener to AG. Any suggestions please. ALTER AVAILABILITY GROUP xxxx ADD LISTENER N'xxxx' ( WITH IP ( ('xx.xx.xx.xx', 'xxx.xxx.xxx.xxx') ), PORT=1433 );  Error: Msg 19456, Level 16, State 0, Line 1 None of the IP addresses configured for the availability group listener can be hosted […] SQL Server 2022 - Development [How to Efficiently Find and Remove Duplicates in a Large SQL Database?]( - I have a large SQL database with millions of records, and I've identified duplicate entries. What's the most efficient way to find and remove these duplicates without compromising database performance or data integrity? Are there any best practices or SQL queries that can help in this situation?" This question addresses a common database maintenance task […]   [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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -