Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Querying Microsoft SQL Server

I am a technology enthusiast and software developer by profession. I am developing .Net/database based enterprise applications from past 3 years.

My skills includes C# ,ASP.NET,SQL Server 2008 and MVC . My areas of interests are database development and application software development using Microsoft Technologies.

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 ROWCOUNT2
SELECT *fromtblName

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

Comments

Leave a comment on the original post [www.queryingsql.com, opens in a new window]

Loading comments...