SQL – Information of TRY, CATCH and Error Handling

In SQL Deadlocks are very crucial things which we need to handle with ease, So for this features, You can do whatever if any error occur.@@ERROR is not used for every statement executed then.TRY and CATCH helps to write logic and error handling code differenly.Whatever code need to execute you can write in TRY blockContinue reading “SQL – Information of TRY, CATCH and Error Handling”

SQL 2016 Features News

1] Stretch Database Stretch Database allows you to keep your most recent production data local to your location but migrate the historical data to the cloud(Microsoft Azure). This new feature gives you the benefit of presenting the data as a single data store while behind the scenes allowing you the flexibility to segregate active andContinue reading “SQL 2016 Features News”

SQL – Index Rebuild / Index Reorganize

Index Rebuild will drop the existing Index and it will recreates the index. ALTER INDEX index_name ON databasename.tablename REBUILD Index Reorganize physically reorganizes the leaf nodes of the index. ALTER INDEX index_name ON databasename.tablename REORGANIZE Index should be rebuild when index fragmentation is most of the time great than 40%. Index should be reorganized whenContinue reading “SQL – Index Rebuild / Index Reorganize”

SQL – Search Text in Stored Procedure

There is below few questions which will ask you while Interview or Any time …How can I find if particular table is being used in the stored procedure?How to search in stored procedures?How can I do dependency check for objects in stored procedure without using sp_depends? The same feature can be implemented using following script.Continue reading “SQL – Search Text in Stored Procedure”

SQL – Temp Table v/s Temp Variable

Temp Tables are originated for the storage and it’s manipulation of temporal data. Also its stored physically created in tempDB. Temp Variables are originated for returning date-sets from table-valued functions. Temp tables might result in stored procedures being recompiled, perhaps often. Table variables will not. Temp Table create using SELECT INTO, which can be quickerContinue reading “SQL – Temp Table v/s Temp Variable”

SQL – Unique Index and Unique Constraint

Unique Index and Unique Constraint both are the same in terms of working in Tables. They achieve same goal. Performance wise also both are same. —Unique ConstraintALTER TABLE table_name ADD CONSTRAINT constraint_name UNIQUE NONCLUSTERED(column_names) ON [PRIMARY] —Unique IndexCREATE UNIQUE NONCLUSTERED INDEX index_name ON table_name(column_name) ON [PRIMARY] In both syntax are different the effect is the same. Unique Constraint creates Unique Index to maintain theContinue reading “SQL – Unique Index and Unique Constraint”

How to Delete Duplicate Records from Table

Below query is useful to delete the duplicate Ids data from table. The table must have identity column, which will be used to identify the duplicate records. CREATE TABLE StudentDetail(ID INT IDENTITY(1,1),StudentID INT,Name VARCHAR(10),Address VARCHAR(100)) INSERT INTO StudentDetailSELECT 10001,’Test1′,’Mumbai’INSERT INTO StudentDetailSELECT 10002,’Test2′,’Mumbai’INSERT INTO StudentDetailSELECT 10003,’Test3′,’Mumbai’INSERT INTO StudentDetailSELECT 10004,’Test4′,’Mumbai’INSERT INTO StudentDetailSELECT 10005,’Test5′,’Mumbai’INSERT INTO StudentDetailSELECT 10001,’Test11′,’Mumbai’INSERT INTOContinue reading “How to Delete Duplicate Records from Table”

SQL – DISTINCT and GROUP BY Difference

Distinct and Group By executed the same result as well as query plan of execution.Group BY clause is used whenever aggregate functions are used.Distinct use to remove the duplicates and it can’t be use in aggregate functions. examples for DISTINCTSELECT DISTINCT col1FROM table1 examples for GROUP BY SELECT col1FROM table1GROUP BY col1 both query willContinue reading “SQL – DISTINCT and GROUP BY Difference”

Create your website with WordPress.com
Get started