Skip to content

Desi banjara

learn and grow together

  • Azure
    • Azure Compute
      • Azure Logic Apps
      • Azure Mobile Apps
      • Azure App Service
      • Azure Serverless Computing
        • Azure Functions
    • Azure Networking services
      • Azure Networking – VNET
    • Azure Database Services
      • Azure SQL
      • Azure Data Factory
      • Azure Databricks
    • Azure Analytics Services
    • Azure Security
      • Azure Identity and Access Management
      • Azure Active Directory
      • Azure Defender
      • Azure security tools for logging and monitoring
        • Azure Sentinel
        • Azure Sentinel – Data connectors
    • Azure Cognitive Services
    • Azure Data and Storage
    • Azure Devops
    • Azure landing zone
    • Azure IaaS
    • Azure Internet of Things (IoT)
      • Azure Machine Learning
      • Azure AI and ML services
    • Azure Migration
  • Agile Software development
    • Atlassian Jira
  • Amazon Web Services (AWS)
    • Amazon EC2
    • Amazon ECS
    • AWS Lambda
  • Google
    • Google Cloud Platform (GCP)
    • gmail api
    • Google Ads
    • Google AdSense
    • Google Analytics
    • Google Docs
    • Google Drive
    • Google Maps
    • Google search console
  • Software architecture
    • Service-oriented architecture (SOA)
    • Domain-Driven Design (DDD)
    • Microservices
    • Event-Driven Architecture
    • Command Query Responsibility Segregation (CQRS) Pattern
    • Layered Pattern
    • Model-View-Controller (MVC) Pattern
    • Hexagonal Architecture Pattern
    • Peer-to-Peer (P2P) pattern
    • Pipeline Pattern
  • Enterprise application architecture
  • IT/Software development
    • API development
    • ASP.Net MVC
    • ASP.NET Web API
    • C# development
    • RESTful APIs
  • Cybersecurity
    • Cross Site Scripting (XSS)
    • Reflected XSS
    • DOM-based XSS
    • Stored XSS attacks
    • Ransomware
    • cyber breaches
    • Static Application Security Testing (SAST)
  • Interview questions
    • Microsoft Azure Interview Questions
    • Amazon Web Services (AWS) Interview Questions
    • Agile Software development interview questions
    • C# interview questions with answers
    • Google analytics interview questions with answers
    • Javascript interview questions with answers
    • Python interview questions with answers
    • WordPress developer interview questions and answers
  • Cloud
    • Cloud computing
    • Infrastructure as a Service (IaaS)
    • Platform as a Service (PaaS)
    • Software as a Service (SaaS)
    • Microsoft Azure
      • Microsoft Azure Log Analytics
    • Zero Trust strategy
  • Toggle search form
  • Command Query Responsibility Segregation (CQRS) Pattern Command Query Responsibility Segregation (CQRS) Pattern
  • Microsoft AI-900 Certification Exam Practice Questions – 6 Microsoft AI-900 Certification Exam
  • Interview question: What is boxing and Unboxing in C#? C# development
  • Top Microsoft Azure Interview Questions Azure
  • What are different types of Azure blobs and difference between them? Azure
  • Layered Pattern Layered Pattern
  • Deploy ASP.NET Core apps to Azure App Service ASP.NET Core
  • Cloud computing – Types of Cloud Models Uncategorized

SQL Server Interview questions

Posted on May 11, 2017 By DesiBanjara 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: Microsoft SQL Server – 50+ useful Sql query

Related Posts

  • Interview question: Can we assign null value into value type variable? C# development
  • Differences between struct and classes in C# : Interview question C# development
  • Interview question: What is C#? C# development
  • Interview question: How does C# differ from C++? C# development
  • Interview question: Which class acts as a base class for all arrays in C#? C# development
  • C# interview Questions – What is struct in C#? C# development

Leave a Reply Cancel reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.



