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 DatabaseNameExample:
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.
Identity(1,1)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.
Id int primary key identity(1,1)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.
Id int uniqueForeign 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.
CustomerID int FOREIGN KEY REFERENCES Customers(CustomerID)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.
Age int CHECK (Age>=18)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.
Select COALESCE(empno, empname, salary) from employee;FLOOR function is used to round up a non-integer value to the previous least integer. Example is given :-
SELECT FLOOR(6.7)SIGN function is used to determine whether the number specified is Positive, Negative and Zero. This will return +1,-1 or 0.
SELECT SIGN(-35)"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".
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.
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.
TRUNCATEA 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
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 #
• Global temporary tables are visible to all users, and are deleted when the connection that created it is closed.
CREATE TABLE ##
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
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.
Note :- Cursor is basically use of Loop.User Defined functions are being used to handle complex queries.
There are two types of user defined functions –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.
Note: Union All is more faster than Union.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.
Syntax:STUFF (String1, Position, Length, String2)
String1 - String to be overwritten
Position - Starting Position for overwriting
Length - Length of replacement string
String2- String to overwrite
Example:
SELECT STUFF('Arpit',2,2,'mi')
Output: Amit
REPLACE: This function is used to replace all the occurrences of particular string by another string.
Syntax:REPLACE(String1, String2, String3)
Example:
SELECT REPLACE('Arpit Jain','i','m')
Output: Arpmt Jamn
When a complex SQL statement has number of joins then it can be made easier by using Common Table Expression.
Consider the following SQL statement.The syntax of CTE is as followSELECT * 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
This way the query can be made more readable and easy to understand.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.
Syntax: COMMIT; Example: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.
Syntax:ROLLBACK [TO] Savepoint_name;
Example:BEGIN
Statement1;
SAVEPOINT mysavepoint;
BEGIN
Statement2;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK TO mysavepoint;
Statement5;
END;
END;