User Defined Objects

Views

A view is a virtual table based on the result-set of an SQL statement. A view contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database.

Views can join multiple tables to present the data as if the data were coming from a single table.

Views in SQL offer advantages like data security, simplified complexity, and data consistency, but can also lead to performance issues.

Naming views with a preceding v, v_ or vw_ helps to differentiate between tables and view when writing and reading code.

WorldPop Demo Data

This code will be used for the exercises in this section.

Execute script 0501-01-05 to create a WorldPop table and add data to it.

-- 0501-01-10 Demonstration Code ================
-- Show all the rows in the WorldPop table
Select all the rows in the WorldPop table
SELECT * FROM [dbo].[WorldPop]

Creating Views

The Create View command will create a new view.

Note: Before objects such as views, procedures, functions and triggers can be created or altered, all dependencies must already exist. Additionally the CREATE or ALTER statement must be the first statement in a batch, any code above in the script should be followed with GO,

Syntax

CREATE VIEW <ViewName>
AS
sql_statement / code

Notice that in views the output column names must be unique.

-- Code fails because of ambiguous column names from the source tables
CREATE VIEW v_SocialMediaFriends AS
SELECT F.[Name], F.[Friends], L.[Name], L.[Friends]
FROM [Facebook] F
JOIN [LinkedIn] L
ON L.[Name] = F.[Name];
GO

CreatingViews01

The above code will work after adding column aliases

-- 0501-02-05 Demonstration Code ================
CREATE VIEW [v_SocialMediaFriends] AS
SELECT F.[Name] AS F_Name, F.[Friends] AS F_Friends, L.[Name] AS L_Name, L.[Friends] AS L_Friends
FROM [Facebook] F
JOIN [LinkedIn] L
ON L.[Name] = F.[Name];
GO

-- 0501-02-10 Demonstration Code ================
-- Run the v_SocialMediaFriends view
SELECT * FROM [v_SocialMediaFriends]

CreatingViews02

Altering Views

The Alter View command will modify an existing view.

Syntax

ALTER VIEW <ViewName>
AS
sql_statement / code

-- 0501-03-10 Demonstration Code ================
-- Alter the v_SocialMediaFriends view
ALTER VIEW v_SocialMediaFriends AS
SELECT F.[Name] AS F_Name, F.[Friends] AS F_Friends, L.[Name] AS L_Name, L.[Friends] AS L_Friends
FROM [Facebook] f
FULL OUTER JOIN [LinkedIn] l
ON f.[Name] = l.[Name]
GO

-- 0501-03-10 Demonstration Code ================
-- Run the v_SocialMediaFriends view
SELECT * FROM [v_SocialMediaFriends]

AlterView01

Materialized Views

Unlike a normal (virtual) view, which doesn't store data itself but dynamically retrieves it when queried, a materialized view is precomputed and stored as a table-like structure. This can enhance query performance by avoiding the need to recompute results every time the view is queried.

Nested Views

Nested views include other dependencies which may include other views.

Nested views can lead to deployment and performance issues.

To script out a view with its dependencies

Right-click on database
Select Task > Generate SQL script
Choose Select specific database objects
Expand Views
Check the desired view
Click the Next button
Click the Advanced button
Set "Generate script for all dependent objects" = True
Complete the wizard

Updating Data from a View

Updating data in dependent tables can be tricky with views and is not commonly implemented.

Single table view

Updating data can be simple as long as the view is created based on one single table then direct "Update View" statement would work as long as it does not contain something like GROUP BY.

Multi-Table view

But if the view is created based on multiple tables it becomes more complicated.

The View can be updated if based on multiple tables as long as the update only affects one table. The Single-table view rules above will apply.

If multiple tables are to be updated by a view, then advanced methods such as triggers need to be implemented.

Stored Procedures

Creating Stored Procedures

The Create Procedure command will create a new stored procedure.

Syntax

CREATE PROCEDURE <ProcedureName>
AS
sql_statement / code

Ensure the WorldPop table and data exists. (Script 0409-01-05)

It is a good practice to add comments to scripts, this is especially true for stored procedures. It can provide information to others about what the script is used for and how to use it.

-- 0502-01-05 Demonstration Code ================
CREATE PROCEDURE sp_WorldPopList
AS
/*
List all rows ordered by [Land Area (km)] DESC
Usage: EXEC sp_WorldPopList
*/
SELECT * FROM [dbo].[WorldPop]
GO

-- 0502-01-10 Demonstration Code ================
-- Execute the SP that shows all the rows in the WorldPop table
EXEC sp_WorldPopList

Altering Stored Procedures

The Alter Procedure command will modify an existing stored procedure.

Syntax

ALTER PROCEDURE <ProcedureName>
AS
sql_statement / code

-- 0502-02-05 Demonstration Code ================
-- Alter the existing SP sp_WorldPopList to add ORDER BY
ALTER PROCEDURE sp_WorldPopList
AS
/*
List all rows ordered by [Land Area (km)] DESC
Usage: EXEC sp_WorldPopList
*/
SELECT * FROM [dbo].[WorldPop]
ORDER BY [Land Area (km)] DESC
GO

