Monday 28 August 2017

User define function (UDF) in sql server

Function is a database object in Sql Server. User define functions are routines, method or protocol which performs calculations, actions such as complex calculations (find the age or some mathematical calculation) and return the result of that action. Function may or may not have parameters, but always either a scalar value or a result set. The return value can be any data type except text, Image, ntext, cursor and timestamp.
There are three Types of User Define Function UDFS in Sql Server:
1. Scalar
2. Inline Table-Valued
3. Multi-statement Table-Valued

Scalar function

A Scalar UDF can accept zero or many input parameter and will return a single value. A Scalar user-defined function returns one of the scalar (int, char, varchar etc.) data types. Text, ntext, image and timestamp data types are not supported. 
Let’s start step by step to create User Define Functions.
Here I am creating a simple function. In this function I am passing date and returning the year of passing date.
CREATE FUNCTION dbo.fnGet_Year(@Date AS DateTime)
RETURNS INT
AS
BEGIN
    RETURN YEAR(@Date)
END
Function created successfully.
We can call this function using select statement.

Inline Table-Valued

An inline table-valued function returns a variable of data type table whose value is derived from a single SELECT statement. Since the return value is derived from the SELECT statement, there is no BEGIN/END block needed in the CREATE FUNCTION statement. There is also no need to specify the table variable name (or column definitions for the table variable) because the structure of the returned value is generated from the columns that compose the SELECT statement. Because the results are a function of the columns referenced in the SELECT, no duplicate column names are allowed and all derived columns must have an associated alias.
It return a table data type. Inline functions can be used to achieve the functionality of parameterized views.
Example
Here I am creating Inline Table – valued function
CREATE FUNCTION dbo.fn_Get_EmpNames_By_Area
                 ( @eadd nvarchar(50) )
RETURNS table
AS
RETURN (
        SELECT * from EMP
        WHERE empAdd = @eadd
       );
Function created successfully.
Now I am calling this function
  

It returns the list of employee which area is pune.

Multi-statement Table-Valued

A Multi-Statement Table-Valued user-defined function returns a table. It can have one or more than one T-Sql statement. Within the create function command you must define the table structure that is being returned.
See the example
CREATE FUNCTION [dbo].[Get_Gender_Wise_Records]
(
       @Gender varchar(10)
)
RETURNS @Gender_Wise_Records TABLE
(
       EmpID varchar(50) not null,
                   JobTitle varchar(50),
                   MaritalStatus varchar(10)
)
AS
BEGIN 
       IF (@Gender = 'M')
              BEGIN
                     INSERT INTO @Gender_Wise_Records
                     SELECT BusinessEntityID,JobTitle,MaritalStatus
                     FROM Employee
                     WHERE Gender='M'
              END
       ELSE
              BEGIN
                     INSERT INTO @Gender_Wise_Records
                     SELECT BusinessEntityID,JobTitle,MaritalStatus
                     FROM Employee
                     WHERE Gender='F'
              END
       RETURN ;
END
Function is created successfully.
Now I am executing this function.


Benefits of User Define Function
1.       Reusable
2.       Faster
3.       Easy to maintain
4.       Reduce network traffic
Drawback of User Define function
1.       Function is not used to Insert, Update, Delete data in database.
2.       Can’t modify the state of the database.
3.       UDF does not support Try-catch, @ERROR or RAISERROR function.
4.       Function can be nested up to 32 levels.
5.       UDF can’t return XML data type
6.       UDF can have up to 1023 input parameters.
7.       Does not support Row Count.
8.       Doesn't support Exception handling.

Difference between Primary key and foreign key

Primary key-

Ø  A table can have one primary key.
Ø  It cannot accept duplicate values.
Ø  It is default clustered index
Ø  It cannot accept null value

Foreign Key-

Ø  Default is Non_Clustered Index
Ø  A table can have more than one foreign key
Ø  It can accept duplicate value.
Ø  It accepts null value.
Ø  It provide link between data in two tables.

Create Objects Automatically in New Databases in sql server

Suppose whenever a new database is created, we also want to create a database object like stored procedure, table or function automatically (by default) in this new database. We can create it by using Model database. This is the system database.
Read System database: click here
Model database is store the template for the all user defined database. User define object may be table, view, functions or Stored procedures. All can be can be created in the model database and will exist in all future user define database. If we want to keep some generic database objects like as tables, function stored procedures into the newly created database then we put these objects into Model database. Hence when we create a new database then available database objects in Model database, would be copied into newly created database.
See the example.
I am creating an audit table, a function and a stored procedure in Model database.
--Use Modal system database
Use Model
-- Audit table
create table tblAudit
(
Id int identity(1,1),
audit_description varchar(100)
)
--User define function
CREATE FUNCTION dbo.fnGet_Year(@Date AS DateTime)
RETURNS INT
AS
BEGIN
    RETURN YEAR(@Date)
END
--creating Sp
create procedure Modal_Test
as
select 100 as number

All object s are created in Model data


Now I am creating a new database.
  

Click ok. New database is created successfully.


Now I am seeing the object in newly created database.



Get SQL Server Hardware Information

With the help of the sys.dm_os_sys_info system view we will get the sql server hardware information.
select * from  sys.dm_os_sys_info
 
See the output


