Order by:
The Order by command is used to sort the result set in ascending or descending order.
The Order by command sorts the result set in ascending order by default. To sort the records in descending order, use the Desc keyword.
Select into:
The Select into statement copies data from one table into a new table.
Insert into select:
The Insert Into Select statement copies data from one table and inserts it into another table.
Insert Into Select requires that data types in source and target tables match.
The existing records in the target table are unaffected.
Backup:
Partial backup: Contains data from only some of the filegroups in a database, including the data in the primary filegroup, every read/write filegroup, and any optionally-specified read-only files.
Case:
The CASE statement goes through conditions and return a value when the first condition is met. So, once a condition is true, it will stop reading and return the result. If no conditions are true, it returns the value in the ELSE clause.
Null Function:
Storing Null value in a field is storing nothing in the field. ISNULL function is used to check the stored value is null value or not.
Patindex:
Stuff:
The STUFF() function deletes a part of a string and then inserts another part into the string, starting at a specified position.
Comments:
By writing comments the programmer or any other who can open the file is able to know the program within a less time. Whenever a special thing is used in the program then it is better to write a description about it in comments.
Single Line Comments:
Multi Line Comments:
Exception Handling:
An error condition during a program execution is called an exception and the mechanism for resolving such an exception is known as an exception handler. SQL Server provides TRY, CATCH blocks for exception handling. We can put all T-SQL statements into a TRY BLOCK and the code for exception handling can be put into a CATCH block.
Begin and End:
A compound statement allows one or more SQL statements to be grouped together and treated as a unit. A compound statement starts with BEGIN and ends with END.
Commit:
Commit is used for the permanent changes. When we use Commit in any query then the change made by that query will be permanent and visible. We can't Rollback after the Commit.
Rollback:
Rollback is used to undo the changes made by any command but only before a commit is done. We can't Rollback data which has been committed in the database with the help of the commit keyword.
T-sql:
T-SQL (Transact-SQL) is a set of programming extensions from Sybase and Microsoft that add several features to the Structured Query Language, including transaction control, exception and error handling, row processing and declared variables.
Print:
In SQL Server PRINT statement can be used to return message to the client. It takes string expression as input and returns string as a message to the application.
Variable:
A variable allows a programmer to store data temporarily during the execution of code.
IF..ELSE:
IF...ELSE statement is used to execute code when a condition is TRUE, or execute different code if the condition evaluates to FALSE.
ELSE IF:
The SQL Server else if statement handle multiple statements effectively by executing them sequentially.
While:
In SQL Server, you use a WHILE LOOP when you are not sure how many times you will execute the loop body and the loop body may not execute even once.
Return:
Return is immediate and complete and can be used at any point to exit from a procedure, batch or statement block. Statement that follow return are not executed.
The Order by command is used to sort the result set in ascending or descending order.
The Order by command sorts the result set in ascending order by default. To sort the records in descending order, use the Desc keyword.
Select into:
The Select into statement copies data from one table into a new table.
Insert into select:
The Insert Into Select statement copies data from one table and inserts it into another table.
Insert Into Select requires that data types in source and target tables match.
The existing records in the target table are unaffected.
Backup:
A copy of SQL Server data that can be store and recover the data after a server failure. A backup of SQL Server data is created at the level of a database or one or more of its files or filegroups. Table level backups cannot be created. In addition to data backups, the full recovery model requires creating backups of the transaction log.
Recovery Model: A database property that controls transaction log maintenance on a database. Three recovery models exits: simple, full and bulk-logged. The recovery model of database determines its backup and restore requirements.
Restore: A multi-phase process that copies all the data and log pages from a specified SQL Server backup to a specified database, and then rolls forward all the transactions that are logged in the backup by applying logged changes to bring the data forward in time.
Types of Backups:
Copy-Only backup: Backup for a special purpose without affecting the overall backup and restore procedures for the databse.
Types: Copy only full backups, Copy only log backups
Data backup: a backup of data in a complete database or a partial database or a set of data files or filegroups.
Database backup: A backup of a database. It represent the whole database at the time the backup finished.
Differential backup: It contains only changes made to the database since its most recent full database backup.
Full backup: A data backup contains all the data in a specific database or filegroups or files, and enough log files to recovering that data.
Log backup: A backup of transaction logs that include all log records except previous backed up log records.
File backup: A backup of one or more database files or filegroups.
Partial backup: Contains data from only some of the filegroups in a database, including the data in the primary filegroup, every read/write filegroup, and any optionally-specified read-only files.
Case:
The CASE statement goes through conditions and return a value when the first condition is met. So, once a condition is true, it will stop reading and return the result. If no conditions are true, it returns the value in the ELSE clause.
Null Function:
Storing Null value in a field is storing nothing in the field. ISNULL function is used to check the stored value is null value or not.
Patindex:
The PATINDEX() function returns the position of a pattern in a string.
If the pattern is not found, this function returns 0.
Stuff:
The STUFF() function deletes a part of a string and then inserts another part into the string, starting at a specified position.
Comments:
By writing comments the programmer or any other who can open the file is able to know the program within a less time. Whenever a special thing is used in the program then it is better to write a description about it in comments.
Single Line Comments:
Single line comments start with --. Any text between -- and the end of the line will be ignored (will not be executed).
Multi Line Comments:
Multi-line comments start with /* and end with */.
Any text between /* and */ will be ignored.
Exception Handling:
An error condition during a program execution is called an exception and the mechanism for resolving such an exception is known as an exception handler. SQL Server provides TRY, CATCH blocks for exception handling. We can put all T-SQL statements into a TRY BLOCK and the code for exception handling can be put into a CATCH block.
Begin and End:
A compound statement allows one or more SQL statements to be grouped together and treated as a unit. A compound statement starts with BEGIN and ends with END.
Commit:
Commit is used for the permanent changes. When we use Commit in any query then the change made by that query will be permanent and visible. We can't Rollback after the Commit.
Rollback:
Rollback is used to undo the changes made by any command but only before a commit is done. We can't Rollback data which has been committed in the database with the help of the commit keyword.
T-sql:
T-SQL (Transact-SQL) is a set of programming extensions from Sybase and Microsoft that add several features to the Structured Query Language, including transaction control, exception and error handling, row processing and declared variables.
Print:
In SQL Server PRINT statement can be used to return message to the client. It takes string expression as input and returns string as a message to the application.
Variable:
A variable allows a programmer to store data temporarily during the execution of code.
Syntax:
Declare VariableName DataType;
IF..ELSE:
IF...ELSE statement is used to execute code when a condition is TRUE, or execute different code if the condition evaluates to FALSE.
ELSE IF:
The SQL Server else if statement handle multiple statements effectively by executing them sequentially.
While:
In SQL Server, you use a WHILE LOOP when you are not sure how many times you will execute the loop body and the loop body may not execute even once.
Return:
Return is immediate and complete and can be used at any point to exit from a procedure, batch or statement block. Statement that follow return are not executed.