Advertisement
Guest User

CrazyXMLIndexTest

a guest
Mar 29th, 2021
126
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. create table dbo.T
  2. (
  3.   ID int identity primary key,
  4.   X xml not null
  5. );
  6.  
  7. go
  8.  
  9. -- Add some XML stuff
  10. insert into dbo.T(X)
  11. select X.X
  12. from sys.all_columns as C
  13.   cross apply (select C.* for xml path('columns'), type) as X(X);
  14.  
  15. go
  16.  
  17. -- Primary XML index and a secondary Path index
  18. create primary xml index IX_T on dbo.T(X);
  19. create xml index IX_T_P on dbo.T(X) using xml index IX_T for path;
  20.  
  21. go
  22.  
  23. -- Make sure that a node exists, anywhere
  24. insert into dbo.T(X) values('<NodeThatExist/>');
  25.  
  26. go
  27.  
  28. -- Search for a node that exist
  29. -- Uses secondary xml index, Good
  30. select count(*)
  31. from dbo.T
  32. where T.X.exist('NodeThatExist') = 1;
  33.  
  34. -- Search for node that does not exist, anywhere
  35. -- Table scan on primary xml index, Bad
  36. select count(*)
  37. from dbo.T
  38. where T.X.exist('NodeDoesNotExist') = 1;
  39.  
  40. -- Crazy workaround, don't actually insert the node
  41. begin transaction;
  42. insert into dbo.T(X) values('<NodeDoesNotExist/>');
  43. rollback;
  44.  
  45. -- Uses a seek plan
  46. select count(*)
  47. from dbo.T
  48. where T.X.exist('NodeDoesNotExist') = 1;
  49.  
  50. -- Restart SQL Server or actual server does not matter
  51. -- Still uses a seek plan
  52. select count(*)
  53. from dbo.T
  54. where T.X.exist('NodeDoesNotExist') = 1;
  55.  
Advertisement
RAW Paste Data Copied
Advertisement