💎🏭SQL Reloaded: Microsoft Fabric's SQL Databases (Part X: Templates for Database Objects)

One of the new features remarked in SQL databases when working on the previous post is the availability of templates in SQL databases. The functionality is useful even if is kept to a minimum. Probably, more value can be obtained when used in combination with Copilot, which requires at least a F12 capacity.

Schemas

Schemas are used to create a logical grouping of objects such as tables, stored procedures, and functions. From a structural and security point of view it makes sense to create additional schemas to manage the various database objects and use the default dbo schema only occasionally (e.g. for global created objects).

-- generated template - schema
CREATE SCHEMA SchemaName

-- create schema
CREATE SCHEMA Test

One can look at the sys.schemas to retrieve all the schemas available:

-- retrieve all schemas
SELECT schema_id
, name
, principal_id
FROM sys.schemas
ORDER BY schema_id

Tables

Tables, as database objects that contain all the data in a database are probably the elements that need the greatest attention in design and data processing. In some cases a table can be dedenormalized and it can store all the data needed, much like in MS Excel, respectively, benormalized in fact and dimension tables. 

Tables can be created explicitly by defining in advance their structure (see Option 1), respectively on the fly (see Option 2). 

-- Option 1

-- create the table manually (alternative to precedent step
CREATE TABLE [Test].[Customers](
	[CustomerId] [int] NOT NULL,
	[AddressID] [int] NULL,
	[Title] [nvarchar](8) NULL,
	[FirstName] [nvarchar](50) NULL,
	[LastName] [nvarchar](50) NULL,
	[CompanyName] [nvarchar](128) NULL,
	[SalesPerson] [nvarchar](256) NULL
) ON [PRIMARY]
GO

-- insert records
INSERT INTO Test.Customers
SELECT CustomerId
, Title
, FirstName 
, LastName
, CompanyName
, SalesPerson
FROM SalesLT.Customer


-- checking the output (both scenarios)
SELECT top 100 *
FROM Test.Customers

One can look at the sys.tables to retrieve all the tables available:

-- retrieve all tables
SELECT schema_name(schema_id) schema_name
, object_id
, name
FROM sys.tables
ORDER BY schema_name
, name

Views

Views are much like virtual table based on the result-set of an SQL statement that combines data from one or multiple tables.  They can be used to encapsulate logic, respectively project horizontally or  vertically a subset of the data. 

-- create view
CREATE OR ALTER VIEW Test.vCustomers
-- Customers 
AS
SELECT CST.CustomerId 
, CST.Title
, CST.FirstName 
, IsNull(CST.MiddleName, '') MiddleName
, CST.LastName 
, CST.CompanyName 
, CST.SalesPerson 
FROM SalesLT.Customer CST

-- test the view 
SELECT *
FROM Test.vCustomers
WHERE CompanyName = 'A Bike Store'

One can look at the sys.views to retrieve all the views available:

-- retrieve all views
SELECT schema_name(schema_id) schema_name
, object_id
, name
FROM sys.views
ORDER BY schema_name
, name

User-Defined Functions

A user-defined function (UDF) allows to create a function by using a SQL expression. It can be used alone or as part of a query, as in the below example.

-- generated template - user defined function
CREATE FUNCTION [dbo].[FunctionName] (
    @param1 INT,
    @param2 INT
)
RETURNS INT AS BEGIN RETURN
    @param1 + @param2
END

-- user-defined function: 
CREATE OR ALTER FUNCTION Test.GetFirstMiddleLastName (
    @FirstName nvarchar(50),
    @MiddleName nvarchar(50),
    @LastName nvarchar(50)
)
RETURNS nvarchar(150) AS 
BEGIN 
   RETURN IsNull(@FirstName, '') + IsNull(' ' + @MiddleName, '') + IsNull(' ' + @LastName, '') 
END

-- test UDF on single values
SELECT Test.GetFirstMiddleLastName ('Jack', NULL, 'Sparrow')
SELECT Test.GetFirstMiddleLastName ('Jack', 'L.', 'Sparrow')

-- test UDF on a whole table
SELECT TOP 100 Test.GetFirstMiddleLastName (FirstName, MiddleName, LastName)
FROM SalesLT.Customer

One can look at the sys.objects to retrieve all the scalar functions available:

-- retrieve all scalar functions
SELECT schema_name(schema_id) schema_name
, name
, object_id
FROM sys.objects 
WHERE type_desc = 'SQL_SCALAR_FUNCTION'
ORDER BY schema_name
, name

However, UDFs prove to be useful when they mix the capabilities of functions with the ones of views allowing to create a "parametrized view" (see next example) or even encapsulate a multi-line statement that returns a dataset. Currently, there seems to be no template available for creating such functions.

-- table-valued function
CREATE OR ALTER FUNCTION Test.tvfGetCustomers (
    @CompanyName nvarchar(50) NULL
)
RETURNS TABLE
-- Customers by Company
AS
RETURN (
	SELECT CST.CustomerId 
	, CST.CompanyName
	, CST.Title
	, IsNull(CST.FirstName, '') + IsNull(' ' + CST.MiddleName, '') + IsNull(' ' + CST.LastName, '') FullName
	, CST.FirstName 
	, CST.MiddleName 
	, CST.LastName 
	FROM SalesLT.Customer CST
	WHERE CST.CompanyName = IsNull(@CompanyName, CST.CompanyName)
);

-- test function for values
SELECT *
FROM Test.tvfGetCustomers ('A Bike Store')
ORDER BY CompanyName
, FullName

-- test function for retrieving all values
SELECT *
FROM Test.tvfGetCustomers (NULL)
ORDER BY CompanyName
, FullName

One can look at the sys.objects to retrieve all the table-valued functions available:

-- retrieve all table-valued functions
SELECT schema_name(schema_id) schema_name
, name
, object_id
FROM sys.objects 
WHERE type_desc = 'SQL_INLINE_TABLE_VALUED_FUNCTION'
ORDER BY schema_name
, name

Stored Procedures

A stored procedure is a prepared SQL statement that is stored as a database object and precompiled. Typically, the statements considered in SQL functions can be created also as stored procedure, however the latter doesn't allow to reuse the output directly.

-- get customers by company
CREATE OR ALTER PROCEDURE Test.spGetCustomersByCompany (
    @CompanyName nvarchar(50) NULL
)
AS
BEGIN
	SELECT CST.CustomerId 
	, CST.CompanyName
	, CST.Title
	, IsNull(CST.FirstName, '') + IsNull(' ' + CST.MiddleName, '') + IsNull(' ' + CST.LastName, '') FullName
	, CST.FirstName 
	, CST.MiddleName 
	, CST.LastName 
	FROM SalesLT.Customer CST
	WHERE CST.CompanyName = IsNull(@CompanyName, CST.CompanyName)
	ORDER BY CST.CompanyName
	, FullName
END 

-- test the procedure 
EXEC Test.spGetCustomersByCompany NULL -- all customers
EXEC Test.spGetCustomersByCompany 'A Bike Store' -- individual customer

One can look at the sys.objects to retrieve all the stored procedures available:

-- retrieve all scalar functions
SELECT schema_name(schema_id) schema_name
, name
, object_id
FROM sys.objects 
WHERE type_desc = 'SQL_STORED_PROCEDURE'
ORDER BY schema_name
, name

In the end, don't forget to drop the objects created above (note the order of the dependencies):

-- drop function 
DROP FUNCTION IF EXISTS Test.GetFirstMiddleLastName

-- drop function 
DROP FUNCTION IF EXISTS Test.tvfGetCustomers 
-- drop precedure
DROP VIEW IF EXISTS Test.Test.spGetCustomersByCompany

-- drop view 
DROP VIEW IF EXISTS Test.vCustomers

-- drop schema
DROP SCHEMA IF EXISTS Test

Previous Post <<||>> Next Post

References:
[1] Microsoft Learn (2024) Microsoft Fabric: Overview of Copilot in Fabric [link]

💎🏭SQL Reloaded: Microsoft Fabric's SQL Databases (Part IX: From OLTP to OLAP Data Models)

With SQL databases Microsoft brought OLTP to Microsoft Fabric which allows addressing a wider range of requirements, though this involves also some challenges that usually are addressed by the transition from the OLTP to OLAP architectures. Typically, there's an abstraction layer that is built on top of the OLTP data models that allows to address the various OLAP requirements. As soon as OLTP and OLAP models are mixed together, this opens the door to design and data quality issues that have impact on the adoption of solutions by users. Probably, those who worked with MS Access or even MS Excel directly or in combination with SQL Server can still remember the issues they run into.

Ideally, it should be a separation layer between OLTP and the OLAP data. This can be easily achieved in SQL databases by using two different schemas that mimic the interaction between the two types of architectures. So, supposing that the dbo schema from the SalesLT is the data as maintain by the OLTP layer, one can add an additional schema Test in which the OLAP logic is modelled. This scenario is not ideal, though it allows to model the two aspects of the topic considered. The following steps are to be performed in the environment in which the SalesLT database was created. 

Independently in which layer one works, it's ideal to create a set of views that abstracts the logic and ideally simplifies the processing of data. So, in a first step it's recommended to abstract the data from the source by creating a set of views like the one below:

-- drop view (cleaning)
-- DROP VIEW IF EXISTS SalesLT.vCustomerLocations 

-- create view
CREATE VIEW SalesLT.vCustomerLocations
-- Customers with main office
AS
SELECT CST.CustomerId 
, CSA.AddressID
, CST.Title
, CST.FirstName 
, IsNull(CST.MiddleName, '') MiddleName
, CST.LastName 
, CST.CompanyName 
, CST.SalesPerson 
, IsNull(CSA.AddressType, '') AddressType
, IsNull(ADR.City, '') City
, IsNull(ADR.StateProvince, '') StateProvince
, IsNull(ADR.CountryRegion, '') CountryRegion
, IsNull(ADR.PostalCode, '') PostalCode
FROM SalesLT.Customer CST
	 LEFT JOIN SalesLT.CustomerAddress CSA
	   ON CST.CustomerID = CSA.CustomerID
	  AND CSA.AddressType = 'Main Office'
	 	LEFT JOIN SalesLT.Address ADR
		  ON CSA.AddressID = ADR.AddressID

The view uses LEFT instead of FULL joins because this allows more flexibility, respectively identifying the gaps existing between entities (e.g. customers without addresses). In these abstractions, the number of transformations is kept to a minimum to reflect the data as reflected by the source. It may be chosen to minimize the occurrence of NULL values as this simplifies the logic for comparisons (see the use of IsNull).

Once the abstraction from the OLTP layer was built, one can make the data available in the OLAP layer:

-- create schema
CREATE SCHEMA Test

-- dropping the target table (for cleaning)
-- DROP TABLE IF EXISTS Test.CustomerLocations

-- Option 1
-- create the table on the fly
SELECT *
INTO Test.CustomerLocations
FROM SalesLT.vCustomerLocations

-- Option 2
-- create the table manually (alternative to precedent step
CREATE TABLE [Test].[CustomerLocations](
	[CustomerId] [int] NOT NULL,
	[AddressID] [int] NULL,
	[Title] [nvarchar](8) NULL,
	[FirstName] [nvarchar](50) NULL,
	[MiddleName] [nvarchar](50) NULL,
	[LastName] [nvarchar](50) NULL,
	[CompanyName] [nvarchar](128) NULL,
	[SalesPerson] [nvarchar](256) NULL,
	[AddressType] [nvarchar](50) NULL,
	[City] [nvarchar](30) NULL,
	[StateProvince] [nvarchar](50) NULL,
	[CountryRegion] [nvarchar](50) NULL,
	[PostalCode] [nvarchar](15) NULL
) ON [PRIMARY]
GO

-- insert records
INSERT INTO Test.CustomerLocations
SELECT *
FROM SalesLT.vCustomerLocations


-- checking the output (both scenarios)
SELECT top 100 *
FROM Test.CustomerLocations


-- drop the view (for cleaning)
-- DROP VIEW IF EXISTS Test.vCustomerLocations

-- create view
CREATE VIEW Test.vCustomerLocations
-- Customer locations
AS
SELECT CSL.CustomerId 
, CSL.AddressID
, CSL.Title
, CSL.FirstName 
, CSL.MiddleName 
, CSL.LastName 
, Concat(CSL.FirstName, ' ' + CSL.MiddleName, ' ', CSL.LastName) FullName
, CSL.CompanyName 
, CSL.SalesPerson 
, CSL.AddressType
, CSL.City
, CSL.StateProvince
, CSL.CountryRegion 
, CSL.PostalCode
FROM Test.CustomerLocations CSL

-- test the view
SELECT top 100 *
FROM Test.vCustomerLocations

Further on, one can create additional objects as required. Usually, a set of well-designed views is enough, offering the needed flexibility with a minimum of code duplication. In addition, one can build stored procedures and table-valued functions as needed:

-- drop the function (for cleaning)
-- DROP FUNCTION IF EXISTS Test.tvfGetCustomerAddresses

-- generated template - function
CREATE FUNCTION Test.tvfGetCustomerAddresses (
    @CountryRegion nvarchar(50) NULL,
    @StateProvince nvarchar(50) NULL
)
RETURNS TABLE
-- Customers by Country & State province
AS
RETURN (
SELECT CSL.CustomerId 
, CSL.AddressID
, CSL.Title
, CSL.FirstName 
, CSL.MiddleName 
, CSL.LastName 
, CSL.FullName
, CSL.CompanyName 
, CSL.SalesPerson 
, CSL.AddressType 
, CSL.City
, CSL.StateProvince 
, CSL.CountryRegion 
, CSL.PostalCode
FROM Test.vCustomerLocations CSL
WHERE CSL.CountryRegion = IsNull(@CountryRegion, CSL.CountryRegion)
  AND CSL.StateProvince = IsNull(@StateProvince, CSL.StateProvince)
);

-- retrieving all records
SELECT *
FROM Test.tvfGetCustomerAddresses(NULL, NULL)

-- providing parameters
SELECT *
FROM Test.tvfGetCustomerAddresses('United States', 'Utah')

-- filtering on non-parametrized volumns
SELECT *
FROM Test.tvfGetCustomerAddresses('United States', 'Utah')
WHERE City = 'Salt Lake City'



-- drop the procedure (for cleaning)
-- DROP PROCEDURE IF EXISTS Test.spGetCustomerAddresses 

-- generated template - stored procedure
CREATE PROCEDURE Test.spGetCustomerAddresses (
    @CountryRegion nvarchar(50) NULL,
    @StateProvince nvarchar(50) NULL
)
-- Customers by Country & State province
AS
BEGIN
	SELECT CSL.CustomerId 
	, CSL.AddressID
	, CSL.Title
	, CSL.FirstName 
	, CSL.MiddleName 
	, CSL.LastName 
	, CSL.FullName
	, CSL.CompanyName 
	, CSL.SalesPerson 
	, CSL.AddressType 
	, CSL.City
	, CSL.StateProvince 
	, CSL.CountryRegion 
	, CSL.PostalCode
	FROM Test.vCustomerLocations CSL
	WHERE CSL.CountryRegion = IsNull(@CountryRegion, CSL.CountryRegion)
	AND CSL.StateProvince = IsNull(@StateProvince, CSL.StateProvince)
END 

-- retrieving all records
EXEC Test.spGetCustomerAddresses NULL, NULL

-- providing parameters
 EXEC Test.spGetCustomerAddresses 'United States', 'Utah'

These steps can repeated for each entity in scope.

This separation between OLTP and OLAP is usually necessary given that business processes need a certain amount of time until they are correctly reflected as per reporting needs. Otherwise, the gaps can negatively impact the quality of data used for reporting. For some reports these deviation might be acceptable, though there will be probably also (many) exceptions. Independently of the solution used, it's still needed to make sure that the data are appropriate for the processes and reporting. 

If no physical separation is needed between the two types of layers, one can remove the persisted tables from the logic and keep the objects as they are.

Independently of which architecture is chosen, one shouldn't forget to validate one's presumptions in what concerns the data model (e.g. customers without addresses, address types, etc.).

Previous Post <<||>> Next Post

🧩IT: The Annotated Laws that Govern IT Professionals' Lives - Part I

"A bad idea executed to perfection is still a bad idea." (Norman R Augustine) [Augustine's Law]

"Bad code executed by powerful machines is still bad code." [sql-troubles]

"A great many problems do not have accurate answers, but do have approximate answers, from which sensible decisions can be made." (Berkeley's Law)

"It's easier to take/sell approximations as accurate answers than to find accurate answers. In time people will see no difference in between." [sql-troubles]

"About the time you finish doing something, you know enough to start." (James C Kinser) [Kinser's Law]

"By the time you finish something, the problem changed." [sql-troubles]

"Adding manpower to a late software project makes it later." (Fred P Brooks, "The Mythical Man-Month: Essays", 1975) [Brook's Law]

"An object will fall so as to do the most damage." [Law of selective gravity]

"A bug will appear to do the most damage." [sql-troubles]

"Anything can be made to work if you fiddle with it long enough." (Wyszkowski's second law)

"Some problems do require infinite time." [sql-troubles]

"Build a system that even a fool can use, and only a fool will want to use it." [Shaw's principle]

"Doing it the hard way is always easier." (Murphy's paradox)

"Doing it the easy way is always harder." [sql-troubles]

"Don't force it - get a bigger hammer." [Anthony's law of force]

"Don't optimize it, get a more powerful machine." [sql-troubles]

"Every solution breeds new problems." [Murphy's laws]

"Every new problem multiplies the possible solutions." [sql-troubles]

"Everyone has a scheme that will not work." [Howe's law]

"Everything goes wrong all at once." (Quantized revision of Murphy's law)

"Small events converge toward bigger events." [sql-troubles]

"If a problem causes many meetings, the meetings eventually become more important than the problem." (Arthur Bloch, "Murphy's Law (Price/Stern/Sloan", 1977) (Hendrickson’s Law)

"More meetings tend to create more problems." [sql-troubles]

"If a project is not worth doing at all, it's not worth doing well." (Gordon's first law)

"The more a project is not worth doing, the more attention will attract."  [sql-troubles]

"If an experiment works, something has gone wrong." [Finagle's first law]

"If anything can go wrong, it will." [Murphy's laws]

"Things go wrong at a faster pace than one can find solutions." [sql-troubles]

"If there are two or more ways to do something, and one of those ways can result in a catastrophe, then someone will do it." [Murphy's Laws]

"It's enough one way, for things to result in catastrophes." [sql-troubles]

"If they know nothing of what you are doing, they suspect you are doing nothing." (Robert J Graham et al, "The Complete Idiot's Guide to Project Management", 2007)  [Graham's Law]

"People are good at ignoring the obvious." [sql-troubles]

"The more one explains, the more one is misunderstood." [sql-troubles] 

"If you mess with a thing long enough, it'll break." [Schmidt's law]

"Things break by design." [sql-troubles]

"One can learn to break things, by simply playing with them." [sql-troubles] 

"It's easier to break than design things. One can find thousands ways on how to break the same thing." [sql-troubles] 

"In any collection of data, the figure most obviously correct, beyond all need of checking, is the mistake." (Finagle's third law)

"In any collection of data there's at least a mistake." [sql-troubles]

"In any given set of circumstances, the proper course of action is determined by subsequent events." [McDonald's corollary to Murphy's laws]

"In crises that force people to choose among alternative courses of action, most people will choose the worst one possible." (Rudin's law)

"People go wrong with confidence." [sql-troubles]

"The more alternatives, the higher the chances to go wrong." [sql-troubles] 

"Information necessitating a change of design will be conveyed to the designer after - and only after - the plans are complete." [First law of revision:]

"In simple cases, presenting one obvious right way versus one obvious wrong way, it is often wiser to choose the wrong way so as to expedite subsequent revision." (First corollary

"The designer will get ahead of the design." [sql-troubles] 

"It is impossible to make anything foolproof because fools are so ingenious." (Murphy's second corollary)

"It works better if you plug it in." (Sattinger's law)

"It works longer if you don't plug it in." [sql-troubles]

"It's not a question of IF the car will break down, but WHEN it will break down." (Murphy's theory of automobiles)

"It's not a question of IF a program will break down, but when the code will break down." [sql-troubles]

"The longer a program runs smoothly, the higher the chances that will break down soon." [sql-troubles]

"Left to themselves, things tend to go from bad to worse." (Murphy's first corollary)

"The more on tries to fix things, the faster everything goes worse." [sql-troubles]

"Logic is a systematic method of coming to the wrong conclusion with confidence." (Manly's maxim)

 "One doesn't need logic to arrive at the right conclusion." [sql-troubles]

"Matter will be damaged in direct proportion to its value." (Murphy's constant)

"Most problems have either many answers or no answer. Only a few problems have a single answer." [Berkeley's Law]

"It's better to have a multitude of approximate solutions than one correct solution." [sql-troubles]

"Negative expectations yield negative results. Positive expectations yield negative results." (Non-reciprocal law of expectations)

"Negative results yield when there are no expectations." [sql-troubles]

"No matter how many things have gone wrong, there remains at least one more thing that will go wrong." (Murphy's law of the infinite)

"Things can go wrong in a multitude of ways." [sql-troubles]

"No matter how minor the job is, it's still over $50." (Murphy's rule of auto repair)

"No matter what the experiment's result, there will always be someone eager to: (i) misinterpret it, (ii) fake it, or (c) believe it supports his own pet theory." (Finagle's second law)

"It's easier to fake the experiment to get the right results." [sql-troubles]

"Nothing ever goes away." (Commoner's second law of ecology)

"Things do go away, but tend to come back." [sql-troubles]

"Nothing is as easy as it looks." (Murphy's first corollary)

"All things look simple until one dives deeper." [sql-troubles]

"Nothing is ever so bad that it can't get worse." (Gattuso's extension of Murphy's Law)

"Once a job is fouled up, anything done to improve it only makes it worse." (Finagle's fourth law)

"Once a mistake is corrected, a second mistake will become apparent." (Murphy's law of revision)

"Correcting mistakes introduces other mistakes." [sql-troubles]

"The chief cause of problems is solutions." [Sevareid's Law]

"The more time you spend in reporting on what you are doing, the less time you have to do anything. Stability is achieved when you spend all your time doing nothing but reporting on the nothing you are doing." [Cohn's Law]

"Reporting increases the needs for more information. The less one reports, the lower the need for further information." [sql-troubles]

"The more innocuous the modification appears to be, the further its influence will extend and the more plans will have to be redrawn." [H B Fyfe's second law of revision]

"The only thing more costly than stretching the schedule of an established development program is accelerating it, which is itself the most costly action known to man." (Norman R Augustine, "Augustine's Laws", 1983) [Law of economic unipolarity]

"The other line moves faster." (Etorre's observation)

"The other team moves faster." [sql-troubles]

"If you change lines, the one you just left will start to move faster than the one you are now in." (O'Brien's variation

"If you change a line, the whole codes breaks." [sql-trpoubles]

"The longer you wait in line, the greater the likelihood that you are in the wrong line." (The Queue Principal)

"The longer you wait for a deliverable, the greater the likelihood that it contains bugs." [sql-troubles]

"The perceived usefulness of an article is inversely proportional to its actual usefulness once bought and paid for." (Glatum's law of materialistic acquisitiveness)

"The probability of anything happening is in inverse ratio to its desirability." (Gumperson's law)

"The solution to a problem changes the problem." [Peers's Law]

"The tasks to do immediately are the minor ones; otherwise, you’ll forget them. The major ones are often better to defer. They usually need more time for reflection. Besides, if you forget them, they’ll remind you." [Wolf ’s Law of Management]

"There are two states to any large project: Too early to tell and too late to stop." (Ernest Fitzgerald) [Fitzgerald's Law]

"There is a solution to every problem; the only difficulty is finding it." [Evvie Nef's Law]

"There is no mechanical problem so difficult that it cannot be solved by brute strength and ignorance. [William's Law]

"There's no software problem so difficult that can't be solved by brute force and ignorance." [sql-troubles]

"There's always one more bug." (Lubarsky's law of cybernetic entomology)

"Software solutions diverge to a set of bugs." [sql-troubles

"Things get worse under pressure." [Murphy's law of thermodynamics]

"Things get worse also without pressure." [sql-troubles]

"Things go right gradually, but things go wrong all at once." (Murphy's asymmetry principle)

"Tolerances will accumulate unidirectionally toward maximum difficulty of assembly. (Klipstein's law)

"Two wrongs are only the beginning." (Kohn's corollary to Murphy's law)

"When all else fails, read the instructions." [Cahn's axiom]

"Even if you read the instructions, things fall." [sql-troubles]

"When an error has been detected and corrected, it will be found to have been correct in the first place." [Scott's second law]

"When in doubt, use a bigger hammer." [Dobbins’ Law]

"When taking something apart to fix a minor malfunction, you will cause a major malfunction." (Murphy's second law of construction)

"Whenever you set out to do something, something else must be done first." (Murphy's sixth corollary)

"While the difficulties and dangers of problems tend to increase at a geometric rate, the knowledge and manpower qualified to deal with these problems tend to increase linearly." [Dror's First Law]

"Beyond a point, the problems are so complex that people can't differentiate between geometric and linear rates." [sql-troubles]

 Previous Post <<||>> Next Post

🧩IT: Idioms, Sayings, Proverbs and Other Words of Wisdom

In IT setups one can hear many idioms, sayings and other type of words of wisdom that make the audience smile, even if some words seem to rub salt in the wounds. These are some of the idioms met in IT meetings or literature. Frankly, it's worth to write more about each of them, and this it the purpose of the "project". 

"A bad excuse is better than none"

"A bird in the hand is worth two in the bush": a working solution is worth more than hypothetically better solutions. 

"A drowning man will clutch at a straw": a drowning organization will clutch to the latest hope

"A friend in need (is a friend indeed)": 

"A journey of a thousand miles begins with a single step"

"A little learning is a dangerous thing"

"A nail keeps a shoe, a shoe a horse, a horse a man, a man a castle" (cca 1610): A nail keeps the shoe

"A picture is worth a thousand words"

"A stitch in time (saves nine)"

"Actions speak louder than words"

"All good things must come to an end"7

"All roads lead to Rome"

"All is well that ends well"

"An ounce of prevention is worth a pound of cure"

"Another day, another dollar"

"As you sow so shall you reap"

"Beauty is in the eye of the beholder"

"Better late than never": SQL Server and Excel Data

"Better safe than sorry": Deleting obsolete companies

"Big fish eat little fish"

"Better the Devil you know (than the Devil you do not)": 

"Calm seas never made a good sailor"

"Count your blessings"

"Dead men tell no tales"

"Do not bite the hand that feeds you"

"Do not change horses in midstream"

"Do not count your chickens before they are hatched"

"Do not cross the bridge till you come to it"

"Do not judge a book by its cover"

"Do not meet troubles half-way"

"Do not put all your eggs in one basket"

"Do not put the cart before the horse"

"Do not try to rush things; ignore matters of minor advantage" (Confucius): A tale of two cities II

"Do not try to walk before you can crawl"

"Doubt is the beginning, not the end, of wisdom"

"Easier said than done"

"Every cloud has a silver lining"

"Every little bit helps"

"Every picture tells a story"

"Failing to plan is planning to fail": Planning correctly misunderstood...

"Faith will move mountains"

"Fake it till you make it"

"Fight fire with fire"

"First impressions are the most lasting"

"First things first": Ways of looking at data

"Fish always rots from the head downwards"

"Fools rush in (where angels fear to tread)" (Alexander Pope, "An Essay on Criticism", cca. 1711): A tale of two cities II

"Half a loaf is better than no bread"

"Haste makes waste"

"History repeats itself"

"Hope for the best, and prepare for the worst"

"If anything can go wrong, it will" (Murphy's law)

"If it ain't broke, don't fix it.": Approaching a query

"If you play with fire, you will get burned"

"If you want a thing done well, do it yourself"

"Ignorance is bliss"

"Imitation is the sincerest form of flattery"

"It ain't over till/until it's over"

"It is a small world"

"It is better to light a candle than curse the darkness"

"It is never too late": A look back, All-knowing developers are back...

"It's a bad plan that admits of no modification." (Publilius Syrus): Planning Correctly Misunderstood I

"It’s not an adventure until something goes wrong." (Yvon Chouinard): Documentation - Lessons learned

"It is not enough to learn how to ride, you must also learn how to fall"

"It takes a whole village to raise a child"

"It will come back and haunt you"

"Judge not, that ye be not judged"

"Kill two birds with one stone"

"Knowledge is power, guard it well"

"Learn a language, and you will avoid a war" (Arab proverb)

"Less is more"

"Life is what you make it"

"Many hands make light work"

"Moderation in all things"

"Money talks"

"More haste, less speed"

"Necessity is the mother of invention"

"Never judge a book by its cover"

"Never say never"

"Never too old to learn"

"No man can serve two masters"

"No pain, no gain"

"No plan ever survived contact with the enemy.' (Carl von Clausewitz): Planning Correctly Misunderstood I

"Oil and water do not mix"

"One man's trash is another man's treasure"

"One swallow does not make a summer"

"Patience is a virtue"

"Practice makes perfect"

"Practice what you preach"

"Prevention is better than cure"

"Rules were made to be broken"

"Seek and ye shall find"

"Some are more equal than others" (George Orwell, "Animal Farm")

"Spoken words fly away, written words remain." ["Verba volant, scripta manent"]: Documentation - Lessons learned

"Strike while the iron is hot"

"The best defense is a good offense"

"The bigger they are, the harder they fall"

"The devil is in the detail": More on SQL Databases, The Analytics Marathon, The Choice of Tools in PM, Who Messed with My Data?

"The die is cast"

"The exception which proves the rule"

"The longest journey starts with a single step"

"The pursuit of perfection is a fool's errand"

"There are two sides to every question"

"There is no smoke without fire"

"There's more than one way to skin a cat" (cca. 1600s)

"There is no I in team"

"There is safety in numbers"

"Those who do not learn from history are doomed to repeat it" (George Santayana)

"Time is money"

"To learn a language is to have one more window from which to look at the world" (Chinese proverb)[5

"Too little, too late"

"Too much of a good thing"

"Truth is stranger than fiction"

"Two birds with one stone": Deleting sequential data...

"Two heads are better than one": Pair programming

"Two wrongs (do not) make a right"

"United we stand, divided we fall"

"Use it or lose it"

"Unity is strength"

"Variety is the spice of life." (William Cowper)

"Virtue is its own reward"

"Well begun is half done"

"What does not kill me makes me stronger"

"Well done is better than well said"

"What cannot be cured must be endured"

"What goes around, comes around"

"When life gives you lemons, make lemonade"

"When the cat is away, the mice will play"

"When the going gets tough, the tough get going"

"Where there is a will there is a way"

"With great power comes great responsibility"

"Work expands so as to fill the time available"

"You are never too old to learn": All-Knowing Developers are Back in Demand?

"You can lead a horse to water, but you cannot make it drink"

"You cannot make an omelet without breaking eggs"

"(You cannot) teach an old dog new tricks"

"You must believe and not doubt at all": Believe and not doubt

"Zeal without knowledge is fire without light"

Previous Post <<||>> Next Post

References:
[1] Wikipedia (2024) List of proverbial phrases [link]

💠🛠️🗒️SQL Server: Folding [Notes]

Disclaimer: This is work in progress intended to consolidate information from various sources. It considers only on-premise SQL Server, for other platforms please refer to the documentation.

Last updated: 20-Feb-2024

[SQL Server] Folding

• {def} the process by which the optimizer is able to properly determine certain types of indexable expressions even when the column in the expression is involved in a subexpression or nestled in a function 
• is an optimization over older versions of SQL Server in which the optimizer was unable to use an index to service a query clause when the table column was involved in an expression or buried in a function [1]
• {type} constant folding 
• some constant expression is evaluated early
• foldable constant expressions [2]
• arithmetical expressions 
• logical expressions 
• built-in functions whose input doesn’t depend of contextual information, 
• e. g. SET options, language settings, database options, encryption keys
• deterministic built-in functions are foldable, with some exceptions
• certain forms of the LIKE predicate
• [SQL Server 2012+] deterministic methods of CLR user-defined types [3]
• [SQL Server 2012+] deterministic scalar-valued CLR user-defined functions [3]
• nonfoldable expressions [2]
• expressions whose results depend on a local variable or parameter
• user-defined functions
• both T-SQL and CLR
• expressions whose results depend on language settings.
• expressions whose results depend on SET options.
• expressions whose results depend on server configuration options.
• nonconstant expressions such as an expression whose result depends on the value of a column.
• nondeterministic functions
• if the output is a large object type, then the expressions are not folded 
• e.g. text, image, nvarchar(max), varchar(max), varbinary(max), XML
• {benefit} the expression does not have to be evaluated repeatedly at run time [2]
• {benefit} the value of the expression after it is evaluated is used by the query optimizer to estimate the size of the result set of the portion of the query [2]
• e.g. TotalDue > 117.00 + 1000.00
• {type} nonconstant folding
• some expressions that are not constant folded but whose arguments are known at compile time, whether the arguments are parameters or constants, are evaluated by the result-set size (cardinality) estimator that is part of the optimizer during optimization [2]
• deterministic functions: 
• e.g. UPPER, LOWER, RTRIM
• e.g. DATEPART( YY only ), GetDate, CAST, CONVERT
• operators 
• arithmetic operators: +, -, *, /, unary -, 
• logical Operators: AND, OR, NOT
• comparison operators: <, >, <=, >=, <>, LIKE, IS NULL, IS NOT NULL

Previous Post <<||>> Next Post

References:

[1] Ken Henderson (2003) Guru's Guide to SQL Server Architecture and Internals

[2] Microsoft Learn (2012) SQL Server: Troubleshooting Poor Query Performance: Constant Folding and Expression Evaluation During Cardinality Estimation [link]
[3] Microsoft Learn (2025) SQL Server: Query processing architecture guide [link]
[4] SQLShack (2021) Query Optimization in SQL Server for beginners, by Esat Erkec [link]

💠🛠️🗒️SQL Server: Nulls [Notes]

Disclaimer: This is work in progress intended to consolidate information from various sources. It considers only on-premise SQL Server, for other platforms please refer to the documentation.

Last updated: 20-Feb-2024

[SQL Server] Null

• {def} keyword that indicates that the value is unknown [1]
• different from an empty or zero value [1]
• no two null values are equal [1]
• comparisons between two null values, or between a null value and any other value, return unknown because the value of each NULL is unknown [1]
• indicates the the value is
• unknown
• not applicable
• to be added later
• ⇒ can't be used as information that is required to distinguish one row in a table from another row in a table [1]
• can be assigned to a value by
• explicitly stating NULL in an INSERT or UPDATE statement [1[
• leaving a column out of an INSERT statement [1]
• {recommendation} test for null values in queries 
• via IS NULL or IS NOT NULL in the WHERE clause [1]
• WHEN present in data, logical and comparison operators can potentially return a third result of UNKNOWN instead of just TRUE or FALSE [1]
•  ⇐ three-valued logic can be the source for many application errors [1]
• ⇐ parameters and variables not explicitly initialized can cause problems in code
• {recommendation} handle null values in logic
• via IsNull or Coalesce functions
• {constraint} [primary kyes] if any of the columns considered in a primary key contain NULL values, the PRIMARY KEY constraint can’t be created [3]
• {constraint} [UNIQUE constraint] allows the columns that make up the constraint to allow NULLs, but it doesn’t allow all key columns to be NULL for more than one row [3]
• [data warehouse] nullability of columns
• {best practice} define columns as NOT NULL when appropriate 
• {benefit} helps the Query Optimizer 
• {benefit} reduces in some cases the storage space required for the data
• {benefit} allows SQL Server to avoid unnecessary encoding in columnstore indexes and during batch mode execution [2]
• {example} [SQL Server 2000+] bigint column
• when the value is defined as NOT NULL , the value fits into a single CPU register
• ⇒ operations on the value can be performed more quickly
• a nullable bigint column requires another, 65th bit to indicate NULL values
• SQL Server avoids cross-register data storage by storing some of the row values (usually the highest or lowest values) in main memory using special markers to indicate it in the data that resides in the CPU cache [2]
• ⇒ adds extra load during execution
• {recommendation} avoid nullable columns in data warehouse environments [2]
• ⇐ the recommendation can apply also to OLTP databases
• there are database designs that enforces not null values for all attributes
• e.g. Dynamics AX 2009/365 F&O
• {benefit} eliminates the need to test for null values in legacy code
• {recommendation} use CHECK and UNIQUE constraints or indexes when overhead introduced by constraints or unique indexes is acceptable [2]
• {recommendation} consider using filtered indexes instead of normal indexes for columns with many null values
• minimizes the waste of storage space
• ⇐ understand the characteristics of the columns used in the queries [3]

See also:  Null-ifying the World, Dynamic Queries, Handling missing dates

Previous Post <<||>> Next Post

References:

[1] Microsoft Learn (2024) SQL Server 2022: NULL and UNKNOWN (T-SQL)
[2] Dmitri Korotkevitch (2016) Pro SQL Server Internals 2nd Ed.

[3] Microsoft SQL Server 2012 Internals, by Kalen Delaney, Bob Beauchemin, Conor Cunningham, Jonathan Kehayias, Benjamin Nevarez & Paul S. Randal, Microsoft Press, ISBN: 978-0-7356-5856-1 , 2013