The cpu_count returns the number of logical CPUs and hyperthread_ratio returns ratio between 
physical and logical CPUs. I find the hyperthread_ratio to be confusing as it does not tell whether
these are the actual hyper threaded cores or the physical cores. So I can never find out which 
processor is the server using. The physical_memory_kb tells the amount of RAM in my 
server and how much of it is committed using committed_kb. The sqlserver_start_time is useful 
to find since what date and time SQL Server has been running. Find the Most Used Stored 
Procedures in SQL Server

Get the list of all empty tables in SQL Server

With the help of below script we will get the list of all tables which are empty (No record in table).
;WITH EmptyRowstbl AS
(
   SELECT SUM(row_count) AS [TotalRows],
          OBJECT_NAME(OBJECT_ID) AS TableName
                                     FROM sys.dm_db_partition_stats
   WHERE index_id = 0 OR index_id = 1
   GROUP BY OBJECT_ID
)
SELECT * FROM EmptyRowstbl
WHERE [TotalRows] = 0
  
See some table



Time Zones in SQL Server

With the help SYSDATETIMEOFFSET we will get the different time zone at the globe. It is a date function and returns a datetimeoffset value of the computer on which the instance of SQL Server is running.

DECLARE @local DATETIMEOFFSET = SYSDATETIMEOFFSET()
SELECT SYSDATETIMEOFFSET() as 'CurrentDT-W/Offset'
SELECT SWITCHOFFSET (@local, '-08:00') as 'ConvertedDT-W/Offset'
SELECT SYSDATETIMEOFFSET() GetCurrentOffSet;
SELECT TODATETIMEOFFSET(@local, '+08:00') 'Singapore Standard Time and W.Australia Standard Time and Taiei Standard Time’';
SELECT TODATETIMEOFFSET(@local, '+09:00') 'Ulaanbaatar Standard time';
SELECT TODATETIMEOFFSET(@local, '+08:30') 'North Korea Standard Time';
SELECT TODATETIMEOFFSET(@local, '+08:45') 'Aus Central W. Standard Time';
SELECT TODATETIMEOFFSET(@local, '+09:00') 'Tokyo Standard Time';
SELECT TODATETIMEOFFSET(@local, '-04:00') 'GetCurrentOffSet-4';
SELECT TODATETIMEOFFSET(@local, '-02:00') 'GetCurrentOffSet-2';
SELECT TODATETIMEOFFSET(@local, '+00:00') 'GetCurrentOffSet+0';
SELECT TODATETIMEOFFSET(@local, '+02:00') 'GetCurrentOffSet+2';
SELECT TODATETIMEOFFSET(@local, '+04:00') 'GetCurrentOffSet+4';
See the output


Get the list all default values in a SQL Server Database

With the help of below sql query we will get the list of all default value in a sql server.
SELECT obj.name as 'Table', col.name as 'Column',
object_definition(default_object_id) AS [DefaultValue]
FROM   sys.objects obj INNER JOIN sys.columns col
ON obj.object_id = col.object_id
where obj.type = 'U'
See the output.

We can see in the default constraint

  

Sunday 27 August 2017

If exists () in sql server

IF EXISTS condition in SQL Server (Transact-SQL) with syntax and examples. The SQL Server (Transact-SQL) EXISTS condition is used in combination with a sub query and is considered to be met if the sub query returns at least one row. It can be used in a SELECT, INSERT, UPDATE, or DELETE statement.
The IF EXISTS () structure uses the presence of any rows returned from a SQL SELECT statement as a condition. Because it looks for any row, the SELECT statement should select all columns (*). This method is faster than checking an @@rowcount >0 condition because the total number of rows isn’t required.
Syntax of EXISTS ()

See the example
SELECT *
FROM Books
WHERE EXISTS
(SELECT * FROM Books_Audit );
See the output


Using the Set and Select Commands assign the value of a variable

Both the SET command and the SELECT command can assign the value of an expression to a variable. The main difference between the two is that a SELECT can retrieve data from a data source (for example, table, sub query, or view) and can include the other SELECT clauses as well (for example, FROM, WHERE), whereas a SET is limited to retrieving data from expressions.
See the example
USE AdventureWorks2012_CS
DECLARE @ProductID int,

 @ProductName varchar(25);
SET @ProductID = 782;
print 'Using Set Product ID : '+ cast(@ProductID as varchar(100))
SELECT
 @ProductID = ProductID,
 @ProductName = Name
FROM Production.Product
ORDER BY ProductID;
SELECT @ProductID as ProductID, @ProductName as ProductName;
See the output

Both SET and SELECT can include functions. Use the simpler SET command when we need to assign only a function result or constant to a variable and don’t need the Query Optimizer to consider a data source. Additionally, SET can only set the value of one variable, while multiple variable values may be set with a single SELECT statement.
If the SELECT statement retrieves multiple rows, then the values from the last row are stored in the variables. No error will be reported.
See the example
Last value of the select statement

Now I am assigning this value in a variable and see the result.
USE AdventureWorks2012_CS
DECLARE @ProductID int,
 @ProductName varchar(25);
SELECT
 @ProductID = ProductID,
 @ProductName = Name
FROM Production.Product
ORDER BY ProductID desc;
SELECT @ProductID as ProductID, @ProductName as ProductName;

If the select statement did not return any value the variable value will be null. See the example



Popular Posts