SQLServer Interview Questions

Every .NET Developer Should Know About the Database they are working with:
Click @ me 

Is it Possible to Update View in SQL Server
Yes, it is Possible when Data you are updating directly refers to the Column of only one Base Table. With multiple columns of more then Table can not be updated.

Union vs. Union All 
The UNION operator is used to combine the result-set of two or more SELECT statements.

Notice that each SELECT statement within the UNION must have the same number of columns. The columns must also have similar data types. Also, the columns in each SELECT statement must be in the same order.

SQL UNION Syntax

SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;

Note: The UNION operator selects only distinct values by default. To allow duplicate values, use the ALL keyword with UNION.

The difference between Union and Union all is that Union all will not eliminate duplicate rows, instead it just pulls all rows from all tables fitting your query specifics and combines them into a table.

Difference between Unique Key and Primary Key

1. Primary Key does not allow null values whereas unique constraint allows 'single' null value.
2. A table can have only single Primary Key where as it can have multiple unique constraints
( max    16)
3. Primary Key creates clustered index by default whereas Unique Key creates non-clustered index by default.

Difference between TRUNCATE and DELETE commands?

1.TRUNCATE is a DDL command whereas DELETE is a DML command.
2.DELETE operation can be rolled back, but TRUNCATE operation cannot be rolled back. 3.WHERE can be used with DELETE and not with TRUNCATE.
4. DELETE Maintain Log file for each row deletion, but Truncate don’t.
5. Delete Command: Delete a row based on where Condition.Truncate command: Delete all the Rows from table.
6.Truncate Resets Identity Column counters.
7. TRUNCATE removes all rows from a table, but the table structure, its columns, constraints, indexes and so on, remains.

Cast and Convert 

CAST and CONVERT are both used to convert data from one data type to another.
Here is an example using both CAST and CONVERT in the same statement:

SELECT CAST ('10' as int) * 20, CONVERT (int, '10') * 20

CONVERT differences lie in that it accepts an optional style parameter which is used for formatting.
For example, when converting a DateTime datatype to Varchar, you can specify the resulting date’s format, such as YYYY/MM/DD or MM/DD/YYYY.

SELECT CONVERT(VARCHAR,GETDATE(),101) as MMDDYYYY, CONVERT(VARCHAR,GETDATE(),111) as YYYYMMDD

What is mean by constraint?
Constraint : protect columns of the table from unwanted values.NOT NULL, CHECK, UNIQUE, PRIMARY KEY, FOREIGN KEY are the types of constraints define in SQL Server.

What is stored procedure?
It's nothing but a set of T-SQL statements combined to perform a single task of several tasks. It’s basically like a Macro so when you invoke the Stored procedure, you actually run a set of statements.

materilised View
A materialized view is defined just as a regular view but the result set of the query is stored as persistent data object such as table which is frequently updated from the underlying base tables when changes occur. They are useful to aggregate data in business intelligence applications with complex queries

What is the difference between a HAVING CLAUSE and a WHERE CLAUSE?

Both Clause are used to check the Condition at the time of Retrival of records in Database.HAVING specifies a search condition for a group or an aggregate function used in SELECT statement.

[If a condition refers to an aggregate function, put that condition in the HAVING clause. Otherwise, use the WHERE clause.(Where clause doesn’t work with Aggregate Function)]

Example to check Duplicate Records:

select empid from employee where empid not in(select empid from employee group by empid having count(*)>1)

SQL PRIMARY KEY Constraint

The PRIMARY KEY constraint uniquely identifies each record in a database table.
Primary keys must contain unique values.
A primary key column cannot contain NULL values.
Each table should have a primary key, and each table can have only ONE primary key.

To Add Primary Key in A Table

ALTER TABLE Persons ADD CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName)

To Delete Primary Key in a Table

ALTER TABLE Persons
DROP CONSTRAINT pk_PersonID

DML-- DML stands for Data Manipulation Language. DML is used to retrieve, store, modify, delete, insert and update data in database.

What is mean by DDL and Usage?

DDL -- Data Definition Language.
create and modify the structure of database objects.

What keyword does an SQL SELECT statement use for a string search?

The LIKE keyword allows for string searches. The % sign is used as a wildcard.
To Rename a Database- sp_renamedb 'oldname' , 'newname'

What are the 2 types of Temporary Tables in SQL Server?
http://www.codeproject.com/

There are 2 types of temporary tables, local and global in sql server.

Local temporary tables are created using a single pound (#) sign and are visible to a single connection and automatically dropped when that connection ends.

EX:
CREATE TABLE #LocalTempTable(UserID int,
UserName varchar(50),UserAddress varchar(150))

Global temporary tables are created using a double pound (##) sign and are visible across multiple connections and users and are automatically dropped when all SQL sessions stop referencing the global temporary table.

EX:
CREATE TABLE ##LocalTempTable(
UserID int,
UserName varchar(50),
UserAddress varchar(150))

What is NOT NULL Constraint

A NOT NULL constraint enforces that the column will not accept null values. The not null constraints are used to enforce domain integrity, as the check constraints.

What is the difference between primary key and foreign key?
Primary key is use to uniquely identify each row they are unique and cannot be null where as foreign key are use to ensure data integrity and relationship between tables.
PK will enforce entity integrity whereas FK will enforce referential integrity.
  • A Table can have maximum 1024 Columns.
FOREIGN KEY Constraint:
A FOREIGN KEY in one table points to a PRIMARY KEY in another table.

Ex- CREATE TABLE Orders
(
OrderNo int NOT NULL,
P_Id int,
CONSTRAINT fk_PerOrders FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id)
)

To Add Foreign Key in A Table
ALTER TABLE Orders
ADD CONSTRAINT fk_PerOrders
FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id)

There is a table with name Country Table which has only one column - CountryName. Write a query to select the name of the countries whose name repeat more than 3 times in the table.

SELECT CountryName FROM Country Table GROUP BY CountryName HAVING COUNT(*) >=3

Define Identity Column?
Identity column is a column that automatically generates numeric values. Start and increment value are the two properties of prior importance that need to be set with respect to Identity column. Value of this column is mostly used to identify each row uniquely.

Define collation?
Collation is a set of rules that defines how the data  is to be sorted and compared.

Difference between Function and stored procedure?
Store Procedure:

1.       Stored procedure may or not return values.
2.       It will allow DQL select statements as well as DML statements such as insert, update, delete etc.
3.       Stored procedures have mutually input and output parameters.
4.       We can use try and catch blocks for exception handling in stored procedures.
5.       Stored procedure can use transactions inside stored procedures.
6.       Stored procedure can use both table variables as well as temporary table in it.
7.       Stored Procedures be able to call functions.
8.       Procedures can’t be called from Select/Where/Having etc statements. Execute/Exec statement can be used to call/execute stored procedure.
9.       Store procedures can’t be used in Join clause.

UDF User Define Function:
1.       Function necessities return a value.
2.       UDF will allow only Select statement; it will not allow us to use DML statements.
3.       UDF will allow only input parameters, doesn’t support output parameters.
4.       UDF will not allow us to use try-catch blocks.
5.       UDF transactions are not allowed within functions.
6.       We can use only table variables; UDF will not allow using temporary tables.
7.       Stored procedures can’t be called from function.
8.       Functions can be called from select statement.
9.     UDF can be used in join clause as a result set.

What is a VIEW in Sql Server?
View is a Virtual table which contains columns from one or more tables. It does not contain any data directly.

EX- 
CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition

What is composite key?
A key formed by combining at least two or more columns is called composite key.

 The SQL SELECT DISTINCT Statement
Returns only Distinct Value from a table => SELECT DISTINCT City FROM Persons

SQL AND & OR Operators
SELECT * FROM Persons WHERE LastName='Svendson'  AND (FirstName='Tove' OR FirstName='Ola')

SQL ORDER BY Keyword
The ORDER BY keyword is used to sort the result-set
The ORDER BY keyword sorts the records in ascending order by default.

SELECT * FROM Persons ORDER BY LastName DESC

SQL TOP Clause- is used to specify the number of records to return.
SELECT TOP 2 * FROM Persons
SELECT TOP 50 PERCENT * FROM Persons

SQL LIKE Operator-
we want to select the persons living in a city that starts with "s" from the table
SELECT * FROM Persons WHERE City LIKE 's%'
we want to select the persons living in a city that ends with an "s" from the "Persons" table.
SELECT * FROM Persons WHERE City LIKE '%s'

SQL Wildcards Queries---
SELECT * FROM Persons WHERE City LIKE 'sa%'
SELECT * FROM Persons WHERE City LIKE '%nes%'
SELECT * FROM Persons WHERE FirstName LIKE '_la'
SELECT * FROM Persons WHERE LastName LIKE 'S_end_on'
SELECT * FROM Persons WHERE LastName LIKE '[bsp]%'
SELECT * FROM Persons WHERE LastName LIKE '[!bsp]%'

SQL Alias Query
SELECT a.SNAME,a.SMobile,b.AllergyID  from AmitTest.dbo.Student as a,AmitTest.emr.Allergy as b where a.SID= 1

SQL Joins
SQL joins are used to query data from two or more tables, based on a relationship between certain columns in these tables.

The SELECT INTO  statement selects data from one table and inserts it into a different table.
The SELECT INTO statement is most often used to create backup copies of tables.

Ex- SELECT LastName,FirstName INTO Persons_Backup FROM Persons

SQL CHECK Constraint
The CHECK constraint is used to limit the value range that can be placed in a column.

EX-  a Query for Check Constraint on Multiple Columns.
CREATE TABLE Persons(
P_Id int NOT NULL,
FirstName varchar(255),
City varchar(255),
CONSTRAINT chk_Person CHECK (P_Id>0 AND City='Sandnes')
)

The DEFAULT constraint is used to insert a default value into a column.
EX-  this query insert Current date in OrderDate by default

CREATE TABLE Orders(
O_Id int NOT NULL,
P_Id int,
OrderDate date DEFAULT GETDATE()
)

Indexes  allow the database application to find data fast; without reading the whole table.

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.

Index types in SQL Server

Clustered_Index
Only 1 allowed per table physically rearranges the data in the table to confirm to the index constraints for use on columns that are frequently searched for ranges of data for use on columns with low selectivity.

Non-Clustered_Index
Up to 999 allowed per table creates a separate list of key values with pointers to the location of the data in the data pages For use on columns that are searched for single values

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. 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.

How to find out which index is defined on table?
Ans: sp_helpindex tablename

What is the difference between scope_identity() and current_identity()?
Ans: Scope_identity and current _identity both are similar and it will return the last identity value generated in the table.

Scope_Identity will return the identity value in table that is currently in scope

The ALTER TABLE statement is used to add, delete, or modify columns in an existing table.

EX- some Queries.

ALTER TABLE table_name ADD column_name datatype
ALTER TABLE table_name DROP COLUMN column_name
ALTER TABLE table_name ALTER COLUMN column_name datatype (to change datatype of a column)

SQL AUTO INCREMENT Field
Auto-increment allows a unique number to be generated when a new record is inserted into a table.

CREATE TABLE Persons
(
  P_Id int PRIMARY KEY IDENTITY,
  LastName varchar(255) NOT NULL
)

NOW()               Returns the current date and time
CURDATE()     Returns the current date
CURTIME()     Returns the current time
SQL IS NULL and IS NOT NULL

EX-  SELECT LastName,FirstName,Address FROM Persons WHERE Address IS NULL
 SELECT LastName,FirstName,Address FROM Persons WHERE Address IS NOT NULL

