Difference between Function and Stored Procedure?
- User Defined Function (UDF) can be used in the SQL statements anywhere in the SELECT, WHERE, and HAVING section where as Stored procedures cannot be used.
- Functions are designed to send their output to a query or T-SQL statement while Stored Procedures use EXECUTE or EXEC to run.
- We can not use EXECUTE and PRINT commands inside a function but in SPROC
- UDFs that return tables can be treated as another rowset. This can be used in JOINs with other tables.
- UDFs can't change the server environment or your operating system environment, while a SPROC can.
- Inline UDF's can be though of as views that take parameters and can be used in JOINs and other Rowset operations.
- Stored Procedures are stored in compiled format in the database where as Functions are compiled and excuted runtime.
- SPROC can be used with XML FOR Clause but Functions can not be.
- SPROC can have transaction but not Functions.
- Functions can be used in a SPROC but SPROC cann't be used in a Function. Only extended stored procedures can be called from a function.
- Of course there will be Syntax differences and here is a sample of that
(
@parameter1 datatype = DefaultValue,
@parameter2 datatype OUTPUT
)
AS
BEGIN
T-SQL statements
RETURN
END
GO
CREATE FUNCTION dbo.FunctionName
(
@parameter1 datatype = DefaultValue,
@parameter2 datatype
)
RETURNS datatype
AS
BEGIN
SQL Statement
RETURN Value
END
GO
Explain SQL Server JOINs with examples?
I will explain types of SQL JOINs in in this article. JOINs in SQL Server can be classified as follows:
• INNER JOIN
• LEFT OUTER JOIN
• RIGHT OUTER JOIN
• FULL OUTER JOIN
• CROSS JOIN
Each type is explained below with suitable examples:
Let’s consider two tables as Table1 & Table2 for our example.
– CREATE TEST TABLES
CREATE TABLE Table1(ID [int], Code [varchar](10));
GO
CREATE TABLE Table2(ID [int], Amount [int]);
GO
– INSERT DUMMY DATA
INSERT INTO Table1 VALUES
(1,’AAA’),(2,’BBB’),(3,’CCC’)
GO
INSERT INTO Table2 VALUES
(1,50),(2,30),(4,40)
GO
INNER JOIN:
Inner joins return rows only when there is at least one row from both tables that matches the join condition. Inner joins eliminate the rows that do not match with a row from the other table. Below is the query for inner join:
SELECT *
FROM Table1 A
INNER JOIN Table2 B
ON A.ID = B.ID
GO
LEFT OUTER JOIN
Left Outer joins return all rows from the left table referenced with a left outer join and matching rows from other table. Unmatched records will be NULL. Below is the query for Left Outer Join:
SELECT *
FROM Table1 A
LEFT OUTER JOIN Table2 B
ON A.ID = B.ID
GO
RIGHT OUTER JOIN
Right Outer joins return all rows from the right table referenced with a right outer join and matching rows from other table. Unmatched records will be NULL. Below is the query for Right Outer Join:
SELECT *
FROM Table1 A
RIGHT OUTER JOIN Table2 B
ON A.ID = B.ID
GO
FULL OUTER JOIN
Full Outer joins return all rows from both the tables. Unmatched records will be NULL. Below is the query for Full Outer Join:
SELECT * FROM Table1 A
FULL OUTER JOIN Table2 B
ON A.ID = B.ID
GO
CROSS JOIN
In cross joins, each row from first table joins with all the rows of another table. If there are m rows from Table1 and n rows from Table2 then result set of these tables will have m*n rows. Below is the query for
SELECT * FROM Table1 A
CROSS JOIN Table2 B
GO
• INNER JOIN
• LEFT OUTER JOIN
• RIGHT OUTER JOIN
• FULL OUTER JOIN
• CROSS JOIN
Each type is explained below with suitable examples:
Let’s consider two tables as Table1 & Table2 for our example.
– CREATE TEST TABLES
CREATE TABLE Table1(ID [int], Code [varchar](10));
GO
CREATE TABLE Table2(ID [int], Amount [int]);
GO
– INSERT DUMMY DATA
INSERT INTO Table1 VALUES
(1,’AAA’),(2,’BBB’),(3,’CCC’)
GO
INSERT INTO Table2 VALUES
(1,50),(2,30),(4,40)
GO
INNER JOIN:
Inner joins return rows only when there is at least one row from both tables that matches the join condition. Inner joins eliminate the rows that do not match with a row from the other table. Below is the query for inner join:
SELECT *
FROM Table1 A
INNER JOIN Table2 B
ON A.ID = B.ID
GO
LEFT OUTER JOIN
Left Outer joins return all rows from the left table referenced with a left outer join and matching rows from other table. Unmatched records will be NULL. Below is the query for Left Outer Join:
SELECT *
FROM Table1 A
LEFT OUTER JOIN Table2 B
ON A.ID = B.ID
GO
RIGHT OUTER JOIN
Right Outer joins return all rows from the right table referenced with a right outer join and matching rows from other table. Unmatched records will be NULL. Below is the query for Right Outer Join:
SELECT *
FROM Table1 A
RIGHT OUTER JOIN Table2 B
ON A.ID = B.ID
GO
Full Outer joins return all rows from both the tables. Unmatched records will be NULL. Below is the query for Full Outer Join:
SELECT * FROM Table1 A
FULL OUTER JOIN Table2 B
ON A.ID = B.ID
GO
In cross joins, each row from first table joins with all the rows of another table. If there are m rows from Table1 and n rows from Table2 then result set of these tables will have m*n rows. Below is the query for
SELECT * FROM Table1 A
CROSS JOIN Table2 B
GO
How to find all the IDENTITY columns in a database?
Here is the easiest way to list all the IDENTITY Columns of any database:
SELECTOBJECT_NAME([object_id]) as TableName,
name as ColumnName
FROM [DatabaseName].sys.columns
WHERE is_identity = 1
SELECTOBJECT_NAME([object_id]) as TableName,
name as ColumnName
FROM [DatabaseName].sys.columns
WHERE is_identity = 1
What is Stored Procedure?
A stored procedure is the saved collection of T-SQL statements in the SQL Server database that can take and return user-supplied parameters. Stored Procedures (also called sproc) can have input parameters and output parameters. Same stored procedure can be used over the network by several clients by passing different input data for input parameters. When a stored procedure is modified, all clients automatically get the new version. Stored procedures reduce network traffic and improve performance.
Here is the syntax of SPROC:
CREATE PROC [ EDURE ] [owner.] ProcedureName
[ { @parameter data_type } [= DefaultValue] [OUTPUT] ] [ ,...n ]
[ WITH { RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ]
AS
BEGIN
T-SQL-statements
END
Here is an example:
CREATE PROC ProductList (
@ProductType varchar(30) = 'Electronics')
WITH ENCRYPTION
AS
BEGIN
SELECT
ProductName, ProductType,
Model, UnitPrice
FROM Products (NOLOCK)
WHERE ProductType = @ProductType
END
How to execute:
-- Return all the products belongs to Electronics
EXECUTE ProductList
GO
-- Return all the products belongs to Auto Mobiles
EXECUTE ProductList 'Auto Mobiles'
GO
SQL Statement Limitations in a Procedure
Any SET statement can be specified inside a stored procedure except SET SHOWPLAN_TEXT and SET SHOWPLAN_ALL, which must be the only statements in the batch.
Inside a stored procedure, object names used with certain statements must be qualified with the name of the object owner if other users are to use the stored procedure. The statements are:
•ALTER TABLE
•CREATE INDEX
•CREATE TABLE
•All DBCC statements
•DROP TABLE
•DROP INDEX
•TRUNCATE TABLE
•UPDATE STATISTICS
Permissions
CREATE PROCEDURE permissions default to members of the sysadmin fixed server role, and the db_owner and db_ddladmin fixed database roles. Members of the sysadmin fixed server role and the db_owner fixed database role can transfer CREATE PROCEDURE permissions to other users. Permission to execute a stored procedure is given to the procedure owner, who can then set execution permission for other database users.
Here is the syntax of SPROC:
CREATE PROC [ EDURE ] [owner.] ProcedureName
[ { @parameter data_type } [= DefaultValue] [OUTPUT] ] [ ,...n ]
[ WITH { RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ]
AS
BEGIN
T-SQL-statements
END
Here is an example:
CREATE PROC ProductList (
@ProductType varchar(30) = 'Electronics')
WITH ENCRYPTION
AS
BEGIN
SELECT
ProductName, ProductType,
Model, UnitPrice
FROM Products (NOLOCK)
WHERE ProductType = @ProductType
END
How to execute:
-- Return all the products belongs to Electronics
EXECUTE ProductList
GO
-- Return all the products belongs to Auto Mobiles
EXECUTE ProductList 'Auto Mobiles'
GO
SQL Statement Limitations in a Procedure
Any SET statement can be specified inside a stored procedure except SET SHOWPLAN_TEXT and SET SHOWPLAN_ALL, which must be the only statements in the batch.
Inside a stored procedure, object names used with certain statements must be qualified with the name of the object owner if other users are to use the stored procedure. The statements are:
•ALTER TABLE
•CREATE INDEX
•CREATE TABLE
•All DBCC statements
•DROP TABLE
•DROP INDEX
•TRUNCATE TABLE
•UPDATE STATISTICS
Permissions
CREATE PROCEDURE permissions default to members of the sysadmin fixed server role, and the db_owner and db_ddladmin fixed database roles. Members of the sysadmin fixed server role and the db_owner fixed database role can transfer CREATE PROCEDURE permissions to other users. Permission to execute a stored procedure is given to the procedure owner, who can then set execution permission for other database users.
Function to Convert Decimal Number into Binary, Ternary, and Octal
In this article I am sharing user defined function to convert a decimal number into Binary, Ternary, and Octalequivalent.
IF OBJECT_ID(N'dbo.udfGetNumbers', N'TF') IS NOT NULL
DROP FUNCTION dbo.udfGetNumbers
GO
CREATE FUNCTION dbo.udfGetNumbers
(@base [int], @lenght [int])
RETURNS @NumbersBaseN TABLE
(
decNum [int] PRIMARY KEY NOT NULL,
NumBaseN [varchar](50) NOT NULL
)
AS
BEGIN
WITH tblBase AS
(
SELECT CAST(0 AS VARCHAR(50)) AS baseNum
UNION ALL
SELECT CAST((baseNum + 1) AS VARCHAR(50))
FROM tblBase WHERE baseNum < @base-1
),
numbers AS
(
SELECT CAST(baseNum AS VARCHAR(50)) AS num
FROM tblBase
UNION ALL
SELECT CAST((t2.baseNum + num) AS VARCHAR(50))
FROM numbers CROSS JOIN tblBase t2
WHERE LEN(NUM) < @lenght
)
INSERT INTO @NumbersBaseN
SELECT ROW_NUMBER() OVER (ORDER BY NUM) -1 AS rowID, NUM
FROM numbers WHERE LEN(NUM) > @lenght - 1
OPTION (MAXRECURSION 0);
RETURN
END
GO
-- Unit Test --
-- Example with decimal, binary, ternary and octal
SELECT
U1.decNum AS Base10,
U1.NumBaseN AS Base2,
U2.NumBaseN AS Base3,
U3.NumBaseN AS Base8
FROM dbo.udfGetNumbers(2, 10) U1
JOIN dbo.udfGetNumbers(3, 7) U2
ON u1.decNum = u2.decNum
JOIN dbo.udfGetNumbers(8, 4) U3
ON u2.decNum = u3.decNum
Here is the output:
IF OBJECT_ID(N'dbo.udfGetNumbers', N'TF') IS NOT NULL
DROP FUNCTION dbo.udfGetNumbers
GO
CREATE FUNCTION dbo.udfGetNumbers
(@base [int], @lenght [int])
RETURNS @NumbersBaseN TABLE
(
decNum [int] PRIMARY KEY NOT NULL,
NumBaseN [varchar](50) NOT NULL
)
AS
BEGIN
WITH tblBase AS
(
SELECT CAST(0 AS VARCHAR(50)) AS baseNum
UNION ALL
SELECT CAST((baseNum + 1) AS VARCHAR(50))
FROM tblBase WHERE baseNum < @base-1
),
numbers AS
(
SELECT CAST(baseNum AS VARCHAR(50)) AS num
FROM tblBase
UNION ALL
SELECT CAST((t2.baseNum + num) AS VARCHAR(50))
FROM numbers CROSS JOIN tblBase t2
WHERE LEN(NUM) < @lenght
)
INSERT INTO @NumbersBaseN
SELECT ROW_NUMBER() OVER (ORDER BY NUM) -1 AS rowID, NUM
FROM numbers WHERE LEN(NUM) > @lenght - 1
OPTION (MAXRECURSION 0);
RETURN
END
GO
-- Unit Test --
-- Example with decimal, binary, ternary and octal
SELECT
U1.decNum AS Base10,
U1.NumBaseN AS Base2,
U2.NumBaseN AS Base3,
U3.NumBaseN AS Base8
FROM dbo.udfGetNumbers(2, 10) U1
JOIN dbo.udfGetNumbers(3, 7) U2
ON u1.decNum = u2.decNum
JOIN dbo.udfGetNumbers(8, 4) U3
ON u2.decNum = u3.decNum
Here is the output:
Fun with TRANSACTION
What will be output of below T-SQL code:
CREATE TABLE MyTable
(
MyId [INT] IDENTITY (1,1),
MyCity [NVARCHAR](50)
)
BEGIN TRANSACTION OuterTran
INSERT INTO MyTable VALUES ('Boston')
BEGIN TRANSACTION InnerTran
INSERT INTO MyTable VALUES ('London')
ROLLBACK WORK
IF (@@TRANCOUNT = 0)
BEGIN
PRINT 'All transactions were rolled back'
END
ELSE
BEGIN
PRINT 'Outer transaction is rolling back...'
ROLLBACK WORK
END
DROP TABLE MyTable
Here are the options:
1. All transactions were rolled back
2. Outer transaction is rolling back...
3. ERROR: Incorrect syntax near 'WORK'.
Correct answer: All transactions were rolled back
Explanation: Issuing a ROLLBACK WORK rolls back all the way to the outer BEGIN TRANSACTION
CREATE TABLE MyTable
(
MyId [INT] IDENTITY (1,1),
MyCity [NVARCHAR](50)
)
BEGIN TRANSACTION OuterTran
INSERT INTO MyTable VALUES ('Boston')
BEGIN TRANSACTION InnerTran
INSERT INTO MyTable VALUES ('London')
ROLLBACK WORK
IF (@@TRANCOUNT = 0)
BEGIN
PRINT 'All transactions were rolled back'
END
ELSE
BEGIN
PRINT 'Outer transaction is rolling back...'
ROLLBACK WORK
END
DROP TABLE MyTable
Here are the options:
1. All transactions were rolled back
2. Outer transaction is rolling back...
3. ERROR: Incorrect syntax near 'WORK'.
Correct answer: All transactions were rolled back
Explanation: Issuing a ROLLBACK WORK rolls back all the way to the outer BEGIN TRANSACTION
How to get SQL Server Restore history using T-SQL?
How to find SQL Server databases Restore History?
How to find Last Restore Date od SQL Server database?
Few developers asked me how to find the last time a database was restored using T-SQL. I have formed the following query using MSDB..RestoreHistory table.
SELECT *
FROM MSDB..RestoreHistory WITH (nolock)
WHERE destination_database_name = 'DatabaseName'
ORDER BY restore_date DESC
How to find Last Restore Date od SQL Server database?
Few developers asked me how to find the last time a database was restored using T-SQL. I have formed the following query using MSDB..RestoreHistory table.
SELECT *
FROM MSDB..RestoreHistory WITH (nolock)
WHERE destination_database_name = 'DatabaseName'
ORDER BY restore_date DESC
What is lock escalation?
Lock escalation is the process of converting a lot of low level locks (For example row locks, and page locks) into higher level locks (e.g. table locks). Every lock is a memory structure too many locks would mean, more memory being occupied by locks. To prevent this from happening, SQL Server escalates the many fine-grain locks to fewer coarse-grain locks. Lock escalation threshold was definable in SQL Server 6.5, but from SQL Server 7.0 onwards it's dynamically managed by SQL Server.
In the hierarchy of the objects in a SQL Server instance, at the top level we have Database, followed by Schema, Tables, Table Partitions, Pages and then finally the individual Rows. If you acquire a lock at higher level, it can cover more resources there by you consume fewer lock resources (each lock structure takes approximately 100 bytes) and the locking overhead but this comes at a price of lower concurrency. So for example, if you want to select all the rows of a table, if you acquire a lock at table level, you will not need to lock individual rows or pages but then it will block any concurrent update transaction. Similarly, if you lock individual rows, you will get higher concurrency but then you will incur the overhead of acquiring/releasing locks on each row and lot more locking resources depending upon the isolation level of your transaction, as you may need to hold the locks on all the rows till the end of transaction.
Depending upon the estimates during query compilation, the SQL Server recommends the locking granularity (i.e. row, page or table) appropriately and during query execution, depending on the concurrent work load, the appropriate locking granularity is applied. User can override the locking granularity option explicitly by providing locking hints and/or by executing sp_indexoption stored procedure. While locking granularity is chosen at the start of query execution but during the execution, the SQL Server may choose to escalate the lock to lower level of granularity depending on the number of locks acquired and the availability of memory at run time.
In the hierarchy of the objects in a SQL Server instance, at the top level we have Database, followed by Schema, Tables, Table Partitions, Pages and then finally the individual Rows. If you acquire a lock at higher level, it can cover more resources there by you consume fewer lock resources (each lock structure takes approximately 100 bytes) and the locking overhead but this comes at a price of lower concurrency. So for example, if you want to select all the rows of a table, if you acquire a lock at table level, you will not need to lock individual rows or pages but then it will block any concurrent update transaction. Similarly, if you lock individual rows, you will get higher concurrency but then you will incur the overhead of acquiring/releasing locks on each row and lot more locking resources depending upon the isolation level of your transaction, as you may need to hold the locks on all the rows till the end of transaction.
Depending upon the estimates during query compilation, the SQL Server recommends the locking granularity (i.e. row, page or table) appropriately and during query execution, depending on the concurrent work load, the appropriate locking granularity is applied. User can override the locking granularity option explicitly by providing locking hints and/or by executing sp_indexoption stored procedure. While locking granularity is chosen at the start of query execution but during the execution, the SQL Server may choose to escalate the lock to lower level of granularity depending on the number of locks acquired and the availability of memory at run time.
T-SQL Challenge
What will be the output of below T-SQL code:
CREATE TABLE #TestDate
(
[ID] int IDENTITY(1,1)
,[FullDate] datetime DEFAULT (GETDATE()),
)
GO
INSERT INTO #TestDate VALUES
('01/07/2010'),('2010/07/01'),('07/01/2010')
GO
SELECT COUNT([FullDate]) FROM #TestDate
WHERE CAST([FullDate] as int) = 40358
GO
DROP TABLE #TestDate
GO
Answer this without cheating (without executing the Query). Here are the options:
A. 1
B. 2
C. 3
D. Error
F. None of the above
Correct Answer is B.
Reason: Dates 07/01/2010 and 2010/07/01 are inserted as 2010-07-01 00:00:00.000 in the database which is equal to 40358 of int type. So when the conversion into int and then taking ceiling of the values, these 2 records generate the same values. However, 01/07/2010 is saved as 2010-01-07 00:00:00.000 in the database which is equal to 40183 of type int.
CREATE TABLE #TestDate
(
[ID] int IDENTITY(1,1)
,[FullDate] datetime DEFAULT (GETDATE()),
)
GO
INSERT INTO #TestDate VALUES
('01/07/2010'),('2010/07/01'),('07/01/2010')
GO
SELECT COUNT([FullDate]) FROM #TestDate
WHERE CAST([FullDate] as int) = 40358
GO
DROP TABLE #TestDate
GO
Answer this without cheating (without executing the Query). Here are the options:
A. 1
B. 2
C. 3
D. Error
F. None of the above
Correct Answer is B.
Reason: Dates 07/01/2010 and 2010/07/01 are inserted as 2010-07-01 00:00:00.000 in the database which is equal to 40358 of int type. So when the conversion into int and then taking ceiling of the values, these 2 records generate the same values. However, 01/07/2010 is saved as 2010-01-07 00:00:00.000 in the database which is equal to 40183 of type int.
Subscribe to:
Posts (Atom)