SQL Server is a widely used and popular relational database management system (RDBMS) developed by Microsoft. While SQL Server offers promising career prospects for experienced ones, it's important to note that technology landscapes can evolve over time. It's always beneficial to familiarize yourself with the advanced SQL Server interview questions to make a sustainable and successful career. Read the following and prepare for your next interview.
If you're looking for SQL Server Interview Questions for Experienced or Freshers, you are in the right place. There are a lot of opportunities from many reputed companies in the world. According to research, The average salary for SQL Server ranges from approximately $69,682 pa. So, You still have the opportunity to move ahead in your career in SQL Server. MindMajix offers Advanced SQL Server Interview Questions For 2-3 Years of Experience that help you in cracking your interview & acquire a dream career as an SQL Server Developer.
We have categorized SQL Server Interview Questions - 2023 (Updated) into 4 levels they are:
1. What is the difference between COALESCE() & ISNULL()?
2. How do you generate file output from SQL?
3. What is a correlated subquery?
4. What is the OSQL utility?
5. What the difference between UNION and UNIONALL?
6. Why we use the OPEN XML clause?
7. Explain UNION, MINUS, UNION ALL, INTERSECT?
8. Write a Query to display the third max salary of an employee?
9. Write a Query to display a number of employees based on the city?
10.Write a Query to display the total salary of employees based on region?
|If you want to enrich your career and become a professional in SQL, then enroll in "SQL Server Training" - This course will help you to achieve excellence in this domain.|
The clauses of the select are processed in the following sequence
SQL Server can be lined to any server provided it has an OLE-DB provider from Microsoft to allow a link.
For E.g. Oracle has an OLE-DB provider for oracle that Microsoft provides to add it as a linked server to the SQL Server group.
YES, SQL Server drops all related objects, which exist inside a table like constraints, indexes, columns, defaults, etc. BUT dropping a table will not drop Views and Stored Procedures as they exist outside the table.
How would you determine the time zone under which a database was operating?
YES, SQL Server support this
OUTER LEFT/RIGHT JOIN with WHERE clause can act like an INNER JOIN if not used wisely or logically.
ISNULL accepts only 2 parameters. The first parameter is checked for a NULL value, if it is NULL then the second parameter is returned, otherwise, it returns the first parameter.
COALESCE accepts two or more parameters. One can apply 2 or as many parameters, but it returns only the first non NULL parameter,
While using SQL Server Management Studio or Query Analyzer, we have an option in Menu BAR.QUERTY >> RESULT TO >> Result to FILE
SET NOCOUNT OFF
with T as ( select * , row_number() over (partition by Emp_ID order by Emp_ID) as rank from employee ) delete from T where rank > 1
The pattern matching operator is LIKE and it has to use with two attributes
1. % means matches zero or more characters and
2. _ ( underscore ) means matching exactly one character
-- Statement 1 SELECT COUNT ( * ) FROM Employees -- Statement 2 SELECT SUM ( 1 ) FROM Employees
They’re the same unless table Employee table is empty, in which case the first yields a one-column, a one-row table containing zero, and the second yields a one-column, one-row table "containing a null."
Yes, We can modify views but a DML statement on a join view can modify only one base table of the view (so even if the view is created upon a join of many tables, only one table, the key preserved table can be modified through the view).
would like to avoid cursor in the OLTP database as much as possible, Cursors are mainly only used for maintenance or warehouse operations.
When a subquery is tied to the outer query. Mostly used in self joins.
YES, We can call. Dll from SQL Server.
Should be avoided if possible as Scalar functions in these places make the query slow down dramatically.
User-defined data types let you extend the base SQL Server data types by providing a descriptive name, and format to the database. Take for example, in your database, there is a column called Flight_Num which appears in many tables. In all these tables it should be varchar(8). In this case, you could create a user-defined data type called Flight_num_type of varchar(8) and use it across all your tables.
See sp_addtype, sp_droptype in books online.
This integration provides a wider range of development with the help of CLR for database servers because CLR helps developers to get flexibility for developing database applications and also provides language interoperability just like Visual C++, Visual Basic .Net and Visual C# .Net.
The CLR helps developers to get the arrays, classes and exception handling available through programming languages such as Visual C++ or Visual C# which is used in stored procedures, functions and triggers for creating database application dynamically and also provide more efficient reuse of code and faster execution of complex tasks. We particularly liked the error-checking powers of the CLR environment, which reduces run-time errors
Clustered Index:- A Clustered index is a special type of index that reorders the way records in the table are physically stored. Therefore table may have only one clustered index.
Non-NonClustered Index:- A Non-Clustered index is a special type of index in which the logical order of the index does not match the physical stored order of the rows in the disk. The leaf nodes of a non-clustered index do not consist of the data pages. instead, the leaf node contains index rows.
Here’s the basic syntax: (Also checkout SELECT in books online for advanced syntax)
SELECT select_list [INTO new_table_] FROM table_source [WHERE search_condition] [GROUP BY group_by__expression] [HAVING search_condition] [ORDER BY order__expression [ASC | DESC] ]
Joins are used in queries to explain how different tables are related. Joins also let you select data from a table depending upon data from another table.
Types of joins:
INNER JOINs, OUTER JOINs, CROSS JOINs
OUTER JOINs are further classified as
LEFT OUTER JOINS, RIGHT OUTER JOINS and FULL OUTER JOINS.
For more information see pages from books online titled: "Join Fundamentals" and "Using Joins".
OSQL is a command-line tool that is used to execute the query and display the result the same as a query analyzer but everything is in the command prompt.
OSQL is the command-line tool that executes the query and displays the result the same as a query analyzer but the query analyzer is graphical and OSQL is a command-line tool. OSQL is quite useful for batch processing or executing remote queries.
CASCADE allows deletions or updates of key values to cascade through the tables defined to have foreign key relationships that can be traced back to the table on which the modification is performed.
256, check SQL Server Limits
The MAGIC tables are automatically created and dropped, in case you use TRIGGERS. SQL Server has two magic tables named, INSERTED and DELETED
These are maintained by the SQL server for their Internal processing. When we use update insert or delete on tables these magic tables are used. These are not physical tables but are Internal tables. Whenever we use insert statement is fired the Inserted table is populated with newly inserted Row and whenever delete statement is fired the Deleted table is populated with the deleted row.
But in case of update statement is fired both Inserted and Deleted tables used for records the Original row before updating get stored in the Deleted table and the new row Updated gets store in Inserted table.
YES, we can disable a single trigger on the database by using “DISABLE TRIGGER triggerName ON <>”
we also have an option to disable all the triggers by using, “DISABLE Trigger ALL ON ALL SERVER”
We can’t create an Index on an Index... The index is stored in the user_index table. Every object that has been created on Schema is Schema Object like Table, View etc. If we want to share the particular data to various users we have to use the virtual table for the Base table. So that is a view.
Indexing is used for faster search or to retrieve data faster from the various tables. Schema containing a set of tables, basically schema means logical separation of the database. The view is crated for faster retrieval of data. It’s a customized virtual table. we can create a single view of multiple tables. Only the drawback is..view needs to be get refreshed for retrieving updated data.
Union will remove the duplicate rows from the result set while Union all doesn't.
system table contains information on constraints on all the tables created
Below are the different types of SQL Server Joins:
A livelock is one, where a request for an exclusive lock is repeatedly denied because a series of overlapping shared locks keeps interfering. SQL Server detects the situation after four denials and refuses further shared locks. A livelock also occurs when read transactions monopolize a table or page, forcing a write transaction to wait indefinitely.
When SQL Server executes a statement with nested subqueries, it always executes the innermost query first. This query passes its results to the next query and so on until it reaches the outermost query. It is the outermost query that returns a result set.
ALTER TABLE Department ADD (AGE, NUMBER);
YES, to delete a column in a table, use ALTER TABLE table_name DROP COLUMN column_name
To remove padded spaces, you use the "fm" prefix before the date element that contains the spaces. TO_CHAR(SYSDATE,’fmMonth DD, YYYY’)
You use the EXCEPT operator to return all rows from one query except where duplicate rows are found in a second query. The UNION operator returns all rows from both queries minus duplicates. The UNION ALL operator returns all rows from both queries including duplicates. The INTERSECT operator returns only those rows that exist in both queries.
The AS keyword is optional when specifying a column alias.
The clauses of the subselect are processed in the following sequence (DB2):
sp_depends system stored procedure or query the says depends on system table to return a list of objects that a user-defined function depends upon
SELECT DISTINCT so1.name, so2.name FROM sysobjects so1 INNER JOIN sysdepends sd ON so1.id = sd.id INNER JOIN sysobjects so2 ON so2.id = sd.depid WHERE so1.name = '<>'
A query first takes the lowest level lock possible with the smallest footprint (row-level). When too many rows are locked (requiring too much RAM) the lock is escalated to a range or page lock. If too many pages are locked, it may escalate to a table lock.
When we did the operation on SQL SERVER that is not committed directly to the database. All operations must be logged in to Transaction Log files after that they should be done on to the main database.CheckPoint is the point that alerts SQL Server to save all the data to the main database if no checkpoint is there then log files get full we can use the Checkpoint command to commit all data in the SQL SERVER. When we stop the SQL Server it will take a long time because Checkpoint is also fired.
|Read these latest SQL Interview Questions For 5+ Years Experienced that helps you grab high-paying jobs|
OPENXML parses the XML data in SQL Server in an efficient manner. Its primary ability is to insert XML data into the DB.
YES, we can store this sort of data using a blob datatype.
YES, we can store Videos inside SQL Server by using FILESTREAM data type, which was introduced in SQL Server 2008.
YES, while creating stored procedure we can use WITH ENCRYPTION which will convert the original text of the CREATE PROCEDURE statement to an encrypted format.
Indexed with included columns were developed in SQL Server 2005 that assists in covering queries. Indexes with Included Columns are non clustered indexes that
have the following benefits:
An execution plan is basically a road map that graphically or textually shows the data retrieval methods chosen by the SQL Server query optimizer for a stored procedure or ad-hoc query and is a very useful tool for a developer to understand the performance characteristics of a query or stored procedure since the plan is the one that SQL Server will place in its cache and use to execute the stored procedure or query.
From within 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 the query execution plan in a separate window when the query is run again.
|→ Explore SQL Server Sample Resumes Download & Edit, Get Noticed by Top Employers!|
SQL Server DATEADD() Function
SELECT DATEADD(dd, 15,getdate())
SELECT DATEADD(mm, 2, getdate())
SELECT DATEADD(dd, -15, getdate())
SQL Server DATEDIFF() Function
SELECT * DATEDIFF(yy, doj, getdate()) AS ‘Exp’ FROM employee
SELECT * DATEDIFF(yy, doj, getdate()) AS ‘Exp’ FROM employee WHERE DATEDIFF(yy, doj, getdate())>3 AND dept_name=’ECE’
SELECT * DATEDIFF(yy, dob, getdate()) AS ‘Age’ FROM employee
SELECT * DATEDIFF(yy, dob, getdate()) AS ‘Age’ FROM employee WHERE DATEDIFF(yy, dob, getdate())>18
SQL Server Multi-Row Functions
SELECT MIN (salary) FROM employee
SELECT MAX(salary) FROM employee
SELECT SUM(salary) FROM employee
SELECT AVG(salary) FROM employee
SELECT COUNT(*) FROM employee
SELECT MIN(salary) AS ‘min sal’, MAX(salary) AS ‘max sal’ FROM employee
SELECT COUNT(*) FROM employee WHERE dept_name=’ECE’
SELECT MAX(salary) FROM employee WHERE salary < (SELECT MAX(salary) FROM emp)
SELECT MAX(salary) FROM employee WHERE salary < (SELECT MAX(salary) FROM emp where salary < (SELECT MAX(salary) FROM emp))
SQL SERVER: GROUP BY Clause
SELECT city, SUM(salary) FROM employee GROUP BY city;
SELECT city, COUNT(emp_no) FROM employee GROUP BY city;
SELECT city, COUNT(emp_no) AS ‘no.of employees’ FROM employee GROUP BY city;
SELECT region, SUM(salary) AS ‘total_salary’ FROM employee GROUP BY region;
SELECT region, COUNT(gender) FROM employee GROUP BY region;
SELECT region, COUNT(gender) AS ‘no.of males’ FROM employee GROUP BY region;
SELECT dept_name, MIN(salary) AS ‘min sal’, MAX(salary) AS ‘max sal’ FROM employee GROUP BY dept_name
SELECT dept_name, SUM(salary) AS ‘total_sal’ FROM employee GROUP BY dept_name
SELECT dept_name, COUNT(gender) FROM employee GROUP BY dept_name WHERE gender=’male’
SELECT dept_name, COUNT(gender) AS ‘no.of males’ FROM employee WHERE gender=’male’ GROUP BY dept_name;
Note: We cannot apply where condition in GROUP BY CLAUSE if we want to apply use having clause.
We have to use WHERE condition before GROUP BY but cannot apply where condition after GROUP BY.
SQL SERVER: Having Clause
SELECT city, SUM(salary) AS ‘total_salary’ FROM employee GROUP BY city HAVING SUM(salary)>12000;
SELECT city, SUM(salary) AS ‘total_salary’ FROM employee GROUP BY city HAVING AVG(salary) >= 23000;
SQL SERVER: SUB QUERIES
SELECT * FROM employee WHERE Emp_No in (101, 102)
SELECT * FROM employee WHERE Emp_No in (select emp_no from emp)
SELECT Emp_No, Emp_Name, Salary FROM employee WHERE dept_no in (select dept_no from dept where dept_name = ‘ECE’)
SQL SERVER TOP Clause
SELECT TOP 1 * FROM employee
SELECT TOP 3 * FROM employee
SELECT TOP 1 * FROM employee ORDER BY emp_no descending
SQL SERVER: Ranking Functions
Student Details Table:
SELECT *, ROW_NUMBER() OVER (ORDER BYstudent_name) AS ‘Row_ID’ FROM employee
SELECT * FROM ( SELECT *, ROW_NUMBER() OVER (ORDER BY student_no) AS ‘ Row_ID’ FROM student) WHERE row_id %2=0
SELECT * FROM (SELECT *, ROW_NUMBER() OVER (ORDER BY student_no) AS Row_ID FROM student) WHERE row_id %2!=0
|SSAS||SQL Server DBA|
|Team Foundation Server||BizTalk Server Administrator|
Stay updated with our newsletter, packed with Tutorials, Interview Questions, How-to's, Tips & Tricks, Latest Trends & Updates, and more ➤ Straight to your inbox!
|SQL Server Training||Jun 03 to Jun 18|
|SQL Server Training||Jun 06 to Jun 21|
|SQL Server Training||Jun 10 to Jun 25|
|SQL Server Training||Jun 13 to Jun 28|
Arogyalokesh is a Technical Content Writer and manages content creation on various IT platforms at Mindmajix. He is dedicated to creating useful and engaging content on Salesforce, Blockchain, Docker, SQL Server, Tangle, Jira, and few other technologies. Get in touch with him on LinkedIn and Twitter.
Copyright © 2013 - 2023 MindMajix Technologies