SQL Server Inetrview FAQ 3

1.       How to find the maximum no of connection allowed in SQL Server?

 Select @@MAX_Connections

2.       How to select top 2 rows without using top?

 It can be achieved by using rowcount. For example

SET ROWCOUNT 2

SELECT *from tblName

3.       What is the purpose of SET ANSI NULLS ON?

SET ANSI NULLS ON is used to follow ANSI standerds.So if you are working with distibuted queries running across multiple server,You need to SET ANSI NULLS ON,to maintain compatibility for all servers.

For example: We should not use <> or != for checking NULL condition,It should be is NULL or is NOT NULL as per ANSI standerds.

4.       How to insert Multiple Rows in single query?

We can use Row Constructor as an example

INSERT INTO TABLENAME(COL1,COL2,COL3)

VALUES

('VAL1','VAL2','VAL3'),

('VAL11','VAL22','VAL33'),

('VAL111','VAL222','VAL333')

5.       Which type of column we can’t update using UPDATE?

TIMESTAMP type of column can’t be updated.

6.       How can you apply restrictions on database objects?

We can create constraints, triggers or rules and defaults to apply restrictions. Constraints are better than triggers and rules. Triggers and rules should only be used if constraints are not an option because triggers make overhead on system.

7.       CAST vs. Convert

Convert does everything that CAST does. The only difference is that CAST is ANSI/ISO compliant while CONVERT is not.

8.       What is the default port no of SQL server

SQL Server listen TCP port 1433 by default.

9.       What are DMVs?

DMV: Dynamic Management Views are used to monitor server state information as health of server instance, performance, connections.

For example:

SELECT * FROM sys.dm_os_wait_stats;

It will return operating system wait states.

SELECT * FROM sys.dm_exec_sessions;

It will return cureent sessions. Some other DMVs are

·         dm_broker_connections

·         dm_broker_forwarded_messages

·         dm_broker_queue_monitors

·         dm_cdc_errors

·         dm_cdc_log_scan_sessions

·         dm_clr_appdomains

·         dm_clr_loaded_assemblies

·         dm_clr_properties

·         dm_clr_tasks

10.   OLTP vs OLAP

OLTP: Online Transaction Processing (It is used for usual applications).Most of applications are OLTP based. It emphasizes on Update.

OLAP: (Online Analytic Processing)It is used for multidimensional queries and better approach for MIS and decision making systems. In Business Intelligence OLAP used. It emphasizes on Retrieval.