Top posts
-
PL/SQL Functions
PL/SQL Functions PL/SQL functions block create using CREATE FUNCTION statement. The major difference between PL/SQL function or procedure, function return always value where as procedure may or may not return value. When you create a function or procedure,...
-
What is view with check option in SQL SERVER
What is view with check option? **** The with check option causes the where clause of the view to check the data being inserted or updated through the view in addition to the data being retrieved. In a sense, it makes the where clause a two-way restriction....
-
SQL AND & OR Operators
SQL AND & OR Operators SQL AND operator is used to filter the records by combining two boolean expressions and returns true when both boolean expressions are true whereas SQL OR operator returns true value when either the first condition or the second...
-
XML indexes in SQL Server
What are types of XML indexes in SQL Server? Microsoft SQL Server supports different types of XML indexes. An XML index is different than a relational index. There are basically TWO types of XML Indexes viz., Primary XML Indexes and Secondary XML indexes....
-
Sql server limitations of IDENTITY Column
What are limitations of IDENTITY Column? IDENTITY column can be used as a surrogate key. This column values cannot be updated once generated (automatically or manually). As we have possibility to duplicate these values within a table we may require to...
-
SQL QURIES
Write a Query To Delete The Repeated Rows from emp table; SQL>Delete from emp where rowid not in(select min(rowid)from emp group by ename) TO DISPLAY 5 TO 7 ROWS FROM A TABLE SQL>select ename from emp where rowid in(select rowid from emp where rownum<=7...
-
What are the main events and columns helpful in troubleshooting performance issues using profiler?
What are the main events and columns helpful in troubleshooting performance issues using profiler? Ans: Events: Event Group: Performance Event: ShowPlan_ALL (BinaryData column must be selected) Event: ShowPlan_XML Event Group: T-SQL Event: SQL:BatchStarted...
-
analytical functions in Oracle.
FIRST_VALUE and LAST_VALUE analytical functions by sqlandplsql Both FIRST_VALUE and LAST_VALUE are analytical functions in Oracle. FIRST_VALUE It is used to find the first value from a ordered set of values. Syntax :- 1) FIRST_VALUE(column) over(partition...
-
Nth highest salary
Nth highest salary 1) Find highest salary/salaries using DENSE_RANK() function select * from (select empno,salary,dense_rank() over(order by salary desc) as rk from emp) where rk = 1; EMPNO SALARY RK ———- ———- ———- 10 12000 1 2) Find 2nd highest salary/salaries...
-
PL/SQL functions in oracle
PL/SQL functions block create using CREATE FUNCTION statement. The major difference between PL/SQL function or procedure, function return always value where as procedure may or may not return value. When you create a function or procedure, you have to...
-
What are the advantages and disadvantages of merge replication?
What are the advantages and disadvantages of merge replication? Advantages It provides built-in and custom conflict resolution capabilities. It allows for the synchronization of data from multiple tables at one time. It provides rich data replication...
-
Difference between Primary Key & Foreign Key
Difference between Primary Key & Foreign Key Primary Key Primary key uniquely identify a record in the table. Primary Key can't accept null values. By default, Primary key is clustered index and data in the database table is physically organized in the...
-
triggers & integrity constraints
diff between triggers & integrity constraints? Constraint Vs Trigger Constraints will check for existing rows also Triggers will not check for existing rows Constraints provide standard error messages Triggers provide user friendly error messages Constraints...
-
Bulk Binds and Triggers
Bulk Binds and Triggers For bulk updates and deletes the timing points remain unchanged. Each row in the collection triggers a before statement, before row, after row and after statement timing point. For bulk inserts, the statement level triggers only...
-
Plsql Collection Methods
Collection Methods A collection method is a built-in PL/SQL subprogram that returns information about a collection or operates on a collection. Collection methods make collections easier to use, and make your applications easier to maintain. You invoke...
-
Bulk Binds in Oracle
Bulk Binds Bulk binding decreases context switch between PL/SQL engine and SQL Engine that makes code execution faster. Context switch occurs every time when PL/SQL calls SQL engine to parse, execute or fetch from cursor. Since context switching uses...
-
TRIGGERS
A trigger is an database object which executes automatically in response to a an on a particular table in a database. the event can be insert , update or delete. Triggers are stored programs, which are automatically executed or fired when some events...
-
INDEXS IN SQL SERVER
clustered index --------------- datapages store into leaf level nodes only one clustered index for table (data arrange physically only one way) good performance not allow duplicates If we create primary key on table on any key column then automatically...
-
ORACLE DBA INTERVIEW QUESTIONS
Q: What are the Oracle Background Processes? A: The Oracle Background Processes are programs or tasks that run on the Oracle background such as log writers, db writers, archives, checkpoint, etc. Q: Describe the V$BGPROCESS view. A: The V$BGPROCESS view...
-
How to REBUILD all indexes in database with FILLFACTOR?
xecute the following Microsoft SQL Server T-SQL database administration script in SSMS Query Editor to REBUILD all indexes in the AdventureWorks2008 sample database. -- SQL Server 2008 script to REBUILD all indexes for all tables USE AdventureWorks2008;...
-
Top 10 questions and answers about SQL Server Indexes?
1: What is an index? 2: What does a table look like without indexes? 3: What types of indexes are available in SQL Server? 4: What happens when you create a clustered index? 5: What about nonclustered indexes? 6: What about included columns? How can they...
-
SQL query performance killers – understanding poor database indexing
SQL Server performance is affected by many factors. The most common SQL Server performance killers are poor database design, poor indexing, poor query design, not reusable execution plans, frequent query recompilation, excessive fragmentation, and more....
-
Wipro interview questions 2017
1.what is diffrence between 2014 vs 2016 ?2.How you configure SQl cluster?3.Do you know about ITIL Process?4.What is problem ticket?5.What kind of problems tickets you resloved?6.How to add a node in existing cluster?7.What kind of dmvs using for performance...
-
SQL Server Terminology
SQL Server Terminology Database – A database is an organized collection of data for one or more purposes, usually in digital form. Relational Database – A relational database stores data in separate tables instead of placing all data in one large table.....
-
sql server Fragmentation:
Fragmentation: Storing data non-contiguously on disk or page is known as fragmentation. 2 types: 1. Internal fragmentation 2. Extrenal fragmentation 1. Internal fragmentation: When records are stored non-contiguously inside the page, then it is called...