Categories

  • Agile Software development
  • Amazon AWS Certification Exam
  • Amazon EC2
  • Amazon ECS
  • Amazon Web Services (AWS)
  • Apache Kafka
  • API development
  • Apple Mac
  • ASP.NET Core
  • ASP.Net MVC
  • ASP.NET Web API
  • Atlassian Jira
  • AWS DevOps Engineer Professional Exam
  • AWS Lambda
  • AZ-300: Microsoft Azure Architect Technologies Exam
  • Azure
  • Azure Active Directory
  • Azure AI and ML services
  • Azure Analytics Services
  • Azure App Service
  • Azure Cognitive Services
  • Azure Compute
  • Azure Container Instances (ACI)
  • Azure Core Services
  • Azure Data and Storage
  • Azure Data Factory
  • Azure Data Lake Storage
  • Azure Database Services
  • Azure Databricks
  • Azure Defender
  • Azure Devops
  • Azure Functions
  • Azure IaaS
  • Azure Identity and Access Management
  • Azure Internet of Things (IoT)
  • Azure Kubernetes Service (AKS)
  • Azure landing zone
  • Azure Logic Apps
  • Azure Machine Learning
  • Azure Machine Learning
  • Azure Migration
  • Azure Mobile Apps
  • Azure Networking – VNET
  • Azure Networking services
  • Azure Pricing and Support
  • Azure Security
  • Azure Security
  • Azure security tools for logging and monitoring
  • Azure Security, Privacy, Compliance, and Trust
  • Azure Sentinel
  • Azure Sentinel – Data connectors
  • Azure Serverless Computing
  • Azure SQL
  • Azure SQL Database
  • Azure Storage
  • Azure Storage services
  • Azure Stream Analytics
  • Azure Synapse Analytics
  • Azure Virtual Machine
  • Azure VNET
  • Business
  • C# development
  • C# interview questions with answers
  • ChatGPT
  • CI/CD pipeline
  • CISSP certification
  • Cloud
  • Cloud computing
  • Cloud Computing Concepts
  • Cloud services
  • COBIT
  • Command Query Responsibility Segregation (CQRS) Pattern
  • Content management system
  • Continuous Integration
  • conversational AI
  • Cross Site Scripting (XSS)
  • cyber breaches
  • Cybersecurity
  • Data Analysis
  • Database
  • DevOps
  • DevSecOps
  • DOM-based XSS
  • Domain-Driven Design (DDD)
  • Dynamic Application Security Testing (DAST)
  • Enterprise application architecture
  • Event-Driven Architecture
  • GIT
  • git
  • gmail api
  • Google
  • Google Ads
  • Google AdSense
  • Google Analytics
  • Google analytics interview questions with answers
  • Google Cloud Platform (GCP)
  • Google Docs
  • Google Drive
  • Google Maps
  • Google search console
  • Hexagonal Architecture Pattern
  • HTML
  • Information security
  • Infrastructure as a Service (IaaS)
  • Internet of Things (IoT)
  • Interview questions
  • IT governance
  • IT Infrastructure networking
  • IT/Software development
  • Javascript interview questions with answers
  • Layered Pattern
  • Leadership Quote
  • Life lessons
  • Low-code development platform
  • Microservices
  • Microservices
  • Microsoft
  • Microsoft 365 Defender
  • 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 Azure Log Analytics
  • Microsoft Cloud Adoption Framework
  • Microsoft Exam AZ-220
  • Microsoft Exam AZ-400
  • Microsoft Excel
  • Microsoft Office
  • Microsoft Teams
  • Microsoft word
  • Model-View-Controller (MVC) Pattern
  • Monitoring and analytics
  • NoSQL
  • OpenAI
  • OutSystems
  • Peer-to-Peer (P2P) pattern
  • Pipeline Pattern
  • PL-100: Microsoft Power Platform App Maker
  • PL-200: Microsoft Power Platform Functional Consultant Certification
  • PL-900: Microsoft Power Platform Fundamentals
  • Platform as a Service (PaaS)
  • postman
  • Postman
  • Project management
  • Python interview questions with answers
  • Ransomware
  • Reflected XSS
  • RESTful APIs
  • SC-100: Microsoft Cybersecurity Architect
  • Scrum Master Certification
  • Service-oriented architecture (SOA)
  • Software architecture
  • Software as a Service (SaaS)
  • SonarQube
  • Splunk
  • SQL
  • SQL Azure Table
  • SQL Server
  • Static Application Security Testing (SAST)
  • Stored XSS attacks
  • Table Storage
  • Test Driven Development (TDD)
  • Top technology trends for 2023
  • Uncategorized
  • User Experience (UX) design
  • Version control system
  • WCF (Windows Communication Foundation)
  • Web development
  • WordPress
  • WordPress developer interview questions and answers
  • Zero Trust strategy



Recent Posts

  • Azure Container Instances (ACI)
  • Azure Kubernetes Service (AKS)
  • Azure Identity and Access Management
  • Azure Analytics Services
  • Azure Storage services

Recent Comments

    • Object Oriented Programming (OOP) in C# C# development
    • Sample Exam Questions 6: AZ-300: Microsoft Azure Architect Technologies AZ-300: Microsoft Azure Architect Technologies Exam
    • Microsoft AZ-900 Certification Exam Practice Questions – 4 Microsoft AZ-900 Certification Exam
    • !!! “सफलता के 20 मँत्र” !!! Life lessons
    • 75 Best inspiring quotes on Leadership Business
    • Interview question: What is the purpose of “as” operator in C#? C# development
    • Differences between struct and classes in C# : Interview question C# development
    • Microsoft AZ-104 Certification Exam Practice Questions – 4 Microsoft AZ-104 Certification Exam

    Copyright © 2023 Desi banjara.

    Powered by PressBook News WordPress theme