SQL Server Basics of Cursors

Cursor is a database objects to retrieve data from a result set one row at a time, instead of the T-SQL commands that operate on all the rows in the result set at one time.
We use cursor when we need to update records in a database table in singleton fashion means row by row.
Life Cycle of Cursor:
Declare Cursor:
Syntax:
DECLARE cursor_name CURSOR
FOR select_statement --define SQL Select statement
FOR UPDATE [col1,col2,...coln] --define columns that need to be updated
Open Cursor:
Syntax:
OPEN [GLOBAL] cursor_name --by default it is local
Fetch Cursor:
Syntax:
INTO @Variable_name[1,2,..n]
Close Cursor
Syntax:
CLOSE cursor_name --after closing it can be reopen
Deallocate Cursor
Syntax:
DEALLOCATE cursor_name --after deallocation it can't be reopen

An Example of Cursor:

create PROCEDURE sp_rohit
AS  
BEGIN
declare @empCode as varchar(30)
declare @emp_name as varchar(100)

--start cursor for check employee
DECLARE rohit_cursor cursor
for select empcode, emp_name from master_employee

OPEN rohit_cursor
/* first fetch starts the process */
FETCH rohit_cursor  INTO @empCode ,@emp_name
WHILE @@fetch_status = 0 /* while there's still records to process */
BEGIN
                        print @emp_name 
                        FETCH rohit_cursor  INTO @empCode ,@emp_name
                        end
close rohit_cursor
DEALLOCATE rohit_cursor
--end this cursor
end --end store procedure

SQL Server - Indexes

Relational databases like SQL Server use indexes to find data quickly when a query is processed. 
Creating and removing indexes from a database schema will rarely result in changes to an application's code; indexes operate 'behind the scenes' in support of the database engine. 
The SQL Server engine uses an index in much the same way a reader uses a book index. For example, one way to find all references to INSERT statements in a SQL book would be to begin on page one and scan each page of the book.
 We could mark each time we find the word INSERT until we reach the end of the book. This approach is pretty time consuming and laborious.
 Alternately, we can also use the index in the back of the book to find a page number for each occurrence of the INSERT statements. This approach produces the same results as above, but with tremendous savings in time.

Syntax:
CREATE INDEX [IDX_UnitPrice] ON Products (UnitPrice)
To verify that the index is created, use the following stored procedure to see a list of all indexes on the Products table:
EXEC sp_helpindex Customers 

How It Works
The database takes the columns specified in a CREATE INDEX command and sorts the values into a special data structure known as a B-tree. A B-tree structure supports fast searches with a minimum amount of disk reads, allowing the database engine to quickly find the starting and stopping points for the query we are using.

Advantage of Indexes:

Searching For Records
The most obvious use for an index is in finding a record or set of records matching a WHERE clause. Indexes can aid queries looking for values inside of a range (as we demonstrated earlier), as well as queries looking for a specific value. By way of example, the following queries can all benefit from an index on UnitPrice:
DELETE FROM Products WHERE UnitPrice = 1 
UPDATE Products SET Discontinued = 1 WHERE UnitPrice > 15 
SELECT * FROM PRODUCTS WHERE UnitPrice BETWEEN 14 AND 16 
Indexes work just as well when searching for a record in DELETE and UPDATE commands as they do for SELECT statements.

Sorting Records
SELECT * FROM Products ORDER BY UnitPrice ASC 

Grouping Records
SELECT Count(*), UnitPrice FROM Products GROUP BY UnitPrice

Maintaining a Unique Column
CREATE UNIQUE INDEX IDX_ProductName On Products (ProductName)

Index Drawbacks

1. Indexes are stored on the disk, and the amount of space required will depend on the size of the table,
2. Another downside to using an index is the performance implication on data modification statements. Any time a query modifies the data in a table (INSERT, UPDATE, or DELETE), the database needs to update all of the indexes where data has changed.

Type of Indexes in SQL Server - Clustered Indexes & Nonclustered Indexes

Type of Indexes
Clustered Indexes

1. A common analogy for a clustered index is a phone book. A phone book still sorts entries into alphabetical order. 
2. The difference is, once we find a name in a phone book, we have immediate access to the rest of the data for the name, such as the phone number and address.
3. For a clustered index, the database will sort the table's records according to the column (or columns) specified by the index. 
4. A clustered index contains all of the data for a table in the index, sorted by the index key, just like a phone book is sorted by name and contains all of the information for the person inline. 

create a clustered index:
CREATE CLUSTERED INDEX IDX_SupplierID ON Products(SupplierID) 

• we can only have one clustered index per table,
•  In SQL Server, creating a primary key constraint will automatically create a clustered index (if none exists) using the primary key column as the index key.

Non-Clustered Indexes

1. A book index stores words in order with a reference to the page numbers where the word is located. 
2. This type of index for a database is a nonclustered index; only the index key and a reference are stored.
3. The nonclustered indexes created earlier in the chapter contain only the index key and a reference to find the data, which is more like a book index. 

A Disadvantage to Clustered Indexes

1. If we update a record and change the value of an indexed column in a clustered index, the database might need to move the entire row into a new position to keep the rows in sorted order. This behavior essentially turns an update query into a DELETE followed by an INSERT, with an obvious decrease in performance.
2.  A table's clustered index can often be found on the primary key or a foreign key column, because key values generally do not change once a record is inserted into the database.

User Defined Functions (UDFs) in SQL Server

User Defined Functions (UDFs)

User defined functions are routines that encapsulates SQL logic inside it. Like stored procedures User defined functions can also be passed input parameters but user defined functions are compiled and executed at runtime so pretty slower than stored procedures.

Certain limitations for User defined functions:

i)  UDF can’t perform DML (data manipulation language) operations like Insertion, Update and Deletion on the base table.

ii) UDF can’t return non deterministic values like GETDATE () etc.

iii) Stored procedure can’t be called from inside a UDF whereas a stored procedure can call a user defined function or another stored procedure inside it.

Syntax:

CREATE FUNCTION dbo.Function

     (

     /*

     @parameter1 datatype = default value,

     @parameter2 datatype

     */

     )

  RETURNS /* datatype */

  AS

     BEGIN

      /* sql statement ... */

     RETURN /* value */

     END

 

Type of UDF :

1) Scalar Functions   (returns a single value)

CREATE FUNCTION EmployeeContactID(@Empid int)

RETURNS int

AS

BEGIN 

Declare @returnvalue int

Select @returnvalue=Employee.ContactID from Employee where Employee.EmployeeID=@Empid

        RETURN   @returnvalue

END

 

Execution:

select dbo.EmployeeContactID(1)

 

2) Inline Functions   (returns a table)

CREATE FUNCTION dbo.GetEmployeeFunction(@empid int)

RETURNS TABLE

AS

RETURN SELECT *

FROM employee where employee.EmployeeID=@empid

 

Execution:

 

select * from dbo.GetEmployeeFunction(1)

Difference between Stored procedures and User defined functions:

i)A stored procedure is pre compiled while a User defined function is compiled and executed at runtime.

ii)A Stored procedure is more flexible than user defined function like you can write complex logic (for example exceptional handling using try catch block is possible in stored procedures which is not possible in user defined functions)

iii) A stored procedure can call another stored procedure or user defined function inside it but a user defined function can’t call stored procedure inside it.

iv)A stored procedure can return non deterministic values but a user defined function can’t return a non deterministic values like Get Date () function.

v) A user defined functions does not support DML operations like insertion, deletion and update on the base table but it is possible via stored procedure.

vi) A user defined function is easier to execute and can be used  inside selection and even for joins but stored procedure can’t be used inside selection queries and it can’t be used to join with other tables.

 

'CASE' in SQL Server

where IsActive =column name
systempostalcode=table name


column IsActive has 1 or 0 values.
we want to return true if IsActive =1, and return false if IsActive =0

select  City, StateCode,  CountryCode, case when IsActive = 1 then 'True' else 'False' end as IsActive   from systempostalcode