What Are The Points To Be Considered For Creating A Fast Performing Stored Procedure
The performance of any application largely depends upon the backend process which extracts data. If it is faster then the application will be also responsive. The designing of the stored procedure is very critical and need to be careful to remove any We can consider the following points while designing stored procedure :
How Can Sql Server Instances Be Hidden
When you launch SQL Server Management Studio , you see the choice to connect with a DB instance. Whenever wanted, you can browse instances running on your system. Simply click on the drop down and at the base, there is a Browse for more alternative:
This enables you to look for local or network instances. I have four instances running on my workstation you see them showed in this list:
We should expect one of these instances is top secret and we don’t need clients to see the instance name. That is conceivable through SQL Server Configuration Manager . Open up SSCM and explore to your SQL Server instances:
select the instance of SQL Server, right click and select Properties. Subsequent to choosing properties you will simply set Hide Instance to “Yes” and click OK or Apply. After the change is made, you have to restart the instance of SQL Server to not uncover the name of the instance.
What Is An Outer Join
With an SQL OUTER JOIN, unmatched rows in one or both tables can be returned. There are several variations of the OUTER JOIN clause, some of which we have covered already in this article. Here are the common types of OUTER JOIN clauses:
- LEFT OUTER JOIN
- RIGHT OUTER JOIN
- FULL OUTER JOIN
LEFT JOIN is a synonym for LEFT OUTER JOIN. The functionality of both is identical. This may be one of the SQL JOIN interview questions you are asked! The same can be said for RIGHT JOIN and RIGHT OUTER JOIN, and FULL JOIN and FULL OUTER JOIN. Letâs look at an example for each.
Don’t Miss: Pre Recorded Video Interview Introduction
Q26 What Are Union Minus And Intersect Commands
The UNION operator is used to combine the results of two tables while also removing duplicate entries.
The MINUS operator is used to return rows from the first query but not from the second query.
The INTERSECT operator is used to combine the results of both queries into a single row.Before running either of the above SQL statements, certain requirements must be satisfied
Within the clause, each SELECT query must have the same amount of columns.
The data types in the columns must also be comparable.
In each SELECT statement, the columns must be in the same order.
Lets move to the next question in this SQL Interview Questions.
What Exactly Is Log Shipping
The process of backing up and restoring database and transaction log files from a production SQL server to a backup SQL server is known as log shipping.
Enterprise Editions are the only ones that can send logs. Therefore, when log shipping is employed, the transaction log file from one server is updated automatically into the backup database on the other server.
You May Like: What Are Some Good Interview Questions To Ask The Employer
What Is A Database
This question can be most probably asked among various other SQL interview questions A database refers to a structured collection of data that can be stored, maintained, and accessed digitally from a local or remote computer network. A fixed design and modeling technique is used to build databases, which can be large and complex. Large databases are housed on multiple computers or cloud services, whereas smaller databases can be stored on a file system.
Get Software Engineering degrees from the Worlds top Universities. Earn Executive PG Programs, Advanced Certificate Programs, or Masters Programs to fast-track your career.
What Is The Difference Between Having And Where Clauses
The distinction between HAVING and WHERE clauses in SQL is that while the WHERE clause cannot be used with aggregates, the HAVING clause is used with the aggregated data. The WHERE clause works on the data from a row and not with the aggregated data.
Let us consider the employee table below.
| Name |
| 150,000 |
You May Like: What Are Some Interview Questions For Customer Service
What Is Lock Escalation In Sql
This is the strategy used by SQL server to avoid locking a large number of database resources. The locking on resource use lot of memory spaces. Let’s try to understand this by a use case where we have to apply locks on 30,000 records where each record size is 500 bytes for triggering DELETE operation. If we consider the memory space requirement for the above use case then we would be requiring one shared lock on database, one intent lock on a table, exclusive locks on the pages would be in the range of 1875 and around 30000 exclusive locks on rows. If we consider 96 bytes size for each lock then total memory space requirement would be 3MB for single delete operation.
To avoid such a SQL server leverage lock escalation strategy. This will prevent the need of large memory space requirement by escalating those locks to single lock instead of many locks. So in cases where we need thousands of locks on many resources, lock escalation will ensure single lock which will fulfill our objective and at the same time taking care of memory space issue. The exclusive lock on the table will ensure that we do not need page level lock to ensure data integrity. Instead of having so many locks required on many columns and pages lock, SQL Server will escalate to the exclusive lock on a table.
What Are Different Replication Agents And What’s Their Purpose
- Snapshot Agent– The Snapshot Agent is used with all types of replication for the initial copy of an article from publisher to the subscriber. It generates schema copy and bulk copy files of published tables and other objects. Later same is applied to the subscriber. Snapshot Agent runs at the Distributor.
- Log Reader Agent – Log Reader Agent is used with transactional replication. Its responsible for moving transactions marked for replication from the transaction log on the Publisher to the distribution database.
- Distribution Agent – Distribution Agent is responsible for applying Snapshot & Transaction logs at subscriber moved to the distributor by Snapshot or Log Reader agent. Distribution Agent runs on Distributor for push subscriptions or at the Subscriber for pull subscriptions.
- Merge Agent – Merge Agent is used by merge replication to move initial snapshot followed by movement and reconciliation of incremental data changes. Each merge subscription has its own Merge Agent works with both the Publisher and the Subscriber and updates both. Merge Agent runs on Distributor for push subscriptions or at the Subscriber for pull subscriptions.
You May Like: How To Prepare For Mckinsey Interview
How To Find Count Of Duplicate Rows
Answer:
Select rollno, count from Student
Group by rollno
Having count > 1
Order by count desc
4.How to find Third highest salary in Employee table using self-join?
Answer:
5.How to Show the Max marks and min marks together from student table?
Answer:
Select max from Student
Union
Select min from Student
Tip : Use the concept of union to show the max and min marks together.
6.How to display following using query?
Answer:
We cannot use dual table to display output given above. To display output use any table. I am using Student table.
SELECT lpad FROM Student WHERE ROWNUM < 4
7.How to display Date in DD-MON-YYYY table?
Answer:
Select to_date Date_Format from Employee
8.If marks column contain the comma separated values from Student table. How to calculate the count of that comma separated values?
| Student Name |
Want to display output like :
| Student Name |
Answer:
Select Student_name, regexp_count + As Marks Count from Student
Tip: In real scenarios, lot of times developer needs to calculate the number of commas in the column then regexp_count function is used.
9.How to create the Student_1 table, which is exact replica of Student table?
Answer:
Create Table Student_1 as select * from Student
10.What is Query to drop all user tables from Oracle?
Answer:
To Drop all tables user needs to write simple PLSQL block
Begin
For I In
Tabs is system table in which user get the different user defined table names.
Loop
Execute immediate
End loop
Answer:
See I Have An Environment Sunday Night Full Backup Everyday Night Diff Backup And Every 45 Min A Transactional Backup The Disaster Happened At : 30 Pm On Saturday You Suddenly Found That The Last Sunday Backup Has Been Corrupted Whats Your Recovery Plan
When you find that the last full backup is corrupted or otherwise unrestorable, making all differentials after that point useless. You then need to go back a further week to the previous full backup and restore that, plus the differential from 8 days ago, and the subsequent 8 days of transaction logs .
If youre taking daily full backups, a corrupted full backup only introduces an additional 24 hours of logs to restore.
Alternatively, a log shipped copy of the database could save your bacon .
Read Also: What Are The Most Common Interview Questions And Answers
If A Column Is Having> 50 % Null Then Which Index We Should Choose
If a column in the table is having > 50% NULL values then index selection if very selective.
Index schema is based on B-Tree structure and if the column is having more than 50% NULL values then all data will reside on one side of the tree result ZERO benefits of the index on query execution.
The SQL server is having a special index called filtered index which can be used here. You can create an index on column only on NON NULL values. NULL data will not be included in the index.
What Is Udf In Sql And How Many Different Types Exist For Udf
UDF represents user-defined represents which are designed to accept the parameter and does processing on parameter before returning a result set of processing or actions. The result could be either a scalar value or a complete result set. The UDFs are widely used in scripts, stored procedures and within others UDF as well.
There are several benefits of using UDF :
There are two types of UDF based on the output value of UDFs
- The scalar functions-The output of this function is scalar values. It may accept zero or more parameters and can return any SQL defined data type other than text, ntext, image, cursor, and timestamp.
- Table values functions- This type of function return table as resultset. It can accept zero or more parameters but always return table.
Also Check: What Are Great Interview Questions
What Are The Types Of Views In Sql
In SQL, the views are classified into four types. They are:
- Simple View: A view that is based on a single table and does not have a GROUP BY clause or other features.
- Complex View: A view that is built from several tables and includes a GROUP BY clause as well as functions.
- Inline View: A view that is built on a subquery in the FROM clause, which provides a temporary table and simplifies a complicated query.
- Materialized View: A view that saves both the definition and the details. It builds data replicas by physically preserving them.
What Are The Subsets Of Sql
SQL queries are divided into four main categories:
- Data Definition Language DDL queries are made up of SQL commands that can be used to define the structure of the database and modify it.
- CREATE Creates databases, tables, schema, etc.
- DROP: Drops tables and other database objects
- DROP COLUMN: Drops a column from any table structure
- ALTER: Alters the definition of database objects
- TRUNCATE: Removes tables, views, procedures, and other database objects
- ADD COLUMN: Adds any column to the table schema
Read Also: How To Write A Email Thank You After An Interview
Q3 What Do You Mean By Dbms What Are Its Different Types
A Database Management System is a software application that interacts with the user, applications, and the database itself to capture and analyze data. A database is a structured collection of data.
A DBMS allows a user to interact with the database. The data stored in the database can be modified, retrieved and deleted and can be of any type like strings, numbers, images, etc.
There are two types of DBMS:
- Relational Database Management System: The data is stored in relations . Example MySQL.
- Non-Relational Database Management System: There is no concept of relations, tuples and attributes. Example MongoDB
Lets move to the next question in this SQL Interview Questions.
How To Change A Table Name In Sql
This is the command to change a table name in SQL:
ALTER TABLE table_nameRENAME TO new_table_name
We will start off by giving the keywords ALTER TABLE, then we will follow it up by giving the original name of the table, after that, we will give in the keywords RENAME TO and finally, we will give the new table name.
For example, if we want to change the employee table to employee_information, this will be the command:
ALTER TABLE employeeRENAME TO employee_information
Don’t Miss: How To Prepare For Data Analyst Interview
Q26 Write A Query To Display The First And The Last Record From The Employeeinfo Table
To display the first record from the EmployeeInfo table, you can write a query as follows:
SELECT * FROM EmployeeInfo WHERE EmpID = FROM EmployeeInfo)
To display the last record from the EmployeeInfo table, you can write a query as follows:
SELECT * FROM EmployeeInfo WHERE EmpID = FROM EmployeeInfo)
Explain The Difference Between Sequence Vs Identity
An identity column in the table has auto-generate & auto increase value with each new row insert. The user cannot insert value in the identity column.
The sequence is a new feature introduced with SQL Server 2012 similar to Oracles sequence objects. A sequence object generates a sequence of unique numeric values as per the specifications mentioned. Next VALUE for a SEQUENCE object can be generated using the NEXT VALUE FOR clause.
- IDENTITY is column level property & tied to a particular table. This cannot be shared among multiple tables.
- SEQUENCE is an object defined by the user with specific details and can be shared by multiple tables. This is not tied to any particular table.
- IDENTITY property cannot be reset to its initial value but the SEQUENCE object can be reset to initial value any time.
- Maximum value cannot be defined for IDENTITY whereas this can be done for SEQUENCE object.
Read Also: What To Write In A Thank You Email After Interview
What Are The Different Types Of Sql Server Replication
- Snapshot replication Snapshot replication works on a snapshot of the published objects. Snapshot agent takes care of it and applies it to a subscriber. Snapshot replication overwrites the data at the subscriber each time a snapshot is applied. Its best suited for fairly static data or sync interval is not an issue. Subscriber does not always need to be connected.
- Transactional replication Transactional replication replicates each transaction for the article being published. For initial setup, Snapshot or backup is required to copy article data at the subscriber. After that Log Reader Agent reads it from the transaction log and writes it to the distribution database and then to the subscriber. Its the most widely used replication.
- Merge replication Merge replication is the most complex types of replication which allow changes to happen at both the publisher and subscriber. Changes happen at publisher & subscriber are merged to keep data consistency and a uniform set of data. For an initial setup like transactional replication, Snapshot or backup is required to copy article data at the subscriber. After that Merge Reader Agent reads it from the transaction log and writes it to the distribution database and then to the subscriber. The merge agent is capable of resolving conflicts that occur during data synchronization.
