Difference Between Rebuild and Reorganize indexes

Syntax :
Rebuild : ALTER INDEX ALL ON  table_name REBUILD
Reorganize : ALTER INDEX ALL ON  table_name REORGANIZE

S.no	REBUILD	REORGANIZE
1	This process drops the existing Index and Recreates the index	This process doesn’t drop indexes; It physically reorganizes the leaf nodes of the index.
2	Syntax: ALTER INDEX ALL ON table_name REBUILD	Syntax: ALTER INDEX ALL ON table_name REORGANIZE
3	Index should be rebuild when index fragmentation is great than 30%	Index should be reorganized when index fragmentation is between 10% to 30%
4	Index rebuilding process uses more CPU and it locks the database resources	Index reorganize process uses less CPU and it doesn’t the database resources
5	Rebuilding an index can be executed online or offline	Reorganizing an index is always executed online
6	A nice thing about rebuilding an index is that you can change the setting for index properties like the fill factor and index padding	Index options cannot be specified when reorganizing an index
7	Requires ALTER permission on the table or view or  User must be a member of the sysadmin fixed server role or the db_ddladmin and db_owner fixed database roles	Requires ALTER permission on the table or view or  User must be a member of the sysadmin fixed server role or the db_ddladmin and db_owner fixed database roles
8	REBUILD locks the table for the whole operation period except in Enterprise Edition with ONLINE = ON	REORGANIZE doesn't lock the table.
9	It is changing the whole B-tree, recreating the index	It is a process of cleaning, organizing, and defragmenting of only  "leaf level" of the B-tree
10	REBUILD will automatically rebuild all index-column statistics	Statistics are NOT updated with a reorganization
11	This reclaims disk space by compacting the pages in new index	It works in two phases – compaction and defrag
12	Rebuild does require extra space as same the size of index	Reorganize essentially just swaps one page with another and thus does not require free space
13	Rebuild can fix extent fragmentation as well	Reorganize can remove almost all of the logical fragmentation but it cannot necessarily fix extent fragmentation
14	Index (clustered or non-clustered) cannot be built online if it contains LOB data (text, ntext, varchar(max), nvarchar(max), varbinary(max), image and xml data type columns).	Its always online operation.
15	If an index has multiple partitions, then you cannot rebuild a single partition online	You can reorganize a single index partition online
16	After rebuild statistics on non-indexed columns doesn’t get re-computed	Statistics are NOT updated with a reorganization
17	Rebuild is an atomic operation	Reorganize can be stopped while it is occurring and you won't lose the work it has already completed
18	Rebuild indexes always generates similar amount of  t-log for same size index	For large, heavily fragmented indexes the reorganization operation will generate more t-log as compared to less fragmented index
19	REBUILD requires enough free space in the database to accommodate the new index	REORGANIZE only requires 8KB of additional space in the database
20	REBUILD can use multiple CPUs so the operation runs faster	REORGANIZE is always single-threaded
21	REBUILD can use minimal-logging to reduce transaction log growth	REORGANIZE is always fully logged, but doesn’t prevent transaction log clearing.
22	An index rebuild will always rebuild the entire index, regardless of the extent of fragmentation	An index reorganize will only take care of the existing fragmentation
23	SQL 2000 use DBCC DBREINDEX	SQL 2000 use DBCC INDEXDEFRAG
24	Even If the index spans multiple data files, it will be taken care	If the index spans multiple data files, reorganizing will only reorder pages within the same file, it won’t move pages betweenfiles

CONVERT DATE FUNCTIONS

SQL CAST and CONVERT

It converts an expression from one data type to another.
CAST and CONVERT have similar functionality.

SQL CAST and CONVERT Syntax
Using CAST:
CAST ( expression AS data_type ) 

Using CONVERT:CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

Example of SQL Cast and Convert
SQL Cast and Convert - StringSELECT SUBSTRING('CAST and CONVERT', 1, 3)
Return Value = CAS (it get from index 1 to 3)

SELECT CAST('CAST and CONVERT' AS char(3))
Return Value = CAS (it get 3 char only)


SQL Cast and Convert - Date Time
-Converting date time to character data(vachar)
-The default values (style 0 or 100, 9 or 109, 13 or 113, 20 or 120, and 21 or 121) always return the Without century year(yy).
-Add 100 to a style value to get a four-place year that includes the century year(yyyy).
-Below is example for converting 1 format of date time to different format of date time, so that it can be use in various condition.

Value of current Date Time GETDATE()
SELECT (GETDATE()) = 2007-06-06 23:41:10.153

SELECT CONVERT(varchar,GETDATE(),0)
Return Value = Jun 6 2007 11:07PM
SELECT CONVERT(varchar,GETDATE(),100)
Return Value = Jun 6 2007 11:07PM


