CTA

SQL is one of the most common skills that recruiters look for not only in IT-related jobs but also in various other job profiles, including business, finance, insurance, and other domains. This SQL Server Interview Questions blog comprises all the questions that are asked by companies in most SQL-based job interviews. Following are some of these frequently asked SQL Server interview questions in the corporate world:

Q1. Compare SQL Server with Oracle.

Q2. How can SQL Server instances be hidden?

Q3. Can we add a CPU to SQL Server?

Q4. How can we check whether the port number is connecting or not on a Server DBA?

Q5. How can you start SQL Server in different modes?

Q6. Why is SHRINKFILE/SHRINKDB/AUTOSHRINK not preferable?

Q7. What different steps will a SQL Server Developer take to secure SQL Server?

Q8. What action plan is preferred if SQL Server is not responding?

Q9. Which are the third-party tools used in SQL Server and why would you use them?

Q10. What are Hotfixes and Patches?

Mentioned below are the three sections into which this SQL Server Interview Questions blog is majorly classified:

1. Basic

2. Intermediate

3. Advanced

Check out this video on SQL Interview Questions and Answers:

Top SQL Server Interview Questions and Answers Top SQL Server Interview Questions and Answers

Learn for free ! Subscribe to our youtube Channel.

Basic Interview Questions

1. Compare SQL Server with Oracle.

Criteria SQL Server Oracle Supported platforms Windows and Linux Windows, Solaris, Linux, and Unix Language used T-SQL (Transact-SQL) PL/SQL (Procedural Language/ SQL) Usage Simpler and easy to use Complex but powerful Syntax Simple Complex

2. How can SQL Server instances be hidden?

To hide a SQL Server instance, we need to make a change in SQL Server Configuration Manager. To do this, we have to follow the below steps:

First, in SQL Server Configuration Manager, we have to expand ‘SQL Server Network Configuration’

Right-click on Protocols for <server instance> and select ‘Properties’

Once we do that, we will find a ‘HideInstance’ box in which, on the ‘Flags’ tab, we have to select ‘Yes’

After that, click on ‘OK’

Note: While hiding a named instance, we need to provide the port number in the connection string so that even if the browser is running it is possible to connect to the hidden instance.

3. Can we add a CPU to SQL Server?

Yes. we can add CPUs physically by adding new hardware, either logically by online hardware partitioning or virtually through a virtualization layer. Starting with its 2008 version, SQL Server supports CPU Hot Add. There are a few requirements to use CPU Hot Add:

Hardware that supports CPU hot add

64-bit edition of Windows Server 2008 Datacenter or Windows Server 2008 Enterprise Edition for Itanium-based system OS

SQL Server Enterprise

Once the CPU is added, we need to run RECONFIGURE, and then SQL Server recognizes the newly added CPU.

Learn about SQL Server Certification and leave behind your peers!

4. How can we check whether the port number is connecting or not on a Server DBA?

TELNET PORTNUMBER TELNET PAXT3DEVSQL24 1433 TELNET PAXT3DEVSQL24 1434

Common Ports:

MSSQL Server: 1433 HTTP TCP 80 HTTPS TCP 443

5. How can you start SQL Server in different modes?

Single-user Mode (-m):

sqlcmd –m –d master –S PAXT3DEVSQL11 –c –U sa –P *******

DAC (-A):

sqlcmd –A –d master –S PAXT3DEVSQL11 –c –U sa –P *******

Emergency:

ALTER DATABASE test_db SET EMERGENCY

6. Why is SHRINKFILE / SHRINKDB / AUTOSHRINK not preferable?

In the SHRINKFILE command, SQL Server isn’t careful about where it puts the pages that are moved from the end of the file toward the beginning of the file.

Data becomes fragmented, potentially up to 100 percent, and hence it is a performance killer for the database.

Slow operation: All pointers, being moved to/from the page/rows, have to be fixed and the SHRINKFILE operation is single-threaded, so it can be really slow (the single-threaded nature of SHRINKFILE is not going to change any time soon)

Recommendations:

First, use TRUNCATEONLY to shrink the file. It removes the inactive part of the log and then performs the shrink operation.

to shrink the file. It removes the inactive part of the log and then performs the shrink operation. Rebuild/reorganize indexes once the shrink is done so that the fragmentation level is decreased.

Learn more about SQL Server from Intellipaat’s SQL Programming Tutorial for Beginners and take your career to the next level!

7. What different steps will a SQL Server Developer take to secure SQL Server?

Preferring NT authentication

Using server, database, and application roles to control access to data

Securing physical database files using NTFS permissions

Using an unusable SA password for restricting physical access to SQL Server

Renaming the Administrator account on the SQL Server computer

Disabling the Guest account

Enabling auditing using multiprotocol encryption

Setting up SSL and firewalls

Isolating SQL Server from the webserver

Do you have any more queries on this? Visit our SQL Server Community and clarify all your doubts today!

