Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- /*
- --------- Test queries for SQL_VARIANT_PROPERTY documentation ---------
- Created By: Solomon Rutzky / Sql Quantum Leap ( https://SqlQuantumLeap.com/ )
- Created On: 2020-02-25
- Updated On: n/a
- Location: https://pastebin.com/edit/3RAFxLbH
- Related PR: https://github.com/MicrosoftDocs/sql-docs/pull/4218
- Related Documentation: https://docs.microsoft.com/en-us/sql/t-sql/functions/sql-variant-property-transact-sql
- */
- -- https://sqlquantumleap.com/2018/12/28/prevent-full-script-execution-understanding-and-using-parseonly-and-noexec/
- SET PARSEONLY ON;
- GO
- -- Generate initial set of test queries (to copy and paste into this script).
- -- Results of this query are the basis for the next section.
- SELECT N'
- DECLARE @Thing ' + [name] + N' = '
- + IIF([name] LIKE N'date%' OR [name] = N'time', 'GETDATE()', '1' ) + N';
- SELECT ''' + [name] + N''' AS [DataType],
- SQL_VARIANT_PROPERTY(@Thing, ''Scale'') AS [Scale],
- SQL_VARIANT_PROPERTY(@Thing, ''Precision'') AS [Precision];
- GO
- '
- FROM sys.types
- WHERE [precision] <> 0
- OR [scale] <> 0
- ORDER BY [name];
- /* -- Condensed results from the next section. Items missing from documentation:
- -- MISSING (p):
- DATE -- 10
- DATETIME2(s) -- if s = 0 then 19 else s + 20
- DATETIMEOFFSET -- 34
- DATETIMEOFFSET(s) -- if s = 0 then 26 else s + 27
- DECIMAL / NUMERIC -- 18
- TIME -- 16
- TIME(s) -- if s = 0 then 8 else s + 9
- -- MISSING (s):
- DATETIME2(s) -- s (0 - 7)
- DATETIMEOFFSET -- 7
- DATETIMEOFFSET(s) -- s (0 - 7)
- DECIMAL / NUMERIC -- 0
- TIME -- 7
- TIME(s) -- s (0 - 7)
- */
- -- No need to list "float (s)" separately:
- SELECT SQL_VARIANT_PROPERTY(CONVERT(FLOAT(4), 1), 'basetype');
- -- real
- -- Just in case anyone is curious why the "if s = 0" value is
- -- always 1 less than for the other possible values (i.e. 1 - 7):
- -- it's due to the decimal separator, which is missing when s = 0.
- SELECT CONVERT(DATETIME2(1), SYSDATETIME()) AS [has decimal; 21 characters];
- -- 2020-02-26 00:11:11.2
- SELECT CONVERT(DATETIME2(0), SYSDATETIME()) AS [no decimal; 19 characters];
- -- 2020-02-26 00:11:11
- --------------------------------------------------------
- -- The following tests were initially generated from the query shown
- -- above. Then, tests for datatypes that are known to have optional
- -- "scale" values (datetime2, datetimeoffset, float, and time) were
- -- copied and pasted to create additional tests. No additional tests
- -- were created for the decimal datatype as it was already documented
- -- as having user-provided precision and scale.
- DECLARE @Thing bigint = 1;
- SELECT 'bigint' AS [DataType],
- SQL_VARIANT_PROPERTY(@Thing, 'Scale') AS [Scale],
- SQL_VARIANT_PROPERTY(@Thing, 'Precision') AS [Precision];
- GO
- DECLARE @Thing bit = 1;
- SELECT 'bit' AS [DataType],
- SQL_VARIANT_PROPERTY(@Thing, 'Scale') AS [Scale],
- SQL_VARIANT_PROPERTY(@Thing, 'Precision') AS [Precision];
- GO
- DECLARE @Thing date = GETDATE();
- SELECT 'date' AS [DataType],
- SQL_VARIANT_PROPERTY(@Thing, 'Scale') AS [Scale],
- SQL_VARIANT_PROPERTY(@Thing, 'Precision') AS [Precision];
- GO
- DECLARE @Thing datetime = GETDATE();
- SELECT 'datetime' AS [DataType],
- SQL_VARIANT_PROPERTY(@Thing, 'Scale') AS [Scale],
- SQL_VARIANT_PROPERTY(@Thing, 'Precision') AS [Precision];
- GO
- DECLARE @Thing datetime2 = GETDATE();
- SELECT 'datetime2' AS [DataType],
- SQL_VARIANT_PROPERTY(@Thing, 'Scale') AS [Scale],
- SQL_VARIANT_PROPERTY(@Thing, 'Precision') AS [Precision];
- GO
- DECLARE @Thing datetime2(5) = GETDATE();
- SELECT 'datetime2(5)' AS [DataType],
- SQL_VARIANT_PROPERTY(@Thing, 'Scale') AS [Scale],
- SQL_VARIANT_PROPERTY(@Thing, 'Precision') AS [Precision];
- GO
- DECLARE @Thing datetime2(1) = GETDATE();
- SELECT 'datetime2(1)' AS [DataType],
- SQL_VARIANT_PROPERTY(@Thing, 'Scale') AS [Scale],
- SQL_VARIANT_PROPERTY(@Thing, 'Precision') AS [Precision];
- GO
- DECLARE @Thing datetime2(0) = GETDATE();
- SELECT 'datetime2(0)' AS [DataType],
- SQL_VARIANT_PROPERTY(@Thing, 'Scale') AS [Scale],
- SQL_VARIANT_PROPERTY(@Thing, 'Precision') AS [Precision];
- GO
- DECLARE @Thing datetimeoffset = GETDATE();
- SELECT 'datetimeoffset' AS [DataType],
- SQL_VARIANT_PROPERTY(@Thing, 'Scale') AS [Scale],
- SQL_VARIANT_PROPERTY(@Thing, 'Precision') AS [Precision];
- GO
- DECLARE @Thing datetimeoffset(5) = GETDATE();
- SELECT 'datetimeoffset(5)' AS [DataType],
- SQL_VARIANT_PROPERTY(@Thing, 'Scale') AS [Scale],
- SQL_VARIANT_PROPERTY(@Thing, 'Precision') AS [Precision];
- GO
- DECLARE @Thing datetimeoffset(1) = GETDATE();
- SELECT 'datetimeoffset(1)' AS [DataType],
- SQL_VARIANT_PROPERTY(@Thing, 'Scale') AS [Scale],
- SQL_VARIANT_PROPERTY(@Thing, 'Precision') AS [Precision];
- GO
- DECLARE @Thing datetimeoffset(0) = GETDATE();
- SELECT 'datetimeoffset(0)' AS [DataType],
- SQL_VARIANT_PROPERTY(@Thing, 'Scale') AS [Scale],
- SQL_VARIANT_PROPERTY(@Thing, 'Precision') AS [Precision];
- GO
- DECLARE @Thing decimal = 1;
- SELECT 'decimal' AS [DataType],
- SQL_VARIANT_PROPERTY(@Thing, 'Scale') AS [Scale],
- SQL_VARIANT_PROPERTY(@Thing, 'Precision') AS [Precision];
- GO
- DECLARE @Thing float = 1;
- SELECT 'float' AS [DataType],
- SQL_VARIANT_PROPERTY(@Thing, 'Scale') AS [Scale],
- SQL_VARIANT_PROPERTY(@Thing, 'Precision') AS [Precision];
- GO
- DECLARE @Thing float(25) = 1;
- SELECT 'float(25)' AS [DataType],
- SQL_VARIANT_PROPERTY(@Thing, 'Scale') AS [Scale],
- SQL_VARIANT_PROPERTY(@Thing, 'Precision') AS [Precision];
- GO
- DECLARE @Thing float(2) = 1;
- SELECT 'float(2)' AS [DataType],
- SQL_VARIANT_PROPERTY(@Thing, 'Scale') AS [Scale],
- SQL_VARIANT_PROPERTY(@Thing, 'Precision') AS [Precision];
- GO
- DECLARE @Thing int = 1;
- SELECT 'int' AS [DataType],
- SQL_VARIANT_PROPERTY(@Thing, 'Scale') AS [Scale],
- SQL_VARIANT_PROPERTY(@Thing, 'Precision') AS [Precision];
- GO
- DECLARE @Thing money = 1;
- SELECT 'money' AS [DataType],
- SQL_VARIANT_PROPERTY(@Thing, 'Scale') AS [Scale],
- SQL_VARIANT_PROPERTY(@Thing, 'Precision') AS [Precision];
- GO
- DECLARE @Thing numeric = 1;
- SELECT 'numeric' AS [DataType],
- SQL_VARIANT_PROPERTY(@Thing, 'Scale') AS [Scale],
- SQL_VARIANT_PROPERTY(@Thing, 'Precision') AS [Precision];
- GO
- DECLARE @Thing real = 1;
- SELECT 'real' AS [DataType],
- SQL_VARIANT_PROPERTY(@Thing, 'Scale') AS [Scale],
- SQL_VARIANT_PROPERTY(@Thing, 'Precision') AS [Precision];
- GO
- DECLARE @Thing smalldatetime = 1;
- SELECT 'smalldatetime' AS [DataType],
- SQL_VARIANT_PROPERTY(@Thing, 'Scale') AS [Scale],
- SQL_VARIANT_PROPERTY(@Thing, 'Precision') AS [Precision];
- GO
- DECLARE @Thing smallint = 1;
- SELECT 'smallint' AS [DataType],
- SQL_VARIANT_PROPERTY(@Thing, 'Scale') AS [Scale],
- SQL_VARIANT_PROPERTY(@Thing, 'Precision') AS [Precision];
- GO
- DECLARE @Thing smallmoney = 1;
- SELECT 'smallmoney' AS [DataType],
- SQL_VARIANT_PROPERTY(@Thing, 'Scale') AS [Scale],
- SQL_VARIANT_PROPERTY(@Thing, 'Precision') AS [Precision];
- GO
- DECLARE @Thing time = GETDATE();
- SELECT 'time' AS [DataType],
- SQL_VARIANT_PROPERTY(@Thing, 'Scale') AS [Scale],
- SQL_VARIANT_PROPERTY(@Thing, 'Precision') AS [Precision];
- GO
- DECLARE @Thing time(5) = GETDATE();
- SELECT 'time(5)' AS [DataType],
- SQL_VARIANT_PROPERTY(@Thing, 'Scale') AS [Scale],
- SQL_VARIANT_PROPERTY(@Thing, 'Precision') AS [Precision];
- GO
- DECLARE @Thing time(1) = GETDATE();
- SELECT 'time(1)' AS [DataType],
- SQL_VARIANT_PROPERTY(@Thing, 'Scale') AS [Scale],
- SQL_VARIANT_PROPERTY(@Thing, 'Precision') AS [Precision];
- GO
- DECLARE @Thing time(0) = GETDATE();
- SELECT 'time(0)' AS [DataType],
- SQL_VARIANT_PROPERTY(@Thing, 'Scale') AS [Scale],
- SQL_VARIANT_PROPERTY(@Thing, 'Precision') AS [Precision];
- GO
- DECLARE @Thing tinyint = 1;
- SELECT 'tinyint' AS [DataType],
- SQL_VARIANT_PROPERTY(@Thing, 'Scale') AS [Scale],
- SQL_VARIANT_PROPERTY(@Thing, 'Precision') AS [Precision];
- GO
- --------------------------------------------------------
- -- The BIT datatype is valid yet not mentioned in the "BaseType" section:
- DECLARE @Table TABLE (c1 BIT)
- INSERT INTO @Table (c1) VALUES (1);
- SELECT SQL_VARIANT_PROPERTY(tmp.c1, 'BaseType') AS [Bit]
- FROM @Table tmp;
- -- The following datatypes are not valid for SQL_VARIANT, hence
- -- cannot be tested via SQL_VARIANT_PROPERTY():
- DECLARE @NotForSqlVariant TABLE (c1 IMAGE, c2 TEXT, c3 NTEXT, c4 SQL_VARIANT, c5 ROWVERSION, c6 XML);
- --------------------------------------------------------
- GO
- SET PARSEONLY OFF;
- GO
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement