Here, in this list of the top SQL Server interview questions, you will learn the most important interview questions to clear your SQL Server interview. The topics include how to secure SQL Server, third-party tools used in SQL Server, SQL Server encryption key management, how to upgrade SQL Server, detaching a database from an older version to a new version of SQL Server, and much more. Learn about SQL Server from Intellipaat's SQL Training and fast-track your career!
Learn for free ! Subscribe to our youtube Channel.
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:
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.
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:
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!
TELNET PORTNUMBER TELNET PAXT3DEVSQL24 1433 TELNET PAXT3DEVSQL24 1434
MSSQL Server: 1433 HTTP TCP 80 HTTPS TCP 443
sqlcmd –m –d master –S PAXT3DEVSQL11 –c –U sa –P *******
sqlcmd –A –d master –S PAXT3DEVSQL11 –c –U sa –P *******
ALTER DATABASE test_db SET EMERGENCY
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.
Do you have any more queries on this? Visit our SQL Server Community and clarify all your doubts today!
Connect using DAC via CMD or SSMS
Some of the third-party tools used in SQL Server are:
Benefits of using these third-party tools:
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.
Learn more about SQL Server from Intellipaat’s SQL Programming Tutorial for Beginners and take your career to the next level!
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).
To enable named pipe connections, we need to open Port 445. Named pipes communicate across TCP Port 445.
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.
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!
No. The only way if we want to get a backup is by using Windows backup for option resource MDF and IDF files.
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.
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:
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.
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.
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.
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.
The below command enforces an exclusive lock on the database, which makes the process faster:
DBCC CHECKDB (‘TestDB’) WITH NO_INFOMGS, TABLOCK
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
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.
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.
The READ_COMMITTED_SNAPSHOT option for the READ_COMMITTED optimistic model and the ALLOW_SNAPSHOT_ISOLATION option for the SNAPSHOT isolation level.
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:
The graphical execution plan should be read from Right to Left:
From the execution plan, we can find out the bottlenecks and give possible solutions to avoid latency
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!
Thanks a lot, Really nice Interview Questions.
Such a big effort, thanks a lot
Your email address will not be published. Required fields are marked *
Solve : * 10 + 20 =