8. What action plan is preferred if SQL Server is not responding?

Connect using DAC via CMD or SSMS:

Connect via CMD

SQLCMD -A –U myadminlogin –P mypassword -SMyServer –dmaster

Once you connect to the master database, run the diagnostic queries to find the problem

Correct the issue and restart the server

9. Which are the third-party tools used in SQL Server and why would you use them?

Some of the third-party tools used in SQL Server are:

SQL Check (Idera): For monitoring server activities and memory levels

SQL Doc 2 (Redgate): For documenting databases

SQL Backup 5 (Redgate): For automating the backup process

SQL Prompt (Redgate): For providing IntelliSense for SQL Server 2005/2000

LiteSpeed 5.0 (Quest): For backup and restore processes

Benefits of using these third-party tools:

Faster and flexible backup and recovery options

Secure backups with encryption

An enterprise view of the backup and recovery environment

Easy identification of optimal backup settings

Visibility into the transaction log and transaction log backups

A timeline view of backup history and schedules

Recovery of individual database objects

Encapsulation of a complete database restore into a single file to speed up restore time

Improving SQL Server functionality

Saving time and proving better information or notification

CTA

10. What are Hotfixes and Patches?

Hotfixes are software patches applied to live systems (the ones still running). A hotfix is a single, cumulative package that includes one or more files used to address a problem in a software product (i.e., a software bug).

In the Microsoft SQL SERVER context, hotfixes are small patches designed to address specific issues, most commonly to freshly-discovered security holes. For example, if a select query returns duplicate rows with aggregations, the result may be wrong. This can be fixed by a hotfix.

Intermediate Interview Questions

11. Suppose, there is a trigger defined for INSERT operations on a table in an OLTP system. The trigger is written to instantiate a COM object and passes the newly inserted rows to it for some custom processing. What do you think of this implementation? Can this be implemented better?

Instantiating COM objects is a time-consuming process and since it is done within a trigger, it impedes the data insertion process. The same is the case with sending emails from triggers. This rundown can be better implemented by logging all the necessary data into a separate table and having a job that checks this table and does the needful.

12. Which key provides the strongest encryption in SQL Server DBA?

AES (256-bit).

If we choose a longer key, then encryption will be better. Hence, we need to choose longer keys for more encryption. However, there is a larger performance penalty for longer keys. DES (Data Encryption Standard) is a relatively old and weaker algorithm than AES (Advanced Encryption Standard).

13. Which port do you need to open on your server firewall to enable named pipe connections?

To enable named pipe connections, we need to open Port 445. Named pipes communicate across TCP Port 445.

14. Suppose, we have 300 SSIS packages to be deployed to production. How can we make it easier? What are the easy and short ways to deploy all SSIS packages at once?

We have to store this data as XML-based files, not in the MSDB database.

With the configuration files, we can point the packages from prod to dev (and vice versa) in just a few seconds.

The packages and the config files are stored in a directory of our choice.

Resources permit to create a standalone SSIS server away from the primary SQL Server

15. How to decide between active and passive nodes?

Cluster Administrator checks the SQL Server group where we can see the current owner. The current owner is the active node and the other nodes are passive nodes.

16. What are the common trace flags used with SQL Server?

Deadlock Information: 1204, 1205, and 1222

Network Database Files: 1807

Log Record for Connections: 4013

Skip Startup Stored Procedures: 4022

Disable Locking Hints: 8755

Forces uniform extent allocations instead of mixed page allocations 1118 (SQL Server 2005 and 2008) to reduce TempDB contention

Become a master of SQL Server by enrolling in this online SQL Server Training!

17. Can we take a backup for Resource DB?

No. The only way if we want to get a backup is by using Windows backup for option resource MDF and IDF files.

18. Does SQL Server Upgrade Advisor analyze remote instances?

Upgrade Advisor can analyze remote instances of SQL Server, except for SQL Server Reporting Services. To analyze Reporting Services, Upgrade Advisor must be installed and executed on the Report Server.

19. How to upgrade SQL Server 2000 to SQL Server 2008?

To upgrade SQL Server 2000 to SQL Server 2008, the safest approach would be a side-by-side upgrade. We can do this either by using backup and restore or by detaching/attaching the database files. However, it is suggested to use the former as it is a safer approach. The steps are as follows:

Run the Upgrade Analysis tool from Microsoft. Address any issues raised there, first

Identify DTS packages. These must be migrated manually unless we buy Pragmatic Works

Rebuild the DTS packages as SSIS

Script out all SQL Agent jobs

Script out all security

Backup the systems and validate the backups (preferably by restoring them to another system)

Run the security script on the new system

Run restore on the new system

Validate the databases by running DBCC

Manually update all statistics

Run the SQL Agent script

20. Can you detach SQL Server 2005 database and attach it to a SQL Server 2008?

