Question: 1) Explain the difference between DELETE and TRUNCATE

Answer:

• TRUNCATE is a DDL command, whereas DELETE is a DML command.
• We can’t execute a trigger in case of TRUNCATE whilst with DELETE, we can accomplish a trigger.
• TRUNCATE is quicker than DELETE, because DELETE will log all delete operations, however TRUNCATE will not. We can not recover TRUNCATEd data. (However, remember we can always recover from backups)
• We can use any condition in WHERE clause using DELETE but it is not possible with TRUNCATE.
• If a table is referenced by any foreign key constraints, then TRUNCATE won’t work; however, DELETE may work if the row to be deleted is not associated with any row from the foreign side

Question 2) Explain (and do more exercises) on String (text) manipulation t-sql commands below

• CHARINDEX( findTextData, textData, [startingPosition] ) – Returns the starting position of the specified expression in a character string. The starting position is optional.
• LEFT( character_expression , integer_expression ) – Returns the left part of a character string with the specified number of characters.
• LEN( textData ) – Returns integer value of the length of the string, excluding trailing blanks.
• LOWER ( character_expression ) – Returns a character expression after converting uppercase character data to lowercase.
• LTRIM( textData) – Removes leading blanks. PATINDEX( findTextData, textData ) – Returns integer value of the starting position of text found in the string.
• REPLACE( textData, findTextData, replaceWithTextData ) – Replaces occurrences of text found in the string with a new value.
• REPLICATE( character_expression , integer_expression ) – Repeats a character expression for a specified number of times.
• REVERSE( character_expression ) – Returns the reverse of a character expression.
• RTRIM( textData) – Removes trailing blanks. SPACE( numberOfSpaces ) – Repeats space value specified number of times.
• STUFF( textData, start , length , insertTextData ) – Deletes a specified length of characters and inserts another set of characters at a specified starting point.
• SUBSTRING( textData, startPosition, length ) – Returns portion of the string.
• UPPER( character_expression ) – Returns a character expression with lowercase character data converted to uppercase