SQL Server Interview Questions and answers
SQL Server Interview Questions and answers for beginners and experts. List of frequently asked SQL Server Interview Questions with answers by Besant Technologies. We hope these SQL Server Interview Questions and answers are useful and will help you to get the best job in the networking industry. This SQL Server Interview Questions and answers are prepared by SQL Server Professionals based on MNC Companies expectation. Stay tuned we will update New SQL Server Interview questions with Answers Frequently.
Best SQL Server Interview Questions and answers
Besant Technologies supports the students by providing SQL Server Interview Questions and answers for the job placements and job purposes. SQL Server is the leading important course in the present situation because more job openings and the high salary pay for this SQL Server and more related jobs. We provide the SQL Server online training also for all students around the world through the Gangboard medium. These are top SQL Server Interview Questions and answers, prepared by our institute experienced trainers. If you want to learn Practical SQL Server Training then please Join our SQL Server Training in Chennai .
SQL Server Interview Questions and answers for the job placements
Here is the list of most frequently asked SQL Server Interview Questions and answers in technical interviews. These questions and answers are suitable for both freshers and experienced professionals at any level. The questions are for intermediate to somewhat advanced SQL Server professionals, but even if you are just a beginner or fresher you should be able to understand the answers and explanations here we give.
Full Backup, Differential Backup, Log Backup, and Filegroup Backup.
The only difference between full and full-copy is that full-copy does not break the differential chain. A copy-only backup doesn’t affect that differential base. Neither Full or Full-Copy only backup breaks the log chain as neither of them truncates the log file.
SIMPLE, FULL & BULK LOGGED
Atomicity, Consistency, Isolation, and Durability
READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SNAPSHOT and SERIALIZABLE
It updates the distribution of key values for an index, Table or an indexed view. Statistics can be for columns or indexes.
Trace flags :1204 & 1222
“Deadlock graph”, “Lock:Deadlock” and “Lock:Deadlock Chain” events
In case of deadlock, SQL Server will pick one task/session, whichever will be least expensive to rollback and kill it. This session/task is termed as a deadlock victim.
Master, Model, TempDB & MSDB.
Its s read only database and has all system tables and views. These objects appear in the sys schema of each database.
It’s a template database, changes made to Model database are applied to all databases created afterward.
Sys.objects , sys.allocation_units , sys.columns etc
Sysadmin, server admin , setupadmin
db_owner, db_securityadmin and db_accessadmin
Example: GRANT Execute on TestTable to TestUser1
Always Encrypted, Dynamic Data Masking, Stretch database, Polybase etc
No, if the primary database is not online, Secondary database should be brought online manually.
Log shipping is at database level while replication replicates data at the individual table level.
high-safety mode and high-performance mode
No, it is an optional instance. It is needed only if automatic failover is required.
Yes, the database needs to be in FULL recovery mode.
Active – Active and Active – Passive configuration type
- SQL Server Database Emgine
- SQL Server Agent
- SQL Server analysis services
Generally, when the fragmentation level is more than 30% , it is advisable to rebuild indexes and when the fragmentation is less than 30% we can re-organize indexes.
- DBCC CHECKDB
- DBCC Show_statistics
- DBCC Opentran
- DBCC Inputbuffer
Q33) Whats I ste default port number for SQL Server instance, if it has not been chnaged during installation
- Records which have been marked for deletion (logically deleted) but have not been physically deleted are termed as Ghost records. This feature was introduced to enhanceperformance.
- SQL Server has a background thread that checks for Ghost records on a periodic basis and deletes it .
- It should have atleast two files – Data file and Log file.
Yes, Indexes can be moved to a separate data file. This will help from performance point of view as well.
- Logins are at instance level while Users are at the database level. Instance level privileges and permissions are granted to Logins and they are also known as ‘Server principals’.
- Users are at the database level and are granted database level permissions. They are also known as
- ‘database principals’. Logins are mapped to users at the database level.
- A database user (based on a login) which is not mapped to a valid login is termed as an Orphan user.
- This can occur when the login is deleted or the database is moved from one server to the other.
Q39) What is the minimum level of permission required for a login to be able to change the password of another login?
A login can change its own password, but it needs at least “Alter Any Login” permission to be able to change the password for other logins, except for logins with “sysadmin” role.
Whenever SQL Server starts, it needs a minimum of below listed three startup
- Master database data file location (-d parameter)
- Errorlog file location (-e parameter)
- Master database transaction log file location (-l parameter)
PAGEIOLATCH_SH, CXPACKET, SOS_SCHEDULER_YIELD, ASYNC_NETWORK_IO etc
This can be done using Database Mail feature
Run the following query: SELECT * FROM sys.configurations WHERE name = ‘Database Mail XPs’GO
Enterprise, Standard, Express and Developer
Q45) Transparent data encryption (TDE) is a new feature in SQL Server that is available with which all editions?
We cannot use production data in Developer edition.
Always Encrypted technology, row-level security, dynamic data masking, transparent data encryption (TDE) etc
SQL Server 2017
Backup, Copy , Restore jobs
Both MySQL Server and MS SQL Server are used to maintain big comoanies database system but there are differences between them.MySQL is an open source Real time Database Management System and MS SQL Server is the non-free licensed computer software. MySQL is used for database management and MS SQL server is only used to manage data.
Model Database maintain information related with system level for SQL server like, login details, linked servers, end points, and also other details of configuration setting. Model database is also a store point for SQL Server for storing other database information and their file location.
SQL profiler is a utility which you can use for looking the traffic in SQL Server and control the traffic on particular instance.
3 types of replications.
- Snap: It captures snap short of instance
- Merge: Merge uses the snap replication and perform central repository serve.
- Dealing: Dealing works both with snap and merge .
SQL Agent give a scope to the database administrator to execute the process automatically, managing other system based works and a backup process.
One of the important data recovery tool in SQL server is Checkpoint and it maintains the change and modification in cache of database page.
Database Console Commands in short DBCC is available in 4 types.
You can set the fill factor on your indexes, at the time of re-indexing SQL server will automatically get the update about how much free space to leave in the index pages.
It is used to give power to the computation of question optimisation statistics table or indexed read.Your data changes and sometimes auto update statics is not enough; in that case a query is beneficiary for updating those kinds of statistics to more frequently.
Correlated sub-query is a sub query which uses values from outer side query and as it is related with the column of outer side query you must re-execute it for each row of the result.
Windows Authentication and mixed-mode. You can use both Windows Authentication and SQL Server Authentication in the mixed-mode to log into your SQL server.
It is very common Questions in most of the interview. By asking this question the selection board wants to know about your working experience with multiple versions of SQL Server and make an idea about your capability to choose the right person for the job.
2000,2005, 2008 and SQL server7 are the published version till date. The work experience depends on every individual candidate.
Two types of Indexes, Clustered Indexes and Non-Clustered Indexes.
Clustered index is used in table formation, to arranged data pages according to its key. In Non-Clustered index, the leaf level pages do not contain data pages instead it contains pointers to the data pages..
Extra added features in SQL Server 2005 are
- Database Snapshots
- Dynamic Management Views
- Resource Database
- SQL Server Integration Services
- Database Partitioning
- System Catalogue Views
- Support for Analysis Services on a Failover Cluster.
The solutions are Log Shipping, Database Mirroring, Failover cluster and Replication high-available in SQL Server.
Select the unsuccessful job, right click and select view history from the drop-down menu. The implementation history of the job is displayed and you will select the implementation time. There you will get all the details about the error and how much time to fix it .
Q69) What number files will an information contains in the SQL Server? How many types of data forms exist on SQL Server? How many of these files can exist for a single database?
- The contained information will be a most of 32 to 767 files.
- 2 types of data files, Primary data file and Secondary data file(s)
- There is only one Primary file and multiple numbers of secondary information files which is smaller amount than 32 to 767 files.
Data Control Language.
RANT, DENY and REVOKE.
Fill issue is a setting used to apply Index in SQL Server. The fill issue determines price of the proportion of written information in a created/rebuilt index page.
It is zero by default.
You can get it from Management Studio, right-click the SQL Server and choose properties, change the value as you wanted there and click OK to save the changes.
In the Installation procedure of SQL Server , SQL server installed adatabase called System Database .There are 4 types of system database master, MSDB, model and TempDB. Most of the SQL server systems are maintained by system database like logins, databases, linked servers , jobs , schedules , reports , report data sources etc. If the system database corrupts the whole SQL server will stop.User database is porously created to store information for our necessity. If user database corrupts it will not effect on the SQL server.
3 types of recovery model are available in the market , FULL, Simple and Bulk-Logged.
Recovery model maintain the total transaction log in SQL server. So if you have to protect data loss issue the suggestion is to use Full Recovery model, because it has the complete protection capability.
Replication is a technology of coping and distribution of data from one server to another.The benefits of Replication is improvement in data availability, user can easily browse or share the same data without any interruption.
3 types of Replication are available Snapshot Replication, Transactional Replication and Merge Replication.
- Snapshot Replication is used to take data snapshots in one server to relocate it to another server. It is the simplest replication process and used for small table works.
- Transactional Replication is used to copy data from main source to scatter between the users. Transactional Replication is used for duplicating sales order like tables.
- Merge Replication is the collector of data from multiple sources. It works with both Snapshot and Transaction Replication. Merge Replication is useful for the remote areas where network is unavailable for most of the time.
3 types of elements are available in Replication Publisher, Distributor, and Subscriber. The publisher is responsible for data publication. The distributor is responsible for catering the info objects to various sources. The subscriber is the element where the published subscription is copied.
Replication can be any of the following:
- Publisher, Distributor, and Subscriber are on the same SQL Instance.
- Publisher and Distributor are on same SQL Instance and Subscriber on a separate Instance.
- Publisher, Distributor, and Subscriber all are on individual SQL Instances.
Q82) If you were given access to an SQL server, how would you discover if the SQL instance is a named instance or a default instance?
First I would go to SQL Server Configuration Manager. Then click the left panel of the tool, I would select SQL Server Services, the right side panel displays all of the SQL Server Services/components that are installed on that machine. If the Service is displayed as (MSSQLSERVER), then it indicates it is a default instance, else there will be the Instance name displayed.
Q83) What are the various authentication methods on SQL Server and how can you change authentication mode?
2 Authentication modes; Windows Authentication and SQL Server. Windows Authentication mode also referred to as Mixed Mode.
SQL Server 2005, installing failover cluster is a single step process whereas on SQL Server 2008 or above it is a multi-step process. In SQL Server 2005, the Installation process itself installs all of the nodes (be it 2 nodes or 3 nodes) but in 2008 or 2008 R2 this process is modified, you have to install separately on every nodes, 2 times for two nodes and 3 times for three nodes.
The design of Active –Passive cluster is a failover cluster in which only one node is active and other node, called as the Passive node is always online but disabled. But if the Active Node fails the Passive Node takes its place on the SQL Server Services and becomes the Active Node, the failed Active Node become Passive Node.
Design of an Active-Active cluster is a failover cluster in which both the cluster nodes are active at any given point in time. One Instance of SQL Server is running on every nodes of the cluster and if one amongst the nodes failed, both the Instances run on the only one node till the failed node is repaired. The instance will then back to its selected node.
Virtual network name for the SQL Server, Virtual IP address for SQL Server, IP addresses for the Public Network and Private Network(also referred as Heartbeat) for each node in the failover cluster, shared drives for SQL Server Data and Log files, Quorum Disk, and MSDTC Disk.
Transparent Data Encryption is a mechanism newly added in SQL Server 2008 for coding and encryption to defend unauthorized access of information files of SQL Server. Also, TDE will protect the backup information of the instance.
Q88) At the time of transmission data across network does Transparent Data Encryption provide encryption?
No, Transparent Data Encryption doesn’t provide encryption at the time of data transmission.
Q89) What is the difference between the High-Safety and Superior operating modes of Database Mirroring
- High-Safety Mode ensure that the Principal and reflected info area unit synchronisation, transactions are committed at the same time on both servers to secure stable condition, but there is/might be a time lag.
- High-Performance Mode is to ensure that the Principal database run faster, without depending on the Mirrored database to commit the transactions. There is very little chance of loss of knowledge and the material behind the collectively reflective information can be insulated
Drop info deletes the info with its source file, without a backup you can’t recover it. Offline database is not a deleted database only unavailable for user unless you back to online.
The better option is to change the Autogrowth database settings into Megabyte than Percentage.
Row compression and Page Compressionn.
2 types, In-place upgrade and Side-by-Side Upgrade.
Q94) On a Windows Server 2003 Active – Passive failover cluster, how do you find the node which is active?
Use Cluster Administrator to connect to the cluster and select the SQL Server cluster and select the SQL Server group, at the right side of the console, the “Owner” column indicates the proper node on which the SQL Server group is currently active.
From begin -> Run and sort CluAdmin (case insensitive) and therefore the Cluster Administrator console is displayed or you can even head to begin -> All Programs -> body Tools -> Cluster Administrator.
Go to Cluster Administrator then right click on the SQL Server Group and from the popup menu item choose to Take Offline.
- T-SQL; Create Database command.
- Using Management Studio
- Restoring a database backup
- Copy Database wizard
Two operational modes High-Safety Mode and Superior Mode.
Q99) Once the transcript is set, is it possible to own a publisher as a distributor or subscriber, as a twenty-four bit SQL server and thirty-bit bit SQL server?
Yes, it’s possible to own numerous configurations in a very Replication atmosphere.