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]
.jpg)
