Custom Statistics Distributions in SQL Server 2022 (2024-04-01)

SQLServerCentral Newsletter for April 1, 2024 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [Custom Statistics Distributions in SQL Server 2022]( - [Choosing Between SSIS vs ADF: What You Need to Know]( - [From the SQL Server Central Blogs - The PASS Data Community Summit 2024 Call for Speakers]( - [From the SQL Server Central Blogs - Interview around DevOps, Data, AI and Microsoft Fabric]( - [Pro T-SQL 2022: Toward Speed, Scalability, and Standardization for SQL Server Developers]( Question of the Day - [Wargames]( The Voice of the DBA  CosmosDB APIs Inside SQL Server 2024 CosmosDB has been a great data platform in the Azure cloud that helps companies deal with disparate types of data. The CosmosDB APIs include those for MongoDB, PostgreSQL, Cassandra, and Gremlin. These wire-level protocols let you work in a way that is compatible with those systems for storing data. That's coming to SQL Server. For the last few years the engineers at Microsoft have been working to separate query processing from storage, and they've used this to build products like Hyperscale. That work is being added to SQL Server 2024, so that you'll be able to add a separate storage engine that doesn't use the same RDBMS/extent/page structure, but instead is optimized to let you store documents, JSON, and other structures. In addition, there will be new query processors that can run queries from your MongoDB, Cassandra, and Gremlin graph clients. Like CosmosDB, however, you can't have relational data and say, MongoDB data in the same database. You'll need a separate database, and at least initially, you won't be able to make cross-database queries. However, you will be able to store your relational and NoSQL data inside the same instance. This will make life easier for developers, who will be able to use one server for all their data. Of course, this is an April Fools joke and there are no NoSQL enhancements coming to SQL Server. Not for now. I'm not even sure if SQL Server 2024 is a thing. Hopefully, you're dreaming and smiling. Maybe the engineers at Microsoft are as well. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [SQLServerCentral Article]( [Custom Statistics Distributions in SQL Server 2022]( Steve Jones - SSC Editor from SQLServerCentral You can create custom statistics distributions, sampling, and histograms in SQL Server 2022 with a new trace flag [External Article]( [Choosing Between SSIS vs ADF: What You Need to Know]( Additional Articles from MSSQLTips.com In this article, we look at how you might go about choosing whether to use SSIS or ADF for your next ETL/ELT project. [Blog Post]( From the SQL Server Central Blogs - [The PASS Data Community Summit 2024 Call for Speakers]( Steve Jones - SSC Editor from The Voice of the DBA The PASS Data Community Summit call for speakers and volunteers is open. You have until April 10 to submit something, and you can do that here: Data Community Summit... [Blog Post]( From the SQL Server Central Blogs - [Interview around DevOps, Data, AI and Microsoft Fabric]( HamishWatson from The Hybrid DBA's Blog A few weeks ago I did an interiview with Nagaraj Venkatesan – a good friend of mine who works for Microsoft Singapore. You can watch video below – I... [Pro T-SQL 2022: Toward Speed, Scalability, and Standardization for SQL Server Developers]( Site Owners from SQLServerCentral Learn how to write and design simple and efficient T-SQL code. This is a hands-on book that teaches you how to write better T-SQL with examples and straightforward explanations.   Question of the Day Today's question (by Steve Jones - SSC Editor):  Wargames In the movie Wargames, what game does the high school student get the computer to play in order to learn about no-win scenarios? 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 Cloned Database Size I have a small test sandbox database on an instance with default master, model, msdb, and tempdb settings. The database has these files: [database file properties] I now run this command: DBCC CLONEDATABASE(sandbox, sandbox_clone); GO When I examine the database file properties, what do they show? Answer: The file growth and size settings match model, not the original database Explanation: The growth sizes of the clone are taken from the model database. Ref: DBCC CLONEDATABASE - [ [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 [need help in optimizing long running UPDATE statements]( - Hi All, Need help in tuning below UPDATE statements. Have this long running 3 UPDATE statements doing lot of reads. we r trying to refresh prod to one of sub-prod env. Will share the plans in some time. Is there a good way to optimize below queries? Few i can think of is, please provide […] SQL Server 2012 - T-SQL [Pivot 1 row to multiple columns using T SQL]( - Hello. I have data in a table that I want to pivot from 1 row to multiple columns. I only want the colums with dx to pivot if there is a value in them. Thanks. This is a sample of what I have: MemberNbr     dx       […] SQL Server 2019 - Administration [Permission related question]( - I have a view in a database A which is created from a table in database B. This view has PII data and I want to restrict users from running select against it. But those users are part of AD group (We don't create individual accts) and they must have data reader permission on database […] [SSRS Report Job failing in first attempt]( - Today I can see my job running SSRS Report using stored procedure inside and failing in first attempt with below error , but in 2nd attempt the job runs fine. Please help me to solve this issue and what could be the reason behis this failure. Microsoft.ReportingServices.ReportProcessing.ProcessingAbortedException: An error has occurred during report processing. ---> […] [Best way to compare tables from SSMS and Oracle]( - Hi, I need to compare tables from SSMS against Oracle. what is the best way to compare tables from different databases and different servers. Any automation tool or any approach to ease the work? SQL Server 2019 - Development [is there an ssrs feature that plays nice with tablix's and just one footer?]( - Hi i inherited some multi document (eg print all invoices in the invoice number from and to parameter range i am providing) crystal reports for conversion to ssrs 2019 A theme i see is repeating tablix lines till they fill up pages and only printing footer like info at the BOTTOM of the last page […] [Help with date range subquery]( - I have two large tables. One table(Table A) contains trouble tickets that are created when applications fail. The other table(Table B) contains warning notifications that are emailed whenever there may be an issue with an application. I need to write an efficient query that will select fields from Table A where a NotificationDate (Table B) […] [can i query roles and users from my various tabular databases]( - hi we've begun recording apps x roles at my company. Currently the apps are erp's but i think roles attached to tabular models would blend nicely, especially if i can find a way to also expand the groups assigned to certain roles. i'd feed the data daily. from what i've seen AD has some kind […] [create XML]( - Hello.. I want to create an XML file with the below structure. I have all the elements in respective tables. And I am using FOR XML AUTO , ELEMENTS to generate the file. My only issue is that I am not able to generate the incremental recordIdentifier tag that increases in value under each node […] Powershell [unix timestamp to datetime]( - I have a sql table where the app is writing. A unix timestamp value to the field. I would like to iterate thru the table and pull from a specific datetime but not sure how to code that in PS that also would export to csv file. I just pulled the latest record from table […] Anything that is NOT about SQL! [how can i find just my posts in sql server central?]( - Hi as in other forums but possibly worse, there doesnt seem to be an easy way to show just my posts in this forum. If i search on my first name i see only a random post from a week or two ago but there are quite a few posts since then and of course […] SQL Server 2022 - Administration [OLDEDB drivers]( - How sql server OLDEBB or ODBC etc drivers intalled? Are they installed from SSMS installation on the client? Recently I have a new computer I installed SSMS 19.X version, and VS 2022, in visual studio 2022 SSIS project, when I create a connection manager the SQL native client driver is no longer available, I understand […] [SQL Agent Failing Backup using Domain Account]( - Good Day, I am hoping you can help with a frustrating issue i have encountered. We have recently setup a domain joined SQL 2022 Cluster using HYPER-V failover. All is working well with the databases and the failover. The issue We have encountered is with our SQL Agent jobs. We have a few setup and […] [Always on availability group vs Always on Failover cluster instance question]( - Hello, we are currently running a WSFC cluster two node setup (nodes are on two different locations) and we have a few instances (roles) on this cluster. We are using SAN as a storage solution. Because we are getting new hardware we explored a few options for HA and DR. We need to provide 0 […] SQL Server 2022 - Development [[CA IDMS ODBC Driver]Restricted data type attribute violation]( - I moving data from mainframe to SQL server. Error: Unable to copy contents of source table to destination (Insert/SelectFull) Unable to get record from table (table_3). STAT:2 ERR:-2147467259 SQLSTATE:S1000 Microsoft OLE DB provider for ODBC Drivers : [CA IDMS ODBC Driver]Restricted data type attribute violation using CA IDMS ODBC driver to read on mainframe […]   [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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -