💎🏭SQL Reloaded: Microsoft Fabric's SQL Databases (Part XI: Database and Server Properties)

When taking over a SQL Server, respectively database, one of the first checks I do focuses on the overall configuration, going through the UI available for admins to see if I can find anything that requires further investigation. If no documentation is available on the same, I run a few scripts and export their output as baseline. 

Especially when documenting the configuration, it's useful to export the database options and properties defined at database level. Besides the collation and probably the recovery mode, typically the rest of the configuration is similar, though in exceptional cases one should expect also surprises that require further investigation! 

The following query retrieves in a consolidated way all the options and properties of a SQL database in Microsoft Fabric. 

-- database settings/properties 
SELECT DATABASEPROPERTYEX(DB_NAME(), 'Collation') Collation
--, DATABASEPROPERTYEX(DB_NAME(), 'ComparisonStyle')  ComparisonStyle
, DATABASEPROPERTYEX(DB_NAME(), 'Edition') Edition
--, DATABASEPROPERTYEX(DB_NAME(), 'IsAnsiNullDefault') IsAnsiNullDefault
--, DATABASEPROPERTYEX(DB_NAME(), 'IsAnsiNullsEnabled') IsAnsiNullsEnabled
--, DATABASEPROPERTYEX(DB_NAME(), 'IsAnsiPaddingEnabled') IsAnsiPaddingEnabled
--, DATABASEPROPERTYEX(DB_NAME(), 'IsAnsiWarningsEnabled') IsAnsiWarningsEnabled
--, DATABASEPROPERTYEX(DB_NAME(), 'IsArithmeticAbortEnabled') IsArithmeticAbortEnabled
--, DATABASEPROPERTYEX(DB_NAME(), 'IsAutoClose') IsAutoClose
, DATABASEPROPERTYEX(DB_NAME(), 'IsAutoCreateStatistics') IsAutoCreateStatistics
--, DATABASEPROPERTYEX(DB_NAME(), 'IsAutoCreateStatisticsIncremental') IsAutoCreateStatisticsIncremental
--, DATABASEPROPERTYEX(DB_NAME(), 'IsAutoShrink') IsAutoShrink
, DATABASEPROPERTYEX(DB_NAME(), 'IsAutoUpdateStatistics') IsAutoUpdateStatistics
--, DATABASEPROPERTYEX(DB_NAME(), 'IsClone') IsClone
--, DATABASEPROPERTYEX(DB_NAME(), 'IsCloseCursorsOnCommitEnabled') IsCloseCursorsOnCommitEnabled
--, DATABASEPROPERTYEX(DB_NAME(), 'IsDatabaseSuspendedForSnapshotBackup') IsDatabaseSuspendedForSnapshotBackup
, DATABASEPROPERTYEX(DB_NAME(), 'IsFulltextEnabled') IsFulltextEnabled
--, DATABASEPROPERTYEX(DB_NAME(), 'IsInStandBy') IsInStandBy
--, DATABASEPROPERTYEX(DB_NAME(), 'IsLocalCursorsDefault') IsLocalCursorsDefault
--, DATABASEPROPERTYEX(DB_NAME(), 'IsMemoryOptimizedElevateToSnapshotEnabled') IsMemoryOptimizedElevateToSnapshotEnabled
--, DATABASEPROPERTYEX(DB_NAME(), 'IsMergePublished') IsMergePublished
--, DATABASEPROPERTYEX(DB_NAME(), 'IsNullConcat') IsNullConcat
--, DATABASEPROPERTYEX(DB_NAME(), 'IsNumericRoundAbortEnabled') IsNumericRoundAbortEnabled
--, DATABASEPROPERTYEX(DB_NAME(), 'IsParameterizationForced') IsParameterizationForced
--, DATABASEPROPERTYEX(DB_NAME(), 'IsQuotedIdentifiersEnabled') IsQuotedIdentifiersEnabled
--, DATABASEPROPERTYEX(DB_NAME(), 'IsPublished') IsPublished
--, DATABASEPROPERTYEX(DB_NAME(), 'IsRecursiveTriggersEnabled') IsRecursiveTriggersEnabled
--, DATABASEPROPERTYEX(DB_NAME(), 'IsSubscribed') IsSubscribed
--, DATABASEPROPERTYEX(DB_NAME(), 'IsSyncWithBackup') IsSyncWithBackup
--, DATABASEPROPERTYEX(DB_NAME(), 'IsTornPageDetectionEnabled') IsTornPageDetectionEnabled
--, DATABASEPROPERTYEX(DB_NAME(), 'IsVerifiedClone') IsVerifiedClone
--, DATABASEPROPERTYEX(DB_NAME(), 'IsXTPSupported') IsXTPSupported
, DATABASEPROPERTYEX(DB_NAME(), 'LastGoodCheckDbTime') LastGoodCheckDbTime
, DATABASEPROPERTYEX(DB_NAME(), 'LCID') LCID
--, DATABASEPROPERTYEX(DB_NAME(), 'MaxSizeInBytes') MaxSizeInBytes
, DATABASEPROPERTYEX(DB_NAME(), 'Recovery') Recovery
--, DATABASEPROPERTYEX(DB_NAME(), 'ServiceObjective') ServiceObjective
--, DATABASEPROPERTYEX(DB_NAME(), 'ServiceObjectiveId') ServiceObjectiveId
, DATABASEPROPERTYEX(DB_NAME(), 'SQLSortOrder') SQLSortOrder
, DATABASEPROPERTYEX(DB_NAME(), 'Status') Status
, DATABASEPROPERTYEX(DB_NAME(), 'Updateability') Updateability
, DATABASEPROPERTYEX(DB_NAME(), 'UserAccess') UserAccess
, DATABASEPROPERTYEX(DB_NAME(), 'Version') Version
--, DATABASEPROPERTYEX(DB_NAME(), 'ReplicaID') ReplicaID

Output:

Collation

Edition

IsAutoCreateStatistics

IsAutoUpdateStatistics

IsFulltextEnabled

LastGoodCheckDbTime

LCID

Recovery

SQLSortOrder

Status

Updateability

UserAccess

Version

SQL_Latin1_General_CP1_CI_AS

FabricSQLDB

1

1

1

12/31/1899

1033

FULL

52

ONLINE

READ_WRITE

MULTI_USER

981

The query can be run also against the SQL analytics endpoints available for warehouses in Microsoft Fabric.

Output:

Collation

Edition

IsAutoCreateStatistics

IsAutoUpdateStatistics

IsFulltextEnabled

LastGoodCheckDbTime

LCID

Recovery

SQLSortOrder

Status

Updateability

UserAccess

Version

Latin1_General_100_BIN2_UTF8

DataWarehouse

1

1

1

12/31/1899

1033

SIMPLE

0

ONLINE

READ_WRITE

MULTI_USER

987

Respectively, for lakehouses:

Collation

Edition

IsAutoCreateStatistics

IsAutoUpdateStatistics

IsFulltextEnabled

LastGoodCheckDbTime

LCID

Recovery

SQLSortOrder

Status

Updateability

UserAccess

Version

Latin1_General_100_BIN2_UTF8

LakeWarehouse

1

1

1

12/31/1899

1033

SIMPLE

0

ONLINE

READ_WRITE

MULTI_USER

987

A similar output is obtained if one runs the query against SQL database's SQL analytics endpoint:

Output:

Collation

Edition

IsAutoCreateStatistics

IsAutoUpdateStatistics

IsFulltextEnabled

LastGoodCheckDbTime

LCID

Recovery

SQLSortOrder

Status

Updateability

UserAccess

Version

Latin1_General_100_BIN2_UTF8

LakeWarehouse

1

1

1

12/31/1899

1033

SIMPLE

0

ONLINE

READ_WRITE

MULTI_USER

987

SQL databases seem to inherit the collation from the earlier versions of SQL Server.

Another meaningful value for SQL databases is MaxSizeInBytes, which in my environment had a value of 3298534883328 bytes ÷ 1,073,741,824 = 3,072 GB.

There are however also server properties. Here's the consolidated overview:

-- server properties
SELECT --SERVERPROPERTY('BuildClrVersion') BuildClrVersion
 SERVERPROPERTY('Collation') Collation
--, SERVERPROPERTY('CollationID') CollationID
, SERVERPROPERTY('ComparisonStyle') ComparisonStyle
--, SERVERPROPERTY('ComputerNamePhysicalNetBIOS') ComputerNamePhysicalNetBIOS
, SERVERPROPERTY('Edition') Edition
--, SERVERPROPERTY('EditionID') EditionID
, SERVERPROPERTY('EngineEdition') EngineEdition
--, SERVERPROPERTY('FilestreamConfiguredLevel') FilestreamConfiguredLevel
--, SERVERPROPERTY('FilestreamEffectiveLevel') FilestreamEffectiveLevel
--, SERVERPROPERTY('FilestreamShareName') FilestreamShareName
--, SERVERPROPERTY('HadrManagerStatus') HadrManagerStatus
--, SERVERPROPERTY('InstanceDefaultBackupPath') InstanceDefaultBackupPath
, SERVERPROPERTY('InstanceDefaultDataPath') InstanceDefaultDataPath
--, SERVERPROPERTY('InstanceDefaultLogPath') InstanceDefaultLogPath
--, SERVERPROPERTY('InstanceName') InstanceName
, SERVERPROPERTY('IsAdvancedAnalyticsInstalled') IsAdvancedAnalyticsInstalled
--, SERVERPROPERTY('IsBigDataCluster') IsBigDataCluster
--, SERVERPROPERTY('IsClustered') IsClustered
, SERVERPROPERTY('IsExternalAuthenticationOnly') IsExternalAuthenticationOnly
, SERVERPROPERTY('IsExternalGovernanceEnabled') IsExternalGovernanceEnabled
, SERVERPROPERTY('IsFullTextInstalled') IsFullTextInstalled
--, SERVERPROPERTY('IsHadrEnabled') IsHadrEnabled
--, SERVERPROPERTY('IsIntegratedSecurityOnly') IsIntegratedSecurityOnly
--, SERVERPROPERTY('IsLocalDB') IsLocalDB
--, SERVERPROPERTY('IsPolyBaseInstalled') IsPolyBaseInstalled
--, SERVERPROPERTY('IsServerSuspendedForSnapshotBackup') IsServerSuspendedForSnapshotBackup
--, SERVERPROPERTY('IsSingleUser') IsSingleUser
--, SERVERPROPERTY('IsTempDbMetadataMemoryOptimized') IsTempDbMetadataMemoryOptimized
, SERVERPROPERTY('IsXTPSupported') IsXTPSupported
, SERVERPROPERTY('LCID') LCID
, SERVERPROPERTY('LicenseType') LicenseType
, SERVERPROPERTY('MachineName') MachineName
, SERVERPROPERTY('NumLicenses') NumLicenses
, SERVERPROPERTY('PathSeparator') PathSeparator
--, SERVERPROPERTY('ProcessID') ProcessID
, SERVERPROPERTY('ProductBuild') ProductBuild
--, SERVERPROPERTY('ProductBuildType') ProductBuildType
--, SERVERPROPERTY('ProductLevel') ProductLevel
--, SERVERPROPERTY('ProductMajorVersion') ProductMajorVersion
--, SERVERPROPERTY('ProductMinorVersion') ProductMinorVersion
--, SERVERPROPERTY('ProductUpdateLevel') ProductUpdateLevel
--, SERVERPROPERTY('ProductUpdateReference') ProductUpdateReference
--, SERVERPROPERTY('ProductUpdateType') ProductUpdateType
, SERVERPROPERTY('ProductVersion') ProductVersion
, SERVERPROPERTY('ResourceLastUpdateDateTime') ResourceLastUpdateDateTime
, SERVERPROPERTY('ResourceVersion') ResourceVersion
, SERVERPROPERTY('ServerName') ServerName
, SERVERPROPERTY('SqlCharSet') SqlCharSet
, SERVERPROPERTY('SqlCharSetName') SqlCharSetName
, SERVERPROPERTY('SqlSortOrder') SqlSortOrder
, SERVERPROPERTY('SqlSortOrderName') SqlSortOrderName
, SERVERPROPERTY('SuspendedDatabaseCount') SuspendedDatabaseCount

Output (consolidated):

