SQL Server Interview Questions and Answers
MS SQL Server is a computer programs that is primarily used to store and retrieve information. This article describes some important questions with answers in MS SQL Server. Hope it will help you to build successful carrier.
What is MS SQL Server?
Microsoft SQL Server or MS SQL Server or SQL Server or MSSQL is the most famous Relational Database Management System or RDMS. It is developed by Microsoft Corporation. It supports ANSI SQL and runs on T-SQL.
Microsoft SQL Server or MS SQL Server or SQL Server or MSSQL is the most famous Relational Database Management System or RDMS. It is developed by Microsoft Corporation. It supports ANSI SQL and runs on T-SQL.
What is the initial version of SQL Server?
The first version of SQL Server is MS SQL Server 1.0 and released on 1989.
The first version of SQL Server is MS SQL Server 1.0 and released on 1989.
What is last stable version of SQL Server?
Last stable version is SQL Server 2014 and it is released on 2014
Last stable version is SQL Server 2014 and it is released on 2014
What are the Editions of SQL Server?
The editions of MS SQL server are given bellow:
The editions of MS SQL server are given bellow:
- Enterprise
- Standard
- Workgroup
- Express
What is the default size of the SQL Database?
After a fresh installation of SQL Server the default size of mdf file is 3MB and ldf file is 1MB
After a fresh installation of SQL Server the default size of mdf file is 3MB and ldf file is 1MB
What is SQL Server default TCP/IP port?
Generally SQL Server runs on port 1433. It is its default port. But we can be change it from the Network Utility TCP/IP properties.
Generally SQL Server runs on port 1433. It is its default port. But we can be change it from the Network Utility TCP/IP properties.
What are the SQL Server system databases?
MS SQL server system database are given bellow:
MS SQL server system database are given bellow:
- Master
The master database contains all the system information for an SQL Server. Such as login accounts, configuration settings, SQL server initialization information, remote server information, ongoing processes, system error messages, tapes and disks available on the system, active locks. It also stores the location of all the other databases. Without it, the SQL server can’t start, the other databases cannot be found. - Model
The model database is used as the template for all new databases. If we creates some database objects (table, views, SP, Functions, etc) on the model and create any new database then every objects of the model will be available on that newly created database. - Msdb
The msdb database is used by the SQL Server Agent for scheduling alerts and jobs. It contains operational records such as database backup and restores history. If anyone doesn’t want to work with jobs, alerts, log shipping, etc, he/she can easily ignore it. - Tempdb
The tempdb holds the intermediate results of query processing and sorting. It is dropped automatically when the SQL server is restarted. - Distribution
The distribution database is used for replication purpose. It is stored as a system database on the Distributor server. It does not store any user table. It only stores the replication history information, transactions, snapshot jobs, synchronization status. - Resource
The resource database is a hidden system database. It was introduced with SQL server 2005.
It holds the system objects. Normally It isn’t possible to see the resource database. But we can see the data file from the location C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Binn.
What are the authentication modes in SQL Server? How can it be changed?
Windows Mode and Mixed Mode – SQL and Windows.
To change authentication mode open SQL Server Management Studio. Write click the server and select properties. Click Security tab and changed Server authentication according to your requirements.
Windows Mode and Mixed Mode – SQL and Windows.
To change authentication mode open SQL Server Management Studio. Write click the server and select properties. Click Security tab and changed Server authentication according to your requirements.
What is a query execution plan?
The query execution plan is a useful tools offered by SQL Server. It describes the steps and order of a SQL query during executions. After that a DBA can decide which part of the query is slow. SQL Server can create execution plans in two ways:
Actual Execution Plan – (CTRL + M) – It is created after execution of the query and contains the steps that were performed
Estimated Execution Plan – (CTRL + L) – It is created without executing the query and contains an approximate execution plan
The query execution plan is a useful tools offered by SQL Server. It describes the steps and order of a SQL query during executions. After that a DBA can decide which part of the query is slow. SQL Server can create execution plans in two ways:
Actual Execution Plan – (CTRL + M) – It is created after execution of the query and contains the steps that were performed
Estimated Execution Plan – (CTRL + L) – It is created without executing the query and contains an approximate execution plan
What is SQL Profiler?
SQL Profiler is a graphical tool that is available with SQL Server that allows system administrators to monitor events in an instance of Microsoft SQL Server. It can perform various significant tasks such as tracing what is running under the SQL Server Engine’s, and finding out how queries are resolved internally and what scripts are running to accomplish any SQL command. We can start SQL profiler by using the following two methods:
Start >> All Programs >> Microsoft SQL Server 2008 >> Performance Tools >> SQL Server Profiler
SQL Server Management Studio >> Go to Tools >> SQL Server Profiler
SQL Profiler is a graphical tool that is available with SQL Server that allows system administrators to monitor events in an instance of Microsoft SQL Server. It can perform various significant tasks such as tracing what is running under the SQL Server Engine’s, and finding out how queries are resolved internally and what scripts are running to accomplish any SQL command. We can start SQL profiler by using the following two methods:
Start >> All Programs >> Microsoft SQL Server 2008 >> Performance Tools >> SQL Server Profiler
SQL Server Management Studio >> Go to Tools >> SQL Server Profiler
What is SQL Server Agent?
The SQL Server Agent is one kind of service that helps us to configure scheduled tasks and system alerts. It runs continuously in the background as a Windows Service.
The SQL Server Agent is one kind of service that helps us to configure scheduled tasks and system alerts. It runs continuously in the background as a Windows Service.
What is Log Shipping?
Log shipping is the automated backup system of database and transaction log files, and restoring them onto a standby server. Only Enterprise Editions supports log shipping. In log shipping the transactional log file from one server is automatically updated into the backup database on the other server. If one server is down, the other server will have the same db and can be used this as the Disaster Recovery plan. The key feature of log shipping is that it will automatically backup transaction logs throughout the day and automatically restore them on the standby server at defined interval.
Log shipping is the automated backup system of database and transaction log files, and restoring them onto a standby server. Only Enterprise Editions supports log shipping. In log shipping the transactional log file from one server is automatically updated into the backup database on the other server. If one server is down, the other server will have the same db and can be used this as the Disaster Recovery plan. The key feature of log shipping is that it will automatically backup transaction logs throughout the day and automatically restore them on the standby server at defined interval.
What is the WITH CHECK OPTION clause for a view?
In a view the WITH CHECK OPTION prevents data modifications that do not confirm to the WHERE clause of the view. It allows data to be updated via the view, only if it belongs in the view.
In a view the WITH CHECK OPTION prevents data modifications that do not confirm to the WHERE clause of the view. It allows data to be updated via the view, only if it belongs in the view.
What are DBCC commands?
The Database Consistency Checker (DBCC) provides a set of commands to maintain databases (validation, and status checks). Example is given bellow:
DBCC CHECKALLOC
It check disk allocation consistency.
DBCC OPENTRAN
It display information about recent transactions.
DBCC HELP
It display help for DBCC commands.
The Database Consistency Checker (DBCC) provides a set of commands to maintain databases (validation, and status checks). Example is given bellow:
DBCC CHECKALLOC
It check disk allocation consistency.
DBCC OPENTRAN
It display information about recent transactions.
DBCC HELP
It display help for DBCC commands.
What is BCP? When does it used?
BulkCopy or BCP is a tools used to copy huge amount of data from tables and views. BCP does not copy the structures same as source to destination. In SQL BULK INSERT command helps us to import a data file into a database table or view in a user-specified format.
BulkCopy or BCP is a tools used to copy huge amount of data from tables and views. BCP does not copy the structures same as source to destination. In SQL BULK INSERT command helps us to import a data file into a database table or view in a user-specified format.
What is a Database Lock?
Database lock tells a transaction, if the data item is currently being used by other transactions. It is two types:
Database lock tells a transaction, if the data item is currently being used by other transactions. It is two types:
- Shared Lock
It allow only read operation - Exclusive Lock
It don’t allow read or write operation
What does the NOLOCK QUERY hit do?
When we use the NOLOCK query hint it inform the storage engine that we want to access the data no matter if the data is locked (like using transaction) by another process or not. It makes the select statement faster. But newly data may miss. Microsoft advises to use READUNCOMMITTED instead of NOLOCK. Sample example is given bellow:
When we use the NOLOCK query hint it inform the storage engine that we want to access the data no matter if the data is locked (like using transaction) by another process or not. It makes the select statement faster. But newly data may miss. Microsoft advises to use READUNCOMMITTED instead of NOLOCK. Sample example is given bellow:
SELECT * FROM Students WITH (NOLOCK)
What are DMVs?
Dynamic management views (DMVs) returns the health information of a server instance. If is introduced in SQL Server 2005. Two examples are given bellow:
Dynamic management views (DMVs) returns the health information of a server instance. If is introduced in SQL Server 2005. Two examples are given bellow:
SELECT * FROM sys.dm_os_wait_stats; SELECT * FROM sys.dm_exec_sessions;
What is a CTE?
A common table expression or CTE is a temporary result set that can be used within other statements (SELECT, INSERT, UPDATE, and DELETE). It is not stored as an object and lasts only for the duration of the query. It is defined using the WITH statement. A sample example is given bellow:
A common table expression or CTE is a temporary result set that can be used within other statements (SELECT, INSERT, UPDATE, and DELETE). It is not stored as an object and lasts only for the duration of the query. It is defined using the WITH statement. A sample example is given bellow:
WITH myCTE as
(
SELECT
CollectionID
,ROW_NUMBER() OVER(ORDER BY CollectionID) RowID
FROM TestDB.dbo.Collections
)
SELECT * from myCTE
WHERE RowID between 5 and 10
When to use Common table expression (CTE)?
A CTE can be used for the following purpose:
A CTE can be used for the following purpose:
- Recursive query
- Instead of view when we don’t want to store the definition of views
- Reference the same table multiple times in the same statement
What is data warehouse?
The data warehouse or data warehousing is a centralized storage of large amount of data and used for reporting and data analysis purpose. Traditional database systems are operational or transactional oriented. But data warehouse are subject/business oriented. Warehouse data must be secured, reliable, easy to retrieve and easy to manage.
The data warehouse or data warehousing is a centralized storage of large amount of data and used for reporting and data analysis purpose. Traditional database systems are operational or transactional oriented. But data warehouse are subject/business oriented. Warehouse data must be secured, reliable, easy to retrieve and easy to manage.
No comments