Indexes

An index in a database is a list of values in a table with the storage locations of rows in the table that contain each value. In a database, an index allows the database program to find data in a table without scanning the entire table.Indexes can be created on either a single column or a combination of columns in a table and are implemented in the form of B-trees. An index contains an entry with one or more columns (the search key) from each row in a table. A B-tree is sorted on the search key, and can be searched efficiently on any leading subset of the search key. For example, an index on columns A, B, C can be searched efficiently on A, on A, B, and A, B, C. The performance benefits of indexes, however, do come with a cost. Tables with indexes require more storage space in the database. Also, commands that insert, update, or delete data can take longer and require more processing time to maintain the indexes.
When you design and create indexes, you should ensure that the performance benefits outweigh the extra cost in storage space and processing resources.

Cursors, How to use them and when to use

· A cursor is a mechanism you can use to fetch rows one at a time.
· Transact-SQL cursors are used mainly in stored procedures, triggers, and Transact-SQL scripts in which they make the contents of a result set available to other Transact-SQL statements.
· When writing code for a transaction where the result set includes several rows of data, you may declare and use a cursor.
· For example, if you write code that includes a SELECT statement or stored procedure that returns multiple rows, you must declare a cursor and associate it with the SELECT statement. Then, by using the FETCH statement, you can retrieve one row at a time from the result set.
The typical process for using a Transact-SQL cursor in a stored procedure or trigger is:
· Declare Transact-SQL variables to contain the data returned by the cursor. Declare one variable for each result set column. Declare the variables to be large enough to hold the values returned by the column and with a data type that can be implicitly converted from the data type of the column.
· Associate a Transact-SQL cursor with a SELECT statement using the DECLARE CURSOR statement. The DECLARE CURSOR statement also defines the characteristics of the cursor, such as the cursor name and whether the cursor is read-only or forward-only.
· Use the OPEN statement to execute the SELECT statement and populate the cursor.
· Use the FETCH INTO statement to fetch individual rows and have the data for each column moved into a specified variable. Other Transact-SQL statements can then reference those variables to access the fetched data values. Transact-SQL cursors do not support fetching blocks of rows.
· When you are finished with the cursor, use the CLOSE statement. Closing a cursor frees some resources, such as the cursor's result set and its locks on the current row, but the cursor structure is still available for processing if you reissue an OPEN statement. Because the cursor is still present, you cannot reuse the cursor name at this point. The DEALLOCATE statement completely frees all resources allocated to the cursor, including the cursor name. After a cursor is de-allocated, you must issue a DECLARE statement to rebuild the cursor.
Disadvantages of cursors
· Each time a row is fetched from the cursor, it results in a network roundtrip; where as a normal SELECT query makes only one roundtrip, however large the result set is.
· Cursors are also costly because they require more resources and temporary storage (results in more IO operations).
· Further, there are restrictions on the SELECT statements that can be used with some types of cursors.

@@DATEFIRST function in SQL Server

@@DATEFIRST in SQL Server will returns the current value, for the session, of SET DATEFIRST.SET DATEFIRST indicates the specified first day of each week.
The U.S. English default is 7, Sunday.
Language settings affect date information. In the following example, the language is first set to italian.
SELECT @@DATEFIRST returns 1.
The language is then set to us_english.
SELECT @@DATEFIRST returns 7.

Get the size of the database

SELECT table_schema "Database", sum( data_length + index_length ) / 1024 / 1024 "Size (MB)", sum( data_free )/ 1024 / 1024 "Free (MB)" FROM information_schema.TABLES GROUP BY table_schema ;

Check for Duplicate Records in SQL Server

SELECT email,COUNT(email) AS NumOccurrencesFROM usersGROUP BY emailHAVING ( COUNT(email) > 1 )

Date Functions

CURRENT_DATE ( ) Returns the current date.
CURRENT_TIME () Returns the current local time.
CURRENT_TIMESTAMP () Returns the current local date and local time as a timestamp value.
CURDATE ( ) Returns the current date.
CURTIME ( ) Returns the current local time.
DAYNAME (date_exp) Returns a character string containing the data source/specific name of the day (for example, Sunday through Saturday).
DAYOFMONTH (date_exp) Returns the day of the month based on the month field in date_exp as an integer value in the range of 1-31.
DAYOFWEEK (date_exp) Returns the day of the week based on the week field in date_exp as an integer value in the range of 1-7, where 1 represents Sunday.
DAYOFYEAR(date_exp) Returns the day of the year based on the year field in date_exp as an integer value in the range of 1-366.
EXTRACT (extract-field FROM extract-source) Returns the extract-field portion of the extract-source. The extract-source argument is a datetime or interval expression.
HOUR (time_exp) Returns the hour based on the hour field in time_exp as an integer value in the range of 0-23.
MINUTE (time_exp) Returns the minute based on the minute field in time_exp as an integer value in the range of 0-59.
MONTH (date_exp) Returns the month based on the month field in date_exp as an integer value in the range of 1-12.
MONTHNAME(date_exp) Returns a character string containing the data source/specific name of the month (for example, January through December). Currently only supports English locale.
NOW ( ) Returns current date and time as a timestamp value.
QUARTER(date_exp) Returns the quarter in date_exp as an integer value in the range of 1-4, where 1 represents January 1 through March 31.
SECOND (time_exp) Returns the second based on the second field in time_exp as an integer value in the range of 0-59.

Difference between two dates in T-SQL

for day datediff(days,[column_name1],[column_name2])
for month datediff(monts,[column_name1],[column_name2])
for year datediff(year,[column_name1],[column_name2])