SQL Function

The AVG() function returns the average value of a numeric column.|
EX- SELECT AVG(column_name) FROM table_name
       SELECT Customer FROM Orders WHERE OrderPrice>(SELECT AVG(OrderPrice) FROM Orders)

The COUNT() function returns the number of rows that matches a specified criteria.
Ex- SELECT COUNT(DISTINCT Customer) AS NumberOfCustomers FROM Orders

The MAX() function returns the largest value of the selected column.
EX- SELECT MAX(OrderPrice) AS LargestOrderPrice FROM Orders

The SUM() function returns the total sum of a numeric column.
Ex- SELECT SUM(OrderPrice) AS OrderTotal FROM Orders

The GROUP BY statement is used in conjunction with the aggregate functions to group the result-set by one or more columns.

EX- SELECT Customer,SUM(OrderPrice) FROM Orders GROUP BY Customer

The MID() function is used to extract characters from a text field.
Syntax- SELECT MID(column_name,start[,length]) FROM table_name
EX- SELECT MID(City,1,4) as SmallCity FROM Persons

Difference between SQL Server 2008 and SQL Server 2012
In short, we can explain to differences in deeply.
  1. In SQL Server 2012, uses 48 bit precision for spatial.
  2. In SQL server 2012, has unlimited concurrent connections are available.
  3. In SQL server 2012, by default supports 15,000 partitions in DB.
  4. In SQL server 2012, available new string function CONCAT to strings.
  5. In SQL server 2012, available new string function FORMAT to strings.
  6. In SQL server 2012, available new conversion  functions are PARSE ,TRY_CONVERT, and TRY_PARSE. 
Transaction can be defined as a Sequence of Operations performed togather as a Single logical unit of Work, that must be complited to maintain the Consistency and Integrity of Database.
a single unit of work must process the four Properties.- ACID

Automicity- this states that either all the operations are performed or none of them.
Consistency- this states that all Data is in a Consistent state after a Transaction is Completed.
Isolation- this states that any Data Modification made by one Transaction must be Isolated from the modification made by other Transaction.
Durability- this states that any Changes in the Data by a Completed Transactions  remains Permanently in effect in the System.

A Trigger is a SQL Procedure that initiates an Action when an event (Insert, Update, Delete) Occures.

The COALESCE() function returns the first non-null expression in a list.

COALESCE(expr1, expr2, ...., expr_n)

Common table expression (CTE) 
A common table expression (CTE) can be thought of as a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query. Unlike a derived table, a CTE can be self-referencing and can be referenced multiple times in the same query.

CTEs can be defined in user-defined routines, such as functions, stored procedures, triggers, or views.

The basic syntax structure for a CTE is:

WITH expression_name [ ( column_name [,...n] ) ]
AS
( CTE_query_definition )

Difference between char, varchar and nvarchar 

char, varchar and nvarchar are SQL Server data type that used for allocated memory in storage device. But there is little difference between them which described below:
charIt stores fixed length of character. For example if you declare char (10) then it allocates memory for 10 characters. If you store 5 character words then it stores it in 5 characters memory location and other 5 character's memory location will be wasted. Per character, it takes 1 Byte memory.

varchar
varchar is flexible data type means it allocates memory as per data stored into this. If you specify varchar(10) at the time of declaration then it allocates 0 memory location at the starting if you specify, it can be null. When you store 5 characters word it will allocate memory for 5 characters and store into that. So there will be no memory loss. It stores only non-unicode characters means for English language only. Per character, it takes 1 Bytes memory.

nvarchar
nvarchar means unicode variable characters. When you want to store international character in database then used nvarhcar. It used really because it increases database size twice than varchar datatype because it takes 2 Bytes memory for each character.

Trim method in SQL Server

SQL server does not have Trim method, but for trimming blank spaces (leading and trailing) from string we have used LTRIM and RTRIM method in SQL Server. User can easily use LTRIM() and RTRIM() together and simulate TRIM() functionality. For example

Trim in Simple Query
SELECT RTRIM(LTRIM('      SQL Server Trim() Demo       ')) AS Trim_String;
It return ‘SQL Server Trim() Demo ’ string without white space.

 The RANK() function in SQL Server returns the position of a value within the partition of a result set, with gaps in the ranking where there are ties.

The DENSE_RANK() function in SQL Server returns the position of a value within the partition of a result set, leaving no gaps in the ranking where there are ties.

Display all tables in a SQL Server database:

USE <YOURDBNAME>

SELECT * from information_schema.tables WHERE  table_type = 'base table'

Copy a table from one database to another in SQL Server 2008
If you have a table in a database and you would like to copy the table to another database,
use this query:

SELECT * INTO AdventureWorks.dbo.CustomersTemp FROM Northwind.dbo.Customers

Just remember that using this query will only transfer the schema and data. It does not transfer the indexes, foreign keys, statistics etc.

If you want to transfer all the objects from one database to another,
open Sql Server Management Studio > Right click on your database > All Tasks > Generate SQL Scripts. Then run these scripts against the new database.

Transfer both schema and data
To copy both data and schema, use the Microsoft SQL Server Database Publishing Wizard 1.1. This tool works for both SQL 2000 and SQL 2005 and generates a single SQL script file which can be used to recreate a database (both schema and data).

When do you use SQL Profiler?

SQL Profiler utility allows us to basically track connections to the SQL Server and also determine activities such as which SQL Scripts are running, failed jobs etc..

What do you understand by database tuning advisor?
Ans: Database Engine Tuning Advisor examines how queries are processed in the databases you specify, and then recommends how you can improve query processing performance by modifying database structures such as indexes, indexed views, and partitioning.

How can you optimize stored procedures?
1. Use the SET NOCOUNT ON statement
2. Use the schema name with the object name
3. Do not use the prefix "sp_" in the name
4. Use IF EXISTS (SELECT 1) instead of SELECT *
5. Avoid using cursors
6. Keep the transactions short as possible.
  •  Use sp_executesql instead of the EXEC statement for dynamic SQL
Why to avoid naming stored procs with "sp_" prefix - Slows down execution
Causes SQL Server to search for the stored proc in the master db

Suppose that you have 100 lines of query ,this query is executed successfully with in 5 min ,next day again you have to run the same query that time query is take a long time around 1 hour ,So how can you analyze the query..(what happen query performance is decreased)

Ans: You can explain your own way, but what I think that there would be some memory allocation problem. So first I will check is there any other users allocated more memory in the server, if yes try when sever has sufficient memory. Next check when the tables got analyzed. Next check is there any degree of parallelism has changed. There are so many factors causing performance.

How do you optimize queries? 
You can use SQL Profiler to locate long running queries
In general, keep your DBs normalized
Move queries to stored procedures:SPs are compiled and run on the server
Limit your searches as much as possible-only grab what you need
Don't use joins if you don't have to, they are expensive. Try to join on indexed fields if you can.
Add indexes to fields you often filter by, especially if they are an Integer, Boolean, or Number

You can limit the number of rows returned, or possibly do a pagination, so you don't have to get a giant amount of rows.

1 comment:

Topics

ADO .Net (2) Ajax (1) Angular Js (17) Angular2 (24) ASP .Net (14) Azure (1) Breeze.js (1) C# (49) CloudComputing (1) CMS (1) CSS (2) Design_Pattern (3) DI (3) Dotnet (21) Entity Framework (3) ExpressJS (4) Html (3) IIS (1) Javascript (6) Jquery (9) Lamda (3) Linq (11) Mongodb (1) MVC (48) NodeJS (7) RDLC (1) Report (1) Sql Server (29) SSIS (3) SSRS (2) UI (1) WCF (12) Web Api (10) Web Service (1) XMl (1)