How to create custom formatting, conversion or validation using scalar functions

Problem: How to create a custom conversion or validation check of an input value.

Solution: Scalar - Valued Functions.

CREATE FUNCTION

User-defined scalar functions return a single data value of the type defined in the RETURNS clause. For an inline scalar function, there is no function body; the scalar value is the result of a single statement. For a multi statement scalar function, the function body, defined in a BEGIN...END block, contains a series of Transact-SQL statements that return the single value. The return type can be any data type except text, ntext, image, cursor, and timestamp.

Example:

Let’s try few examples on what we can do with scalar functions.

Some of us always try to format phone numbers or social security numbers to an easy look, So I am going to show you how we can convert an integer to a stylish format using stuff function. We can go as fancy as we want based on the set logic, u can also use conditional case statements to do validation checks and convert accordingly.

Please see the below sample code for a scalar function to format the phone number.

CREATE FUNCTION [dbo].[FormatPhoneNumber]

(@Phone bigint )

RETURNS varchar(max)

AS

BEGIN

                DECLARE @Text varchar(15) = ''

                SET @Text =  stuff(stuff(stuff(stuff(@Phone, 1, 0, '('),5, 0, ')'), 6, 0, ' '), 10, 0, '-')                        

                                                 

RETURN @Text

END

GO

SELECT  [CallId]

      ,[CallerName]

      ,[Location]

     ,[phone]

      ,[dbo].[FormatPhoneNumber]([Phone]) as [formatted phone number]

      ,[Status]

  FROM [dbo].[T_Call]

Output:

Read More

How to create and use a table valued function.

Problem: 

How to create a table result set Object in the FROM Clause based on a parameter passed.

Solution: 

Table Valued Function, CREATE FUNCTION

Functions are generally used for calculating business logic, encapsulate complex query and for simplifying large block of code to re-use.

Table Valued Function is a User-defined function that return a table data type can be powerful alternatives to views. A table-valued user-defined function can be used where table or view expressions are allowed in Transact-SQL queries. While views are limited to a single SELECT statement, user-defined functions can contain additional statements that allow more powerful logic than is possible in views.

A table-valued user-defined function can also replace stored procedures that return a single result set. The table returned by a user-defined function can be referenced in the FROM clause of a Transact-SQL statement, but stored procedures that return result sets cannot.

Functions also can be schema bound to secure the metadata inside.

Example:

I have an Employees table and I would like to select all columns in employees table by their job type. This is done by creating a table valued function and passing a parameter into the function to retrieve specified output. Please see the following code to create a sample table valued result set.

CREATE FUNCTION dbo.Fn_ReturnAllEmployeesbyEmployeeType

(@EmployeeType VARCHAR(50))

RETURNS TABLE

AS

RETURN

(

SELECT  [EmployeeID]

      ,[Name]

      ,[PhoneNumber]

      ,[EmployeeType]

      ,[Company_Id]

  FROM [dbo].[Employees]

  WHERE EmployeeType =@EmployeeType

 )

 select * from dbo.Fn_ReturnAllEmployeesbyEmployeeType('Developer')

Please see the below screenshot to see the table results from function output.

Read More

How to check foreign key constraints columns on a table using a query.

Problem: 

How to query foreign key constraints on a given table.

Solution: 

sp_fkeys, sp_foreignkeys, sys.foreign_keys, sys.foreign_key_columns

There are procedures which can through the output and also views which can show the information from the object catalog.

All these views store the dependencies information of a table and some of them store referencing objects as well. To go through all of them at one place Sp_fkeys is the system procedure which accepts the parent table or foreign key table and the System procedure sp_foreignKeys will show the constraints on the linked server tables as well.

Example:

As always let’s explore few methods on how to get the foreign key constraints on a table using a SQL query.

-- Using sp_Fkey storedprocedure

EXEC sp_fkeys  @fktable_name = N'History' 

    ,@pktable_owner = N'dbo'; 

-- Using Sys.Foreign_keys view

SELECT NAME,

                object_name(parent_object_id) AS parent_Table,

                object_name(referenced_object_id) AS refereneced_table

FROM sys.foreign_keys

