New answers tagged

8

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

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 ...


6

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> ...


1

Below will help you declare @sql xml set @sql = '<row> <ID_Cota>162986</ID_Cota> <ID_Taxa_Plano>1000</ID_Taxa_Plano> <ID_Plano_Venda>1020</ID_Plano_Venda> <ID_Pessoa>18522</ID_Pessoa> </row>' select s.n.value('(ID_Cota)[1]', 'int') as ID_Cota ,s.n.value('(ID_Taxa_Plano)[1]', ...


1

I would be among the first to say that my xQuery is rusty but the following worked for me: WITH xml_string AS ( SELECT val = CAST('<row><ID_Cota>162986</ID_Cota><ID_Taxa_Plano>1000</ID_Taxa_Plano><ID_Taxa_Venda>1020</ID_Taxa_Venda><ID_Pessoa>18522</ID_Pessoa></row>' AS XML) ...


3

I thought I would try a solution using XML. SEDE Demo Tables DECLARE @TableA AS table ( ID integer PRIMARY KEY, Section varchar(10) NOT NULL, AccountID char(2) NOT NULL ); DECLARE @TableB AS table ( AccountID char(2) PRIMARY KEY, Name varchar(20) NOT NULL ); Data INSERT @TableA (ID, Section, AccountID) VALUES (1, 'shoes', ...



Top 50 recent answers are included