Microsoft SQL has two variants i.e. CROSS APPLY and OUTER APPLY. Both operators are in SQL used to perform table-valued function /correlated subqueries against rows in another table. CROSS APPLY OUTER APPLY Points to consider:
Author Archives: nchaudhari65
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 – Trigger Disable in Database
In various interview questions asked below questions related to Disable Trigger. 1] How to disable the triggers for database? 2] How to disable all the triggers for all servers? 1]DISABLE TRIGGER table_name ON database_name;GO 2]DISABLE TRIGGER ALL ON ALL SERVER;GO
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”
Identify Locking SPs
In a previous section we learn about how to get Blocking SPs, In Todays section we seen how to identify locking . Locking is integral part of successful transactions of RDBMS. It is important in SQL Server that transactions pass the ACID test: Atomic – the transaction performs in an all-or-nothing fashion Consistent Isolated –Continue reading “Identify Locking SPs”
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.tech.blog 