WHERE object_name(parent_object_id) = 'History'

--using Sys.Foreing_key_columns_usage

SELECT object_name(constraint_object_id) AS Constraint_name,

                object_name(parent_object_id) AS parent_Table,

                object_name(referenced_object_id) AS refereneced_table

FROM sys.foreign_key_columns

WHERE object_name(parent_object_id) = 'History'

--using Information_schema views

SELECT table_schema,table_name,column_name,KCU.constraint_name

FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE KCU

INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC

 ON KCU.CONSTRAINT_CATALOG = RC.CONSTRAINT_CATALOG 

    AND KCU.CONSTRAINT_SCHEMA = RC.CONSTRAINT_SCHEMA

    AND KCU.CONSTRAINT_NAME = RC.CONSTRAINT_NAME

WHERE table_name = 'History'

--using Information_schema views

SELECT table_schema,table_name,column_name,KCU.constraint_name

FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU

INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC

 ON KCU.CONSTRAINT_CATALOG = RC.CONSTRAINT_CATALOG 

    AND KCU.CONSTRAINT_SCHEMA = RC.CONSTRAINT_SCHEMA

    AND KCU.CONSTRAINT_NAME = RC.CONSTRAINT_NAME

WHERE table_name = 'History'

 Please see the below screenshot on the results and the information to see which method to prefer depending on your needs.

Read More

How to create a view and use options to protect the view.

Problem: How to create a view or a read only version of a table data.

Solution: Views, Views.

Views are virtual tables having metadata based on the query inside the view definition, views have rows and columns just like any tables that exists in SQL Server. Views don’t consume space on the database, if they are indexed they would have index space on the database.

Views have few options which can provide security and consistency based on the options selected in the object definition.

WITH METADATA – Specifies that the instance of SQL Server will return to the DB-Library, ODBC, and OLE DB APIs the metadata information about the view, instead of the base table or tables, when browse-mode metadata is being requested for a query that references the view. Browse-mode metadata is additional metadata that the instance of SQL Server returns to these client-side APIs. This metadata enables the client-side APIs to implement updatable client-side cursors.

ENCRYPTION -Encrypts the entries in sys.syscomments that contain the text of the CREATE VIEW statement. Using WITH ENCRYPTION prevents the view from being published as part of SQL Server replication.

WITH CHECK OPTION - Forces all data modification statements executed against the view to follow the criteria set within select_statement. When a row is modified through a view, the WITH CHECK OPTION makes sure the data remains visible through the view after the modification is committed.

SCHEMA BINDING - Binds the view to the schema of the underlying table or tables. When SCHEMABINDING is specified, the base table or tables cannot be modified in a way that would affect the view definition. The view definition itself must first be modified or dropped to remove dependencies on the table that is to be modified. When you use SCHEMABINDING, the select_statement must include the two-part names (schema.object) of tables, views, or user-defined functions that are referenced. All referenced objects must be in the same database.

Example:

As Always let’s explore few varieties and options in views. I am going to show first how to create a basic view and then let’s go further north and explore the available options inside a view.

Please see the below script, where I am going to select columns from a tableA and alias the columns to change accordingly as the user wants to see.

CREATE VIEW [dbo].[View_A]

AS

SELECT col1 AS V_Col1,

                col2 AS V_col2,

                col3 AS V_Col3

FROM dbo.TableA

Let’s see an example for how to encrypt a view definition and see the results from sys.syscomments to see whether the text is showing in the system views or not.

CREATE VIEW [dbo].[View_A]

WITH ENCRYPTION

AS

SELECT col1 AS V_Col1,

                col2 AS V_col2,

                col3 AS V_Col3

FROM dbo.TableA

GO

-- tableA results

SELECT  * FROM sys.syscomments  where object_name(id) ='View_A'

To alter a view or modify the definition inside the view query. please see the below script where i am renaming the alias of the view columns.

ALTER VIEW dbo.VIEW_A

AS

SELECT col1 AS Version_Col1,

col2 AS Version_col2,

col3 AS Version_Col3

FROM dbo.TableA

GO

The below script shows us how to drop a view.