-- 0502-02-10 Demonstration Code ================
-- Execute the SP that shows all the rows in the WorldPop table
-- with ORDER BY
EXEC sp_WorldPopList

Magic 8 Ball Procedure

This SP will emulate the Magic 8 Ball toy. Ask it a question and it will return a random answer.

This script uses a parameter to accept a question (@Question) as a nvarchar.

-- 0502-03-05 Demonstration Code ================
-- Create Majic8Ball Procedure
CREATE PROCEDURE [dbo].[sp_Magic8Ball]
(
@Question nvarchar(max)
)
AS
/*
Provide a random answer to a question
Usage: EXEC sp_Magic8Ball 'any question'
*/
BEGIN
SET NOCOUNT ON

DECLARE @Answer nvarchar(100)
DECLARE @Magic8BallAnswers TABLE
(
Answer_ID int
,Answer_Type nvarchar(25)
,Answer_Text nvarchar(100)
)

INSERT INTO @Magic8BallAnswers
VALUES (1,'Positive','It is certain')
,(2,'Positive','It is decidedly so')
,(3,'Positive','Without a doubt')
,(4,'Positive','Yes definitely')
,(5,'Positive','You may rely on it')
,(6,'Positive','As I see it, yes')
,(7,'Positive','Most likely')
,(8,'Positive','Outlook good')
,(9,'Positive','Yes')
,(10,'Positive','Signs point to yes')
,(11,'Neutral','Reply hazy try again')
,(12,'Neutral','Ask again later')
,(13,'Neutral','Better not tell you now')
,(14,'Neutral','Cannot predict now')
,(15,'Neutral','Concentrate and ask again')
,(16,'Negative','Don''t count on it')
,(17,'Negative','My reply is no')
,(18,'Negative','My sources say no')
,(19,'Negative','Outlook not so good')
,(20,'Negative','Very doubtful')

SELECT TOP 1 @Answer = Answer_Text FROM @Magic8BallAnswers ORDER BY ABS(CHECKSUM(NEWID()) % 100)
PRINT @Answer
END

-- 0502-03-10 Demonstration Code ================
-- Test the Procedure Majic8Ball
EXEC sp_Magic8Ball 'Does the code work?'

Enhanced SP

In order for this SP to work properly, ensure the WorldPop table exist and the data is imported.

-- 0502-04-05 Demonstration Code ================
-- Procedure with parameters to control result sorting
CREATE PROCEDURE sp_WorldPopList
@Sort [varchar](128),
@Order [varchar](128) = 'ASC',
@Count [varchar](3)=10
AS
/*
List all rows ordered by passed value
Usage
EXEC sp_WorldPopList @Sort='Fert Rate'
EXEC sp_WorldPopList @Sort='Migrants', @Order='DESC'
EXEC sp_WorldPopList @Sort='Migrants', @Order='ASC'
EXEC sp_WorldPopList @Sort='Net Change', @Order='DESC', @Count = 15
*/
BEGIN
SET NOCOUNT ON
DECLARE @SqlCode [nvarchar](MAX)
SET @SqlCode = N'SELECT TOP(' + @Count + ') * FROM [dbo].[WorldPop]' +
' ORDER BY [' + @Sort + '] ' + @Order
PRINT (@sqlCode)
EXEC(@sqlCode)
END
GO

Various Queries

EXEC sp_WorldPopList @Sort='Fert Rate'

EXEC sp_WorldPopList @Sort='Migrants', @Order='DESC'

EXEC sp_WorldPopList @Sort='Migrants', @Order='ASC'

EXEC sp_WorldPopList @Sort='Net Change', @Order='DESC', @Count = 15

Functions

A function is prepared SQL code that is saved, so the code can be reused repeatedly.

Functions are stored in a database. Functions added to the server are "user defined" functions (UDF)

Database > Programmability > Functions

SQL Server comes with many built-in functions.

<DatabaseName> > Programmability > Functions > System Functions

Additionally, the master database contains built-in functions that are available globally to the instance.

master > Programmability > Functions > System Functions

Functions are stored in categorized sub hives

Table-valued Functions
Scalar-valued Functions
Aggregate Functions

Stored Procedures vs Functions

Stored procedures can take both input and output parameters but Functions take can only input parameters. Functions cannot return values of type text.

Stored procedures perform larger activities and can modify data, whereas functions focus on computations and data retrieval, providing flexibility in SQL statements.

Naming functions with a preceding f_, fn_, ufn_ or udf_ helps to differentiate between other database objects when writing and reading code.

Table-valued Functions

Table Valued Functions return a dataset. They are used in the FROM clause.

They behave identically to a subquery in terms of how you'd incorporate into a multi-table FROM clause. Namely, if it needs columns from those tables to execute you'd SQL Standard LATERAL JOIN (i.e., CROSS APPLY) the reference and you'd get iterative / correlated execution, otherwise you can just treat its output like any other relation that you'd combine using a join predicate.

Generally, inline table-valued functions perform better than scalar functions, as they can be optimized by the query optimizer and benefit from parallelism. Multi-statement table-valued functions, on the other hand, can be slower than scalar functions, as they incur more overhead and cannot be optimized.