Property	SQL database	Warehouse	Lakehouse
Collation	SQL_Latin1_General_CP1_CI_AS	SQL_Latin1_General_CP1_CI_AS	SQL_Latin1_General_CP1_CI_AS
ComparisonStyle	196609	196609	196609
Edition	SQL Azure	SQL Azure	SQL Azure
EngineEdition	12	11	11
InstanceDefaultDataPath	NULL	NULL	NULL
IsAdvancedAnalyticsInstalled	1	1	1
IsExternalAuthenticationOnly	1	0	0
IsExternalGovernanceEnabled	1	1	1
IsFullTextInstalled	1	0	0
IsXTPSupported	1	1	1
LCID	1033	1033	1033
LicenseType	DISABLED	DISABLED	DISABLED
MachineName	NULL	NULL	NULL
NumLicenses	NULL	NULL	NULL
PathSeparator	\	\	\
ProductBuild	2000	502	502
ProductVersion	12.0.2000.8	12.0.2000.8	12.0.2000.8
ResourceLastUpdateDateTime	11/6/2024 3:41:27 PM	3/5/2025 12:05:50 PM	3/5/2025 12:05:50 PM
ResourceVersion	16.00.5751	17.00.502	17.00.502
ServerName	...	....	....
SqlCharSet	1	1	1
SqlCharSetName	iso_1	iso_1	iso_1
SqlSortOrder	52	52	52
SqlSortOrderName	nocase_iso	nocase_iso	nocase_iso
SuspendedDatabaseCount	NULL	0	0

It's interesting that all three instances have the same general collation, while the Engine Edition of SQL databases is not compatible with the others [2]. The Server Names has been removed manually from the output from obvious reasons. The warehouse and lakehouse are in the same environment (SQL Azure instance, see sys.databases), and therefore the same values are shown (though this might happen independently of the environments used).

The queries were run in a trial Microsoft Fabric environment. Other environments can have upon case different properties. Just replace the "--" from the commented code to get a complete overview.

The queries should run also in the other editions of SQL Server. If DATABASEPROPERTYEX is not supported, one should try DATABASEPROPERTY instead.

Happy coding!

Previous Post <<||>> Next Post

References:
[1] Microsoft Learn (2024) SQL Server 2022: DATABASEPROPERTYEX (Transact-SQL) [link]
[2] Microsoft Learn (2024) SQL Server 2022: SERVERPROPERTY (Transact-SQL) [link]

💎🤖SQL Reloaded: Copilot Stories (Part IX: Working with the Stuff Function)

One of the underused string functions in SQL Server is Stuff, which returns part of a character, binary, text, or image expression. This post evaluates the answers provided by Microsoft 365 Copilot in the use of the respective function.

Disclaimer: The below code was generated by Copilot via prompts and is used for demonstration purposes!

Prompt:
Give me 10 scenarios with examples in which the Stuff function can be used in SQL Server.

Output:

/* Example 1: Inserting a Substring */

-- Insert a substring into a specific position within a string
SELECT STUFF('Hello World', 6, 0, 'SQL ') AS Result; -- Result: 'Hello SQL World'

/* Example 2: Replacing a Substring */

-- Replace a part of a string with another substring.
SELECT STUFF('Hello World', 7, 5, 'SQL Server') AS Result; -- Result: 'Hello SQL Server'

/* Example 3: Removing Characters */

-- Remove a specific number of characters from a string
SELECT STUFF('Hello World', 6, 5, '') AS Result; -- Result: 'Hello'

/* Example 4: Masking Sensitive Data */

-- Mask a credit card number
SELECT STUFF('1234-5678-9012-3456', 6, 9, '****-****') AS MaskedCard; -- Result: '1234-****-****-3456'

/* Example 5: Formatting Phone Numbers */

-- insert formatting characters into a phone number.
SELECT STUFF(STUFF('1234567890', 4, 0, '-'), 8, 0, '-') AS FormattedPhone; -- Result: '123-456-7890'

/* Example 6: Creating Initials */

-- extract initials from a full name.
SELECT STUFF(STUFF('John Doe', 2, 3, '.'), 5, 3, '.') AS Initials; -- Result: 'J.D.'

/* Example 7: Dynamic SQL Generation */

-- modify parts of a dynamic SQL query.
DECLARE @sql NVARCHAR(MAX) = 'SELECT * FROM Table WHERE Column = Value';
SELECT STUFF(@sql, CHARINDEX('Value', @sql), LEN('Value'), 'NewValue') AS ModifiedSQL; -- Result: 'SELECT * FROM Table WHERE Column = NewValue'

/* Example 8: URL Manipulation */

-- Insert or replace parts of a URL.
SELECT STUFF('http://example.com/page', 8, 0, 'www.') AS ModifiedURL; -- Result: 'http://www.example.com/page'

/* Example 9: Handling CSV Data */

-- Modify parts of a CSV string
SELECT STUFF('1,2,3,4,5', 3, 1, '10') AS ModifiedCSV; -- Result: '1,10,3,4,5'

/* Example 10: Custom String Formatting */ 

-- Insert custom formatting into a string
SELECT STUFF('20250305', 5, 0, '-') AS FormattedDate; -- Result: '2025-0305'

The first 6 examples are typical usages of the Stuff function while the other might raise the eyebrows of some developers.

Example 1: The example is not entirely correct (counting the characters starts at 1).

Example 2: In exchange, this example is correct.

Example 3: The example is unfortunately incorrect (same issue like in the first example). The technique can be useful when only one contiguous region is to be replaced once. Otherwise, one should use the Replace function. 

Example 4: Starting with SQL Server 2016 one can use dynamic masking for similar purposes, which should provide more flexibility in many cases. 

Example 5: Formatting phone number can be more complex than this because there can be different formats, country codes or any other special formats.

Example 6: Names can be quite complex, each part having different size.

Example 7: The example might work in certain cases, though it's enough for a field to contain the keyword "value" for the whole logic to generate errors. 

Example 8: The example is valid, though web addresses can use also other transfer protocols (ftp, ftps, sftp, scp, etc.).

Example 9: A more reliable method is splitting the string in parts and replacing the value from the nth position then inserting a text when the length of the values is not known.

Example 10: Even if the technique is valid, the example isn't entirely valid even if it might be met in practice. See 20th example below for a better approach. 

Prompt:
Give me 10 more scenarios with examples in which the Stuff function can be used in SQL Server.

Output:

/* Example 11: Reversing a string */

-- Reverse a string by replacing characters in a specific order.
SELECT STUFF(STUFF(STUFF('abcde', 1, 1, 'e'), 2, 1, 'd'), 3, 1, 'c') AS ReversedString; -- Result: 'edcba'

/* Example 12: Generating unique identifiers */

-- Modify parts of a GUID to create a custom identifier.
SELECT STUFF('123e4567-e89b-12d3-a456-426614174000', 1, 8, 'abcdef12') AS CustomGUID;-- Result: 'abcdef12-e89b-12d3-a456-426614174000'

