SHARE
TWEET

SQL_VARIANT_PROPERTY documentation tests

SqlQuantumLeap Feb 26th, 2020 (edited) 170 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. /*
  2.  --------- Test queries for SQL_VARIANT_PROPERTY documentation ---------
  3.  
  4. Created By: Solomon Rutzky / Sql Quantum Leap ( https://SqlQuantumLeap.com/ )
  5. Created On: 2020-02-25
  6. Updated On: n/a
  7.  
  8. Location:               https://pastebin.com/edit/3RAFxLbH
  9. Related PR:             https://github.com/MicrosoftDocs/sql-docs/pull/4218
  10. Related Documentation:  https://docs.microsoft.com/en-us/sql/t-sql/functions/sql-variant-property-transact-sql
  11.  
  12. */
  13.  
  14. -- https://sqlquantumleap.com/2018/12/28/prevent-full-script-execution-understanding-and-using-parseonly-and-noexec/
  15. SET PARSEONLY ON;
  16. GO
  17.  
  18.  
  19.  
  20. -- Generate initial set of test queries (to copy and paste into this script).
  21. -- Results of this query are the basis for the next section.
  22. SELECT N'
  23. DECLARE @Thing ' + [name] + N' = '
  24.   + IIF([name] LIKE N'date%' OR [name] = N'time', 'GETDATE()', '1' ) + N';
  25. SELECT ''' + [name] + N''' AS [DataType],
  26.       SQL_VARIANT_PROPERTY(@Thing, ''Scale'') AS [Scale],
  27.       SQL_VARIANT_PROPERTY(@Thing, ''Precision'') AS [Precision];
  28. GO
  29. '
  30. FROM sys.types
  31. WHERE  [precision] <> 0
  32. OR [scale] <> 0
  33. ORDER BY [name];
  34.  
  35.  
  36. /* -- Condensed results from the next section. Items missing from documentation:
  37.  
  38. -- MISSING (p):
  39. DATE -- 10
  40. DATETIME2(s) -- if s = 0 then 19 else s + 20
  41. DATETIMEOFFSET -- 34
  42. DATETIMEOFFSET(s) -- if s = 0 then 26 else s + 27
  43. DECIMAL / NUMERIC -- 18
  44. TIME -- 16
  45. TIME(s) -- if s = 0 then 8 else s + 9
  46.  
  47.  
  48. -- MISSING (s):
  49. DATETIME2(s) -- s (0 - 7)
  50. DATETIMEOFFSET -- 7
  51. DATETIMEOFFSET(s) -- s (0 - 7)
  52. DECIMAL / NUMERIC -- 0
  53. TIME -- 7
  54. TIME(s) -- s (0 - 7)
  55. */
  56.  
  57.  
  58. -- No need to list "float (s)" separately:
  59. SELECT SQL_VARIANT_PROPERTY(CONVERT(FLOAT(4), 1), 'basetype');
  60. -- real
  61.  
  62.  
  63. -- Just in case anyone is curious why the "if s = 0" value is
  64. -- always 1 less than for the other possible values (i.e. 1 - 7):
  65. -- it's due to the decimal separator, which is missing when s = 0.
  66. SELECT CONVERT(DATETIME2(1), SYSDATETIME()) AS [has decimal; 21 characters];
  67. -- 2020-02-26 00:11:11.2
  68. SELECT CONVERT(DATETIME2(0), SYSDATETIME()) AS [no decimal; 19 characters];
  69. -- 2020-02-26 00:11:11
  70.  
  71.  
  72.  
  73. --------------------------------------------------------
  74.  
  75. -- The following tests were initially generated from the query shown
  76. -- above. Then, tests for datatypes that are known to have optional
  77. -- "scale" values (datetime2, datetimeoffset, float, and time) were
  78. -- copied and pasted to create additional tests. No additional tests
  79. -- were created for the decimal datatype as it was already documented
  80. -- as having user-provided precision and scale.
  81.  
  82.  
  83. DECLARE @Thing bigint = 1;
  84. SELECT 'bigint' AS [DataType],
  85.        SQL_VARIANT_PROPERTY(@Thing, 'Scale') AS [Scale],
  86.        SQL_VARIANT_PROPERTY(@Thing, 'Precision') AS [Precision];
  87. GO
  88.  
  89. DECLARE @Thing bit = 1;
  90. SELECT 'bit' AS [DataType],
  91.        SQL_VARIANT_PROPERTY(@Thing, 'Scale') AS [Scale],
  92.        SQL_VARIANT_PROPERTY(@Thing, 'Precision') AS [Precision];
  93. GO
  94.  
  95. DECLARE @Thing date = GETDATE();
  96. SELECT 'date' AS [DataType],
  97.        SQL_VARIANT_PROPERTY(@Thing, 'Scale') AS [Scale],
  98.        SQL_VARIANT_PROPERTY(@Thing, 'Precision') AS [Precision];
  99. GO
  100.  
  101. DECLARE @Thing datetime = GETDATE();
  102. SELECT 'datetime' AS [DataType],
  103.        SQL_VARIANT_PROPERTY(@Thing, 'Scale') AS [Scale],
  104.        SQL_VARIANT_PROPERTY(@Thing, 'Precision') AS [Precision];
  105. GO
  106.  
  107. DECLARE @Thing datetime2 = GETDATE();
  108. SELECT 'datetime2' AS [DataType],
  109.        SQL_VARIANT_PROPERTY(@Thing, 'Scale') AS [Scale],
  110.        SQL_VARIANT_PROPERTY(@Thing, 'Precision') AS [Precision];
  111. GO
  112.  
  113. DECLARE @Thing datetime2(5) = GETDATE();
  114. SELECT 'datetime2(5)' AS [DataType],
  115.        SQL_VARIANT_PROPERTY(@Thing, 'Scale') AS [Scale],
  116.        SQL_VARIANT_PROPERTY(@Thing, 'Precision') AS [Precision];
  117. GO
  118.  
  119. DECLARE @Thing datetime2(1) = GETDATE();
  120. SELECT 'datetime2(1)' AS [DataType],
  121.        SQL_VARIANT_PROPERTY(@Thing, 'Scale') AS [Scale],
  122.        SQL_VARIANT_PROPERTY(@Thing, 'Precision') AS [Precision];
  123. GO
  124.  
  125. DECLARE @Thing datetime2(0) = GETDATE();
  126. SELECT 'datetime2(0)' AS [DataType],
  127.        SQL_VARIANT_PROPERTY(@Thing, 'Scale') AS [Scale],
  128.        SQL_VARIANT_PROPERTY(@Thing, 'Precision') AS [Precision];
  129. GO
  130.  
  131. DECLARE @Thing datetimeoffset = GETDATE();
  132. SELECT 'datetimeoffset' AS [DataType],
  133.        SQL_VARIANT_PROPERTY(@Thing, 'Scale') AS [Scale],
  134.        SQL_VARIANT_PROPERTY(@Thing, 'Precision') AS [Precision];
  135. GO
  136.  
  137. DECLARE @Thing datetimeoffset(5) = GETDATE();
  138. SELECT 'datetimeoffset(5)' AS [DataType],
  139.        SQL_VARIANT_PROPERTY(@Thing, 'Scale') AS [Scale],
  140.        SQL_VARIANT_PROPERTY(@Thing, 'Precision') AS [Precision];
  141. GO
  142.  
  143. DECLARE @Thing datetimeoffset(1) = GETDATE();
  144. SELECT 'datetimeoffset(1)' AS [DataType],
  145.        SQL_VARIANT_PROPERTY(@Thing, 'Scale') AS [Scale],
  146.        SQL_VARIANT_PROPERTY(@Thing, 'Precision') AS [Precision];
  147. GO
  148.  
  149. DECLARE @Thing datetimeoffset(0) = GETDATE();
  150. SELECT 'datetimeoffset(0)' AS [DataType],
  151.        SQL_VARIANT_PROPERTY(@Thing, 'Scale') AS [Scale],
  152.        SQL_VARIANT_PROPERTY(@Thing, 'Precision') AS [Precision];
  153. GO
  154.  
  155. DECLARE @Thing decimal = 1;
  156. SELECT 'decimal' AS [DataType],
  157.        SQL_VARIANT_PROPERTY(@Thing, 'Scale') AS [Scale],
  158.        SQL_VARIANT_PROPERTY(@Thing, 'Precision') AS [Precision];
  159. GO
  160.  
  161. DECLARE @Thing float = 1;
  162. SELECT 'float' AS [DataType],
  163.        SQL_VARIANT_PROPERTY(@Thing, 'Scale') AS [Scale],
  164.        SQL_VARIANT_PROPERTY(@Thing, 'Precision') AS [Precision];
  165. GO
  166.  
  167. DECLARE @Thing float(25) = 1;
  168. SELECT 'float(25)' AS [DataType],
  169.        SQL_VARIANT_PROPERTY(@Thing, 'Scale') AS [Scale],
  170.        SQL_VARIANT_PROPERTY(@Thing, 'Precision') AS [Precision];
  171. GO
  172.  
  173. DECLARE @Thing float(2) = 1;
  174. SELECT 'float(2)' AS [DataType],
  175.        SQL_VARIANT_PROPERTY(@Thing, 'Scale') AS [Scale],
  176.        SQL_VARIANT_PROPERTY(@Thing, 'Precision') AS [Precision];
  177. GO
  178.  
  179. DECLARE @Thing int = 1;
  180. SELECT 'int' AS [DataType],
  181.        SQL_VARIANT_PROPERTY(@Thing, 'Scale') AS [Scale],
  182.        SQL_VARIANT_PROPERTY(@Thing, 'Precision') AS [Precision];
  183. GO
  184.  
  185. DECLARE @Thing money = 1;
  186. SELECT 'money' AS [DataType],
  187.        SQL_VARIANT_PROPERTY(@Thing, 'Scale') AS [Scale],
  188.        SQL_VARIANT_PROPERTY(@Thing, 'Precision') AS [Precision];
  189. GO
  190.  
  191. DECLARE @Thing numeric = 1;
  192. SELECT 'numeric' AS [DataType],
  193.        SQL_VARIANT_PROPERTY(@Thing, 'Scale') AS [Scale],
  194.        SQL_VARIANT_PROPERTY(@Thing, 'Precision') AS [Precision];
  195. GO
  196.  
  197. DECLARE @Thing real = 1;
  198. SELECT 'real' AS [DataType],
  199.        SQL_VARIANT_PROPERTY(@Thing, 'Scale') AS [Scale],
  200.        SQL_VARIANT_PROPERTY(@Thing, 'Precision') AS [Precision];
  201. GO
  202.  
  203. DECLARE @Thing smalldatetime = 1;
  204. SELECT 'smalldatetime' AS [DataType],
  205.        SQL_VARIANT_PROPERTY(@Thing, 'Scale') AS [Scale],
  206.        SQL_VARIANT_PROPERTY(@Thing, 'Precision') AS [Precision];
  207. GO
  208.  
  209. DECLARE @Thing smallint = 1;
  210. SELECT 'smallint' AS [DataType],
  211.        SQL_VARIANT_PROPERTY(@Thing, 'Scale') AS [Scale],
  212.        SQL_VARIANT_PROPERTY(@Thing, 'Precision') AS [Precision];
  213. GO
  214.  
  215. DECLARE @Thing smallmoney = 1;
  216. SELECT 'smallmoney' AS [DataType],
  217.        SQL_VARIANT_PROPERTY(@Thing, 'Scale') AS [Scale],
  218.        SQL_VARIANT_PROPERTY(@Thing, 'Precision') AS [Precision];
  219. GO
  220.  
  221. DECLARE @Thing time = GETDATE();
  222. SELECT 'time' AS [DataType],
  223.        SQL_VARIANT_PROPERTY(@Thing, 'Scale') AS [Scale],
  224.        SQL_VARIANT_PROPERTY(@Thing, 'Precision') AS [Precision];
  225. GO
  226.  
  227. DECLARE @Thing time(5) = GETDATE();
  228. SELECT 'time(5)' AS [DataType],
  229.        SQL_VARIANT_PROPERTY(@Thing, 'Scale') AS [Scale],
  230.        SQL_VARIANT_PROPERTY(@Thing, 'Precision') AS [Precision];
  231. GO
  232.  
  233. DECLARE @Thing time(1) = GETDATE();
  234. SELECT 'time(1)' AS [DataType],
  235.        SQL_VARIANT_PROPERTY(@Thing, 'Scale') AS [Scale],
  236.        SQL_VARIANT_PROPERTY(@Thing, 'Precision') AS [Precision];
  237. GO
  238.  
  239. DECLARE @Thing time(0) = GETDATE();
  240. SELECT 'time(0)' AS [DataType],
  241.        SQL_VARIANT_PROPERTY(@Thing, 'Scale') AS [Scale],
  242.        SQL_VARIANT_PROPERTY(@Thing, 'Precision') AS [Precision];
  243. GO
  244.  
  245. DECLARE @Thing tinyint = 1;
  246. SELECT 'tinyint' AS [DataType],
  247.        SQL_VARIANT_PROPERTY(@Thing, 'Scale') AS [Scale],
  248.        SQL_VARIANT_PROPERTY(@Thing, 'Precision') AS [Precision];
  249. GO
  250.  
  251. --------------------------------------------------------
  252.  
  253. -- The BIT datatype is valid yet not mentioned in the "BaseType" section:
  254. DECLARE @Table TABLE (c1 BIT)
  255. INSERT INTO @Table (c1) VALUES (1);
  256. SELECT SQL_VARIANT_PROPERTY(tmp.c1, 'BaseType') AS [Bit]
  257. FROM   @Table tmp;
  258.  
  259.  
  260. -- The following datatypes are not valid for SQL_VARIANT, hence
  261. -- cannot be tested via SQL_VARIANT_PROPERTY():
  262. DECLARE @NotForSqlVariant TABLE (c1 IMAGE, c2 TEXT, c3 NTEXT, c4 SQL_VARIANT, c5 ROWVERSION, c6 XML);
  263.  
  264. --------------------------------------------------------
  265.  
  266.  
  267.  
  268. GO
  269. SET PARSEONLY OFF;
  270. GO
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
Top