Stairway to XML: Level 3 - Working with Typed XML (2020-11-11)

 SQLServerCentral Newsletter for November 11, 2020 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [Stairway to XML: Level 3 - Working with Typed XML]( - [Bridging the divide between Data Management and DevOps]( - [SQL Server Clustered Index Fragmentation on GUID Columns]( - [Free eBook: Understanding SQL Server Concurrency]( - [From the SQL Server Central Blogs - External tables vs T-SQL views on files in a data lake]( Question of the Day - [R function kronecker()]( The Voice of the DBA  Daily Coping Tip Change your normal routine today and notice how you feel I also have [a thread at SQLServerCentral]( dealing with coping mechanisms and resources. Feel free to participate. For many of you out there working in a new way, I'm including a thought of the day on how to cope in this challenging time from [The Action for Happiness Coping Calendar](. My items will be on [my blog](, feel free to share yours. The Pace of Data Platform Change I was watching [Vicky Harp in the 2020 October GroupBy]( conference recently talking about her challenges of working with the SQL Server platform as it's grown. It's a good keynote, with Vicky noting that she started with SQL Server 2000 and the journey to today is incredible. There's a great view of her world at the 10:00 mark (after the start). She had this quote, which I found really thoughtful. This is something I think is true and something that many of us don't like. "Usually growth comes at the expense of the previous comfort of safety." - [Josh Waitzkin, The Art of Learning](~:text=%25E2%2580%259CThe%2520key%2520to%2520pursuing%2520excellence%2520is%2520to%2520embrace%2520an%2520organic%2Cof%2520previous%2520comfort%2520or%2520safety.%25E2%2580%259D). What's your response? Run, fight? Embrace change? Resist doing anything different? I think many of us want to think that we easily embrace change, but think about the last time someone wanted to change something at work? Reorg, new protocol, etc. Did you resist and think it was silly or go along and give it a chance? For many people, it's the former. To be fair, it is for me as well, and I think many people that have some success in their career often want to stick with the things they are experienced in. That's not necessarily a problem, but it is worth investigating and embracing some new things, just to see if they might be better. The data platform is certainly one of those technologies in my life that has changed dramatically, and the pace is sometimes overwhelming. At this point, it's hard to keep up, and hard to understand sometimes if new tech is better or worse. I've started to try and assume there is some good reason why Microsoft makes some changes, and then experiment, test, and evaluate the tech. Not just once when something is released or I encounter it, but by also watching what others do and then learning where they've had success or failure. ADF is one of those areas where I initially dismissed it as a poor port of SSIS, but I've come to appreciate some of the ways in which this is an improvement to many flows, especially with hybrid workflows. The data platform is an exciting place to work these days, and I hope you embrace some of the changes and see where a new technology might improve your environment. Of course, lots of traditional features out there work very well, and it's not worth changing just to change. Make sure there is value and an improvement in some way, beyond you just enjoying working with something shiny and new. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [Stairway to XML]( [Stairway to XML: Level 3 - Working with Typed XML]( Rob Sheldon from SQLServerCentral.com You can enforce the validation of an XML data type, variable or column by associating it with an XML Schema Collection. SQL Server validates a typed XML value against the rules defined in the schema collection so that INSERT or UPDATE operations will succeed only if the value being inserted or updated is valid as per the rules defined in the Schema Collection. [Bridging the divide between Data Management and DevOps]( Additional Articles from Redgate The speed, flexibility and collaboration of DevOps can be difficult to achieve against the rigid schemas, manual processes, and silos in the world of data management. Join Microsoft Data Platform MVP, Grant Fritchey to discover the key strategies you can implement to bridge the divide between data management and DevOps in your organization. [SQL Server Clustered Index Fragmentation on GUID Columns]( Additional Articles from MSSQLTips.com In this article we take a look at using GUID columns in your SQL Server database tables and the impact GUID columns may have on indexing. [Free eBook: Understanding SQL Server Concurrency]( Press Release from Redgate When you can’t get to your data because another application has it locked, a thorough knowledge of SQL Server concurrency will give you the confidence to decide what to do. From the SQL Server Central Blogs - [External tables vs T-SQL views on files in a data lake]( James Serra from James Serra's Blog A question that I have been hearing recently from customers using Azure Synapse Analytics (the public preview version) is what is the difference between using an external table versus...   Question of the Day Today's question (by tomaz.kastrun):  R function kronecker() What does the following script R return (the function is part of the base R Package): kronecker(1:2,t(1:3))  Think you know the answer? [Click here](, and find out if you are right.    Yesterday's Question of the Day (by Junior Galvão - MVP) Self-numbered primary key creation I have a database, called Stock, and I have the need to create a new table, called Orders, according to the code below: -- Accessing the Stock Database -- Use Stocks Go -- Creating the Orders Table -- Create Table Orders   (ID Int IDENTITY(1,1) Not Null Primary Key,    CustomerID Int Not Null,    SalesPersonID Varchar(30) Not Null,    Quantity smallint Not Null,    NumericValue numeric(18, 2) Not Null,    Today Date Not Null) Go Right after creating the table, you choose to use some recursive CTEs the amount of 100,000 rows of records in the Orders table: ;WITH E1(N)    AS (SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))E0(N)),       E10(N)   AS (SELECT 1 FROM E1 a, E1 b, E1 c, E1 d, E1 e, E1 f, E1 g, E1 h, E1 i, E1 j),       TallY(N) AS (SELECT TOP(@RowCount) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E10) INSERT INTO dbo.Orders (CustomerID, SalesPersonID, Quantity, NumericValue, Today)  SELECT  CustomerID     = @RowCount+1-t.N         ,SalesPersonID  = SUBSTRING(ca.Texto,ABS(CHECKSUM(NEWID())%126)+1,2)                         + SUBSTRING(ca.Texto,ABS(CHECKSUM(NEWID())%124)+1,4)                         + SUBSTRING(ca.Texto,ABS(CHECKSUM(NEWID())%124)+1,4)         ,Quantity       = ABS(CHECKSUM(NEWID())%1000)         ,NumericValue   = RAND(CHECKSUM(NEWID()))*100+5         ,Today          = DATEADD(dd,ABS(CHECKSUM(NEWID())%1000),GETDATE())    FROM TallY t   CROSS APPLY (SELECT '0123456789@ABCDEFGHIJKLMNOPQRSTUVWXYZ_abcdefghijklmnopqrstuvwxyzŽŸ¡ÀÁÂÃÄÅÆÇÈÉÊËÌÍÎÏÐÑÒÓÔÕÖÙÚÛÜÝà áâãäåæçèéêëìíîïðñòóôõöùúûüýÿ')ca(Texto) SELECT ID, CustomerID, SalesPersonID, Quantity, NumericValue, Today FROM Orders Go Knowing that from the moment we create a new table containing a primary key, Microsoft SQL Server adds a Statistic, a feature that aims to help us get the dataset through the Select command faster. What will happen to the Histogram, its column structure that indicates how often data will be created?  Answer: 2 - Your column structure that indicates the frequency of data will be created but at this time without the data bands that represent the frequency of data queried. Explanation: The correct answer is number 2. The Histogram component, which relates to the Statistics of tables and indexes, will have its frequency distribution of data queried from the first effective manipulation performed to our Orders table accompanied by the Where clause declared for one or more columns. To understand and simulate this behavior, run the following shared code blocks: -- Querying the Statistics Metadata -- -- Consultando os Metadados das Estatísticas -- Select OBJECT_NAME(s.object_id) As Tabela, s.name As Estatistica, s.auto_created As Tipo, c.name As Coluna from sys.stats_columns sc Inner Join sys.columns c ON sc.object_id = c.object_id AND sc.column_id = c.column_id Inner Join sys.stats s ON sc.object_id = s.object_id AND sc.stats_id = s.stats_id Where OBJECT_NAME(s.object_id) = 'Orders' Go -- Introducing the Histogram -- DBCC SHOW_STATISTICS("Pedidos", [type here the name of the statistic created for primary key/) With Histogram Go And now, I leave it to you to process the Select command by using the Where clause, so that the Histogram can create the data distribution range. References: - - -  [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 [SQL input parameters are defaulting to nvarchar causing full table scans]( - When our applications are submitting SQL via ADO.Net, with input parameters, the parameter definitions are defaulting to nvarchar. If that field is defined as a varchar and an index on the database table, the index is not used resulting in a scan instead of a seek. We are converting from Teradata to SQL Server so […] [Need to learn about ALWAYS ON High Availability Group from scratch - any advice?]( - Please advise me what resources I can use to learn rather deeply about Always ON if I never have learned or seen it before but need to be up to some knowledge within a couple of days (having started in a new Sr DBA role...) . What are the key pre-requisite knowledge that I must […] SQL Server 2017 - Development [SQL - How to compare data of a column while iterating row by row and insert new]( - I have the below data in an SQL Server(2017) table POS_ID Term Code Status IsActive TR 101 In Progress true TR 102 In Progress true TR 103 In Progress true CA 151 In Progress true CA 152 In Progress true DA 161 In Progress true The requirement is I want to iterate each row and […] SQL Server 2016 - Administration [PARALLEL_REDO_WORKER_WAIT_WORK.: Not sure how relevant this may be..]( - Attached file shows the top waits... PARALLEL_REDO_WORKER_WAIT_WORK.: Not sure how relevant this may be for this morning Latency reported by multiple users accessing a number of web pages and apps that connect to this server.. just seeing it first time But this wait type has been predominantly much higher than all other waits. Comparing to […] [Distributed Availability group]( - Is there a guideline or does anyone have any step by step process on removing Distributed Availability group between 2 servers properly? I am trying to beak it a part because the secondary server (forwarder) is old and ready to be decommissioned. This is how servers are setup: DAG between SQL1P (forwarder) and SQL2P (primary) […] [MAXDOP Setting]( - When upgrading and creating my new SQL server farm on 2016 I researched things to set on the server, one being MAXDOP setting. From reading up about the setting I have set it at 8 on a server that has 8 logical cores. The server is a VM and I believe it has a single […] SQL Server 2016 - Development and T-SQL [Is Index Fragmentation Still Relevant in a Cloud Base/Virtual SQL Server Setup]( - We moved our mission critical DB to the software vendors private cloud. This meant we (my company) no longer handle the setup and maintenance of the SQL server hosting the DB. I don't know how the vendor has it setup (our access is limited) but I'm certain everything is virtualized, no more separate physical drives […] Administration - SQL Server 2014 [Data type Date]( - Previously we had some issues with deadlocks caused by transaction that happen so fast that the current timestamp column is not accurate enough to prevent a collision. To prevent such an occurrence we need to change the data type used by the timestamp column in order to make it more precise, both for existing tables […] [Can we know the detailed information of a table ?]( - we can use sp_spaceused to know how many space reservred by a table and know how many spaces for data space and index space, but if there is a approach to know the following informations of table , thanks! how can we know the start page and end page used for storing data for a […] SQL Server 2012 - T-SQL [Updating from previous row until next value is found]( - I am trying to create an update that will update a value from previous row until the next none null value is found then update with that value. For instance in my table there are 2 columns SeqNum and AcctNum. The SeqNum will always start with 0000003 and the AcctNum will have a value and […] SQL Server 2019 - Administration [SQL 2012 AlwaysON migration]( - We have a SQL AlwaysON setup on windows 2012 R2 / SQL 2012 (to latest patch). We want to migrate to 2019. Can we add to the cluster and to the AlwaysON a Node3 and Node4 (Windows 2019 / SQL 2019) Then failover to Node3 and REMOVE Node1 and Node2. Will that migration work? Has […] [Raising errors between 1 and 13000]( - Hello, I've been struggling with a task which requires me to raise a number of errors for testing purposes. Of course the time consuming errors are between 1 and 13000. I've managed to do them, but I need to do it several times on multiple test instances and I wanted to ask if there's a […] [Restore - what happens after 100%]( - Just curious. I restore databases very often as I am a developer. Some of them has a size, that means that it takes some time. I use to backup with the same version as I restore from. I am aware, that if i restore a SQL 2012 with a SQL 2019, the converting takes some […] SQL Server 2019 - Development [Trying to use rowversion (Timestamp) in SSIS as a for a high watermark]( - Trying to use rowversion (Timestamp) in SSIS as a for a high watermark to extract changed data Failure 1: try to store the Max Rowversion in a variable Varbinary(8) to Variable type Byte Get me this error. " failed with the following error: "Input string was not in a correct format.". Possible failure reasons: Problems […] Amazon AWS and other cloud vendors [AWS Reserved Instance for Old Servers]( - We have a couple of SQL server instances(i2 and r3) from about 5 years ago. We are looking to reserve the instances now without upgrading to new, but I don't see any of these instances listed in the "Reserved Instance" section. In other words, the instances are not there to be purchased. I got to […]   [RSS Feed]([Twitter]( 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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -