Top posts
-
SQL UNION
SQL UNION Operator The UNION operator is used to combine the result-set of two or more SELECT statements. Notice that each SELECT statement within the UNION must have the same number of columns. The columns must also have similar data types. Also, the...
-
Linked server:SQL Server
Linked server:SQL Server 2005 new feature. A linked server is simply a connection to an Object Linking and Embedding Database (OLEDB) data source. THE MAIN PURPOSE is to access remote databases from source server instance without login. Permissions to...
-
SQL Server Transaction Log FILE?
SQL Server Transaction Log There has always been some debate as to whether or not there are real benefits to be gained from accessing the information in the transaction log. This article will endeavor to answer that question by looking at the following:...
-
SQL INNER JOIN
SQL INNER JOIN Keyword The INNER JOIN keyword selects all rows from both tables as long as there is a match between the columns in both tables. SQL INNER JOIN Syntax SELECT column_name(s) FROM table1 INNER JOIN table2 ON table1.column_name=table2.column_name;...
-
SQL DBA QUESTIONS
1) What is SQL Server? Microsoft SQL Server 2005 is a database platform for large-scale online transaction processing (OLTP), data warehousing, and e-commerce applications; it is also a business intelligence platform for data integration, analysis, and...
-
SQL Dynamic management Views
SQL DMVs Top 15 SQL Dynamic management Views 1.sys.dm_db_index_physical_stats: This dynamic management function returns information regarding data and index information for data tables and views 2.sys.dm_exec_requests: This view shows information regarding...
-
Cursor Advantages and Disadvantages
Cursor Advantages and Disadvantages Cursor advantages: Using cursor to getting multiple values. One cursor have multiple select statements Cursor parameters cursor cursor_name (parameter datatype) is select * from table_name where(condition) Where Current...
-
What type of locking occurs during the Snapshot generation?
What type of locking occurs during the Snapshot generation? Locking depends on the type of replication used: In snapshot replication, the snapshot agent locks the object during the entire snapshot generation process. In transactional replication, locks...
-
PL/SQL Cursors For Loop
PL/SQL Cursors For Loop ----------------------------------- PL/SQL cursor FOR loop has one great advantage of loop continued until row not found. In sometime you require to use explicit cursor with FOR loop instead of use OPEN, FETCH, and CLOSE statement....
-
Compound Triggers
Oracle 11g offers a new twist on triggers, the compound trigger, a trigger than can act both before and after an update, insert or delete has occurred. This makes possible the abilty in one trigger to perform processing similar to a stored procedure without...
-
PL/SQL RAISE_APPLICATION_ERROR
PL/SQL RAISE_APPLICATION_ERROR In PL/SQL RAISE_APPLICATION_ERROR function use to assign exception name and exception error code. Define RAISE_APPLICATION_ERROR function syntax, raise_application_error(error_number, error_message); Example Code raise_app_error.sql...
-
PL/SQL pragma EXCEPTION_INIT
PL/SQL pragma EXCEPTION_INIT pragma EXCEPTION_INIT : Pragma is a keyword directive to execute proceed at compile time. pragma EXCEPTION_INIT function take this two argument, exception_name error_number You can define pragrma EXCEPTION_INIT in DECLARE...
-
Plsql function exception
Plsql function exception ORA-01403: no data found Cause: No data was found from the objects. Action: There was no data from the objects which may be due to end of fetch Reference: Oracle Documentation ORA-01403 is a very common error. ORA-01403 occurs...
-
Left Anti Semi Join Operator in SQL SERVER
What is Left Anti Semi Join Operator? The Left Anti Semi Join operator returns the result set for each row from the first (top) input when there is no matching row in the second (bottom) input. If no join predicate exists in the Argument column, each...
-
Slipstreaming SQL Server
What is Slipstreaming? It is a term used to describe merging original source media with updates in memory and then installing the updated files. Slipstreaming has been supported by Windows Operating systems for awhile but has just been added to SQL Server...
-
Attach\Detach in SQL Server database:
Attach\Detach: 1. Take full database backup before performing activity. 2. After go to SQL Server database> tasks> detach> select drop all connections options 3. Copy files from one location to another location by using copy option but not cut method....
-
REF CURSOR
REF CURSOR A REF Cursor is a datatype that holds a cursor value in the same way that a VARCHAR2 variable will hold a string value. A REF Cursor can be opened on the server and passed to the client as a unit rather than fetching one row at a time. One...
-
PL/SQL Functions Syntax in oracle
CREATE [OR REPLACE] FUNCTION [SCHEMA..] function_name [ (parameter [,parameter]) ] RETURN return_datatype IS | AS [declaration_section variable declarations; constant declarations; ] BEGIN [executable_section PL/SQL execute/subprogram body ] [EXCEPTION]...
-
what is bulk collect?
BULK COLLECT Bulk binds can improve the performance when loading collections from a queries. The BULK COLLECT INTO construct binds the output of the query to the collection. It can be used with all three types of collections: associative arrays, nested...
-
REPLACE Function in SQL
What is REPLACE Function in SQL? Replaces the Characters in a string with required set of characters. It Translate Pattern by Pattern. Example: SELECT ' TECHBLOG' STRING, REPLACE('TECHBLOG','TECH','SQL') REPLACE FROM DUAL; WHERE ENAME LIKE'%A%'; STRING...
-
SQL Server
SQL SQL Server 2014 Create Database SELECT Statement WHERE Clause ALIAS Column ORDER BY Clause TOP Clause SELECT DISTINCT Statement SELECT INTO Statement INSERT INTO SELECT Statement GROUP BY Clause HAVING Clause BULK INSERT Statement SQL Query Builder...
-
SQL ACID properties
SQL ACID ACID properties When a transaction processing system creates a transaction, it will ensure that the transaction will have certain characteristics. The developers of the components that comprise the transaction are assured that these characteristics...
-
Refresh Materialized Views
Refresh Materialized Views If a materialized view is configured to refresh on commit, you should never need to manually refresh it, unless a rebuild is necessary. Remember, refreshing on commit is a very intensive operation for volatile base tables. It...
-
SQL SERVER KEYS
Types of SQL Keys We have following types of keys in SQL which are used to fetch records from tables and to make relationship among tables or views. Super Key Super key is a set of one or more than one keys that can be used to identify a record uniquely...
-
How to enable SQL Server Agent XPs to start SQL Server Agent?
If you see Agent XPs disabled from SQL Server Management Studio (as per below picture), you will be unable to start SQL Server Agent from Management Studio directly. Option 1: Start SQL Server Agent from SQL Server Configuration Manager. Agent XPs will...