Friday, 27 December 2019

Top 20 SQL Server Interview Questions


Ques: 1. What is SQL server agent?

Ans: The SQL Server agent plays a vital role in day to day tasks of SQL server administrator (DBA). Server agent's purpose is to implement the tasks easily with the scheduler engine which allows our jobs to run at scheduled date and time.


Ques: 2. What is a Trigger?

Ans: Triggers are used to execute a batch of SQL code when insert or update or delete commands are executed against a table. Triggers are automatically triggered or executed when the data is modified. It can be executed automatically on insert, delete and update operations.


Ques: 3. What is the use of SET NOCOUNT ON/OFF statement?

Ans: By default, NOCOUNT is set to OFF and it returns number of records got affected whenever the command is getting executed. If the user doesn't want to display the number of records affected, it can be explicitly set to ON- (SET NOCOUNT ON).


Ques: 4. What is SQL injection?

Ans: SQL injection is an attack by malicious users in which malicious code can be inserted into strings that can be passed to an instance of SQL server for parsing and execution. All statements have to checked for vulnerabilities as it executes all syntactically valid queries that it receives.


Ques: 5. What will be the maximum number of index per table?

Ans: For SQL Server 2008 100 Index can be used as maximum number per table. 1 Clustered Index and 999 Non-clustered indexes per table can be used in SQL Server.
1000 Index can be used as maximum number per table. 

1 Clustered Index and 999 Non-clustered indexes per table can be used in SQL Server.


Ques: 6. What is Filtered Index?

Ans: Filtered Index is used to filter some portion of rows in a table to improve query performance, index maintenance and reduces index storage costs. When the index is created with WHERE clause, then it is called Filtered Index


Ques: 7. List the different index configurations possible for a table?

Ans: A table can have one of the following index configurations:
  • No indexes 
  • A clustered index 
  • A clustered index and many non-clustered indexes 
  • A non-clustered index 
  • Many non-clustered indexes

Ques: 8. What is sub query and its properties?

Ans: A sub-query is a query which can be nested inside a main query like Select, Update, Insert or Delete statements. This can be used when expression is allowed. Properties of sub query can be defined as
  • A sub query should not have order by clause.
  • A sub query should be placed in the right hand side of the comparison operator of the main query. 
  • A sub query should be enclosed in parenthesis because it needs to be executed first before the main query. 
  • More than one sub query can be included.

Ques: 9. What is Mirroring?

Ans: Mirroring is a high availability solution. It is designed to maintain a hot standby server which is consistent with the primary server in terms of a transaction. Transaction Log records are sent directly from the principal server to a secondary server which keeps a secondary server up to date with the principal server.


Ques: 10. What is an execution plan?

Ans: An execution plan is a graphical or textual way of showing how the SQL server breaks down a query to get the required result. It helps a user to determine why queries are taking more time to execute and based on the investigation user can update their queries for the maximum result.

In Query Analyzer is an option called “Show Execution Plan” (located on the Query drop-down menu). If this option is turned on it will display a query execution plan in a separate window when a query is run again.


Ques: 11. What is a performance monitor?

Ans: Windows performance monitor is a tool to capture metrics for the entire server. We can use this tool for capturing events of the SQL server also.
Some useful counters are – Disks, Memory, Processors, Network, etc.


Ques: 12. What is the difference between a Local and a Global temporary table?

Ans: If defined inside a compound statement a local temporary table exists only for the duration of that statement but a global temporary table exists permanently in the database but its rows disappear when the connection is closed.


Ques: 13. What is the SQL Profiler?

Ans: SQL Profiler provides a graphical representation of events in an instance of SQL Server for monitoring and investment purpose. We can capture and save the data for further analysis. We can put filters as well to captures the specific data we want.


Ques: 14. What are the properties of the Relational tables?

Ans: Relational tables have six properties:
  1. Values are atomic. 
  2. Column values are of the same kind. 
  3. Each row is unique. 
  4. The sequence of columns is insignificant. 
  5. The sequence of rows is insignificant. 
  6. Each column must have a unique name.

Ques: 15. What is View?

Ans: A view is a virtual table that contains data from one or more tables. Views restrict data access of the table by selecting only required values and make complex queries easy.
Rows updated or deleted in the view are updated or deleted in the table the view was created with. It should also be noted that as data in the original table changes, so does data in the view, as views are the way to look at part of the original table. The results of using a view are not permanently stored in the database


Ques: 16. Why is replication required on the SQL Server?

Ans: Replication is the mechanism that is used to synchronize the data among the multiple servers with the help of a replica set.
This is mainly used to increase the capacity of the reading and to provide an option to its users to select among various servers to perform the read/write operations.


Ques: 17. What part does database design have to play in the performance of a SQL Server-based application?

Ans. It plays a very major part. When building a new system, or adding to an existing system, it is crucial that the design is correct. Ensuring that the correct data is captured and is placed in the appropriate tables, that the right relationships exist between the tables and that data redundancy is eliminated is an ultimate goal when considering performance. Planning a design should be an iterative process, and constantly reviewed as an application is developed. It is rare, although it should be the point that everyone tries to achieve, when the initial design and system goals are not altered, no matter how slightly. Therefore, a designer has to be on top of this and ensure that the design of the database remains efficient.


Ques: 18. What command is used to create a database in the SQL Server and how?

Ans: CREATEDATABASE Command is used to create any database in the SQL Server. Following is the way to use this command:

CREATEDATABASE Name of the Database

Example: If the name of a database is “employee” then create command to create this database that can be written as CREATEDATABASE employee.


Ques: 19. What is an extended stored procedure? Can you instantiate a COM object 
by using T-SQL?

Ans: An extended stored procedure is a function within a DLL (written in a programming language like C, C++ using Open Data Services (ODS) API) that can be called from T-SQL, just the way we call normal stored procedures using the EXEC statement.
 
Yes, you can instantiate a COM (written in languages like VB, VC++) object from T-SQL by using sp_OACreate stored procedure. 


Ques: 20. When should SQL Server-based cursors be used, and not be used?

Ans: SQL Server cursors are perfect when you want to work one record at a time, rather than taking all the data from a table as a single bulk. However, they should be used with care as they can affect performance, especially when the volume of data increases. 

From a beginner’s viewpoint, I really do feel that cursors should be avoided every time because if they are badly written, or deal with too much data, they really will impact a system’s performance. There will be times when it is not possible to avoid cursors, and I doubt if many systems exist without them. If you do find you need to use them, try to reduce the number of records to process by using a temporary table first, and then building the cursor from this. The lower the number of records to process, the faster the cursor will finish. Always try to think “out of the envelope”.



Thursday, 26 December 2019

Top 20 Docker Interview Questions



Ques: 1. What do you understand by Docker?
 
Ans: Docker is an open-source lightweight containerization technology. It has gained widespread popularity in the cloud and application packaging world. It allows you to automate the deployment of applications in lightweight and portable containers.


Ques: 2. What is Docker image?

Ans: The Docker image help to create Docker containers. You can create the Docker image with the build command. Due to this, it creates a container that starts when it begins to run. Every docker images are stored in the Docker registry.


Ques: 3. What is Virtualization?

Ans: Virtualization is a method of logically dividing mainframes to allow multiple applications to run simultaneously.

However, this scenario changed when companies and open source communities were able to offer a method of handling privileged instructions. It allows multiple OS to run simultaneously on a single x86 based system.


Ques: 4. Can you explain the process of scaling your Docker containers?
 
Ans: The Docker containers can be scaled to any level starting from a few hundred to even thousands or millions of containers. The only condition for this is that the containers need the memory and the OS at all times, and there should not be a constraint when the Docker is getting scaled.


Ques: 5. What is CNM?

Ans: CNM stands for Container Networking Model. It is a standard or specification from Docker, Inc. that forms the basis of container networking in a Docker environment. This docker's approach provides container networking with support for multiple network drivers.


Ques: 6. Can you explain the Docker Trusted Registry?

Ans: Docker Trusted Registry is the enterprise-grade image storage toll for Docker. You should install it after your firewall so that you can securely manage the Docker images you use in your applications.


Ques: 7. How do I run multiple copies of Compose file on the same host?

Ans: Compose uses the project name which allows you to create unique identifiers for all of a project's containers and other resources. To run multiple copies of a project, set a custom project name using the -a command-line option or using COMPOSE_PROJECT_NAME environment variable.


Ques: 8. What is Hypervisor?

Ans: A hypervisor is a software that makes virtualization possible. It is also called Virtual Machine Monitor. It divides the host system and allocates the resources to each divided virtual environment. You can basically have multiple OS on a single host system. There are two types of Hypervisors:

Type 1: It’s also called Native Hypervisor or Bare metal Hypervisor. It runs directly on the underlying host system. It has direct access to your host’s system hardware and hence does not require a base server operating system.

Type 2: This kind of hypervisor makes use of the underlying host operating system. It’s also called Hosted Hypervisor.


Ques: 9. What is containerization?

Ans:In the software development process, code developed on one machine might not work perfectly fine on any other machine because of the dependencies. This problem was solved by the containerization concept. 

So basically, an application that is being developed and deployed is bundled and wrapped together with all its configuration files and dependencies. This bundle is called a container. Now when you wish to run the application on another system, the container is deployed which will give a bug-free environment as all the dependencies and libraries are wrapped together. Most famous containerization environments are Docker and Kubernetes.


Ques: 10. What is the difference between virtualization and containerization?

Ans: Once you’ve explained containerization and virtualization, the next expected question would be differences. The question could either be differences between virtualization and containerization or differences between virtual machines and containers. Either way, this is how you respond.

Containers provide an isolated environment for running the application. The entire user space is explicitly dedicated to the application. Any changes made inside the container is never reflected on the host or even other containers running on the same host. Containers are an abstraction of the application layer. Each container is a different application.

Whereas in Virtualization, hypervisors provide an entire virtual machine to the guest(including Kernal). Virtual machines are an abstraction of the hardware layer. Each VM is a physical machine.


Ques: 11. Explain Docker Architecture?

Ans: Docker Architecture consists of a Docker Engine which is a client-server application with three major components:

  1. A server which is a type of long-running program called a daemon process (the docker command). 
  2. A REST API which specifies interfaces that programs can use to talk to the daemon and instruct it what to do. 
  3. A command line interface (CLI) client (the docker command). 
  4. The CLI uses the Docker REST API to control or interact with the Docker daemon through scripting or direct CLI commands. Many other Docker applications use the underlying API and CLI.

  
Ques: 12. What is a Dockerfile?

Ans: Let’s start by giving a small explanation of Dockerfile and proceed by giving examples and commands to support your arguments.

Docker can build images automatically by reading the instructions from a file called Dockerfile. A Dockerfile is a text document that contains all the commands a user could call on the command line to assemble an image. Using docker build, users can create an automated build that executes several command-line instructions in succession.


Ques: 13. What is the life cycle of a Docker Container?
Ans: This is one of the most popular questions asked in Docker interviews. Docker containers have the following life cycle:

  • Create a container 
  • Run the container 
  • Pause the container(optional) 
  • Un-pause the container(optional) 
  • Start the container 
  • Stop the container 
  • Restart the container 
  • Kill the container 
  • Destroy the container


Ques: 14. How do you scale your Docker containers?
Ans: Docker containers can be scaled to any level, starting from a few hundreds to even thousands or millions of containers. The only condition is that the containers need the memory and the OS all the time, and there should not be a constraint on these when the Docker is getting scaled.


Ques: 15. What is the difference between the COPY and ADD commands in a Dockerfile?

Ans: Although ADD and COPY are functionally similar, generally speaking, COPY is preferred. 

That’s because it’s more transparent than ADD. COPY only supports the basic copying of local files into the container, while ADD has some features (like local-only tar extraction and remote URL support) that are not immediately obvious. Consequently, the best use for ADD is local tar file auto-extraction into the image, as in ADD rootfs.tar.xz /.


Ques: 16. What are the most common instructions in Dockerfile?

Ans: Some of the common instructions in Dockerfile are as follows:

  • FROM: We use FROM to set the base image for subsequent instructions. In every valid Dockerfile, FROM is the first instruction. 
  • LABEL: We use LABEL to organize our images as per project, module, licensing etc. We can also use LABEL to help in automation. In LABEL we specify a key value pair that can be later used for programmatically handling the Dockerfile. 
  • RUN: We use RUN command to execute any instructions in a new layer on top of the current image. With each RUN command we add something on top of the image and use it in subsequent steps in Dockerfile. 
  • CMD: We use CMD command to provide default values of an executing container. In a Dockerfile, if we include multiple CMD commands, then only the last instruction is used.


Ques: 17. Can you explain the basic Docker usage workflow?

Ans:
  1. Everything starts with the Dockerfile. The Dockerfile is the source code of the Image. 
  2. Once the Dockerfile is created, you build it to create the image of the container. The image is just the "compiled version" of the "source code" which is the Dockerfile. 
  3. Once you have the image of the container, you should redistribute it using the registry. The registry is like a git repository -- you can push and pull images. 
  4. Next, you can use the image to run containers. A running container is very similar, in many aspects, to a virtual machine (but without the hypervisor).

 
Ques: 18. What are some similarities between Virtual Machine & Docker ?

Ans: Docker is not just like a Virtual Machine. It uses the host kernel & can’t boot a different operating system. So 5 similarities between Docker & VIrtual Machine can be:

Virtual Machine
Docker
Process in on VM cant see processes in other VMs
Process in one container cant see processes in other container
Each VM has its own root filesystem

Each container has its own root file system (Not Kernal)

Each VM gets its own virtual network adapter

Docker can get virtual network adapter. It can have  separate IP and ports

VM is a running instance of physical files(.VMS and .VMDK)

Docker containers are running instances of Docker Image

Host OS can be difference from guest OS

Host OS can be different from Container OS


Ques: 19. What are some of the differences between Docker container & Virtual Machine?

Ans: The differences between docker container and virtual machines are following:

Virtual Machine

Docker
Each Virtual Machine runs its own OS.

All containers share the same kernel of the host.
Boot up time is in minutes.
Containers instantiate in seconds.
Virtual Machines snapshots are used sparingly.

Images are build incrementally on top of another like layers. Lots operating system images/snapshots.
Not effective diffs.Not version controlled.

Images can be diffed and can be version controlled.Dockerhub is like GITHUB.

Cannot run more than couple of VMs on an average laptop.
Can run many Docker containers in a laptop.

Only one VM can be started from one set of VMx and VMDK files.

Multiple Docker containers can be started from one Docker image.



Ques: 20. Can I use JSON instead of YAML for my Docker Compose file? 

Ans: Yes. YAML is a superset of json so any JSON file should be valid YAML. To use a JSON file with Compose, specify the filename to use, for example:
 
 docker-compose -f docker-compose.json up


You can use json instead of YAML for your compose file, to use json file with compose, specify the filename to use.