/* Example 13: Combining columns */

-- Combine multiple columns into a single formatted string.
SELECT STUFF(FirstName + ' ' + LastName, LEN(FirstName) + 1, 0, ', ') AS FullName
FROM Employees;-- Result: 'John, Doe'

/* Example 14: Trimming strings */

-- Remove leading or trailing characters from a string.
SELECT STUFF('   Hello World   ', 1, 3, '') AS TrimmedString;-- Result: 'Hello World   '

/* Example 15: Creating abbreviations */

-- Generate abbreviations from a full string.
SELECT STUFF('North Atlantic Treaty Organization', 6, 22, 'ATO') AS Abbreviation;-- Result: 'NATO'

/* Example 16: Modifying JSON Data */

-- Insert or replace parts of a JSON string 
SELECT STUFF('{"name":"John","age":30}', 9, 4, 'Doe') AS ModifiedJSON;-- Result: '{"name":"Doe","age":30}'

/* Example 17: Handling XML Data */

-- Modify parts of an XML string.
SELECT STUFF('<name>John</name>', 7, 4, 'Doe') AS ModifiedXML;-- Result: '<name>Doe</name>'

/* Example 18: Generating random strings */

-- Insert random characters into a string.
SELECT STUFF('abcdef', 3, 0, CHAR(65 + ABS(CHECKSUM(NEWID())) % 26)) AS RandomString;-- Result: 'abXcdef' (X is a random character)

/* Example 19: Creating custom error messages  */

-- Insert dynamic values into error messages.
DECLARE @ErrorMessage NVARCHAR(100) = 'Error: Value {0} is not valid.';
SELECT STUFF(@ErrorMessage, CHARINDEX('{0}', @ErrorMessage), 3, '123') AS CustomErrorMessage;-- Result: 'Error: Value 123 is not valid.'

/* Example 20: Formatting dates */

-- Insert separators into a date string.
SELECT STUFF(STUFF('20250305', 5, 0, '-'), 8, 0, '-') AS FormattedDate; -- Result: '2025-03-05'

Example 11: Copilot got the use partially wrong given that each character except the pivoting character needs to be replaced accordingly. So for 6 characters one needs to replace the Stuff function 5 times!

Example 12:  Implementing custom GUID is a process more complex than this as one needs to take care of not generating duplicates.

Example 13: This is an example on how to handle changes dynamically.

Example 14: One should use the Trim function whenever possible, respectively the combination LTrim and RTrim, if Trim is not available (it was introduced in SQL 2017).

Example 15: The example is incorrect. One should consider the length of the string in the formula. 

Example 16:  One must know the values in advance, otherwise the example doesn't hold. Moreover, the same issue like in the first example occurs.

Example 17:  The example in not dynamic.

Example 18:  It's an interesting technique for generating "random" characters given that unique values are generated across a dataset.

Example 19: One can write error messages with multiple placeholders, though the Replace function is simpler to use. 

Example 20: It's easier to cast the value as datetime and apply the required formatting accordingly. Not testing whether the value is a date can lead to curious results.

I met some of the usages exemplified above, though I used the Stuff function seldom (see a previous post), when no other functions were available. Frankly, Copilot could prove to be a useful tool for learning SQL or other programming language in similar ways.

Happy coding!

Previous Post <<||>> Next Post 

💎🤖💫SQL Reloaded: Copilot Stories (Part VI: Queries based on Dynamics 365 for Finance & Operations)

As part of ad-hoc queries, SSRS/Paginated Reports and Data Warehouse solutions, during the past 15 years I wrote hundreds of queries based on Dynamics AX, respectively on Dynamics 365 (D365) for Finance, Supply Chain, Production, Human Resources and a few other modules. So, given that the documentation for the D365 schema is available online, I've been wondering how easy would be to write a basic query using M365 Copilot. A relatively simple example would be the join between the Products (InventTable) and Prices (InventTableModule) tables, a quite simple thing to do (the joins were discussed in here).

Disclaimer: The below code was generated by Copilot via prompts used for demonstration purposes!

The first attempt proved to be far from the expectations:

Prompt:
Write a query that joins InventTable and InventTableModule to retrieve the Item Number, Description and the Item Prices from Dynamics 365 for Finance. Include tables' schema and rewrite the query by placing the comma first.

Output:

SELECT 
    , IT.ItemId AS [Item Number]
    , IT.ItemName AS [Description]
    , ITM.Price AS [Item Price]
FROM 
    dbo.InventTable IT
JOIN 
    dbo.InventTableModule ITM 
    ON IT.ItemId = ITM.ItemId 
    AND IT.DataAreaId = ITM.DataAreaId
WHERE 
    ITM.ModuleType = 0 -- Assuming 0 represents the sales module, adjust as necessary

A first version of the query didn't included the join on DataAreaId. Another problem is that compared with the previous versions, in D365 the ItemName is not available in the InventTable anymore while the ItemName was incorrectly considered as Description. The differences between the various versions of D365 are minimal, though the differences between D365 and AX 2009 are considerable with important changes due to redesign.

Prompt:
Rewrite the query to provide the other existing prices via a grouping.

Output:

SELECT 
    , IT.ItemId AS [Item Number]
    , IT.ItemName AS [Description]
    , STRING_AGG(CAST(ITM.Price AS NVARCHAR), ', ') AS [Item Prices]
FROM 
    dbo.InventTable IT
JOIN 
    dbo.InventTableModule ITM 
    ON IT.ItemId = ITM.ItemId 
    AND IT.DataAreaId = ITM.DataAreaId
WHERE 
    ITM.ModuleType = 0 -- Assuming 0 represents the sales module, adjust as necessary
GROUP BY 
    IT.ItemId, IT.ItemName

The generated query shows all the prices as a string, solution which might work if we change the delimiter, though is not an optimal solution at all.

Prompt:
Rewrite the query to provide the other existing individual prices via a grouping in separate fields.

Output:

SELECT 
    , IT.ItemId AS [Item Number]
    , IT.ItemName AS [Description]
    , MAX(CASE WHEN ITM.ModuleType = 0 THEN ITM.Price END) AS [Sales Price]
    , MAX(CASE WHEN ITM.ModuleType = 1 THEN ITM.Price END) AS [Purchase Price]
    , MAX(CASE WHEN ITM.ModuleType = 2 THEN ITM.Price END) AS [Cost Price]
FROM 
    dbo.InventTable IT
