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. 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 stored all the data needed much like in MS Excel, respectively normalized in facts 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 , SalesPersonFROM 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 as 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 as 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. 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 as 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 ???. Typically, the statements considered in SQL functions can be created also as stored procedure, however the latter doesn't allow the reuse of 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 as 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]
No comments:
Post a Comment