Skip to content

Desi banjara

  • “Things turn out the best for the people who make the best of the way things turn out.”-John Wooden Motivational & Inspirational Quotes
  • C# Interview questions – How to get one/two year back from current date C# development
  • Love Quotes – Have you ever been in love? Love Quotes
  • “Always remember you are braver than you believe, stronger than you seem, and smarter than you think.” Christopher Robin Motivational & Inspirational Quotes
  • “When things are bad, we take comfort in the thought that they could always get worse. And when they are, we find hope in the thought that things are so bad they have to get better.”– Malcolm S. Forbes Motivational & Inspirational Quotes
  • Top 20 GIT Interview Questions GIT
  • You’re the cutest, most adorable one On your 2nd birthday Here are some hugs, some kisses, And lots of wishes that you have an awesome life ahead. Happy Birthday little one. Birthday Wishes
  • “Nobody can discover the world for somebody else. Only when we discover it for ourselves does it become common ground and a common bond and we cease to be alone.” – Wendell Berry Quotes

SQL Server Interview questions

No Comments on SQL Server Interview questions

SQL Server Interview questions

1- Similarity between Truncate and Delete in SQL

These both command will only delete data of the specified table, they cannot remove the whole table data structure.



2- Difference between Truncate and Delete in SQL

TRUNCATE is a DDL (data definition language) command whereas DELETE is a DML (data manipulation language) command.

We can’t execute a trigger in case of TRUNCATE whereas with DELETE command, we can execute a trigger.

TRUNCATE is faster than DELETE, because when you use DELETE to delete the data, at that time it store the whole data in rollback space from where you can get the data back after deletion. In case of TRUNCATE, it will not store data in rollback space and will directly delete it. You can’t get the deleted data back when you use TRUNCATE.

We can use any condition in WHERE clause using DELETE but you can’t do it with TRUNCATE.

If table is referenced by any foreign key constraints then TRUNCATE will not work.



3- What are cursors and when they are useful?

When it is required to perform the row by row operations which are not possible with the set-based operations then Cursor is used. When we execute any SQL operations, SQL Server opens a work area in memory which is called Cursor.

There are two of cursors

  1. Implicate Cursor
    SQL Server automatically manages cursors for all data manipulation statements. These cursors are called implicit cursors.
  2. Explicit Cursor
    When the programmer wants to perform the row by row operations for the result set containing more than one row, then he explicitly declare a cursor with a name.

They are managed by OPEN, FETCH and CLOSE.

%FOUND, %NOFOUND, %ROWCOUNT and %ISOPEN attributes are used in both types of cursors.



4- What are the advantages of using Stored Procedures?

– Stored Procedures help in reducing the network traffic and latency which in turn boosts application performance.
– They help in promoting code reuse.
– They provide better security to data.
– It is possible to encapsulate the logic using stored procedures. This allows to change stored procedure code without affecting clients.
– It is possible to reuse stored procedure execution plans, which are cached in SQL Server’s memory. This reduces server overhead.



5- What do you mean by ACID?

ACID (Atomicity Consistency Isolation Durability) is a quality sought after in a reliable database. Here’s the relevance of each quality:

  1. Atomicity is an all-or-none proposition.
  2. Consistency – it guarantees that your database is never left by a transaction in a half-finished state.
  3. Isolation – it keeps transactions separated from each other until they’re finished.
  4. Durability – it ensures that the database keeps a track of pending changes in a way that the server can recover from an abnormal termination.

6- What is SQL Injection?

  • SQL Injection is an attack in which attacker take the advantage of insecure application over internet by running the SQL command against the database and to steal information from it that too using GUI of the website.
  • This attack can happen with the applications in which SQL queries are generated in the code.
  • The attacker tries to inject their own SQL into the statement that the application will use to query the database.




7- What is difference between clustered and non clustered index?

  • A table can have only one Clustered Index at a time which is generally created on primary key and can have more than one non clustered indexes (maximum up to 999)
  • The leaf level of clustered index is actual data pages of the table. Whereas in case of non-clustered index the leaf level is a pointer to the data.
  • Non-clustered index is faster than clustered index because when we use DML statement on clustered index, performance issues may occurred since it has to update the index every time a DML statement is executed.




8- What is Scheduled job and how to create it?

If we want to execute any procedural code automatically on specific time either once or repeatedly then we can create a Scheduled job for that code.

Following are the steps to create a Scheduled Job.

1. Connect to your database of SQL server in SQL Server Management Studio.
2. On the SQL Server Agent. There you will find a Jobs folder. Right click on jobs and choose Add New.
3. A New Job window will appear. Give a related name for the job.
4. Click next on the “Steps” in the left menu. A SQL job can have multiple steps either in the form of SQL statement or a stored procedure call.
5. Click on the “Schedules” in the left menu. A SQL job can contain one or more schedules. A schedule is basically the time at which sql job will run itself. You can specify recurring schedules also.

Using scheduled job you can also create alert and notifications.



9- Differentiate between a primary key and a unique key.

UPDATE_STATISTICS command is used when a large processing of data has occurred. If any large amount of deletions, any modifications, or Bulk Copy into the tables has occurred, it has to update the indexes to take these changes into account. UPDATE_STATISTICS updates the indexes on these tables accordingly.



10- How would apply date range filter?

We can use simple conditions like >= and <=, or use between/and but the trick here is to know your exact data type.

To increase query performance you may still want to use between however you should be aware of proper format.



11- Which command using Query Analyzer will give you the version of SQL server and operating system?

SELECT SERVERPROPERTY (‘productversion’), SERVERPROPERTY (‘productlevel’) and SERVERPROPERTY (‘edition’)

12- What is a temp table in SQL?

A temp table is a temporary storage structure. It means you can use a temp table to store data temporarily so you can manipulate and change it before it reaches its destination format.

13- How will you find out 7th highest salary in a table?

SELECT * FROM

(SELECT ROW_NUMBER () OVER (ORDER BY sal_amount DESC) row_number, emp_id,

sal_amount

FROM Salary) a

WHERE row_number = 7



14- Differentiate between a primary key and a unique key.

Primary Key:

  • There can only be one primary key in a table
  • In some DBMS it cannot be NULL – e.g. MySQL adds NOT NULL
  • Primary Key is a unique key identifier of the record

Unique Key:

  • Can be more than one unique key in one table
  • Unique key can have null values
  • It can be a candidate key
  • Unique key can be null and may not be unique

By default, clustered index on the column are created by the primary key whereas non-clustered index are created by unique key.



15- What is Triggers in SQL?

Triggers in SQL is the procedural code that executed when you INSERT, DELETE or UPDATE data in the table. We can say, Triggers are special types of stored procedures that are defined to execute automatically in place of or after data modifications.

Triggers are useful when you want to perform any automatic actions such as cascading changes through related tables, enforcing column restrictions, comparing the results of data modifications and maintaining the referential integrity of data across a database.



16- Differentiate between HAVING and WHERE CLAUSE

HAVING CLAUSE

– HAVING CLAUSE is used only with the SELECT statement.
– It is generally used in a GROUP BY clause in a query.
– If GROUP BY is not used, HAVING works like a WHERE clause.

WHERE Clause

– It is applied to each row before they become a part of the GROUP BY function in a query.



17- What do you mean by an execution plan in SQL?

An execution plan in SQL can be called as a road map that graphically or textually shows the data retrieval methods which have been chosen by the SQL Server query optimizer, for a stored procedure or ad- hoc query.

Why is it used?

It is a very useful tool for a developer to understand the performance characteristics of a query or stored procedure.

How would you view it?

There is an option called “Display Estimated Execution Plan” in Query Analyser. If this option is turned on, it will display query execution plan in separate window when the query is run again.



18- Mention the difference between clustered and a non-clustered index?

  1. A clustered index is a special type of index that reorders the way records in the table are physically stored. Therefore table can have only one clustered index.
  2. 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 on disk. The leaf node of a non clustered index does not consist of the data pages. Instead, the leaf nodes contain index rows.

19- How will you find the 3rd max salary in the employment table?

Select distinct salary from employment e1 where 3= (select count (distinct salary) from employment e2 where e1.salary<=e2.salary)

20- Which TCP/IP port does SQL Server run on? How can it be changed?

SQL Server runs on port 1433. It can be changed from the Network Utility TCP/IP properties.

Interview questions, SQL Server Tags:clustered, Delete, Delete in SQL, Interview questions, non clustered index, Scheduled job, SQL, SQL Injection, SQL Server, Truncate

Post navigation

Previous Post: Some useful Microsoft word shortcut keys
Next Post: Romantic birthday wishes/messages/sms for boyfriend