JOIN 
    dbo.InventTableModule ITM 
    ON IT.ItemId = ITM.ItemId 
    AND IT.DataAreaId = ITM.DataAreaId
GROUP BY 
    IT.ItemId, IT.ItemName

This version is closer to the original query I wrote some time ago:

-- Item prices via group by
SELECT ITD.DataAreaId 
, ITD.ItemId  
, ITD.InventPrice
, ITD.InventUnitId
, ITD.PurchPrice
, ITD.PurchUnitId
, ITD.SalesPrice
, ITD.SalesUnitId
FROM dbo.InventTable ITM
     LEFT JOIN (-- price details
     SELECT ITD.ItemId
     , ITD.DataAreaId 
     , Max(CASE ITD.ModuleType WHEN 0 THEN ITD.Price END) InventPrice
     , Max(CASE ITD.ModuleType WHEN 0 THEN ITD.UnitId END) InventUnitId
     , Max(CASE ITD.ModuleType WHEN 1 THEN ITD.Price END) PurchPrice
     , Max(CASE ITD.ModuleType WHEN 1 THEN ITD.UnitId END) PurchUnitId
     , Max(CASE ITD.ModuleType WHEN 2 THEN ITD.Price END) SalesPrice
     , Max(CASE ITD.ModuleType WHEN 2 THEN ITD.UnitId END) SalesUnitId
     FROM dbo.InventTableModule ITD
     GROUP BY ITD.ItemId
     , ITD.DataAreaId 
    ) ITD
       ON ITD.ItemId = ITM.ItemId
      AND ITD.DataAreaId = ITM.DataAreaId
WHERE ITD.DataAreaID = 'XYZ'
ORDER BY ITD.ItemId

Prompt:
Rewrite the query to provide the other existing individual prices via a grouping in separate fields.

Output:

SELECT 
    , IT.ItemId AS [Item Number]
    , IT.ItemName AS [Description]
    , Sales.Price AS [Sales Price]
    , Purchase.Price AS [Purchase Price]
    , Cost.Price AS [Cost Price]
FROM 
    dbo.InventTable IT
LEFT JOIN 
    dbo.InventTableModule Sales 
    ON IT.ItemId = Sales.ItemId 
    AND IT.DataAreaId = Sales.DataAreaId 
    AND Sales.ModuleType = 0 -- Assuming 0 represents the sales module
LEFT JOIN 
    dbo.InventTableModule Purchase 
    ON IT.ItemId = Purchase.ItemId 
    AND IT.DataAreaId = Purchase.DataAreaId 
    AND Purchase.ModuleType = 1 -- Assuming 1 represents the purchase module
LEFT JOIN 
    dbo.InventTableModule Cost 
    ON IT.ItemId = Cost.ItemId 
    AND IT.DataAreaId = Cost.DataAreaId 
    AND Cost.ModuleType = 2 -- Assuming 2 represents the cost module

The structure of the query looks promising, though same in the previous query Copilot got the uses for ModuleType wrong (see below)! And here's the query I wrote previously:

-- Item Prices without grouping
SELECT ITM.DataAreaId 
, ITM.ItemId 
, ILP.UnitId InventUnitId 
, ILP.Price InventPrice 
, IPP.UnitId PruchaseUnitId
, IPP.Price PurchasePrice
, ISP.UnitId SalesUnitId
, ISP.Price SalesPrice
FROM dbo.InventTable ITM
      LEFT JOIN dbo.InventTableModule ILP
        ON ITM.ItemId = ILP.ItemId
       AND ITM.DataAreaId = ILP.DataAreaId
       AND ILP.ModuleType = 0 -- Warehouse
      LEFT JOIN dbo.InventTableModule IPP
        ON ITM.ItemId = IPP.ItemId
       AND ITM.DataAreaId = IPP.DatareaId 
       AND IPP.ModuleType = 1 -- Purchases
      LEFT JOIN dbo.InventTableModule ISP
        ON ITM.ItemId = ISP.ItemId
       AND ITM.DataAreaId = ISP.DataAreaId 
       AND ISP.ModuleType = 2 -- Sales	
WHERE ITM.DataAreaId = 'XYZ'

Probably the Copilot for Dynamics 365 Finance and Operations [1] works much better than the one from M365. Unfortunately, I don't have access to it yet! Also, if I would invest more time in the prompts the results would be closer to the queries I wrote. It depends also on the goal(s) considered - build a skeleton on which to build the logic, respectively generate the final query via the prompts. Probably the 80-20 rule applies here as well. 

Frankly, for a person not knowing the D365 data model, the final queries generated are a good point to start (at least for searching for more information on the web) as long Copilot got the prompts right. It will be also interesting to see how business rules related to specific business processes (including customizations) will be handled. The future looks bright (at least for the ones still having a job in the field)!

Happy coding!

Previous Post <<||>> Next Post

References:
[1] Microsoft Learn (2024) Microsoft Learn: Overview of Copilot capabilities in finance and operations apps [link]

💎🤖SQL Reloaded: Copilot Stories (Part V: Common Table Expressions for Employee Hierarchies and Interest Rates)

Continuing the series of posts on the use of Copilot for generating simple solutions, in this post let's consider the generation of Common Table Expressions (CTEs) based on several examples.

Disclaimer: The below code was generated by Copilot via prompts used for demonstration purposes!

Use Case 1: Employee Hierarchies

One of the most basic examples in which recursive CTE are used is the traversing of an organization's hierarchy, which can have multiple levels. The following prompts attempt step by step to provide different approaches for traversing the hierarchy. 

One should run the prompts individually.

Prompt(s):
Create a query based on a common table expression that traverses a hierarchy in an organization.
Generate an example on which the query can be tested.
Rewrite the queries by placing the comma first.
Rewrite the queries and condense their structure.

Output:

--Creating the Employees Table
CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY, EmployeeName VARCHAR(100), ManagerID INT
);

INSERT INTO Employees (EmployeeID, EmployeeName, ManagerID) VALUES
    (1, 'Alice', NULL), (2, 'Bob', 1), (3, 'Charlie', 1),
    (4, 'David', 2), (5, 'Eve', 2), (6, 'Frank', 3), (7, 'Grace', 3);


-- CTE Query for Hierarchy Traversal
WITH EmployeeHierarchy AS (
    SELECT EmployeeID, EmployeeName, ManagerID, 0 AS Level
    FROM Employees WHERE ManagerID IS NULL
    UNION ALL
    SELECT e.EmployeeID, e.EmployeeName, e.ManagerID, eh.Level + 1
    FROM Employees e INNER JOIN EmployeeHierarchy eh ON e.ManagerID = eh.EmployeeID
)
SELECT EmployeeID, EmployeeName, ManagerID, Level
FROM EmployeeHierarchy
ORDER BY Level, EmployeeName;

