SQL Server Coding Standards

SQL SERVER – Guidelines and Coding Standards Part – 1

SQL SERVER – Guidelines and Coding Standards Part – 2

SQL Server Interview Questions and Answers

• What is RDBMS?
• What are the Properties of the Relational Tables?
• What is Normalization?
• What is De-normalization?
• How is ACID property related to Database?
• What are the Different Normalization Forms?

• What is a Stored Procedure?
• What is a Trigger?
• What are the Different Types of Triggers?
• What is a View?
• What is an Index?
• What is a Linked Server?
• What is a Cursor?
• What is Collation?

• What is the Difference between a Function and a Stored Procedure?
• What is subquery? Explain the Properties of a Subquery?
• What are Different Types of Join?
• What are Primary Keys and Foreign Keys?
• What is User-defined Functions? What are the types of User-defined Functions that can be created?

• What is an Identity?
• What is DataWarehousing?
• What languages BI uses to achieve the goal?
• What is Standby Servers? Explain Types of Standby Servers.
• What is Dirty Read?
• Why can’t I use Outer Join in an Indexed View?
• What is the Correct Order of the Logical Query Processing Phases?

• Which Command using Query Analyzer will give you the Version of SQL Server and Operating System?
• What is an SQL Server Agent?
• Can a Stored Procedure call itself or a Recursive Stored Procedure? How many levels of SP nesting is possible?
• What is Log Shipping?
• Name 3 ways to get an Accurate Count of the Number of Records in a Table?
• What does it mean to have QUOTED_IDENTIFIER ON? What are the Implications of having it OFF?
• What is the Difference between a Local and a Global Temporary Table?
• What is the STUFF Function and How Does it Differ from the REPLACE Function?
• What is PRIMARY KEY?
• What is UNIQUE KEY Constraint?
• What is FOREIGN KEY?

• What are the basic functions for master, msdb, model, tempdb and resource databases?
• What is the Maximum Number of Index per Table?
• Explain Few of the New Features of SQL Server 2008 Management Studio
• Explain IntelliSense for Query Editing
• Explain MultiServer Query
• Explain Query Editor Regions
• Explain Object Explorer Enhancements
• Explain Activity Monitors

What is the cursor in SQL server

Cursors are used to hold the data temporarily for processing for some logic. It stores the data from table and process one by one each record.

What is CLR

In SQL Server 2008, SQL Server objects such as user-defined functions can be created using such CLR languages. This CLR language support extends not only to user-defined functions, but also to stored procedures and triggers. You can develop such CLR add-ons to SQL Server using Visual Studio 2008.

What is Filtered Index

Filtered Index is used to index a portion of rows in a table that means it applies filter on INDEX which improves query performance, reduce index maintenance costs, and reduce index storage costs compared with full-table indexes. When we see an Index created with some where clause then that is actually a FILTERED INDEX.

What is Policy Management

Policy Management in SQL SERVER 2008 allows you to define and enforce policies for configuring and managing SQL Server across the enterprise. Policy-Based Management is configured in SQL Server Management Studio (SSMS). Navigate to the Object Explorer and expand the Management node and the Policy Management node; you will see the Policies, Conditions, and Facets nodes.

What are the basic functions for master, msdb, model, tempdb and resource databases

1. The master database holds information for all databases located on the SQL Server instance and is theglue that holds the engine together. Because SQL Server cannot start without a functioning masterdatabase, you must administer this database with care.
2. The msdb database stores information regarding database backups, SQL Agent information, DTS packages, SQL Server jobs, and some replication information such as for log shipping.
3. The tempdb holds temporary objects such as global and local temporary tables and stored procedures.
4. The model is essentially a template database used in the creation of any new user database created in the instance.
5. The resoure Database is a read-only database that contains all the system objects that are included with SQL Server. SQL Server system objects, such as sys.objects, are physically persisted in the Resource database, but they logically appear in the sys schema of every database. The Resource database does not contain user data or user metadata.