DROP VIEW dbo.VIEW_A

Let’s explore the schema binding option and then try to change the underlying table definition by altering a column from INT to VARCHAR(10). This will help to protect the views metadata not to break the query inside the view.

CREATE VIEW [dbo].[View_A]

WITH SCHEMABINDING

AS

SELECT col1 AS V_Col1,

                col2 AS V_col2,

                col3 AS V_Col3

FROM dbo.TableA

GO

-- tableA results

ALTER TABLE dbo.TableA

ALTER COLUMN col2 VARCHAR(10)

I am going to show how WITH CHECK OPTION WORKS and insert a record through the view.

CREATE TABLE dbo.TestTable (col_a INTEGER);

GO

CREATE VIEW dbo.TestView (col_a)

AS

SELECT col_a FROM dbo.TestTable WHERE col_a > 0

WITH CHECK OPTION;

GO

-- Inserting into View

INSERT INTO dbo.TestView VALUES (1);

-- Selecting from Table

SELECT * FROM dbo.TestTable

SELECT * FROM dbo.TestView

If you see the above picture this view is used as a check constraint on the input values and it is only accepting positive integers for the col_a.

The last but not the least View to have a view_metadata to show the metadata of the view using client side API’s.

CREATE VIEW [dbo].[View_A]

WITH VIEW_METADATA

AS

SELECT col1 AS V_Col1,

                col2 AS V_col2,

                col3 AS V_Col3

FROM dbo.TableA

GO

-- view metadata

Read More

How to use JOINS in SQL Server

Problem: How to get values from two different tables based on common values and logical operators.

Solution:  

JOIN

There are different types of joins in SQL Server.

1. Inner Join INNER JOIN Operation (Microsoft Access SQL)
2. Outer Join LEFT JOIN, RIGHT JOIN Operations (Microsoft Access SQL)
• Left Outer Join
• Right Outer Join
• Full Outer Join 

·         Inner Join: Inner Join is a default type join of SQL Server. It uses logical operators such as =, <, > to match the records in two tables. Inner Join includes equi join and natural joins.

·         Outer Join: Outer Join has further 3 sub categories as left, right and full. Outer Join uses these category names as keywords that can be specified in the FROM clause.

o    Left Outer Join: Left Outer Join returns all the rows from the table specified first in the Left Outer Join Clause. returns null column values any row has no matching record in the right side table

o    Right Outer Join: Right Outer Join returns all the rows from right table and returns null values for the rows having no match in the left joined table.

o    Full Outer Join: Full outer join returns all the rows from both the tables joined. If there is any match missing from the left table, then it returns null column values for left side table and if there is any match missing from right table then it returns null value columns for the right side table.

Example :

I am going to demonstrate an example each for all these above joins.

SELECT * FROM TableA

SELECT * FROM TableB

The following picture shows that table A has three rows and table B has 4 rows.

-- INNER JOIN

SELECT  A.* ,B.Col2 as TableB_Col2

FROM dbo.TableA A

INNER JOIN dbo.TableB B ON B.Col2=A.COl2

The query above shows a INNER JOIN between table A and B and as we can see from the above screenshot both of the tables have 4 in common on the Col2. So the query brings us the match between both these tables.

-- LEFT OUTER JOIN

SELECT A.* ,B.Col2 as TableB_Col2

FROM dbo.TableA A

LEFT OUTER JOIN dbo.TableB B ON B.Col2=A.COl2

The query above has a LEFT OUTER JOIN which brings all the values form table A and returns null values for those we don't have a match from table B. 

-- RIGHT OUTER JOIN

SELECT  A.* ,B.Col2 as TableB_Col2

FROM dbo.TableA A

RIGHT OUTER JOIN dbo.TableB B ON B.Col2=A.COl2

The query above has a RIGHT OUTER JOIN which brings all the values from table B and returns values for the no match of table A.

-- FULL OUTER JOIN

SELECT A.*,B.Col2 as TableB_Col2

FROM dbo.TableA A

FULL OUTER JOIN dbo.TableB B ON B.Col2=A.COl2

This query gives both left and right joins outputs and shows null values for the unmatched on both the tables.

Read More