SELECT CONVERT(varchar,GETDATE(),1)
Return Value = 06/06/07
SELECT CONVERT(varchar,GETDATE(),101)
Return Value = 06/06/2007


SELECT CONVERT(varchar,GETDATE(),2)
Return Value = 07.06.06
SELECT CONVERT(varchar,GETDATE(),102)
Return Value = 2007.06.06

SELECT CONVERT(varchar,GETDATE(),3)
Return Value = 06/06/07
SELECT CONVERT(varchar,GETDATE(),103)
Return Value = 06/06/2007


SELECT CONVERT(varchar,GETDATE(),4)
Return Value = 06.06.07
SELECT CONVERT(varchar,GETDATE(),104)
Return Value = 06.06.2007

SELECT CONVERT(varchar,GETDATE(),5)
Return Value = 06-06-07
SELECT CONVERT(varchar,GETDATE(),105)
Return Value = 06-06-2007


SELECT CONVERT(varchar,GETDATE(),6)
Return Value = 06 Jun 07
SELECT CONVERT(varchar,GETDATE(),106)
Return Value = 06 Jun 2007


SELECT CONVERT(varchar,GETDATE(),7)
Return Value = Jun 06, 07
SELECT CONVERT(varchar,GETDATE(),107)
Return Value = Jun 06, 2007


SELECT CONVERT(varchar,GETDATE(),8)
Return Value = 23:38:49
SELECT CONVERT(varchar,GETDATE(),108)
Return Value = 23:38:49


SELECT CONVERT(varchar,GETDATE(),9)
Return Value = Jun 6 2007 11:39:17:060PM
SELECT CONVERT(varchar,GETDATE(),109)
Return Value = Jun 6 2007 11:39:17:060PM


SELECT CONVERT(varchar,GETDATE(),10)
Return Value = 06-06-07
SELECT CONVERT(varchar,GETDATE(),110)
Return Value = 06-06-2007


SELECT CONVERT(varchar,GETDATE(),11)
Return Value = 07/06/06
SELECT CONVERT(varchar,GETDATE(),111)
Return Value = 2007/06/06


SELECT CONVERT(varchar,GETDATE(),12)
Return Value = 070606
SELECT CONVERT(varchar,GETDATE(),112)
Return Value = 20070606


SELECT CONVERT(varchar,GETDATE(),13)
Return Value = 06 Jun 2007 23:40:14:577
SELECT CONVERT(varchar,GETDATE(),113)
Return Value = 06 Jun 2007 23:40:14:577


SELECT CONVERT(varchar,GETDATE(),14)
Return Value = 23:40:29:717
SELECT CONVERT(varchar,GETDATE(),114)
Return Value = 23:40:29:717


SELECT CONVERT(varchar,GETDATE(),20)
Return Value = 2007-06-06 23:40:51
SELECT CONVERT(varchar,GETDATE(),120)
Return Value = 2007-06-06 23:40:51


SELECT CONVERT(varchar,GETDATE(),21)
Return Value = 2007-06-06 23:41:10.153
SELECT CONVERT(varchar,GETDATE(),121)
Return Value = 2007-06-06 23:41:10.153


SELECT CONVERT(varchar,GETDATE(),126)
Return Value = 2007-06-06T23:41:10.153


SELECT CONVERT(varchar,GETDATE(),131)
Return Value = 21/05/1428 11:41:10:153PM

STRING FNCTIONS

Example SQL String Function - ASCII
- Returns the ASCII code value of a keyboard button and the rest etc (@,R,9,*) .
Syntax - ASCII ( character)SELECT ASCII('a') -- Value = 97
SELECT ASCII('b') -- Value = 98
SELECT ASCII('c') -- Value = 99
SELECT ASCII('A') -- Value = 65
SELECT ASCII('B') -- Value = 66
SELECT ASCII('C') -- Value = 67
SELECT ASCII('1') -- Value = 49
SELECT ASCII('2') -- Value = 50
SELECT ASCII('3') -- Value = 51
SELECT ASCII('4') -- Value = 52
SELECT ASCII('5') -- Value = 53 


Example SQL String Function - SPACE 
-Returns spaces in your SQL query (you can specific the size of space).
Syntax - SPACE ( integer)
SELECT ('SQL') + SPACE(0) + ('TUTORIALS')
-- Value = SQLTUTORIALS
SELECT ('SQL') + SPACE(1) + ('TUTORIALS')
-- Value = SQL TUTORIALS 


Example SQL String Function - CHARINDEX
-Returns the starting position of a character string.
Syntax - CHARINDEX ( string1, string2 [ , start_location ] ) 
SELECT CHARINDEX('SQL', 'Well organized understand SQL tutorial') 
-- Value = 27SELECT CHARINDEX('SQL', 'Well organized understand SQL tutorial', 20) 
-- Value = 27SELECT CHARINDEX('SQL', 'Well organized understand SQL tutorial', 30)
-- Value = 0 (Because the index is count from 30 and above)