It would be great if the query would return also Manager's name:

Prompt(s):
Rewrite the final query based on EmployeeHierarchy and condense their structure.

Output:

WITH EmployeeHierarchy AS (
    SELECT e.EmployeeID, e.EmployeeName, e.ManagerID, 0 AS Level, NULL AS ManagerName
    FROM Employees e WHERE e.ManagerID IS NULL
    UNION ALL
    SELECT e.EmployeeID, e.EmployeeName, e.ManagerID, eh.Level + 1, m.EmployeeName
    FROM Employees e
    INNER JOIN EmployeeHierarchy eh ON e.ManagerID = eh.EmployeeID
    LEFT JOIN Employees m ON e.ManagerID = m.EmployeeID
)
SELECT eh.EmployeeID, eh.EmployeeName, eh.ManagerID, eh.Level, eh.ManagerName
FROM EmployeeHierarchy eh
ORDER BY eh.Level, eh.EmployeeName;

Unfortunately, running the query leads to the following error message:

"Msg 462, Level 16, State 1, Line 3, Outer join is not allowed in the recursive part of a recursive common table expression 'EmployeeHierarchy'."

Prompt:
Rewrite the query to fix the error "Outer join is not allowed in the recursive part of a recursive common table expression 'EmployeeHierarchy'."

Output:

WITH EmployeeHierarchy AS (
    SELECT e.EmployeeID, e.EmployeeName, e.ManagerID, 0 AS Level
    FROM Employees e WHERE e.ManagerID IS NULL
    UNION ALL
    SELECT e.EmployeeID, e.EmployeeName, e.ManagerID, eh.Level + 1
    FROM Employees e
    INNER JOIN EmployeeHierarchy eh ON e.ManagerID = eh.EmployeeID
)
SELECT eh.EmployeeID, eh.EmployeeName, eh.ManagerID, eh.Level, m.EmployeeName AS ManagerName
FROM EmployeeHierarchy eh
LEFT JOIN Employees m ON eh.ManagerID = m.EmployeeID
ORDER BY eh.Level, eh.EmployeeName;

Now the output looks much better. It will be interesting to see whether Copilot learns in time from such errors. The error and the fact that Copilot was able to generate the proper code after the correction are a bit unexpected.

Prompt:
Generate a sample table for the problem containing five levels. Condense the statements. Generate the statement for dropping the existing table.

Output:

-- Dropping the Existing Table
DROP TABLE IF EXISTS Employees;

-- Creating the Employees Table
CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY, EmployeeName VARCHAR(100), ManagerID INT
);

INSERT INTO Employees (EmployeeID, EmployeeName, ManagerID) VALUES
    (1, 'Alice', NULL), (2, 'Bob', 1), (3, 'Charlie', 1),
    (4, 'David', 2), (5, 'Eve', 2), (6, 'Frank', 3), (7, 'Grace', 3),
    (8, 'Hannah', 4), (9, 'Ivy', 4), (10, 'Jack', 5), (11, 'Karen', 5),
    (12, 'Leo', 6), (13, 'Mona', 6), (14, 'Nina', 7), (15, 'Oscar', 7),
    (16, 'Paul', 8), (17, 'Quinn', 8), (18, 'Rita', 9), (19, 'Sam', 9),
    (20, 'Tom', 10), (21, 'Uma', 10), (22, 'Vera', 11), (23, 'Will', 11),
    (24, 'Xena', 12), (25, 'Yara', 12), (26, 'Zane', 13), (27, 'Amy', 13),
    (28, 'Ben', 14), (29, 'Cathy', 14), (30, 'Dan', 15), (31, 'Ella', 15);

Prompt:
Modify the last query to include the full path from the Manager to the Employee.

Output:

-- CTE Query for Hierarchy Traversal with Full Path
WITH EmployeeHierarchy AS (
    SELECT 
        e.EmployeeID
        , e.EmployeeName
        , e.ManagerID
        , 0 AS Level
        , CAST(e.EmployeeName AS VARCHAR(MAX)) AS Path
    FROM 
        Employees e 
    WHERE 
        e.ManagerID IS NULL
    UNION ALL
    SELECT 
        e.EmployeeID
        , e.EmployeeName
        , e.ManagerID
        , eh.Level + 1
        , CAST(eh.Path + ' -> ' + e.EmployeeName AS VARCHAR(MAX))
    FROM 
        Employees e
    INNER JOIN 
        EmployeeHierarchy eh ON e.ManagerID = eh.EmployeeID
)
SELECT 
    eh.EmployeeID
    , eh.EmployeeName
    , eh.ManagerID
    , eh.Level
    , eh.Path
FROM 
    EmployeeHierarchy eh
ORDER BY 
    eh.Level
    , eh.EmployeeName;

The more lines the code has, the more it makes sense to expand the query as above. It would be great if one could apply a set of prompts to an entire session (e.g. always include the schema when calling an object, always format the code in a certain way, etc.).

Use Case 2: Interest Rates

Another often met problem is to calculate the interest rate for several years based on a list of items. 

Prompt(s):
Create a query based on a common tables expression that applies interest rate of .05 on a yearly basis to a list of items for the past 8 years with projection over other 2 years. Generate an example on which the query can be tested.
Add distinct dates to the table as starting points for the simulation.
Add the schema name to the statements.

Output:

-- Create the table with initial data
CREATE TABLE finance.items (
    item_id INT PRIMARY KEY,
    item_name VARCHAR(50),
    initial_value DECIMAL(10, 2),
    start_date DATE
);

-- Insert example data into the table
INSERT INTO finance.items (item_id, item_name, initial_value, start_date) VALUES
(1, 'Item A', 1000.00, '2017-01-01'),
(2, 'Item B', 1500.00, '2017-06-01'),
(3, 'Item C', 2000.00, '2018-01-01');

-- CTE to calculate the value over the years
WITH ValueProjection AS (
    SELECT
        item_id,
        item_name,
        initial_value AS value,
        start_date,
        start_date AS projection_date,
        0 AS year
    FROM
        finance.items
    UNION ALL
    SELECT
        vp.item_id,
        vp.item_name,
        vp.value * 1.05 AS value,
        vp.start_date,
        DATEADD(YEAR, 1, vp.projection_date) AS projection_date,
        vp.year + 1 AS year
    FROM
        ValueProjection vp
    WHERE
        vp.year < 9
)
SELECT
    item_id,
    item_name,
    value,
    projection_date
