Locking in SQL server

Locking in SQL server:

There are two types of locks in SQL server:
• Read Lock - The locked data is reserved for read by the current session. Other sessions can read the locked data. But they can not write (update) the locked data. A read lock is also called a shared lock.
• Write Lock - The locked data is reserved for write by the current session. Other sessions can not read and write the locked data. A write lock is also called an exclusive lock.

There are three levels of locks:
• Table Lock - The lock is set at the table level. All rows in the locked table are locked.
• Row Lock - The lock is set at the row level. Some rows of a table are locked. But other rows are not locked.
• Column Lock - The lock is set at the column level. Some columns of a row are locked. But other columns are not locked.

Read more »

Split function

Split function:
Split function is a user defined function it accept a string containing a stream of words separated by “,” and display it as individual rows in a table.

Code:
CREATE FUNCTION splitFunction
(@List nvarchar(2000))
RETURNS
 @RtnValue table ( Value nvarchar(100))
AS
BEGIN
While (Charindex(',',@List)>0)
Begin
Insert Into @RtnValue (value)
Select
Value = ltrim(rtrim(Substring(@List,1,Charindex(',',@List)-1)))
Set
@List = Substring(@List,Charindex(',',@List)+len(','),len(@List))
End
Insert Into @RtnValue (Value)
Select Value = ltrim(rtrim(@List))
Return
END

Executing the following query
SELECT *
FROM dbo.splitFunction('Sara,Gopi,Rishma,Madhan,Anbu')

The output is:

Read more »

User Defined Functions

User Defined Functions:

We can create our own functions using CREATE FUNCTION statements. The syntax for creating a function is

Syntax:
CREATE FUNCTION
[owner_name.] function_name

( [ { @parameter_name [AS] scalar_parameter_data_type [ = default ] } [ ,...n ] ] )
RETURNS
scalar_return_data_type
[WITH < function_option> [[,] ...n] ]
[AS]
BEGIN
function_body
RETURN scalar_expression
END

Read more »

Functions in SQL Server

Functions:

A function is mainly used in the case where it must return a value. A function can be created and called in SQL Queries. There are some pre-defined functions available in MS SQL server, they are

• AVG() - Returns the average value
• COUNT() - Returns the number of rows
• FIRST() - Returns the first value
• LAST() - Returns the last value
• MAX() - Returns the largest value
• MIN() - Returns the smallest value
• SUM() - Returns the sum
• UCASE() - Converts a field to upper case
• LCASE() - Converts a field to lower case
• MID() - Extract characters from a text field
• LEN() - Returns the length of a text field
• ROUND() - Rounds a numeric field to the number of decimals specified
• NOW() - Returns the current system date and time
• FORMAT() - Formats how a field is to be displayed

Types of Functions:

=> User Defined Functions
=> Split function

Read more »

Benefits of Stored Procedure

Benefits of Stored Procedure:

• Recompiled execution.
• Reduced client/server traffic.
• Efficient reuse of code and programming abstraction.
• Enhanced security controls.

Read more »

Stored Procedure in SQL Server

Stored Procedure:

• A stored procedure is one or more SQL statements that have been compiled and stored with database. A stored procedure can be started by application code on the client.
• Stored procedure can improve database performance because the SQL statements in each procedure are only compiled and optimized the first time they are executed. In contrast SQL statements that are sent from a client to the server have to be compiled and optimized every time they are executed.
• In addition to SELECT statement, a stored procedure can contain other SQL statements such as INSERT, UPDATE, and DELETE. It also contains control-of-flow language.
     
Syntax:
 CREATE PROCEDURE   procedure_name [ ; number ]                                                            
[ { @parameter data_type } ]
AS sql_statement [...n]
   
Sample Code:

CREATE PROCEDURE sp_GetInventory
                @location varchar (10)
AS
SELECT
Product, Quantity
FROM
Inventory
WHERE
Warehouse = @location

Here, this stored procedure replaces the SELECT query,

SELECT
Product, Quantity
FROM
Inventory
WHERE
Warehouse = 'FL'

Where the query has to be compiled and executed every time and the warehouse manager should have knowledge about the sql queries and appropriate permissions to access the table information.

Where as in SP, the procedure is precompiled. Hence it can be executed easily by only specifying the location name.

EXECUTE sp_GetInventory 'FL'
EXECUTE sp_GetInventory 'NY'

Read more »

Disadvantages of table variable over temporary table

Disadvantages of table variable over temporary table:

• Since it cannot be passed as parameter from one stored procedure to another, there temporary tables are used.
• We cannot create non- clustered index.
• SQL Server does not maintain statistics on table variable.
• Table variable cannot be altered.
• It won’t participate in transaction rollbacks.
• It can’t use dynamic SQL, unless it is declared dynamically.
• While table sharing, nested stored procedures table variables cannot be used.
• It cannot be used for large resultsets, while needs indexes for query optimization.

Read more »