Hot answers tagged

29

The reason for the performance difference lies in how scalar expressions are handled in the execution engine. In this case, the expression of interest is: [Expr1000] = CONVERT(xml,DM_XE_SESSION_TARGETS.[target_data],0) This expression label is defined by a Compute Scalar operator (node 11 in the serial plan, node 13 in the parallel plan). Compute Scalar ...


24

There sure is a lot going on here so we will just have to see where this leads. First off, the difference in timing between SQL Server 2012 and SQL Server 2014 is due to the new cardinality estimator in SQL Server 2014. You can use a trace flag in SQL Server 2014 to force the old estimator and then you will see the same timing characteristics in SQL Server ...


18

You need to use a specific style when you expect to keep the same binary value when converting from a string. Otherwise SQL Server tries to encode the string the same way it would encode 'bob' or 'frank'. That said, your input string doesn't look correct - there is either a byte missing or one byte too many. This works fine if I drop the trailing E: SELECT ...


13

This is close but missing a few pieces. You extract into rows of TINYINT the decimal value from each <Element> in the XML (e.g. 137, 80, 78, etc), but then the FOR XML PATH('') converts them back into strings and concatenates them, leaving you with a UTF-16 encoded string of "1378078...". Converting that into VARBINARY just turns each string digit -- "...


12

Sounds like what you need is sparse columns and filtered indexes and go with option 1. These are fully supported and documented features for exactly this scenario. The SQL Server Database Engine uses the SPARSE keyword in a column definition to optimize the storage of values in that column. Therefore, when the column value is NULL for any row ...


12

Try changing you statement to match this: FOR XML PATH(''), TYPE).value('(./text())[1]','varchar(max)') This uses the .value xml method to solve your entitization problem.


12

You can shred the XML and rebuild it again using XQuery. declare @X xml = ' <Root attr1="val1" attr2="val2"> <Elements> <Element Code="1" Value="aaa" ExtraData="extra" /> <Element Code="2" Value="" ExtraData="extra" /> <Element Code="3" ExtraData="extra" /> </Elements> <ExtraData> ...


11

All you need is persistence of your XML. Use a NoSQL solution or the file system. There is no benefit in using an RDBMS, unless you want to use it instead of NoSQL or the file system.


11