FROM
    ValueProjection
ORDER BY
    item_id,
    projection_date;

In a second attempt, in a second session, Copilot create the logic in the Finance schema (rather than in dbo) and left out the statement for creating it, which is a bit surprising. This can be easily fixed though:

Prompt(s):
Write the statement for creating the schema. 

Output

-- Create the schema
CREATE SCHEMA finance;

Trying to run the query lead to the well-known error message:

"Msg 240, Level 16, State 1, Line 3, Types don't match between the anchor and the recursive part in column "value" of recursive query "ValueProjection"."

Prompt(s):
Rewrite the query to fix the error "Msg 240, Level 16, State 1, Line 3, Types don't match between the anchor and the recursive part in column "value" of recursive query "ValueProjection"."

Output

-- CTE to calculate the value over the years
WITH ValueProjection AS (
    SELECT
        item_id,
        item_name,
        CAST(initial_value AS DECIMAL(10, 2)) AS value,
        start_date,
        start_date AS projection_date,
        0 AS year
    FROM
        finance.items
    UNION ALL
    SELECT
        vp.item_id,
        vp.item_name,
        CAST(vp.value * 1.05 AS DECIMAL(10, 2)) AS value,
        vp.start_date,
        DATEADD(YEAR, 1, vp.projection_date) AS projection_date,
        vp.year + 1 AS year
    FROM
        ValueProjection vp
    WHERE
        vp.year < 9
)
SELECT
    item_id,
    item_name,
    value,
    projection_date
FROM
    ValueProjection
ORDER BY
    item_id,
    projection_date;

This time the query runs successfully.

CTEs are useful for solving many problems, though are there alternatives? Copilot used a loop for obtaining the same result:

Prompt(s):
Write a query to obtain the same result without the use of common table expressions.

Output:

-- Create a temporary table to store the projections
CREATE TABLE #ValueProjection (
    item_id INT,
    item_name VARCHAR(50),
    value DECIMAL(10, 2),
    projection_date DATE,
    year INT
);

-- Insert initial values into the temporary table
INSERT INTO #ValueProjection (item_id, item_name, value, projection_date, year)
SELECT
    item_id,
    item_name,
    initial_value,
    start_date,
    0
FROM
    finance.items;

-- Loop to calculate the value over the years
DECLARE @year INT = 1;
WHILE @year <= 9
BEGIN
    INSERT INTO #ValueProjection (item_id, item_name, value, projection_date, year)
    SELECT
        vp.item_id,
        vp.item_name,
        CAST(vp.value * 1.05 AS DECIMAL(10, 2)) AS value,
        DATEADD(YEAR, 1, vp.projection_date) AS projection_date,
        @year
    FROM
        #ValueProjection vp
    WHERE
        vp.year = @year - 1;

    SET @year = @year + 1;
END;

-- Select the final results
SELECT
    item_id,
    item_name,
    value,
    projection_date
FROM
    #ValueProjection
ORDER BY
    item_id,
    projection_date;

-- Drop the temporary table
DROP TABLE #ValueProjection;

The query seems to be correct, despite the fact that in a previous session the dates were not correctly considered, existing bigger gaps in between than 1 year for some records. Attempts to replicate the issue were unsuccessful, though this behavior is strange. One should always test the queries thoroughly, especially when they were generated by Copilot!

It's important to always consider adding the schema to the objects created, and this should be part of the initial prompt!

One can further increase the complexity of the query. 

One should try also to see if further solutions exist. For example, the next prompt generated a solution based on UNIONs, which usually is not an effective solution even if it gets the job done!

Prompt:
Write a query to obtain the same result without the use of common table expressions or loops.

Output:

-- Query to project values over 10 years
SELECT
    item_id,
    item_name,
    initial_value AS value,
    start_date AS projection_date
FROM
    finance.items
UNION ALL
SELECT
    item_id,
    item_name,
    CAST(initial_value * 1.05 AS DECIMAL(10, 2)) AS value,
    DATEADD(YEAR, 1, start_date) AS projection_date
FROM
    finance.items
UNION ALL
SELECT
    item_id,
    item_name,
    CAST(initial_value * POWER(1.05, 2) AS DECIMAL(10, 2)) AS value,
    DATEADD(YEAR, 2, start_date) AS projection_date
FROM
    finance.items
UNION ALL
SELECT
    item_id,
    item_name,
    CAST(initial_value * POWER(1.05, 3) AS DECIMAL(10, 2)) AS value,
    DATEADD(YEAR, 3, start_date) AS projection_date
FROM
    finance.items
UNION ALL
SELECT
    item_id,
    item_name,
    CAST(initial_value * POWER(1.05, 4) AS DECIMAL(10, 2)) AS value,
    DATEADD(YEAR, 4, start_date) AS projection_date
FROM
    finance.items
UNION ALL
SELECT
    item_id,
    item_name,
    CAST(initial_value * POWER(1.05, 5) AS DECIMAL(10, 2)) AS value,
    DATEADD(YEAR, 5, start_date) AS projection_date
FROM
    finance.items
UNION ALL
SELECT
    item_id,
    item_name,
    CAST(initial_value * POWER(1.05, 6) AS DECIMAL(10, 2)) AS value,
    DATEADD(YEAR, 6, start_date) AS projection_date
FROM
    finance.items
UNION ALL
SELECT
    item_id,
    item_name,
    CAST(initial_value * POWER(1.05, 7) AS DECIMAL(10, 2)) AS value,
    DATEADD(YEAR, 7, start_date) AS projection_date
FROM
    finance.items
UNION ALL
SELECT
    item_id,
    item_name,
    CAST(initial_value * POWER(1.05, 8) AS DECIMAL(10, 2)) AS value,
    DATEADD(YEAR, 8, start_date) AS projection_date
FROM
    finance.items
UNION ALL
SELECT
    item_id,
    item_name,
    CAST(initial_value * POWER(1.05, 9) AS DECIMAL(10, 2)) AS value,
    DATEADD(YEAR, 9, start_date) AS projection_date
FROM
    finance.items
-- Order the results
ORDER BY
    item_id,
    projection_date;

Are these all possible solutions? One can test this via the following prompt which can be used again until all solutions were exhausted:

