SQL Server is DBMS system provided by Microsoft, which is used to communicate with the database and this language supports operations like DML(Data Manipulation Language),DDL(Data Defination Language) And DQL(Data Query Language) Operations.

SQL Profiler is a tool which allows system administrator to monitor events in the SQL server. This is mainly used to capture and save data about each event of a file or a table for analysis.

RDBMS is a database management system which is used to maintain the data records in the tables . Relationships can be created to maintain the data in the table.

Normalization :-

In RDBMS, the process of organizing data to minimize redundancy is called normalization. In normalization database is divided in two or more tables and a relationship is defined among the tables and it is called normalization.

De-Normalization :-

It is a process of attempting to optimize the performance of a database by adding redundant data. Actually redundancy is introduced intentionally in a table to improve performance and it is called de-normalization.

A database is described as an organized way of collection of DATA. It is the collection of schemes, tables, queries, reports, views and other objects.

Syntax:
CREATE DATABASE DatabaseName

Example:
CREATE DATABASE TECHTRICK ----Create a New Database

"Where" clause is used to filter the rows based on condition. "Having" clause used with SELECT clause and this is used with GROUP BY clause. If GROUP BY clause not used then "Having" clause works like a "Where" clause.

Where Clause Syntax :-

SELECT COUNT(OrderID) FROM Customer.CustomerOrderDetail WHERE OrderPrice > 50

Having Clause Syntax :-

SELECT OrderID,SUM(OrderPrice * OrderQty) AS TotalPrice FROM Customer.CustomerOrderDetail GROUP BY CustomerOrderID HAVING CustomerOrderID > 1000

An identity column in the SQL automatically generates numeric values. We can defined a start and increment value of identity column. Identity columns do not need to be indexed.

This is the combination of fields/columns which are used to uniquely specify a row. Primary Key has a unique constraint defined on the column and the value in the column cannot be NULL.

Unique Key constraint will enforce the uniqueness of values in the list of columns of the table. No duplicate values are allowed. Unique key will allow NULL in one row unlike Primary Key.

Foreign key is used to establish a relationship between the columns of other table. Foreign key relationship to be created between two tables by referencing a column of the table to primary key of another table.

A CHECK constraint can be applied to a column in a table to limit the values that can be placed in a column. Check constraint is to enforce integrity.

COALESCE is used to return first non-null expression within the arguments. This function is used to return a non-null from more than one column in the arguments.

FLOOR function is used to round up a non-integer value to the previous least integer. Example is given :-

SIGN function is used to determine whether the number specified is Positive, Negative and Zero. This will return +1,-1 or 0.

"JOIN" is used to get the data from multiple tables by joining those. Keys created in the tables will play a major role in the "JOIN".

• • Inner Join
• • Right outer Join
• • Left outer Join
• • Full outer Join
• • Equi Join
• • Cross Join

Relationships are created by linking the column in one table with the column in another table. There are three different types of relationship that can be created.

• 1. One to One Relationship
• 2. Many to One relationship
• 3. Many to Many relationship

• 1. A clustered index is a special type of index that reorders the way records in the table are physically stored. Therefore table can have only one clustered index. The leaf nodes of a clustered index contain the data pages.
• 2. A non clustered index is a special type of index in which the logical order of the index does not match the physical stored order of the rows on disk. The leaf node of a non clustered index does not consist of the data pages. Instead, the leaf nodes contain index rows.

Delete command removes the rows from a table based on the condition that we provide with a WHERE clause. Truncate will actually remove all the rows from a table and there will be no data in the table after we run the truncate command.

• 1. TRUNCATE is faster and uses fewer system and transaction log resources than DELETE.
• 2. TRUNCATE removes the data by deallocating the data pages used to store the table's data, and only the page deallocations are recorded in the transaction log.
• 3. TRUNCATE removes all rows from a table, but the table structure, its columns, constraints, indexes and so on, remains. The counter used by an identity for new rows is reset to the seed for the column.
• 4. You cannot use TRUNCATE TABLE on a table referenced by a FOREIGN KEY constraint. Because TRUNCATE TABLE is not logged, it cannot activate a trigger.
• 5. TRUNCATE cannot be rolled back.
• 6. TRUNCATE is DDL(data defination language) Command.
• 7. TRUNCATE Resets identity of the table(Starting from 1)

• 1. DELETE removes rows one at a time and records an entry in the transaction log for each deleted row.
• 2. If you want to retain the identity counter, use DELETE instead. If you want to remove table definition and its data, use the DROP TABLE statement.
• 3. DELETE Can be used with or without a WHERE clause.
• 4. DELETE Activates Triggers.
• 5. DELETE can be rolled back.
• 6. DELETE is DML(data manipulation language) Command.
• 7. DELETE does not reset identity of the table.

A Stored Procedure is a collection or a group of T-SQL statements. Stored Procedures are a precompiled set of one or more statements that are stored together in the database. They reduce the network load because of the precompilation. We can create a Stored Procedure using the "Create proc" statement

