SQL Server interview questions
SQL Server 2008 Interview Question with Answers
Data types in SQL ?
Ans. Bigint, Binary, Bit, char, cursor, Datetime, Decimal, Float, image, int, Money, Nchar, Ntext, nvarchar, real, Smalldatetime, Smallint, smallmoney, text, timestamp, Tinyint, Varbinary,Varchar, uniqueidentifier
What are joins and Types of join?
Ans. We need retrive data from two or more tables to make our result complete. We need to perform a join.
INNER JOIN
This join returns rows when there is at least one match in both the tables.
OUTER JOIN
There are three different Outer Join methods.
LEFT OUTER JOIN
This join returns all the rows from the left table with the matching rows from the right table. If there are no field matching in the right table then it returns NULL values
RIGHT OUTER JOIN
Right outer join returns all the rows from the right table with the matching rows from the left table. If there are no field matching in the left table then it returns NULL values
FULL OUTER JOIN
Full outer join merge left outer join and right outer join. this returns row from either table when the conditions are met and returns null value when there is no match
CROSS JOIN
Corss join is does not necessary any condition to join. The output result contains records that are multiplication of record from both the tables.
What is Index and type of index?
An index is an on-disk structure associated with a table or view that speeds retrieval of rows from the table or view. An index contains keys built from one or more columns in the table or view. These keys are stored in a structure (B-tree) that enables SQL Server to find the row or rows associated with the key values quickly and efficiently.A table or view can contain the following types of indexes:Clustered Index
A clustered index sorts and stores the data rows of the table or view in order based on the clustered index key. Physically rearranges the data in the table to conform to the index constraints
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. The leaf nodes of a clustered index contain the data pages.
Only 1 index allowed on one table
Nonclustered Index
A nonclustered index can be defined on a table or view with a clustered index or on a heap. Creates a separate list of key values with pointers to the location of the data in the data pages
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. 249 indexes are allowed on one table
Unique
A unique index ensures that the index key contains no duplicate values and therefore every row in the table or view is in some way unique. Uniqueness can be a property of both clustered and nonclustered indexes.Columnstore
An xVelocity memory optimized columnstore index based on vertical partitioning of the data by columns, stored as large objects (LOB).Index on computed columns
An index on a column that is derived from the value of one or more other columns, or certain deterministic inputs.Filtered
An optimized nonclustered index, especially suited to cover queries that select from a well-defined subset of data. It uses a filter predicate to index a portion of rows in the table. A well-designed filtered index can improve query performance, reduce index maintenance costs, and reduce index storage costs compared with full-table indexes.XML
A shredded, and persisted, representation of the XML binary large objects (BLOBs) in the xml data type column.
Select 3 product which having highest sale price
select top 3 form product order by sale_price desc
Delete the records from product which having null description
delete from product where product_name is null
Display detail of product which having maximum sale
select TOP 1 form
(select product_id, count(*) as cnt from sales group by product_id) a
order by cnt desc
Select customer details and produtct details of cutomer imran
Select p.product_name, c.cutomer_name, p.sale_price from product p, cutomer c, sale s
where p.product_id=s.product_id and c.cutomer_id=s.ccustomer_id and customer_name=’imran’
Difference between DELETE & TRUNCATE statement
Ans. Delete is a DML command. Truncate is a DDL command.
In Delete statement we can use where clause But we can’t use where clause in truncate statement.
Delete activates trigger. Truncate does not activate trigger.
We can rollback delete command. We can not rollback truncate command. Delete does not reset identity of table. Truncate resets identity of table.
Difference between Primary key and Unique Key
Ans. Primary key and Unique key enforce uniqueness of the column on which they are defined. But by default, the primary key creates a clustered index on the column, where as unique key creates a non-clustered index by default. Another major difference is that primary key does not allow NULL value, but unique key allows one NULL value only.
What is constraint in SQL Server?
Ans. A constraint is an object that exists only within confines of a table. i.e. – Rules which are enforced on data being entered, and prevents the users from entering invalid data into tables are called constraints. Thus, constraints super control data being entered in table.
Below are SQL Server constraint
NOT NULL
UNIQUE
PRIMARY KEY
FOREIGN KEY
CHECK
DEFAULT
candidate key
How to create a table by using the structure of another table in SQL Server?
Ans.
CREATE TABLE NEWTABLE AS SELECT * FROM EXISTTABLE WHERE 1=2;
If the WHERE clause condition is true, then it will copy all the rows or rows satisfying the condition to the new table.
What is a View in SQL Server?
Ans. A View is like a virtual table in SQL Server. the data from a View is not stored physically but it is stored in base tables of the sql server datbase.
Syntax of View creation
CREATE VIEW ViewName AS select * from EXISTTABLE WHERE [condition]
The reason why views are created in SQL Server?
1. For data security
2. For data redundancy is to be kept the minimum while maintaining data security.What is Stored procedures in SQL Server?
Ans. A stored procedure is a group of sql statements, set of transect-SQL statement bundled up into a single unit that perform a specific task.
They allowed for variables and parameters and selection and looping constructs.What are the benefits of using Stored Procedures in SQL Server?
Ans . SQL Server store procedure benifit 1 .Precompiled execution and better performance.
SQL Server store procedure benifit 2. Efficient reuse of code and programming abstraction. Stored procedures can be used by multiple users and client programs. If you utilize them in a planned manner, you will find the development cycle takes less time.
SQL Server store procedure benifit 3. Enhanced security controls.
SQL Server store procedure benifit 4. Reduces surface area of attack for sql injection
SQL Server store procedure benifit 5. SQL Server store procedure can abstract complex data processing from application
SQL Server 2008 Interview Question with Answers
Data types in SQL ?
Ans. Bigint, Binary, Bit, char, cursor, Datetime, Decimal, Float, image, int, Money, Nchar, Ntext, nvarchar, real, Smalldatetime, Smallint, smallmoney, text, timestamp, Tinyint, Varbinary,Varchar, uniqueidentifier
What are joins and Types of join?
Ans. We need retrive data from two or more tables to make our result complete. We need to perform a join.
INNER JOIN
This join returns rows when there is at least one match in both the tables.
OUTER JOIN
There are three different Outer Join methods.
LEFT OUTER JOIN
This join returns all the rows from the left table with the matching rows from the right table. If there are no field matching in the right table then it returns NULL values
RIGHT OUTER JOIN
Right outer join returns all the rows from the right table with the matching rows from the left table. If there are no field matching in the left table then it returns NULL values
FULL OUTER JOIN
Full outer join merge left outer join and right outer join. this returns row from either table when the conditions are met and returns null value when there is no match
CROSS JOIN
Corss join is does not necessary any condition to join. The output result contains records that are multiplication of record from both the tables.
What is Index and type of index?
An index is an on-disk structure associated with a table or view that speeds retrieval of rows from the table or view. An index contains keys built from one or more columns in the table or view. These keys are stored in a structure (B-tree) that enables SQL Server to find the row or rows associated with the key values quickly and efficiently.A table or view can contain the following types of indexes:Clustered Index
A clustered index sorts and stores the data rows of the table or view in order based on the clustered index key. Physically rearranges the data in the table to conform to the index constraints
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. The leaf nodes of a clustered index contain the data pages.
Only 1 index allowed on one table
Nonclustered Index
A nonclustered index can be defined on a table or view with a clustered index or on a heap. Creates a separate list of key values with pointers to the location of the data in the data pages
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. 249 indexes are allowed on one table
Unique
A unique index ensures that the index key contains no duplicate values and therefore every row in the table or view is in some way unique. Uniqueness can be a property of both clustered and nonclustered indexes.Columnstore
An xVelocity memory optimized columnstore index based on vertical partitioning of the data by columns, stored as large objects (LOB).Index on computed columns
An index on a column that is derived from the value of one or more other columns, or certain deterministic inputs.Filtered
An optimized nonclustered index, especially suited to cover queries that select from a well-defined subset of data. It uses a filter predicate to index a portion of rows in the table. A well-designed filtered index can improve query performance, reduce index maintenance costs, and reduce index storage costs compared with full-table indexes.XML
A shredded, and persisted, representation of the XML binary large objects (BLOBs) in the xml data type column.
Select 3 product which having highest sale price
select top 3 form product order by sale_price desc
Delete the records from product which having null description
delete from product where product_name is null
Display detail of product which having maximum sale
select TOP 1 form
(select product_id, count(*) as cnt from sales group by product_id) a
order by cnt desc
Select customer details and produtct details of cutomer imran
Select p.product_name, c.cutomer_name, p.sale_price from product p, cutomer c, sale s
where p.product_id=s.product_id and c.cutomer_id=s.ccustomer_id and customer_name=’imran’
Difference between DELETE & TRUNCATE statement
Ans. Delete is a DML command. Truncate is a DDL command.
In Delete statement we can use where clause But we can’t use where clause in truncate statement.
Delete activates trigger. Truncate does not activate trigger.
We can rollback delete command. We can not rollback truncate command. Delete does not reset identity of table. Truncate resets identity of table.
Difference between Primary key and Unique Key
Ans. Primary key and Unique key enforce uniqueness of the column on which they are defined. But by default, the primary key creates a clustered index on the column, where as unique key creates a non-clustered index by default. Another major difference is that primary key does not allow NULL value, but unique key allows one NULL value only.
What is constraint in SQL Server?
Ans. A constraint is an object that exists only within confines of a table. i.e. – Rules which are enforced on data being entered, and prevents the users from entering invalid data into tables are called constraints. Thus, constraints super control data being entered in table.
Below are SQL Server constraint
NOT NULL
UNIQUE
PRIMARY KEY
FOREIGN KEY
CHECK
DEFAULT
candidate key
How to create a table by using the structure of another table in SQL Server?
Ans.
CREATE TABLE NEWTABLE AS SELECT * FROM EXISTTABLE WHERE 1=2;
If the WHERE clause condition is true, then it will copy all the rows or rows satisfying the condition to the new table.
What is a View in SQL Server?
Ans. A View is like a virtual table in SQL Server. the data from a View is not stored physically but it is stored in base tables of the sql server datbase.
Syntax of View creation
CREATE VIEW ViewName AS select * from EXISTTABLE WHERE [condition]
The reason why views are created in SQL Server?
1. For data security
2. For data redundancy is to be kept the minimum while maintaining data security.What is Stored procedures in SQL Server?
Ans. A stored procedure is a group of sql statements, set of transect-SQL statement bundled up into a single unit that perform a specific task.
They allowed for variables and parameters and selection and looping constructs.What are the benefits of using Stored Procedures in SQL Server?
Ans . SQL Server store procedure benifit 1 .Precompiled execution and better performance.
SQL Server store procedure benifit 2. Efficient reuse of code and programming abstraction. Stored procedures can be used by multiple users and client programs. If you utilize them in a planned manner, you will find the development cycle takes less time.
SQL Server store procedure benifit 3. Enhanced security controls.
SQL Server store procedure benifit 4. Reduces surface area of attack for sql injection
SQL Server store procedure benifit 5. SQL Server store procedure can abstract complex data processing from application