Scalar-valued Functions

Scalar Valued Functions return a value. They are typically used in the SELECT or WHERE clause.

Aggregate Functions

An aggregate function is a mathematical computation involving a range of values that results in just a single value expressing the significance of the accumulated data it is derived from.

Aggregate functions are often used to derive descriptive statistics.

The standard aggregate functions are MIN, MAX, AVG, SUM, and COUNT.

Creating Functions

The Create Function command will create a new function.

The return type will determine the function type and the correct function type will automatically be stored in the correct hive.

Syntax (Scalar-valued function)

CREATE FUNCTION <FunctionName> ()
RETURNS <DataType>
AS
sql_statement / code

-- 0503-04-05 Demonstration Code ================
-- Create fn_IsAzureMI function
CREATE FUNCTION [dbo].[fn_IsAzureMI]()
RETURNS [bit]
AS
/*
=======================================
Name: fn_IsAzureMI
Purpose: Utility to determine if the script is running on an Azure SQL Managed Instance.
Parameters: none
Usage: SET @IsAzureMI = (SELECT [dbo].[fn_IsAzureMI] ())
Returns [bit]:
0 if NOT running on an Azure Managed Instance
1 if running on an Azure Managed Instance
Recommended Cadence:
Author: TJ Elias
Version: 231212
=======================================
*/
BEGIN
DECLARE @ReturnValue [int],
@EngineEdition [int],
@IsAzureMI [int]

SET @ReturnValue = 1
SET @IsAzureMI = 0
SET @EngineEdition = CAST((SELECT SERVERPROPERTY ('EngineEdition')) AS int)

IF @EngineEdition = 8
SET @IsAzureMI = 1

RETURN @IsAzureMI
END
GO
/* Code End */

SELECT [dbo].[fn_IsAzureMI] ()

Altering Functions

The Alter Function command will modify an existing function.

Syntax (Scalar-valued function)

ALTER FUNCTION <FunctionName> ()
RETURNS <DataType>
AS
sql_statement / code

Triggers

An SQL trigger allows you to specify SQL actions that will be executed automatically when a specific event occurs in the database.

For example, you can use a trigger to automatically update a record in one table whenever a record is inserted into another table.

Naming functions with a preceding t_ or tri_ helps to differentiate between other database objects when writing and reading code.

Creating Triggers

The Create Trigger command will create a new trigger.

Altering Triggers

The Alter Trigger command will modify an existing trigger.

Useful Tips & Tricks

sp_CreateEmptyObject

This script uses parameters and is a helpful SP that will create an object. It may seem like an overkill for what it does, but the value of this script will be demonstrated later.

Parameters can be passed to a SP to expand functionality. Parameters can be mandatory or optional. Optional parameters are typically defined last.

This procedure is useful for other code that can simply depend on an object already existing so it only needs to Alter the object. Future demonstration code will illustrate how it is used.

-- 0502-01-05 Demonstration Code ================
-- Create a procedure that will create an empty object
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_CreateEmptyObject]') AND type in (N'P', N'PC'))
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[sp_CreateEmptyObject] AS'
GO

ALTER PROCEDURE [dbo].[sp_CreateEmptyObject]
@Type [varchar](2),
@Name [nvarchar](128),
@Schema [nvarchar](50) = 'dbo'
AS
/*
=======================================
Name: sp_CreateEmptyObject
Purpose: Utility to Create an empty Stored Procedure
Parameters:
@Type - Object type {V,P,F,T}
@Name - Procedure name
[@Schema] - (optional) Schema name
Prototype:
EXEC [dbo].[sp_CreateEmptyObject] @Type, @Name [, @Schema]
Usage:
EXEC [dbo].[sp_CreateEmptyObject] @Type="P", @Name='myProcName'
EXEC [dbo].[sp_CreateEmptyObject] @Type="P", @Name='myProcName', @Schema='mySchemaName'
=======================================
*/
BEGIN
SET NOCOUNT ON

DECLARE @FullName [nvarchar](128),
@sqlCode [nvarchar](MAX)

SET @FullName = '[' + @Schema + '].[' + @Name + ']'
SET @sqlCode = N''

IF @Type = 'V'
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(@FullName) AND type in (N'V'))
SET @sqlCode = N'CREATE VIEW ' + @FullName + 'AS BEGIN SELECT 1 END'

IF @Type = 'P'
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(@FullName) AND type in (N'P', N'PC'))
SET @sqlCode = N'CREATE PROCEDURE ' + @FullName + 'AS BEGIN SELECT 1 END'

IF @Type = 'F'
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(@FullName) AND type in (N'FN', N'IF', N'TF', N'FS', N'FT' ))
SET @sqlCode = N'CREATE FUNCTION ' + @FullName + '() RETURNS [bit] AS BEGIN RETURN 1 END'

IF @Type = 'T'
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(@FullName) AND type in (N'TR'))
SET @sqlCode = N'CREATE TRIGGER ' + @FullName + 'AS BEGIN SELECT 1 END'

IF @sqlCode <> N''
EXEC(@sqlCode)
END