By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
(No column name) |
---|
Microsoft SQL Server 2014 (SP3-CU-GDR) (KB4535288) - 12.0.6372.1 (X64) Dec 12 2019 15:14:11 Copyright (c) Microsoft Corporation Express Edition (64-bit) on Windows NT 6.3 <X64> (Build 17763: ) (Hypervisor) |
8 rows affected
5 rows affected
(No column name) |
---|
0000925582 |
0000922221 |
0000925361 |
0000925461 |
0000935461 |
7463847412 |
8463847412 |
4870000925451 |
53434870000925451 |
433343434353434870000925451 |
51 |
5 |
(No column name) |
---|
0000925582 |
0000922221 |
0000925361 |
0000925461 |
0000935461 |
7463847412 |
8463847412 |
4870000925451 |
53434870000925451 |
433343434353434870000925451 |
51 |
5 |
null |
(No column name) |
---|
2855290000 |
1222290000 |
1635290000 |
1645290000 |
1645390000 |
2147483647 |
2147483648 |
1545290000784 |
15452900007843435 |
154529000078434353434343334 |
15 |
5 |
(No column name) |
---|
null |
1222290000 |
1635290000 |
1645290000 |
1645390000 |
2147483647 |
null |
null |
null |
null |
15 |
5 |
0 |
id | data | int_condition |
---|---|---|
5 | {"name":"A Name ", "update_date":"1645390000"} | 1645390000 |
6 | {"name":"An Ordinary Name", "update_date":"2147483647"} | 2147483647 |
id | data | (No column name) | (No column name) | (No column name) | (No column name) |
---|---|---|---|---|---|
1 | {"name":"Cole", "update_date":"2855290000"} | 43 | 32 | 10 | 2855290000 |
2 | {"name":"Peter", "update_date":"1222290000"} | 44 | 33 | 10 | 1222290000 |
3 | {"name":"A Very Long Silly Name", "update_date":"1635290000"} | 61 | 50 | 10 | 1635290000 |
4 | {"name":"A Long Name", "update_date":"1645290000"} | 50 | 39 | 10 | 1645290000 |
5 | {"name":"A Name ", "update_date":"1645390000"} | 46 | 35 | 10 | 1645390000 |
6 | {"name":"An Ordinary Name", "update_date":"2147483647"} | 55 | 44 | 10 | 2147483647 |
7 | {"name":"Some Other Name", "update_date":"2147483648"} | 54 | 43 | 10 | 2147483648 |
8 | {"name":"A Name Yet Again", "update_date":"1545290000784"} | 58 | 44 | 13 | 1545290000784 |
9 | {"name":"A Name Yet Again", "update_date":"15452900007843435"} | 62 | 44 | 17 | 15452900007843435 |
10 | {"name":"A Name Yet Again", "update_date":"154529000078434353434343334"} | 72 | 44 | 27 | null |
11 | {"name":"A Short String", "update_date":"15"} | 45 | 42 | 2 | 15 |
12 | {"name":"Extremely Short", "update_date":"5"} | 45 | 43 | 1 | 5 |
13 | {"name":"", "update_date":""} | 29 | 28 | 0 | 0 |
id | data | (No column name) | (No column name) | (No column name) | (No column name) |
---|---|---|---|---|---|
1 | {"name":"Cole", "update_date":"2855290000"} | 43 | 32 | 10 | 2855290000 |
5 | {"name":"A Name ", "update_date":"1645390000"} | 46 | 35 | 10 | 1645390000 |
6 | {"name":"An Ordinary Name", "update_date":"2147483647"} | 55 | 44 | 10 | 2147483647 |
7 | {"name":"Some Other Name", "update_date":"2147483648"} | 54 | 43 | 10 | 2147483648 |
8 | {"name":"A Name Yet Again", "update_date":"1545290000784"} | 58 | 44 | 13 | 1545290000784 |
9 | {"name":"A Name Yet Again", "update_date":"15452900007843435"} | 62 | 44 | 17 | 15452900007843435 |
id | data |
---|---|
1 | {"name":"Cole", "update_date":"2855290000"} |
4 | {"name":"A Long Name", "update_date":"1645290000"} |
5 | {"name":"A Name ", "update_date":"1645390000"} |
6 | {"name":"An Ordinary Name", "update_date":"2147483647"} |
7 | {"name":"Some Other Name", "update_date":"2147483648"} |
8 | {"name":"A Name Yet Again", "update_date":"1545290000784"} |
9 | {"name":"A Name Yet Again", "update_date":"15452900007843435"} |
10 | {"name":"A Name Yet Again", "update_date":"154529000078434353434343334"} |
(No column name) |
---|
null |
2008-09-24 21:00:00.000 |
2021-10-26 23:13:20.000 |
2022-02-19 17:00:00.000 |
2022-02-20 20:46:40.000 |
2038-01-19 03:14:07.000 |
null |
null |
null |
null |
1970-01-01 00:00:15.000 |
null |
null |
id | data | int_condition |
---|---|---|
5 | {"name":"A Name ", "update_date":"1645390000"} | 1645390000 |
6 | {"name":"An Ordinary Name", "update_date":"2147483647"} | 2147483647 |
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
Rows | Executes | StmtText | StmtId | NodeId | Parent | PhysicalOp | LogicalOp | Argument | DefinedValues | EstimateRows | EstimateIO | EstimateCPU | AvgRowSize | TotalSubtreeCost | OutputList | Warnings | Type | Parallel | EstimateExecutions |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
2 | 1 | SELECT id, data, TRY_CAST ( REVERSE ( SUBSTRING ( REVERSE(data), CHARINDEX('"', REVERSE(data)) + 1, CHARINDEX('"', REVERSE(data), 5) - CHARINDEX('"', REVERSE(data)) -1 ) ) AS INT) AS int_condition FROM test WHERE TRY_CAST ( REVERSE ( SUBSTRING ( REVERSE(data), CHARINDEX('"', REVERSE(data)) + 1, CHARINDEX('"', REVERSE(data), 5) - CHARINDEX('"', REVERSE(data)) -1 ) ) AS INT) > 1645290000 |
1 | 1 | 0 | null | null | null | null | 3.9 | null | null | null | 0.00329903 | null | null | SELECT | False | null |
0 | 0 | |--Compute Scalar(DEFINE:([Expr1003]=TRY_CAST(reverse(substring(reverse([fiddle_d76e583f0e7f45388aa5fb196a96e617].[dbo].[test].[data]),charindex('"',reverse([fiddle_d76e583f0e7f45388aa5fb196a96e617].[dbo].[test].[data]))+(1),charindex('"',reverse([fiddle_d76e583f0e7f45388aa5fb196a96e617].[dbo].[test].[data]),(5))-charindex('"',reverse([fiddle_d76e583f0e7f45388aa5fb196a96e617].[dbo].[test].[data]))-(1))) AS int))) | 1 | 2 | 1 | Compute Scalar | Compute Scalar | DEFINE:([Expr1003]=TRY_CAST(reverse(substring(reverse([fiddle_d76e583f0e7f45388aa5fb196a96e617].[dbo].[test].[data]),charindex('"',reverse([fiddle_d76e583f0e7f45388aa5fb196a96e617].[dbo].[test].[data]))+(1),charindex('"',reverse([fiddle_d76e583f0e7f45388aa5fb196a96e617].[dbo].[test].[data]),(5))-charindex('"',reverse([fiddle_d76e583f0e7f45388aa5fb196a96e617].[dbo].[test].[data]))-(1))) AS int)) | [Expr1003]=TRY_CAST(reverse(substring(reverse([fiddle_d76e583f0e7f45388aa5fb196a96e617].[dbo].[test].[data]),charindex('"',reverse([fiddle_d76e583f0e7f45388aa5fb196a96e617].[dbo].[test].[data]))+(1),charindex('"',reverse([fiddle_d76e583f0e7f45388aa5fb196a96e617].[dbo].[test].[data]),(5))-charindex('"',reverse([fiddle_d76e583f0e7f45388aa5fb196a96e617].[dbo].[test].[data]))-(1))) AS int) | 3.9 | 0 | 3.9E-07 | 4043 | 0.00329903 | [fiddle_d76e583f0e7f45388aa5fb196a96e617].[dbo].[test].[id], [fiddle_d76e583f0e7f45388aa5fb196a96e617].[dbo].[test].[data], [Expr1003] | null | PLAN_ROW | False | 1 |
2 | 1 | |--Filter(WHERE:(TRY_CAST(reverse(substring(reverse([fiddle_d76e583f0e7f45388aa5fb196a96e617].[dbo].[test].[data]),charindex('"',reverse([fiddle_d76e583f0e7f45388aa5fb196a96e617].[dbo].[test].[data]))+(1),charindex('"',reverse([fiddle_d76e583f0e7f45388aa5fb196a96e617].[dbo].[test].[data]),(5))-charindex('"',reverse([fiddle_d76e583f0e7f45388aa5fb196a96e617].[dbo].[test].[data]))-(1))) AS int)>(1645290000))) | 1 | 3 | 2 | Filter | Filter | WHERE:(TRY_CAST(reverse(substring(reverse([fiddle_d76e583f0e7f45388aa5fb196a96e617].[dbo].[test].[data]),charindex('"',reverse([fiddle_d76e583f0e7f45388aa5fb196a96e617].[dbo].[test].[data]))+(1),charindex('"',reverse([fiddle_d76e583f0e7f45388aa5fb196a96e617].[dbo].[test].[data]),(5))-charindex('"',reverse([fiddle_d76e583f0e7f45388aa5fb196a96e617].[dbo].[test].[data]))-(1))) AS int)>(1645290000)) | null | 3.9 | 0 | 2.34E-06 | 4039 | 0.00329864 | [fiddle_d76e583f0e7f45388aa5fb196a96e617].[dbo].[test].[id], [fiddle_d76e583f0e7f45388aa5fb196a96e617].[dbo].[test].[data] | null | PLAN_ROW | False | 1 |
13 | 1 | |--Table Scan(OBJECT:([fiddle_d76e583f0e7f45388aa5fb196a96e617].[dbo].[test])) | 1 | 4 | 3 | Table Scan | Table Scan | OBJECT:([fiddle_d76e583f0e7f45388aa5fb196a96e617].[dbo].[test]) | [fiddle_d76e583f0e7f45388aa5fb196a96e617].[dbo].[test].[id], [fiddle_d76e583f0e7f45388aa5fb196a96e617].[dbo].[test].[data] | 13 | 0.0032035 | 9.28E-05 | 4039 | 0.0032963 | [fiddle_d76e583f0e7f45388aa5fb196a96e617].[dbo].[test].[id], [fiddle_d76e583f0e7f45388aa5fb196a96e617].[dbo].[test].[data] | null | PLAN_ROW | False | 1 |
Table 'test'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
id | data |
---|---|
1 | {"name":"Cole", "update_date":"2855290000"} |
4 | {"name":"A Long Name", "update_date":"1645290000"} |
5 | {"name":"A Name ", "update_date":"1645390000"} |
6 | {"name":"An Ordinary Name", "update_date":"2147483647"} |
7 | {"name":"Some Other Name", "update_date":"2147483648"} |
8 | {"name":"A Name Yet Again", "update_date":"1545290000784"} |
9 | {"name":"A Name Yet Again", "update_date":"15452900007843435"} |
10 | {"name":"A Name Yet Again", "update_date":"154529000078434353434343334"} |
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
Rows | Executes | StmtText | StmtId | NodeId | Parent | PhysicalOp | LogicalOp | Argument | DefinedValues | EstimateRows | EstimateIO | EstimateCPU | AvgRowSize | TotalSubtreeCost | OutputList | Warnings | Type | Parallel | EstimateExecutions |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
8 | 1 | SELECT DTO.* FROM dbo.test AS DTO CROSS APPLY ( SELECT TRY_CONVERT(xml, REPLACE( REPLACE( REPLACE( REPLACE(DTO.[DATA], '"name":', 'name='), ', "update_date":', ' update_date='), '{', '<r '), '}', '/>')) ) AS X (x) WHERE 1 = X.x.exist('r[1][@update_date ge 1645290000]') |
1 | 1 | 0 | null | null | null | null | 11.7 | null | null | null | 11.76689 | null | null | SELECT | False | null |
8 | 1 | |--Nested Loops(Left Semi Join, OUTER REFERENCES:([DTO].[data])) | 1 | 2 | 1 | Nested Loops | Left Semi Join | OUTER REFERENCES:([DTO].[data]) | null | 11.7 | 0 | 4.8906E-05 | 90 | 11.76689 | [DTO].[id], [DTO].[data] | null | PLAN_ROW | False | 1 |
13 | 1 | |--Filter(WHERE:([Expr1013] IS NOT NULL)) | 1 | 3 | 2 | Filter | Filter | WHERE:([Expr1013] IS NOT NULL) | null | 11.7 | 0 | 6.24E-06 | 90 | 0.00330384 | [DTO].[id], [DTO].[data] | null | PLAN_ROW | False | 1 |
0 | 0 | | |--Compute Scalar(DEFINE:([Expr1013]=TRY_CAST(replace(replace(replace(replace([fiddle_d76e583f0e7f45388aa5fb196a96e617].[dbo].[test].[data] as [DTO].[data],'"name":',CONVERT_IMPLICIT(varchar(max),'name=',0)),', "update_date":',CONVERT_IMPLICIT(varchar(max),' update_date=',0)),'{',CONVERT_IMPLICIT(varchar(max),'<r ',0)),'}',CONVERT_IMPLICIT(varchar(max),'/>',0)) AS xml))) | 1 | 4 | 3 | Compute Scalar | Compute Scalar | DEFINE:([Expr1013]=TRY_CAST(replace(replace(replace(replace([fiddle_d76e583f0e7f45388aa5fb196a96e617].[dbo].[test].[data] as [DTO].[data],'"name":',CONVERT_IMPLICIT(varchar(max),'name=',0)),', "update_date":',CONVERT_IMPLICIT(varchar(max),' update_date=',0)),'{',CONVERT_IMPLICIT(varchar(max),'<r ',0)),'}',CONVERT_IMPLICIT(varchar(max),'/>',0)) AS xml)) | [Expr1013]=TRY_CAST(replace(replace(replace(replace([fiddle_d76e583f0e7f45388aa5fb196a96e617].[dbo].[test].[data] as [DTO].[data],'"name":',CONVERT_IMPLICIT(varchar(max),'name=',0)),', "update_date":',CONVERT_IMPLICIT(varchar(max),' update_date=',0)),'{',CONVERT_IMPLICIT(varchar(max),'<r ',0)),'}',CONVERT_IMPLICIT(varchar(max),'/>',0)) AS xml) | 13 | 0 | 1.3E-06 | 4116 | 0.0032976 | [DTO].[id], [DTO].[data], [Expr1013] | null | PLAN_ROW | False | 1 |
13 | 1 | | |--Table Scan(OBJECT:([fiddle_d76e583f0e7f45388aa5fb196a96e617].[dbo].[test] AS [DTO])) | 1 | 5 | 4 | Table Scan | Table Scan | OBJECT:([fiddle_d76e583f0e7f45388aa5fb196a96e617].[dbo].[test] AS [DTO]) | [DTO].[id], [DTO].[data] | 13 | 0.003125 | 0.0001713 | 90 | 0.0032963 | [DTO].[id], [DTO].[data] | null | PLAN_ROW | False | 1 |
8 | 13 | |--Nested Loops(Left Semi Join, OUTER REFERENCES:(XML Reader with XPath filter.[id], [Expr1015])) | 1 | 10 | 2 | Nested Loops | Left Semi Join | OUTER REFERENCES:(XML Reader with XPath filter.[id], [Expr1015]) | null | 1 | 0 | 4.18E-06 | 9 | 11.76354 | null | null | PLAN_ROW | False | 11.7 |
13 | 13 | |--Top(TOP EXPRESSION:((1))) | 1 | 11 | 10 | Top | Top | TOP EXPRESSION:((1)) | null | 1 | 0 | 1E-07 | 913 | 0.0587365 | XML Reader with XPath filter.[id], [Expr1015] | null | PLAN_ROW | False | 11.7 |
0 | 0 | | |--Compute Scalar(DEFINE:([Expr1007]=0x58, [Expr1015]=getdescendantlimit(XML Reader with XPath filter.[id]))) | 1 | 12 | 11 | Compute Scalar | Compute Scalar | DEFINE:([Expr1007]=0x58, [Expr1015]=getdescendantlimit(XML Reader with XPath filter.[id])) | [Expr1007]=0x58, [Expr1015]=getdescendantlimit(XML Reader with XPath filter.[id]) | 1 | 0 | 2E-05 | 1365 | 0.05873533 | XML Reader with XPath filter.[id], [Expr1007], [Expr1015] | null | PLAN_ROW | False | 11.7 |
13 | 13 | | |--Table-valued function | 1 | 13 | 12 | Table-valued function | Table-valued function | null | null | 1 | 0 | 1.004 | 461 | 0.05873416 | XML Reader with XPath filter.[id] | null | PLAN_ROW | False | 11.7 |
8 | 13 | |--Filter(WHERE:(XML Reader with XPath filter.[id]=getancestor(XML Reader with XPath filter.[id],(1)) AND xsd_cast(XML Reader with XPath filter.[value],XML Reader with XPath filter.[lvalue],XML Reader with XPath filter.[tid],(18),(75),(0)) IS NOT NULL AND xsd_cast(XML Reader with XPath filter.[value],XML Reader with XPath filter.[lvalue],XML Reader with XPath filter.[tid],(18),(75),(0))>=1.64529e+009)) | 1 | 24 | 10 | Filter | Filter | WHERE:(XML Reader with XPath filter.[id]=getancestor(XML Reader with XPath filter.[id],(1)) AND xsd_cast(XML Reader with XPath filter.[value],XML Reader with XPath filter.[lvalue],XML Reader with XPath filter.[tid],(18),(75),(0)) IS NOT NULL AND xsd_cast(XML Reader with XPath filter.[value],XML Reader with XPath filter.[lvalue],XML Reader with XPath filter.[tid],(18),(75),(0))>=1.64529e+009) | null | 1 | 0 | 4.644E-05 | 9 | 11.70475 | null | null | PLAN_ROW | False | 11.7 |
13 | 13 | |--Table-valued function | 1 | 25 | 24 | Table-valued function | Table-valued function | null | null | 18 | 0 | 1.00036 | 8501 | 11.70421 | XML Reader with XPath filter.[id], XML Reader with XPath filter.[tid], XML Reader with XPath filter.[value], XML Reader with XPath filter.[lvalue] | null | PLAN_ROW | False | 11.7 |
Table 'test'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.