SELECT TOP 1000 [Id]
,[UserId]
,[Pass]
,[CreatedOn]
FROM [IPEA_IN].[MASTER].[UsedPass] where UserId = 'rikeshkumar';
-------------------------------------------------------------------------------------------------
CREATE DATABASE ISA_IN
-------------------------------------------------------------------------------------------------
USE ISA_IN
-----------------------------------------------------------------------------------------------------------
CREATE TABLE ISA(EID INT, ENAME VARCHAR(20), AGE INT, GENDER VARCHAR(5), ADDRESS VARCHAR(100), SALARY MONEY)
------------------------------------------------------------------------------------------------------------
INSERT INTO ISA(EID , ENAME , AGE , GENDER , EADDRESS , SALARY ) VALUES(1, 'RAM', 31, M, XYZ 120, 21000)
----------------------------------------------------------------------------------------------------------------------
UPDATE ISA SET ENAME = @ENAME , AGE = @AGE, GENDER = @GENDER , EADDRESS = @EADDRESS, SALARY = @SALARY WHERE EID = @EID;
----------------------------------------------------------------------------------------------------------------------
SELECT * FROM ISA
------------------------------------
SELECT * FROM ISA WHERE EID = @EID;
-------------------------------------------------------------------------------
SELECT ENAME , AGE , GENDER , EADDRESS , SALARY FROM ISA WHERE EID = @EID;
------------------------------------
DELETE FROM ISA WHERE EID = @EID;
----------------------
DROP DATABASE ISA_IN
---------------
DROP TABLE ISA
--------------------
TRUNCATE TABLE ISA
-------------------------------------------------------------------------------------------------
SELECT i.ENAME , i.AGE , i.GENDER , i.EADDRESS , i.SALARY, ip.DEPTNAME, ip.LOCATION, ip.EMAIL
FROM ISA i
join IPEA ip
ON ip.EID = i.EID
-------------------------------------------------------------------------------------------------
SELECT i.ENAME , i.AGE , i.GENDER , i.EADDRESS , i.SALARY, ip.DEPTNAME, ip.LOCATION, ip.EMAIL
FROM ISA i
INNER join IPEA ip
ON ip.EID = i.EID
-- --------------------------------------------------------------------------------------------------
SELECT i.ENAME , i.AGE , i.GENDER , i.EADDRESS , i.SALARY, ip.DEPTNAME, ip.LOCATION, ip.EMAIL
FROM ISA i
LEFT join IPEA ip
ON ip.EID = i.EID
-------------------------------------------------------------------------------------------------
SELECT i.ENAME , i.AGE , i.GENDER , i.EADDRESS , i.SALARY, ip.DEPTNAME, ip.LOCATION, ip.EMAIL
FROM ISA i
RIGHT join IPEA ip
ON ip.EID = i.EID
-------------------------------------------------------------------------------------------------
SELECT i.ENAME , i.AGE , i.GENDER , i.EADDRESS , i.SALARY, ip.DEPTNAME, ip.LOCATION, ip.EMAIL
FROM ISA i
FULL OUTER join IPEA ip
ON ip.EID = i.EID
-------------------------------------------------------------------------------------------------
CROSS join: - It will the result in Cartesian product (ex. tbl1 have 2 columns n tblw 2 have 3 columns then result is 2*3 = 6)
SELECT i.ENAME , i.AGE , i.GENDER , i.EADDRESS , i.SALARY, ip.DEPTNAME, ip.LOCATION, ip.EMAIL
FROM ISA i
CROSS join IPEA ip
ON ip.EID = i.EID
-------------------------------------------------------------------------------------------------
SELECT i.ENAME , i.AGE , i.GENDER , i.EADDRESS , i.SALARY, ip.DEPTNAME, ip.LOCATION, ip.EMAIL
FROM ISA i
join IPEA ip
ON ip.EID = i.EID
---------------------------------------CTE--Duplicate data delete krne k liye below code---------------------------------------------
WITH CTE_Duplicates AS (
SELECT USERID, USERNAME
ROW_NUMBER() OVER (PARTITION BY EMAIL ORDER BY ID) AS ROWNUM
FROM [dbo].[aspnet_Users]
)
DELETE FROM CTE_Duplicates
WHERE ROWNUM >1 ;
SELECT * FROM CTE_Duplicates WHERE RowNum > 1;
------------------------------------Duplicate Table create karne k liye below code (testing purpose)-------------------------------
SELECT *
INTO Contact_List
FROM Original_table;
--------------------------------------------------------------------------------------------------------------------------
WITH CTE_Duplicates AS (
SELECT
<columns_you_need>,
ROW_NUMBER() OVER (PARTITION BY <column_to_check_duplicates> ORDER BY <some_column>) AS ROWNUM
FROM
<your_table>
)
SELECT *
FROM CTE_Duplicates
WHERE ROWNUM > 1;
-----------------------------------------Index--------------------------------------------------------------
In SQL, an index is a database object that improves the speed of data retrieval operations on a table. Indexes are created on columns to help locate data without having to search every row in a table,
Basic Syntax:
CREATE CLUSTERED INDEX index_name
ON table_name (column_name);
Types of Indexes in SQL:
Clustered Index:
Sorts and stores the data rows in the table or view based on the key values of the index.
A table can have only one clustered index since the data rows can only be sorted in one order.
The physical order of the rows matches the index order.
Non-Clustered Index:
Creates a separate structure from the data rows, with pointers back to the original rows.
A table can have multiple non-clustered indexes.
The physical order of the data in the table does not change.
Unique Index:
Ensures that all values in the indexed column are unique.
It is either clustered or non-clustered, but the unique constraint adds the guarantee of uniqueness.
Composite Index:
An index on multiple columns.
Useful for queries that filter on multiple columns simultaneously.
Full-Text Index:
Used for full-text searches in SQL Server, enabling efficient search of text-based data.
It allows searching large text fields for words or phrases.
Filtered Index:
A non-clustered index that includes rows that meet a certain condition, helping to reduce the size of the index and improve query performance for specific queries.
Bitmap Index:
Primarily used in data warehouses or environments with read-heavy workloads.
Stores the index in the form of bitmaps and is efficient for columns with a low cardinality (few unique values).
XML Index:
An index specifically designed to optimize XML data retrieval in SQL Server.
When and Why to Use an Index:
1. Frequent Search on a Specific Column (e.g., EmployeeID)
If you often run queries like:
SELECT * FROM Employee WHERE EmployeeID = 103;
Here, you are searching by EmployeeID. To speed up this search, you can create a Clustered Index on the EmployeeID column. This would allow the database to directly jump to the correct row without scanning every row.
Clustered Index:
CREATE CLUSTERED INDEX IX_EmployeeID ON Employee(EmployeeID);
Now, when you search for a specific EmployeeID, the query performance improves because the data is physically sorted based on this column.
Why?
When you know that a particular column will be used frequently in searches (e.g., EmployeeID), a Clustered Index is ideal because it sorts the data in the table itself.
2. Filtering or Sorting Data by a Column (e.g., Department)
If you frequently run queries like:
SELECT * FROM Employee WHERE Department = 'IT';
Non-Clustered Index:
CREATE NONCLUSTERED INDEX IX_Department ON Employee(Department);
3. Prevent Duplicate Values (e.g., Email or National ID)
If you have a column like Email that should have unique values, and you're frequently searching by it:
SELECT * FROM Employee WHERE Email = 'jane.smith@example.com';
You can create a Unique Index on the Email column to ensure all values are unique and to optimize searches on this column.
Unique Index:
CREATE UNIQUE INDEX IX_Email ON Employee(Email);
This ensures that no duplicate email addresses are inserted and also speeds up searches by Email.
Why?
Use a Unique Index when a column requires uniqueness and you frequently search by that column (like Email or NationalID).
4. Composite Index for Multiple Columns
If you often search based on multiple columns together, like:
SELECT * FROM Employee WHERE Department = 'HR' AND HireDate > '2020-01-01';
You can create a Composite Index on both Department and HireDate to optimize this kind of query.
Composite Index:
CREATE NONCLUSTERED INDEX IX_Dept_HireDate ON Employee(Department, HireDate);
This index is helpful when you frequently run queries that filter or sort by multiple columns together.
Summary:
Use indexes when you need to optimize search, filter, or sort operations on large tables.
Clustered indexes are best for primary keys or columns used for frequent lookups.
Non-clustered indexes work well for columns used in filters and sorts.
Unique indexes ensure data uniqueness and improve search performance on unique columns.
Composite indexes optimize queries on multiple columns.
----------------------------------Trigger----------------------------------------------------------------------
Triggers automatically execute when you perform actions like insert, update, or delete on a table.
Triggers can be used for auditing, validations, and automatic updates.
INSTEAD OF triggers replace the original action (like instead of deleting, soft-delete).
AFTER triggers execute after the original action is complete.
BEFORE triggers execute before the original action is complete.
SYNTAX;-
CREATE TRIGGER trigger_name
ON table_name
AFTER INSERT
AS
BEGIN
-- Trigger logic here
PRINT 'Row inserted in the table';
END;
BASIC SYNTAX;-
CREATE TRIGGER trigger_name
ON table_name
[AFTER | BEFORE | INSTEAD OF] [INSERT | UPDATE | DELETE]
AS
BEGIN
-- यहाँ पर आप वह कोड लिख सकते हैं जो ट्रिगर चलने पर निष्पादित होगा
PRINT 'यह ट्रिगर सफलतापूर्वक चला है';
END;
1.AFTER INSERT Trigger:
CREATE TRIGGER trgAfterInsert
ON Employees
AFTER INSERT
AS
BEGIN
-- यहाँ पर ऑडिट टेबल में इन्सर्ट की गई जानकारी लॉग की जा सकती है
INSERT INTO AuditTable (EmployeeID, Action)
SELECT EmployeeID, 'INSERT' FROM inserted;
PRINT 'नया रेकॉर्ड सफलतापूर्वक डाला गया और ऑडिट में रिकॉर्ड हुआ';
END;
-------------------------------Cursor-----------------------------------
SQL Cursor ek database object hota hai jo result set (query ka output) par row-by-row operations karne ki suvidha deta hai. Cursors ka istemal tab hota hai jab aapko query ke result set par iterative operations karne ki zaroorat hoti hai. Cursors ko samajhne ke liye kuch key concepts hain:
Types of Cursors:
1. Implicit Cursors:
Ye automatic generate hote hain SQL statements ke execution ke dauran.
Jab aap ek single SELECT statement execute karte hain, to SQL engine automatically ek implicit cursor create karta hai.
Explicit Cursors:
Ye user-defined hote hain aur inhe manually create, open, fetch, aur close karna hota hai.
2. Explicit cursors ka istemal tab hota hai jab aapko complex logic ya multiple rows par operations perform karna ho.
Cursor ke Operations:
Declare a Cursor:
Cursor declare karte samay aap specify karte hain ki kaunsa result set aap cursor ke liye use karna chahte hain.
DECLARE cursor_name CURSOR FOR
SELECT column1, column2 FROM table_name WHERE condition;
Open the Cursor:
Cursor ko open karne par, SQL server result set ko fetch karne ke liye ready ho jata hai.
OPEN cursor_name;
Fetch from the Cursor:
Fetch operation ke madhyam se aap cursor se ek row ya rows ko retrieve kar sakte hain.
FETCH NEXT FROM cursor_name INTO variable1, variable2;
Process the Fetched Data:
Jab aap data fetch karte hain, to aap us data par kuch operations (jaise insert, update, ya delete) perform kar sakte hain.
Close the Cursor:
Jab aapka kaam khatam ho jata hai, to cursor ko close karna zaroori hota hai taaki resources release ho sake.
CLOSE cursor_name;
Deallocate the Cursor:
Cursor ko deallocate karne se woh memory release hoti hai jo cursor use kar raha tha.
DEALLOCATE cursor_name;
Example:
Yahan ek example diya gaya hai jo cursor ka istemal dikhata hai:
DECLARE @employee_name NVARCHAR(100);
DECLARE employee_cursor CURSOR FOR
SELECT name FROM Employees WHERE department = 'Sales';
OPEN employee_cursor;
FETCH NEXT FROM employee_cursor INTO @employee_name;
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @employee_name; -- Ya koi aur processing
FETCH NEXT FROM employee_cursor INTO @employee_name;
END
CLOSE employee_cursor;
DEALLOCATE employee_cursor;
Advantages of Cursors:
Row-by-Row Processing: Cursors aapko result set par row-by-row processing karne ki suvidha dete hain.
Complex Operations: Aap complex operations perform kar sakte hain jo simple SQL statements se sambhav nahi hote.
Disadvantages of Cursors:
Performance Overhead: Cursors ka istemal performance ko affect kar sakta hai, kyunki ye resources ko hold karte hain aur iterative processing karte hain.
Complexity: Cursors ko use karna kabhi-kabhi complex ho sakta hai, aur inhe sahi tarike se manage karna zaroori hai.
Conclusion:
Cursors SQL me ek powerful tool hain, lekin inka istemal kabhi-kabhi performance issues la sakta hai. Jab bhi sambhav ho, set-based operations ko prefer karna chahiye, lekin jab row-by-row processing ki zaroorat ho, tab cursors ka istemal faydemand ho sakta hai.
No comments: