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 »