SQL Syntax

SQL Syntax:
Data Definition Language (DDL) statements are used to define the database structure or schema.

                                                 DDL Statements

Data Manipulation Language (DML) statements are used for managing data within schema objects.

                                                  DML Statements

Data Control Language (DCL) statements deal with the user privileges.

                                             DCL Statements

Read more »

Query Optimization Tips

Query Optimization Tips:

• Distinct aggregation (e.g. select count(distinct key) …) is a SQL language feature that results     in some very slow queries.
• Try to restrict the queries result set by using the WHERE clause. This can reduce network traffic    and boost the overall performance of the query.
• Try to restrict the queries result set by returning only the particular columns from the table,     not all table's columns.
• Use views and stored procedures instead of heavy-duty queries. This can be used to facilitate       permission management also, because you can restrict user access to table columns they should       not see.
• Try to avoid using SQL Server cursors, whenever possible. It can result in performance      degradation when compared to select statement.
• If you need to return the total table's row count, you can use alternative way instead of
• SELECT COUNT (*) statement.
• You can use sysindexes system table, in this case. There is ROWS column in the sysindexes table.    This column contains the total row count for each table in your database. So, you can use the       following select statement
• SELECT rows FROM sysindexes WHERE id =    OBJECT_ID('table_name') AND indid < 2 So, you can improve the speed of such queries in several times.
• Use table variables instead of temporary tables, if your resultset has less than 100 rows.
• Try to avoid the HAVING clause, whenever possible.
• Try to avoid using the DISTINCT clause, whenever possible.
• Include SET NOCOUNT ON statement into your stored procedures to stop the message indicating the     number of rows affected by a T-SQL statement.
• Use the select statements with TOP keyword or the SET ROWCOUNT statement, if you need to return     only the first n rows.
• Use the FAST number_rows table hint if you need to quickly return 'number_rows' rows.
• Try to use UNION ALL statement instead of UNION, whenever possible.
• Do not use optimizer hints in your queries.

Read more »

Index Optimization Tips

Index Optimization Tips:

• Consider creating index on column(s) frequently used in the WHERE, ORDER BY, and GROUP BY clauses.
• Keep your indexes as narrow as possible.
• Drop indexes that are not used.
• Try to create indexes on columns that have integer values rather than character values.
• Limit the number of indexes, if your application updates data very frequently.
• Check that index you tried to create does not already exist.
• Create clustered index instead of nonclustered to increase performance of the queries that return a range of values and for the queries that contain the GROUP BY or ORDER BY clauses and return the sort results.
• Create nonclustered indexes to increase performance of the queries that return few rows and where the index has good selectivity.
• Create clustered index on column(s) that is not updated very frequently.
• Create clustered index based on a single column that is as narrow as possibly.
• Avoid creating a clustered index based on an incrementing key.
• Create a clustered index for each table.
• Don't create index on column(s) which values has low selectivity.
• If you create a composite (multi-column) index, try to order the columns in the key as to enhance selectivity, with the most selective columns to the leftmost of the key.
• If you create a composite (multi-column) index, try to order the columns in the key so that the WHERE clauses of the frequently used queries match the column(s) that are leftmost in the index.
• If you need to join several tables very frequently, consider creating index on the joined columns.
• Consider creating a surrogate integer primary key (identity, for example).
• Consider creating the indexes on all the columns, which referenced in most frequently used queries in the WHERE clause which contains the OR operator.
• If your application will perform the same query over and over on the same table, consider creating a covering index including columns from this query.
• Use the DBCC DBREINDEX statement to rebuild all the indexes on all the tables in your database periodically (for example, one time per week at Sunday) to reduce fragmentation.
• Use the DBCC INDEXDEFRAG statement to defragment clustered and secondary indexes of the specified table or view.Consider using the SORT_IN_TEMPDB option when you create an index and when tempdb is on a different set of disks than the user database.
• Use the SQL Server Profiler Create Trace Wizard with "Identify Scans of Large Tables" trace to determine which tables in your database may need indexes.

Read more »

Index implementations

Index implementations:

Indexes can be implemented using a variety of data structures. Popular indexes include balanced trees, B+ trees and hashes.
In Microsoft SQL Server, the leaf node of the clustered index corresponds to the actual data, not simply a pointer to data that resides elsewhere, as is the case with a non-clustered index. Each relation can have a single clustered index and many unclustered indexes.

Read more »

Index Architecture

Index Architecture:

A. Clustered index
Clustered indexes sort and store the data rows in the table or view based on their key values.
There can be only one clustered index per table because the rows themselves can be sorted in only one order.
Advantages
It increases the speed while we are trying to retrieve the data.
Limitations
Insertion and deletion will become a slow process as it uses physical index.

B. Non clustered index
It provides the logical index contains the non clustered index key values and each key value entry has a pointer to the data row that contains the key value.
We can have more than one non clustered index for a single table.

C. Unique Indexes
A unique index ensures that the indexed column contains no duplicate values. In the case of multicolumn unique indexes, the index ensures that each combination of values in the indexed column is unique. For example, if a unique index full_name is created on a combination of last_name, first_name, and middle_initial columns, no two people could have the same full name in the table.

Both clustered and nonclustered indexes can be unique. Therefore, provided that the data in the column is unique, you can create both a unique clustered index and multiple-unique nonclustered indexes on the same table.

Read more »

Index Types

Types:

Bitmap index
A bitmap index is a special kind of index that stores the bulk of its data as bit arrays (bitmaps) and answers most queries by performing bitwise logical operations on these bitmaps. The most commonly used index, such as B+trees, are most efficient if the values it indexes do not repeat or repeat a smaller number of times. In contrast, the bitmap index is designed for cases where the values of a variable repeat very frequently. For example, the gender field in a customer database usually contains two distinct values: male or female. For such variables, the bitmap index can have a significant performance advantage over the commonly used trees.

Dense index
A dense index in databases is a file with pairs of keys and pointers for every record in the data file. Every key in this file is associated with a particular pointer to a record in the sorted data file. In clustered indexes with duplicate keys, the dense index points to the first record with that key.

Sparse index
A sparse index in databases is a file with pairs of keys and pointers for every block in the data file. Every key in this file is associated with a particular pointer to the block in the sorted data file. In clustered indexes with duplicate keys, the sparse index points to the lowest search key in each block.

Reverse index
A reverse key index reverses the key value before entering it in the index. E.g., the value 24538 becomes 83542 in the index. Reversing the key value is particularly useful for indexing data such as sequence numbers, where new key values monotonically increase.

Read more »

Index in SQL Server

Index:
Index is mainly used for speed up the storage or retrieval time for a data from or to a particular row of a table in a database.

=>  Types
=>  Index Architecture
=>  Index implementations
=>  Index Optimization Tips

Read more »

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 »

Table variables in SQL Server

Table variables:
• It is a datatype. Unlike other datatype, table variables cannot be used as input and output parameters. Its scope is Stored Procedure; User defined functions, and batches.
• It provides great performance when compared to temp tables.
• It is used instead of temporary tables, when the resultset is less than 100 rows. Hence if resultset is small, table variable is best choice.
• We can insert, delete, and update records into table variable.
• It has restricted scope, hence it brings performance optimization.
• In table variable, all constraints are used like unique, not null, check, default, primary key and unique key. Hence it produces the appropriate resultset.
• No need of recompilation of stored procedure while using table variables.
• During transactions, it produces less locking and logging overhead.
• No need to drop the table variable explicitly. It will be closed, once the application is closed.

Syntax:
DECLARE @tablevariable_name TABLE
{
[Column name (n) datatype]
}
INSERT INTO @tablevariable_name
SELECT * FROM table_name [where (condition)]
(SQL query statements: update or delete)

Sample code:
DECLARE @TibetanYaks TABLE
(YakID int,
YakName char (30)
)
INSERT INTO
@TibetanYaks (YakID, YakName)
SELECT
YakID, YakName
FROM  
dbo.Yaks
WHERE  
YakType = 'Tibetan'

Difference between Temporary table & Table variable:

Read more »

Temporary tables in SQL Server

Temporary tables:

• Temporary tables are used to store and process intermediate results by using the same selection, update, and join capabilities of SQL Server tables. However, using temporary tables can adversely affect system performance
• There are two types of temporary tables: global and local.
• Temp tables can be created locally (#TableName) or globally (##TableName)
• SQL server appends a unique number at the end ‘temporary table’ object to identify it , in order to avoid confusion when two users create the same temporary table name.
• It works pretty quickly. It is cached in memory.
• We should drop the temporary table explicitly.
• We should recompile the stored procedures again and again, if we use temporary tables inside it. It avoids recompilations, always we need to create temporary table and create indexed for that.
• Temp tables allow for multiple indexes to be created
• Temp tables can be used throughout multiple batches.
• Temp tables can be used to hold the output of a stored procedure

Syntax:
CREATE TABLE
#table_name [column_name [n] datatype...]
INSERT INTO
#tablevariable_name
SELECT   * FROM table_name [where (condition)]
(SQL query statements: update or delete, drop)
Sample Code:

CREATE TABLE
  #Yaks
(
YakID int,
YakName char (30)
)

INSERT INTO
#TibetanYaks (YakID, YakName)
SELECT
YakID, YakName
FROM
dbo.Yaks
WHERE YakType = 'Tibetan'

Read more »

CASE Statements in SQL Server

CASE Statements:

SQL CASE is a very unique conditional statement providing if/then/else logic for any ordinary SQL command, such as SELECT or UPDATE.

It provides the ability to manipulate the presentation of the data without actually updating or changing the base table.
It masks the actual values present in a table to end users without altering the data in base table. And provides the necessary information needed by the particular end users.

Syntax:
Select
column_name, ‘new_column_name’=CASE
WHEN (Condition)
THEN
“msg to display / any computation”
ELSE
Some other output
END

Sample code:
Select
  Product, ‘Status’=CASE
WHEN
Quantity>0
THEN
 “In-stock”
ELSE
 “Out-of-stock”
END
 From
 dbo.inventory

Explanation: 
• This query displays the product and status of the quantity of product whether its in-stock or out-of-stock by checking a condition quantity>0, instead of displaying the actual amount of quantity.
• It provides an online catalog to allow users to check the status of items without disclosing the actual amount of inventory the store currently has in stock.

Read more »

Drawbacks of Cursors

Drawbacks of Cursors:

• Fetching a row from the cursor may result in a network round trip each time.
• Repeated network round trips can severely impact the speed of the operation using the cursor.
• Cursors allocate resources on the server, for instance locks, temporary storage etc.
• If a cursor is not properly closed, the resources will not be freed until the SQL session (connection) itself is closed. This wasting of resources on the server can not only lead to performance degradations but also to failures.

Read more »

Types of cursors

Types of cursors:

Scrollable :
? Scrollable cursors can move in either direction.
? It can position the cursor anywhere in the result set using the FETCH SQL statement.
? The keyword SCROLL must be specified when declaring the cursor. The default is NO SCROLL.
? The target position for a scrollable cursor can be specified relative to the current cursor position or absolute from the beginning of the result set.

  Syntax:

          DECLARE
cursor_name sensitivity SCROLL CURSOR
FOR
SELECT...
FROM
FETCH [NEXT | PRIOR | FIRST | LAST] FROM cursor_name|
FETCH ABSOLUTE n FROM cursor_name|
FETCH RELATIVE n FROM cursor_name

Non-Scrollable (forward-only):

• Here, we can FETCH each row at most once and the cursor automatically moves to the immediately following row.
• With-Hold cursors
• Cursors will be closed automatically once the transaction is over i.e. when a
commit or rollback occurs. This property of a cursor can be changed by declared it using the WITH HOLD clause.
• Holdable cursor is open for commit and closed for rollback

DECLARE cursor_name CURSOR WITH HOLD FOR SELECT ... FROM...

Read more »

Cursors in SQL Server

Cursors:
Cursors are simply defined as “It’s an iterator over the collection of rows in the recordset”.
Using cursors, the client can get, put, and delete database records. Database programmers use cursors for processing individual rows returned by the database system for a query.

Syntax:
DECLARE cursor_name CURSOR FOR SELECT ... FROM...
• It declares a cursor with a name for the table to be accessed.

OPEN cursor_name
• Open statement places the cursor before first row in the resultset.

FETCH cursor_name INTO...
• Position cursors on a specific row in the result set with the FETCH statement. A fetch operation transfers the data of the row into the application.

CLOSE cursor_name
• It closes the cursor after completion of the recursive process.

=> Types of cursors
=> Drawbacks of Cursors

Read more »