Prompt(s):
Write a query to obtain the same result without the use of the previous methods used above.

Output:

-- Use a cross join to generate the projections for each year.
-- Create a numbers table to represent the years
CREATE TABLE finance.numbers (
    n INT PRIMARY KEY
);

-- Insert numbers from 0 to 9 into the numbers table
INSERT INTO finance.numbers (n) VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9);

-- Query to project values over 10 years
SELECT
    i.item_id,
    i.item_name,
    CAST(i.initial_value * POWER(1.05, n.n) AS DECIMAL(10, 2)) AS value,
    DATEADD(YEAR, n.n, i.start_date) AS projection_date
FROM
    finance.items i
CROSS JOIN
    finance.numbers n
ORDER BY
    i.item_id,
    n.n;

Frankly, this is the most compact solution! I used variations of it in other contexts via a table created on the fly or a table-valued function. The solution can be easily extended to consider different rates.

Let's try another prompt:

Prompt:
Extend the last query to include different rates for each year that increase or decrease by 0.01 percent.

Output:

-- Query to project values over 10 years with varying interest rates 

SELECT
    i.item_id,
    i.item_name,
    CAST(i.initial_value * EXP(SUM(LOG(1 + 0.05 + (n.n * 0.01)))) OVER (PARTITION BY i.item_id ORDER BY n.n) AS DECIMAL(10, 2)) AS value,
    DATEADD(YEAR, n.n, i.start_date) AS projection_date
FROM
    finance.items i
CROSS JOIN
    finance.numbers n
ORDER BY
    i.item_id,
    n.n;

Unfortunately, this led again to an error:

"Msg 4113, Level 15, State 6, Line 5, The function 'EXP' is not a valid windowing function, and cannot be used with the OVER clause."

Though, it can be fixed with the prompt:

Prompt:
Fix the error "Msg 4113, Level 15, State 6, Line 5, The function 'EXP' is not a valid windowing function, and cannot be used with the OVER clause."

Output:

-- Query to project values over 10 years with varying interest rates
SELECT
    i.item_id,
    i.item_name,
    CAST(i.initial_value * (
        SELECT EXP(SUM(LOG(1 + 0.05 + (n2.n * 0.01))))
        FROM finance.numbers n2
        WHERE n2.n <= n.n
    ) AS DECIMAL(10, 2)) AS value,
    DATEADD(YEAR, n.n, i.start_date) AS projection_date
FROM
    finance.items i
CROSS JOIN
    finance.numbers n
ORDER BY
    i.item_id,
    n.n;

I used the same solution in a previous post, and it isn't trivial at all, even if the trick is simple!

Is this everything? Probably, not! Running again the first statement from the first section in a new session led to the following query, which can be transposed if needed:

-- Query with CTE
WITH InterestProjection AS (
    SELECT
        item_id,
        item_name,
        start_date,
        initial_value,
        initial_value * POWER(1.05, 0) AS value_year_0,
        initial_value * POWER(1.05, 1) AS value_year_1,
        initial_value * POWER(1.05, 2) AS value_year_2,
        initial_value * POWER(1.05, 3) AS value_year_3,
        initial_value * POWER(1.05, 4) AS value_year_4,
        initial_value * POWER(1.05, 5) AS value_year_5,
        initial_value * POWER(1.05, 6) AS value_year_6,
        initial_value * POWER(1.05, 7) AS value_year_7,
        initial_value * POWER(1.05, 8) AS value_year_8,
        initial_value * POWER(1.05, 9) AS value_year_9,
        initial_value * POWER(1.05, 10) AS value_year_10
    FROM
        finance.items
)
SELECT
    item_id,
    item_name,
    start_date,
    value_year_0,
    value_year_1,
    value_year_2,
    value_year_3,
    value_year_4,
    value_year_5,
    value_year_6,
    value_year_7,
    value_year_8,
    value_year_9,
    value_year_10
FROM
    InterestProjection;

One should rerun the prompt also when it appears that all the possible solutions were exhausted!

Prompt(s):
Write a query to obtain the same result without the use of the previous methods used above.

Output:

WITH InterestProjection AS (
    SELECT
        item_id,
        item_name,
        start_date,
        initial_value,
        CAST(initial_value AS DECIMAL(10, 2)) AS value,
        0 AS year
    FROM
        finance.items
    UNION ALL
    SELECT
        ip.item_id,
        ip.item_name,
        ip.start_date,
        ip.initial_value,
        CAST(ip.value * 1.05 AS DECIMAL(10, 2)) AS value,
        ip.year + 1 AS year
    FROM
        InterestProjection ip
    WHERE
        ip.year < 10
)
SELECT
    item_id,
    item_name,
    start_date,
    MAX(CASE WHEN year = 0 THEN value END) AS value_year_0,
    MAX(CASE WHEN year = 1 THEN value END) AS value_year_1,
    MAX(CASE WHEN year = 2 THEN value END) AS value_year_2,
    MAX(CASE WHEN year = 3 THEN value END) AS value_year_3,
    MAX(CASE WHEN year = 4 THEN value END) AS value_year_4,
    MAX(CASE WHEN year = 5 THEN value END) AS value_year_5,
    MAX(CASE WHEN year = 6 THEN value END) AS value_year_6,
    MAX(CASE WHEN year = 7 THEN value END) AS value_year_7,
    MAX(CASE WHEN year = 8 THEN value END) AS value_year_8,
    MAX(CASE WHEN year = 9 THEN value END) AS value_year_9,
    MAX(CASE WHEN year = 10 THEN value END) AS value_year_10
FROM
    InterestProjection
GROUP BY
    item_id, item_name, start_date;

Copilot missed again to cast the value in the CTE to decimal, respectively introduced the RECURSIVE keyword which had to be removed, though it worked after the chances! Frankly, database engines should be smart enough to cast values in similar cases (though this may probably lead to curious cases). 

Further attempts led to the creation of a stored procedure to encapsulate the logic, respectively to other interesting variations. Though, after some time, the solutions started to reappear, even if the differences are minor. 

Conclusion

What can be learned from this exercise? All the roads lead to Rome, or at least it seems to be the case in this post considering that multiple methods could be used to solve the same problem. More important, one shouldn't stop exploring just because a solution is already available! 

Looking forward at the use of Copilot, ideally, one should formulate the simplest pattern and start exploring incrementally by changing the initial parameters, approaching the problem from different perspectives. Copilot might not solve all problems, though it's good at finding alternatives. 

Happy coding!

Previous Post <<||>> Next Post