If you are preparing for a .NET job interview you need to be proficient in SQL Server to an extent. There is a demand for people with .NET and C# programming along with proficiency in SQL. The following SQL Interview questions may help you ace the interview and get the programmer job you are looking for.
Table of Contents
The following set of questions and answers will help you get to the root of the problem. As a result, I’ve compiled a list of both basic and more difficult SQL Server interview questions.
Clustered index is the physical index, It reorders the records physically in the table while storing the data.
Non-Clustered index is the logical index, It does not reorders the data into table, it’s just set the logical order of the index.
When clustered indexing is used in a table, only that table will be sorted. Only one primary key clustered index can be created in a table. The data is arranged alphabetically in a clustered index, just like a dictionary.
It contains pointers to blocks, but not to direct data when working with clustered indexes.
Non-Clustered Index is similar to a book’s index in that it doesn’t have any entries grouped together. If you’re looking for a specific topic or chapter, you can use a book’s index to jump right to that page’s name and number. There’s no need to read a book from cover to cover.
In one location, the data is kept, while in another, the index is kept. A table can have multiple non-clustered indexes because the data and indexes are stored separately.
index holds the data pointer in an non-clustered index.
Union and Union All both are used in select statement, Union will remove the duplicate rows from the final result set of the select statement while UNIONALL will not remove duplicate row from the result set and will return all the row. So you can use UNION clause if you want to avoid data redundancy.
Unique key and Primary key both are constraints in SQL Server but has little bit difference, Both keys are not allow to store duplicate value in the database, but Primary key is a cluster index and Unique key is a non clustered index. Another difference is Primary key can not contains any null value while Unique key contains only 1 null value.
The ALTER TABLE statement is used to add, delete, or modify columns in an existing table.
Below is the query to change column datataype
ALTER TABLE tablename ALTER COLUMN columnname data type
Let’s say we want to change the datatype from int to string of phone number column of employee table then below is query for it.
ALTER TABLE employee ALTER COLUMN phonenumber varchar(15)
On the disk SQL server has 2 data files associated with each database
1. LDF : Which stores generally transaction log
2. MDF : Which stores actual data
CHECK Constraint used to limit the values that can be inserted in a table column
We have these many locks exist here, see below
1. Intent
2. Shared
3. Update
4. Exclusive
5. Schema
6. Bulk Update
Yes we can, There is a very simple query for that as below
INSERT INTO TABLE1 DEFAULT VALUES;
It is very simple you can use Alter table command for that, see the below query to drop primary key constraint
ALTER TABLE tablename DROP CONSTRAINT PK_tablename_columnname
in above query PK_tablename_columnname is the name of primary key constraint.
DDL commands are used to create, modify or delete the structre of the table, like creating table, alter table or drop table, so we can easily say those are the command which deal with the table structure. Those commands are CREATE, ALTER and DROP
DMS commands are used to insert, update or delete the data of in to the table, like insert into, update and delete from, so those are the commands which deal with the table data, Those commands are INSERT, UPDATE and DELETE.
Triggers and Stored procedure are generally same but the only single difference is that
SQL Stored procedures combine SQL statements to carry out a specific set of operations. It aids in the reuse of code and saves both time and code lines.
SQL Triggers are special procedures that only run when certain events like INSERT, UPDATE, and DELETE occur in the table they’re attached to.
SQL Trigger cannot be called directly, it can be called on either insert, update or delete statement of the table based on trigger we define.
When an INSERT, DELETE, or UPDATE occurs in a TABLE, a Trigger is implicitly invoked.
In a table, only trigger nesting is possible. A trigger cannot be defined or called from within another trigger, and vice versa.
Users and applications can use statements and commands like exec, EXECUTE, or stored procedure name to explicitly invoke a Procedure.
Stored procedures can be defined and called within other procedures.
You can take Use of the SET ROWCOUNT command. Here if you had 2 duplicate rows you would issue SET ROWCOUNT 1, then your DELETE command then SET ROWCOUNT 0.
We can check the version of SQL server and operating system version with the help of the following query.
SELECT SERVERPROPERTY (‘productversion’) as Version, SERVERPROPERTY (‘productlevel’) As Level, SERVERPROPERTY (‘edition’) as Edition
NOT NULL is the constraint in the SQL Server. When ever we want to force someone to enter data into particular table column then we need to set it NOT NULL column so it will not accept NULL value.
With the help of the following query we can get 3rd Max Age
Select distinct age from emp e1 where 3 = (select count(distinct age) from emp e2 where e1.age <= e2.age);
With the help of ‘INSERT INTO SELECT’ or ‘SELECT INTO’ queries you can copy data from a data-table to another
Actually Sixth normal form exist but use it when you want a relational system in conjunction with time. At this moment SQL Server does not support it directly.
It is SQL Server tool that facilitate administrator to monitor different events and transaction of particular SQL server instance, You can capture and save data about each event to a file or SQL Server table to analyze later.
It is not available with EXPRESS edition
To sort, total, and count the data stored in 1 table automatically we use PIVOT. It will also rotate tables as a columns to rows and rows to columns.
To automatically sort, count, and total the data stored in one table we use PIVOT. It will also rotate table as rows to columns and vice versa
This post covered the frequently asked questions in SQL server. Hope you found this useful. Please let us know your valuable feedback./queries
OWIN (Open Web Interface for .NET) is an interface between web servers and web applications…
JSON (JavaScript Object Notation) is a commonly used data exchange format that facilitates data exchange…
The CAP theorem is also known as Brewer's theorem. What is CAP Theorem? CAP theorem…
Some of the Key factors that need to consider while architecting or designing a software…
The Interface Segregation Principle (ISP) is one of the SOLID principles of object-oriented design. The…
The Single Responsibility Principle (SRP), also known as the Singularity Principle, is a software design…