Use an external process that does the HTTP work and the inserts into the database. I explicitly advise against using SQLCLR for this. Hijacking precious SQL Server workers for the boring job of waiting for HTTP results will one day impact your server severely. but the size of the XML can be quite variable (esp given that a binary would pass it to the ...


11

The exist() Method (xml Data Type) returns a bit. 1 if at least one node is found and 0 if no nodes are found (empty result set). To get the rows where neither ABC or XYZ exist you just have to compare the result of exist with 0. [myxmlcolumn].exist('for $x in /Root/Row where (($x/user[fn:upper-case(.)=(''ABC'',''XYZ'')])) ...


11

You should shred on /WEBSITES/WEBSITE in your second query as well and use a predicate against the NAME node in the values clause and then get the value from VALUE node. select S.X.value('(WEBSITE_ID/text())[1]', 'nvarchar(100)') as WEBSITE_ID, S.X.value('(WEBSITE_DETAILS/WEBSITE_DETAIL[(NAME/text())[1] eq "COST"]/VALUE/text())[1]', 'nvarchar(100)'),...


10

I bookmarked Phil Factor's blog post Normalisation and 'Anima notitia copia' today as it neatly summarises the case for and against normalising certain types of data. Run the following query on a SQL instance and see if you agree. SELECT * FROM sys.syslanguages SQL enables you to create relational databases. However, even if it smells bad, it is ...


10

That is the code from my article originally posted here: http://www.sqlservercentral.com/articles/deadlock/65658/ If you read the comments you will find a couple of alternatives that don't have the performance problems that you are experiencing, one using a modification of that original query, and the other using a variable to hold the XML before ...


10

There are a lot of facets to this, so it really depends. Does adding the XML typed columns to the same table change the speed of querying that table? More data potentially could be read (I/O), but when you say speed of querying, if you mean finding rows based on columns outside of the XML, this should usually be based on a index and so the xml should ...


10

Thanks to @Tom V for identifying this blog post that identifies the need for a temporary table. Adapting the ideas in the blog post, this now works very quickly: /*************************** shred the Event Data into readable form ***************************/ DECLARE @xml XML; SELECT TOP(1) @xml = CONVERT(xml, xet.target_data) FROM sys....


9

When working with XML in SQL Server you use the xml Data Type Methods and when shredding XML documents you typicly use the nodes() and value() methods. The XML you have here also include a number of namespaces so you have to specify the ones you need using WITH XMLNAMESPACES (Transact-SQL). The XML is quite complex so without knowing how you want the data ...


9

The declaration of singleton in the path expression of the index enforces that you can not add multiple <Number> elements but the XQuery compiler does not take that into consideration when interpreting the expression in the value() function. You have to specify [1] to make SQL Server happy. Using typed XML with a schema does not help with that either. ...


9

Presence of XML field causes most of the table data to be located on LOB_DATA pages (in fact ~90% of table pages are LOB_DATA). Merely having the XML column in the table does not have that effect. It is the presence of XML data that, under certain conditions, causes some portion of a row's data to be stored off row, on LOB_DATA pages. And while one (or ...


9

am I correct in thinking that LOB_DATA pages can cause slow scans not only because of their size, but also because SQL Server can't scan the clustered index effectively Yes, reading LOB data not stored in-row leads to random IO instead of sequential IO. The disk performance metric to use here to understand why it is fast or slow is Random Read IOPS. ...


8

There is no statistics generated on XML columns. The estimates is guessed based on the expressions used when querying the XML. Using this table: create table T(XMLCol xml not null) insert into T values('<root><item value = "1" /></root>') And this rather simple XML query: select X.N.value('@value', 'int') from T cross apply T.XMLCol....


8

There are two points of confusion here. The first is that you seem to think 'NULL' and NULL are the same; they're not. The former is a string literal, the latter merely signifies the absence of a value. The second is that you can use NULL in equality comparisons; and you cannot. Try this: SELECT CASE NULL WHEN NULL THEN 1 ELSE 0 END; Result: ---- 0 ...


8

The .value method must always return a single value so you normally wrap it in brackets and move the ordinal ( eg [1] ) to the end. Here's an example: DECLARE @xml AS XML = '<DATA> <ROW RowNumber="1"> <COLUMN Name="ProdID" Value="1234" /> <COLUMN Name="AppID" Value="20144" /> <COLUMN Name="ConsentDesc" Value="My ...


8

If you just need to replace the whole piece of XML in once, then you can do a normal UPDATE, eg something like this: UPDATE yourTable SET yourXML = '<yourNewValidXML/>' WHERE rowId = 1 If you need to edit individual attributes or elements then you can use the .modify method of the XML data-type in SQL Server to update single values. Here's a ...


8

I get the same error when I do this: DECLARE @foo VARCHAR(32) = CHAR(0); -- 0x0000 SELECT @foo FOR XML PATH, TYPE; So, find all instances of DescFuncao or DescTempoExperiencia (sorry, you don't use table aliasing, so it's impossible to tell which table they come from) where the contents contain CHAR(0), and fix them. For example: UPDATE dbo.whatever ...


8

Part 1: Proper XML Struture In order to get anything other than a flat XML layout, you need to use either FOR XML EXPLICIT mode, or nested FOR XML AUTO queries. Please see the following MSDN sections for complete details, including examples: Use EXPLICIT Mode with FOR XML Use Nested FOR XML Queries Also, you do not need to generate the <...


8

This appears to be a duplicate of this question: Setting up a central CLR stored procedure / function respository library for internal stored procs in other databases to use? However, I do not feel that either of the two answers there are adequate since they do not mention some of the more important aspects of this question. There is no obvious choice ...


7

The second statement is creating an object table. It is almost never the case that you really want to use an object table. That was something that was introduced in the 8i time frame when Oracle was making the database object oriented. While a lot of the object oriented PL/SQL enhancements have been useful, using object types in SQL is not something ...


7

The line: ... SELECT [CodFuncionario], STUFF ( ( SELECT ' / ' + CAST ( [DescFuncao] + '-' + [DescTempoExperiencia] AS VARCHAR(MAX) )... Should be: ... SELECT [CodFuncionario], STUFF (...


7

According to this MSDN article for using FOR XML in SQL Server 2000, "ELEMENTS Specifies that the columns are returned as subelements. Otherwise, they are mapped to XML attributes. This option is supported in AUTO mode only." So, your usage of ELEMENTS with the RAW option isn't supported in SQL Server 2000, but it does work with AUTO. Edit: If you were to ...


7

Use CROSS APPLY and the .nodes method of the XML data-type, eg something like this: IF OBJECT_ID('dbo.yourTable') IS NOT NULL DROP TABLE dbo.yourTable GO CREATE TABLE dbo.yourTable ( rowId INT IDENTITY PRIMARY KEY, ST_Registro_Del XML ) GO INSERT INTO dbo.yourTable ( ST_Registro_Del ) SELECT '<row> <ID_Cota>162986</ID_Cota> ...



Only top voted, non community-wiki answers of a minimum length are eligible