Yes. SQL Server 2005 databases are compatible with SQL Server 2008. Attaching a SQL Server 2005 database to SQL Server 2008 automatically upgrades the SQL Server 2005 database to the latter, and the database is then no longer usable by the SQL Server 2005 installation.

CTA

Check out this video on SQL Tutorial for Beginners:

Top SQL Server Interview Questions and Answers Top SQL Server Interview Questions and Answers

Learn for free ! Subscribe to our youtube Channel.

Advanced Interview Questions

21. Can you detach a SQL Server 2008 database and attach it to a SQL Server 2005?

No. The only way to move a SQL Server 2008 database to a SQL Server 2005 is by transferring the data using methods, such as Data Transformation Services (Import/Export), SSIS, BCP, etc., or by using a query between linked servers.

22. When you upgrade a SQL Server, the upgrade wizard seems to stop responding and fails. Why?

If applications or services have opened ODBC connections to SQL Server 2005 during the conversion process, they may not allow SQL Server to shut down completely. The conversion process will not proceed to the next step if it does not receive verification that SQL Server has been completely stopped.

23. How to rollback the upgrade?

If the legacy SQL Server instance is replaced by a new SQL Server 2008 instance, rolling back an in-place upgrade can be complex and time-consuming; whereas, in a side-by-side upgrade, the legacy instance remains available if a rollback is needed.

24. How to speed up the DBCC CHECKDB execution process?

The below command enforces an exclusive lock on the database, which makes the process faster:

DBCC CHECKDB (‘TestDB’) WITH NO_INFOMGS, TABLOCK

25. What is PHYSICAL_ONLY in DBCC CHECKDB?

The PHYSICAL_ONLY command limits checking the integrity of the physical structure of a page and record headers and can also detect torn pages, checksum failures, and common hardware failures. Using this option may cause shorter run-time for DBCC CHECKDB on large databases and is recommended for frequent use on production systems. Specifying the PHYSICAL_ONLY option causes DBCC CHECKDB to skip all checks of the FILESTREAM data.

DBCC CHECKDB ('TestDB') WITH NO_INFOMSGS, PHYSICAL_ONLY

26. How to check data purity using DBCC CHECKDB?

The below command causes DBCC CHECKDB to check the database for column values that are not valid or out of range:

DBCC CHECKDB ('TestDB') WITH NO_INFOMSGS, DATA_PURITY

With this command, DBCC CHECKDB detects columns with date and time values, which are either larger or less than the acceptable range for the DATETIME data type. It also limits checking the integrity of the physical structure of the page and record.

27. How long are locks held/retained within the REPEATABLE_READ and SERIALIZABLE isolation levels during a read operation, assuming a row-level locking?

Within the REPEATABLE_READ and SERIALIZABLE isolation levels, locks are held/retained for the duration of the transaction, unlike within the READ_COMMITTED isolation level.

28. Which two isolation levels support optimistic/row-version-based concurrency control?

One is the READ_COMMITTED isolation level. This is the only level that supports both a pessimistic (locking-based) and an optimistic (version-based) concurrency control model.

The other is the SNAPSHOT isolation level that supports only an optimistic concurrency control model.

29. What database options must be set to allow the use of an optimistic concurrency control model?

The READ_COMMITTED_SNAPSHOT option for the READ_COMMITTED optimistic model and the ALLOW_SNAPSHOT_ISOLATION option for the SNAPSHOT isolation level.

30. Explain the purpose of Intent locks.

The database engine uses intent locks to protect placing a shared (S) lock or an exclusive (X) lock on a resource (lower in the lock hierarchy). Intent locks are named thus because they are acquired before a lock at the lower level and therefore signal the intent to place the locks at a lower level. Intent locks serve two purposes:

They prevent other transactions from modifying a higher-level resource in a way that would invalidate the lock at the lower level.

They improve the efficiency of the database engine in detecting lock conflicts at a higher level of granularity.

CTA

31. How to read the graphical execution plan?

The graphical execution plan should be read from Right to Left:

Check the graphical execution plan of a stored procedure/query

Table Scan: Index is missing

Index Scan: Proper indexes are not used

BookMark Lookup: Limits the number of columns in the select list

Filter: Removes any functions from the WHERE clause; may require additional indexes

Sort: Checks if the data really needs to be sorted, if an index can be used to avoid sorting, and if sorting can be done at the client-side more efficiently?

DataFlow Arrow (high density): Sometimes, we find few rows as the outcome, but the arrow line density indicates the query/proc processing huge number of rows

Cost: Easily finds out which table/operation taking much time

From the execution plan, we can find out the bottlenecks and give possible solutions to avoid latency

32. What are the permissions required to view the execution plan?

Either a user must be mapped to sysadmin/db_owner/db_creator or the user will be granted the below permission:

GRANT SHOWPLAN TO [username]

Get a better understanding of the execution plan from the SQL Optimization Techniques blog!