Related Posts

  • ASP.Net MVC Interview Questions ASP.Net MVC
  • Interview question: What is the purpose of “is” operator in C#? C# development
  • Interview question: How does C# differ from C++? C# development
  • Interview questions – Microsoft Excel Interview questions
  • Interview question: In c#, How can we create a function which can accept varying number of arguments? C# development
  • Interview question: What are nullable types in C#? C# development

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *



Archives

  • June 2021
  • March 2021
  • January 2021
  • December 2020
  • November 2020
  • October 2020
  • September 2020
  • July 2020
  • June 2020
  • April 2020
  • December 2019
  • December 2018
  • November 2018
  • October 2018
  • September 2018
  • August 2018
  • July 2018
  • June 2018
  • May 2018
  • April 2018
  • January 2018
  • September 2017
  • August 2017
  • July 2017
  • June 2017
  • May 2017
  • April 2017
  • August 2015
  • May 2015
  • April 2014
  • November 2013

Categories

  • Acer
  • Amazon AWS Certification Exam
  • Amazon Kindle
  • Android phones
  • ASP.Net MVC
  • ASP.NET Web API
  • AWS DevOps Engineer Professional Exam
  • AZ-300: Microsoft Azure Architect Technologies Exam
  • Best Wishes Messages
  • birthday messages for boyfriend
  • Birthday messages For Girlfriend
  • Birthday Wishes
  • Birthday Wishes For Mom
  • Business
  • C# development
  • Cameras
  • Canon
  • Cloud
  • Cloud services
  • digital-cameras
  • Diwali
  • Entrepreneurship
  • eReaders
  • Family Quotes
  • Family Quotes
  • Friendship Quotes
  • Gadgets
  • Games
  • Get Well Soon Messages
  • GIT
  • Good Morning Wishes
  • Google
  • Gratitude Quote
  • Guru Nanak Jayanti
  • Halloween
  • Happiness Quote
  • Happy Diwali Wishes
  • Happy Independence Day Wishes
  • Happy New Year Wishes
  • HTC
  • HTC One
  • HTML
  • I Miss You Messages
  • Inspirational Quotes
  • Inspirational Travel Quotes
  • Interview questions
  • IT/Software development
  • Leadership Quote
  • Life lessons
  • Love Quotes
  • Love shayari
  • Messages
  • Microsoft AI-900 Certification Exam
  • Microsoft AZ-104 Certification Exam
  • Microsoft AZ-204 Certification Exam
  • Microsoft AZ-900 Certification Exam
  • Microsoft Azure
  • Microsoft Azure certifications
  • Microsoft Exam AZ-220
  • Microsoft Excel
  • Microsoft Office
  • Microsoft word
  • Mobile phones
  • Motivational & Inspirational Quotes
  • Nature Quotes
  • Nexus
  • Nikon
  • Pixels
  • PL-200: Microsoft Power Platform Functional Consultant Certification
  • PL-900: Microsoft Power Platform Fundamentals
  • postman
  • Quotes
  • Robin Sharma
  • Samsung Galaxy S5
  • Self improvement quotes
  • Self-Confidence Quote
  • SonarQube
  • Sony PlayStation 4
  • SQL
  • SQL Server
  • Success Quotes
  • Travel Quotes
  • Uncategorised
  • Uplifting Quotes
  • WCF (Windows Communication Foundation)
  • Web development
  • Wishes
  • Wishes for Newborn Baby

Meta

  • Log in
  • Entries feed
  • Comments feed
  • WordPress.org



Recent Posts

  • PL-200: Microsoft Power Platform Functional Consultant Certification – Exam Practice Questions
  • SonarQube – Static code analysis
  • Microsoft PL-900 Certification Exam Practice Questions – 2
  • Microsoft AZ-204 Certification Exam Practice Questions – 1
  • AWS DevOps Engineer Professional Exam Practice Questions – 14

Recent Comments

  • Yosianus on Error while sending json model in POST request to web API service using postman
  • Interview question: What is Jagged Array in C#? C# development
  • Hindi Romantic Love shayari Love Quotes
  • Interview question: What is boxing and Unboxing in C#? C# development
  • Interview question: Which class acts as a base class for all arrays in C#? C# development
  • Differences between struct and classes in C# : Interview question C# development
  • Sample Exam Questions 2: AZ-300: Microsoft Azure Architect Technologies AZ-300: Microsoft Azure Architect Technologies Exam
  • All things sweet, all things bright, may you have the best birthday night. Birthday Wishes
  • Hope your special day offers you all what your heart desires. Birthday Wishes

Copyright © 2022 Desi banjara.

Powered by PressBook News WordPress theme