• 1. By using stored procedures we can reuse the code.
• 2. Stored procedure helps in reducing network traffic and latency
• 3. Stored procedures provide better security to your data.
• 4. Stored procedure is cached in SQL Server’s memory. So it helps to reduce the server overhead. It also enhances application performance.
• 5. Stored procedures help us in the encapsulation of the code. The code of the stored procedure can be changed without affecting application.

SQL Server supports recursive stored procedure which calls by itself. Recursive stored procedure can be defined as a method of problem solving wherein the solution is arrived repetitively.

• Local temporary tables are visible when there is a connection, and are deleted when the connection is closed.

CREATE TABLE #<tablename>

• Global temporary tables are visible to all users, and are deleted when the connection that created it is closed.

CREATE TABLE ##<tablename>

COLLATE keyword can be applied to either column definitions or database definitions. For example

SELECT EmpId FROM Employee ORDER BY EmpId COLLATE Latin1_General_CS_AS_KS_WS ASC;

"DISTINCT" keyword is used to remove the duplicate values in a given column value. For example

SELECT DISTINCT column_name From tableName

• • @ROWCOUNT - Used to return the number of rows affected in the table due to last statement.
• • @ERROR – Used to return the error code which is occurred due to last SQL statement. ‘0’ means there are no errors.

Cursor is used in case of row traversal. This can be considered as a pointer pointing to one row at a time in the list of rows. Cursors can be used for retrieval, removal or addition of records in a table.

User Defined functions are being used to handle complex queries.

• • Scalar – This type of functions are used for returning single scalar value.
• • Table Valued – This type of function are used for returning a table which has list of rows. Sql supports datatype called table which is used here for returning a table.

Union is used to combine all result sets and it removes the duplicate records from the final result set obtained unlike UnionAll which returns all the rows irrespective of whether rows are being duplicated or not.

• • Temporary Tables are physically created in the tempdb database. These tables act as the normal table and also can have constraints, index like normal tables.\
• • Table Variable acts like a variable and exists for a particular batch of query execution. It gets dropped once it comes out of batch. It is created in the memory database but may be pushed out to tempdb.

User Names and Passwords are stored in sys.server_principals and sys.sql_logins. But passwords are not stored in normal text.

Both are same but GETDATE can give time till milliseconds and SYSDATETIME can give precision till nanoseconds. SYSDATE TIME is more accurate than GETDATE.

RAISEERROR is the command used to generate and initiates error processing for a given session. Those user defined messages are stored in sys.messages table.

Trigger allows us to execute a batch of SQL code when a table event occurs (Insert, update or delete command executed against a specific table). Triggers are stored in and managed by DBMS. It can also execute stored procedure.

A view is a virtual table which contains data from one or more tables. Views restrict data access of table by selecting only required values and make complex queries easy. Rows updated or deleted in the view are updated or deleted in the table the view was created with. It should also be noted that as data in the original table changes, so does data in the view, as views are the way to look at part of the original table. The results of using a view are not permanently stored in the database.

STUFF: This function is used to replace the part of string with some another string.

STUFF (String1, Position, Length, String2)

String1 - String to be overwritten

Position - Starting Position for overwriting

Length - Length of replacement string

String2- String to overwrite

SELECT STUFF('Arpit',2,2,'mi')
Output: Amit

REPLACE: This function is used to replace all the occurrences of particular string by another string.

REPLACE(String1, String2, String3)

SELECT REPLACE('Arpit Jain','i','m')
Output: Arpmt Jamn

• • It is not possible to change the table data with the use of User defined functions but you can do it by using stored procedure.
• • The execution of User defined function will be stopped if any error occurred in it. But in the case of Stored procedure when an error occurs the execution will ignore the error and jumps to the next statement.
• • User defined functions do not return output parameters while stored procedure can return output parameters.

When a complex SQL statement has number of joins then it can be made easier by using Common Table Expression.

SELECT * FROM (

SELECT emp.EmpName,dept.Department,emp.Gender FROM Employee emp

left join Department dept on emp.DeptID = dept.DeptID) E

WHERE E.Gender = 'Male'

ORDER BY T.EmpName

• - The CTE Name (followed by WITH keyword)
• - The Column List (Optional)
• - The Query (Appears within parentheses after the AS keyword)

With E(EmpName, Department, Gender)

AS

(

SELECT emp.EmpName,dept.Department,emp.Gender FROM Employee emp

left join Department dept on emp.DeptID = dept.DeptID

)

SELECT * FROM E WHERE E.Gender = 'Male'

ORDER BY E.EmpName

COMMIT statement is used to end the current transaction and once the COMMIT statement is exceucted the transaction will be permanent and undone.

BEGIN

UPDATE EmpDetails SET EmpName = 'Akash' where Dept = 'Developer'

COMMIT;

END;

ROLLBACK statement is used to end the current transaction and undone the changes which was made by that transaction.

ROLLBACK [TO] Savepoint_name;

BEGIN

Statement1;

SAVEPOINT mysavepoint;

BEGIN

Statement2;

EXCEPTION

WHEN OTHERS THEN

ROLLBACK TO mysavepoint;

Statement5;

END;

END;