Example SQL String Function - REPLACE
-Replaces all occurrences of the string2 in the string1 with string3.
Syntax - REPLACE ( 'string1' , 'string2' , 'string3' )
SELECT REPLACE('All Function' , 'All', 'SQL')
-- Value = SQL Function


Example SQL String Function - QUOTENAME
-Returns a Unicode string with the delimiters added to make the input string a valid Microsoft® SQL Server™ delimited identifier.
Syntax - QUOTENAME ( 'string' [ , 'quote_character' ] ) 
SELECT QUOTENAME('Sql[]String')
-- Value = [Sql[]]String]


Example SQL String Function - STUFF
- Deletes a specified length of characters and inserts string at a specified starting index.
Syntax - STUFF ( string1 , startindex , length , string2 ) 
SELECT STUFF('SqlTutorial', 4, 6, 'Function')
-- Value = SqlFunctional
SELECT STUFF('GoodMorning', 5, 3, 'good')
-- Value = Goodgoodning


Example SQL String Function - LEFT
-Returns left part of a string with the specified number of characters.
Syntax - LEFT ( string , integer) 
SELECT LEFT('TravelYourself', 6) 
-- Value = Travel
SELECT LEFT('BeautyCentury',6) 
-- Value = Beauty


Example SQL String Function - RIGHT
-Returns right part of a string with the specified number of characters.
Syntax - RIGHT( string , integer)
SELECT RIGHT('TravelYourself', 6)-- Value = urself
SELECT RIGHT('BeautyCentury',6)-- Value = Century


Example SQL String Function - REPLICATE
-Repeats string for a specified number of times.
Syntax - REPLICATE (string, integer)SELECT REPLICATE('Sql', 2) 
-- Value = SqlSql


Example SQL String Function - SUBSTRING
-Returns part of a string.
Syntax - SUBSTRING ( string, startindex , length )
SELECT SUBSTRING('SQLServer', 4, 3) 
-- Value = Ser


Example SQL String Function - LEN
-Returns number of characters in a string.
Syntax - LEN( string) 
SELECT LEN('SQLServer')
-- Value = 9


Example SQL String Function - REVERSE
-Returns reverse a string.Syntax - REVERSE( string)SELECT REVERSE('SQLServer') 
-- Value = revreSLQS


Example SQL String Function - UNICODE
-Returns Unicode standard integer value.
Syntax - UNICODE( char) 
SELECT UNICODE('SqlServer') 
-- Value = 83 (it take first character)
SELECT UNICODE('S')
-- Value = 83


Example SQL String Function - LOWER
-Convert string to lowercase.Syntax - LOWER( string )SELECT LOWER('SQLServer') 
-- Value = sqlserver


Example SQL String Function - UPPER
-Convert string to Uppercase.
Syntax - UPPER( string ) 
SELECT UPPER('sqlserver') 
-- Value = SQLSERVER


Example SQL String Function - LTRIM
-Returns a string after removing leading blanks on Left side.
Syntax - LTRIM( string )SELECT LTRIM(' sqlserver')-- Value = 'sqlserver' (Remove left side space or blanks)


Example SQL String Function - RTRIM 
-Returns a string after removing leading blanks on Right side.
Syntax - RTRIM( string )SELECT RTRIM('SqlServer ')
-- Value = 'SqlServer' (Remove right side space or blanks)

DATE FNCTIONS

select GETDATE()

select DAY('12-22-2014')

select MONTH(getdate())

select YEAR(getdate())

select DATEPART(WEEKDAY,getdate())

select DATEPART(DAY,GETDATE())


select DATEPART(MONTH,getdate())

select DATEPART(YEAR,GETDATE())

select DATEPART(WEEK,GETDATE())

select DATENAME(WEEKDAY,GETDATE())

select DATENAME(DAY,GETDATE())

select DATENAME(WEEK,GETDATE())

select DATENAME(MONTH,GETDATE())

SELECT DATENAME(YEAR,GETDATE())

SELECT DATEADD(DAY,1,GETDATE())

SELECT DATEADD(WEEKDAY,5,GETDATE())

SELECT DATEADD(WEEK,5,GETDATE())

SELECT DATEADD(MONTH,1,GETDATE())

SELECT DATEADD(YEAR,1,GETDATE())

SELECT DATEDIFF(DAY,'01-01-2014',GETDATE())

SELECT DATEDIFF(WEEKDAY,'01-01-2010','12-02-2014')

SELECT DATEDIFF(WEEK,'01-01-2010','12-02-2014')

SELECT DATEDIFF(MONTH,'01-01-2010','12-02-2014')

SELECT DATEDIFF(YEAR,'03-20-1989',getdate())