Advantages of Stored Procedure in Microsoft SQL Server

There are many advantages of using stored procedures in Microsoft SQL Server. These are :

1. Easier to maintain and troubleshoot as they are modular.
2. Stored procedures enable better tuning for performance.
3. While using stored procedures is much easier from a GUI end than building complex queries.
4. Stored procedures help in reducing network usage.
5. Stored procedures provide more scalability to an application.
6. Reusable and hence reduce code.
7. Stored procedures can be part of a separate layer which allows separating the concerns. Hence Database layer can be handled by separate developers proficient in database queries.

How to Code Efficient Transactions in Microsoft SQL Server

To Code efficient transactions in Microsoft SQL Server, We must take care of following things :

1. Don't allow input from users during a transaction.
2. Don't open transactions while browsing through data.
3. Keep the transaction as short as possible.
4. Use lower transaction isolation levels.
5. Access the least amount of data possible while in a transaction.

Security Features of Stored Procedures in Microsoft SQL Server

Security features of stored procedures are available in Microsoft SQL Server. These are :

1. Provide more granular security control through stored procedures rather than complete control on underlying data in tables.
2. Grants users permissions to execute a stored procedure irrespective of the related tables.
3. Grant users permission to work with a stored procedure to access a restricted set of data yet no give them permissions to update or select underlying data.
4. Stored procedures can be granted execute permissions rather than setting permissions on data itself.

Usage of Triggers in Microsoft SQL Server

Triggers are used in Microsoft SQL Server for various purpose. These are used when :

1. Creation of audit log of database activity.
2. For applying business rules.
3. For applying calculation on data from tables which is not stored in them.
4. To enforce referential integrity.
5. When alter data in a third party application needed.
6. To execute SQL statements as a result of an event or condition automatically.

Restriction on Views in Microsoft SQL Server

Restrictions are applied when we create views in Microsoft SQL Server. These restrictions are listed below :

1. Views created, only referencing tables and views in current database. 
2. View name must not match with the name of table owned by that user.
3. Views can be built based on other views and on procedures that reference views.
4. We cannot associate Rules or DEFAULT definitions with views.
5. We can only associate INSTEAD OF triggers with views.
6. ORDER BY, COMPUTE, COMPUTE BY clauses and INTO keyword cannot be included in query that defines view.
7. Full-text index definitions cannot be used for views.
8. Temporary views cannot be created
9. Views cannot be created on temporary tables.

Checkpoints in Transaction Log in Microsoft SQL Server

Checkpoints are created in a transaction log When :

1. Checkpoint is explicitly executed.
2. Logged operation is performed on the database.
3. Database files have been altered using Alter Database command.
4. Microsoft SQL Server has been stopped explicitly or on its own.
5. Microsoft SQL Server periodically generates checkpoints.
6. Backup of a database is taken.

User Defined Data Types in Microsoft SQL Server

User defined data types in Microsoft SQL Server are based on system data types.

User defined data types are used when multiple tables need to store the same type of data in a column and you need to ensure that all these columns are exactly the same including length, and nullability.

User defined datatype contains following parameters:

• Name
• System data type on which user defined data type is based upon.
• Nullability

For example

User defined data type in Microsoft SQL Server called user_string could be created based on char system data type.

Service Broker in Microsoft SQL Server

Service Broker in Microsoft SQL Server allows internal and external processes to send and receive guaranteed and asynchronous messaging.

Messages can also be sent to remote servers hosting databases as well. 


Service Broker use the concept of queues to put a message in a queue and continue with other applications asynchronously. This enables client applications to process messages at their leisure without blocking the service broker. 


The concepts of message ordering, coordination, multithreading and receiver management is used in Service Broker to solve some major message queuing problems. It allows for loosely coupled services, for database applications.

Analysis Service Repository in Microsoft SQL Server

Microsoft SQL Server running analysis service has a repository to store objects of the computer running Analysis Services an Analysis service repository.

It stores the information about the cubes, dimensions and other objects of the analysis server. 


Server computer where the analysis service is installed has by default repository an .mdf database.

Microsoft SQL Server Lock Escalation

Lock escalation is dynamically managed by Microsoft SQL Server. It is process of conversion of row locks and page locks into table locks thereby “escalating” the smaller or finer locks.

Lock escalation increases the system performance as each lock is nothing but a memory structure. Too many locks would mean more consumption of memory. Hence, escalation is used.

Blocking in Microsoft SQL Server

Blocking is one of the main problem which Microsoft SQL Server experience. Blocking happens due to following reason :

When one connection from an application holds a lock and a second connection requires a conflicting lock type. This causes the second connection to wait, blocked on the first. 


Second connection will be active only when first connection release lock required by second connection.

Failover Clustering in Microsoft SQL Server

Failover clustering in Microsoft SQL Server is used for data availability.

Typically two machines are used in a failover cluster. One machine provides the basic services and the second is available to run the service when the primary system fails. 


The primary system is monitored periodically to check if it works. This monitoring may be performed by the failover computer or an independent system also called as cluster controller. 


In an event of failure of primary computer, the failover system takes control.

Builtin Administrator Account in Microsoft SQL Server

Every Instance of Microsoft SQL Server has Built in Administrator Account. It is used during some setup to join some machine in the domain.

Built in Administrator account in Microsoft SQL Server should be disabled immediately thereafter. 


For any disaster recovery, the built in administrator account will be automatically enabled. It should not be used for normal operations.

Microsoft SQL Server Public Role

In Microsoft SQL Server, every database has a public role which holds all the default permissions for the users in database.

Public role in Microsoft SQL Server cannot be dropped and we cannot assign roles or groups to it. 


Minimum permissions should be granted to public role, due to security reasons.

Scalability of Databases in Microsoft SQL Server

Scalability of Database System can be enhanced efficiently by Microsoft SQL Server.

Microsoft SQL Server 2000 automatically adjusts the granularity of locking for each table used by a query. 


Parallel Execution Plans is in placed automatically. This split the processing of a SQL statement into pieces that can be run on a different CPU, which causes the complete result set to build faster.

Why Use English Query in Microsoft SQL Server

English Query in Microsoft SQL Server used to build applications that can accept query in questions form e.g question is in English language.

While processing English Query, the application passes the questions string to the English query engine. This English Query engine returns a SQL statement or a Multi-Dimensional Expression (MDX) query to the application, Which will return the answer to the user’s question.

Microsoft SQL Server Execute SQL Statements in Different Ways

There are many different ways Microsoft SQL Server uses to execute SQL statements. These different way are following :

• Single SQL statement processing
• Batch processing
• Stored procedure and trigger execution
• Execution plan caching and reuse
• Parallel query processing

Why Use SQL Profiler in Microsoft SQL server

Uses of SQL Profiler in Microsoft SQL server

SQL Profiler captures SQL Server events from a server. These events are saved in a trace file that can be used to analyze and diagnose problem.

There are many other useful function of SQL Profiler, these are following :

• SQL Profiler used to find the cause of the problem by stepping through problem queries.
• SQL Profiler is very useful to analyze the cause of slow running queries.
• SQL Profiler can be used to tune workload of the Microsoft SQL server.
• SQL Profiler also stores security-related actions that can be reviewed by a security administrator.
• SQL Profiler also supports auditing the actions performed on instances of Microsoft SQL Server.

Why Use Full Text Query in Microsoft SQL Server

Full Text Query in Microsoft SQL Server

Full Text Query search used to search for the entire text instead of using “%”. We use it to search for text with data type TEXT or NOTE etc . 

Some predicates like FREETEXT, CONTAINS are used in full text query search. FREETEXT finds the word or words you give it anywhere in the search column. For example

SELECT Apparel FROM Products WHERE FREETEXT (Apparel, 'Van' )

The output of above query will be : Best Van Huesen trousers

Complete Phases of Transaction in Microsoft SQL Server

There are several phases of transaction in Microsoft sql server. These are following :

When transaction starts, database is in consistent state .

BEGIN TRANSACTION statement starts the transaction explicity.

Transaction generates the first record for data modification and write these records to log.

Modification starts one table at time, at this stage database is in inconsistent state.

Upon successful completion of all modification, database is once again consistent, and then application commits the transaction.

In case some errors occur, it rolls back all the modifications processed on data. This process returns the database to the point of consistency it was at before the transaction started.

Uses of XPath language

XPath is a language defined by the W3C. It used to select nodes from XML documents. 


Microsoft SQL Server 2000 supports XPath language that helps to retrieve data from XML documents. 


Microsoft SQL server uses a mapping schema to define an XML based view of the data in one or more SQL Server tables and views. The mapping schema is then used to retrieve data.

Constraints for Designing of Files and File groups in Microsoft SQL Server

There are some constraints for designing files and file group in Microsoft SQL server. These constraints are following :


A file or file group can only be used by one database. For example, the files abcd.mdf and abcd.ndf contains data and objects for abc database, cannot be used by any other database.

A file can be a member of only one file group.


Data and transaction log information cannot be part of the same file or file group.

Transaction log files can't be part of a file group.

Types of Authentication Modes in Microsoft SQL Server

There are two types of Authentication modes in Microsoft SQL Server. These are following :

• Windows Authentication
• Mixed mode

Windows authentication mode

Windows authentication mode allows users to connect using Windows domain like NT/2000 account. The access to the Microsoft SQL server is controlled by Windows NT/2000 account or group used when logged in, means that Windows username and password are used to access the database on Microsoft SQL Server server.

Mixed mode authentication

Mixed mode authentication allows use of Windows credentials and the local Microsoft SQL server account to logged into Database.

DDL DCL and DML in Microsoft SQL Server

Data definition language

Data definition language (DDL) is used to define and manage all attributes and properties of a database in Microsoft SQL Server. DDL statements include following commands :

• CREATE
• ALTER
• DROP

Data Control Language

Data control language (DCL) is used to control permissions on database objects. Permissions are controlled by using following commands :

• GRANT
• REVOKE
• DENY

Data Manipulation Language

Data manipulation language (DML) is used to select, insert, update, and delete data in the objects defined with Data Definition Language.

Why Avoid Permitting NULL values in Microsoft SQL Server

NULL value in column in Microsoft SQL Server means that no entry has been made into the column. It states that the corresponding value is either unknown or undefined. It is also different from zero or "". 

We should avoid permitting null values because Column with NULL values can't have PRIMARY KEY constraints. Certain calculations can be inaccurate if NULL columns are involved.

Log Shipping in Microsoft SQL Server

Log shipping in Microsoft SQL Server is process used to automatically taking backup of the database and transaction files from one Microsoft SQL Server and then restoring them on a backup server. 

Log shipping keeps the two Microsoft SQL Server instances in sync with each other. If production server fails, then users simply need to be pointed to the backup server. 

Log shipping process consists of following three steps :

1. Backup transaction logs of the Production server.
2. Copy these logs on the backup server.
3. Restore the log on backup server.

Types of Temporary Table in Microsoft SQL Server

There are two types of temporary tables in Microsoft SQL Server. These are: 

1. Local temporary table
2. Global temporary table. 

Local Temporary Table

Local temporary table is available only to the current Db connection for current user and are cleared when connection is closed. Local temporary table can not be shared among multiple users.

Global Temporary Table

Global temporary table is available to any connection once created. They are cleared when the last connection is closed. Global temporary table can be shared by multiple user sessions.

Temporary tables are used to allow short term use of data in Microsoft SQL Server.

Microsoft SQL Server 2008 PowerShell

Windows PowerShell is a new and powerful scripting shell which can be used by developers and administrators for automating server administration and deployment of the application. The PowerShell supports complex logic than the Transact-SQL scripts, which provides the Microsoft SQL Server
administrators the power to build robust administration scripts. Other Microsoft products can be administered by PowerShell. In a nut shell, PowerShell is a common scripting language across different servers.

Below are two Windows PowerShell snap-ins provided by Microsoft SQL Server provides.

SQL Server provider

It enables the navigation mechanism similar to the file system paths. Paths that are similar to file system paths can be built. The process is associated with Microsoft SQL Server management object model and the other nodes are object model classes based.

Set of cmdlets

These are the commands which are utilized in PowerShell scripts for specifying an action of Microsoft SQL Server. The actions such as running sqlcmd script which contains Transact-SQL or XQuery statements, are supported by cmdlets.

Microsoft SQL Server 2008 Declarative Management Framework

Declarative Management Framework is a system for managing the instances of Microsoft SQL Server 2008. It is a policy based system. The database security can be tighten with polity management, automated administration and explicit administration. A policy can be designed for prohibiting the usage of unauthorized applications and the naming conventions on the database are implied for developers.

Various tasks are planned by many DBAs and backing up databases, reviewing events logs, scanning for improper / unauthorized object creations, long running query SPIDs killing are being performed. Lot of tactical and reactionary items on the list of tasks can keep the conscientious DBA busy.

Microsoft SQL Server will enforce to define the policies by using Management Studio and select certain servers for enforcing the policy. These can be monitored from SSMS, which acts as a central console.

Microsoft SQL Server 2008 Extended Events

Extended Events are the enhanced event handling system in Microsoft SQL Server. It is an architecture that is highly scalable, highly configurable. It allows the DBAs for collecting the required information, could it be little or much, for problem identification or problem trouble shooting.

Data collection which can output to an Event Tracing for Windows target is one of the key features of Extended Events. This allows the correlating data with the data collected from the corresponding operating system with the help of Event Tracing for Windows. Even the wait events could be correlated with the kernel EWT data. This process is done within a single view for isolating the waits for a specific reason.

The events are handled by an engine which is a services and objects collection which allows defining, process and managing event sessions, event data and event sessions respectively.

Microsoft SQL Server 2008 Filestream Storage

The complexity of application development reduces the managing unstructured data cost. The manageability is increased by extending various capabilities which are available only to relational data to non-relational data.

There are 2 new capabilities in Microsoft SQL Server 2008 for persisting BLOB data.

1. FILESTREAM
2. Remote BLOB storage

FILESTREAM

It is an attribute that can be set on a ‘varbinary’ column for data persistence on the file streams. This enables the benefits from fast streaming capabilities and storage capabilities.

Remote BLOB storage

It is a client-side API which reduces the application building complexity and relies on an external persistence for BLOBs and a database for relational data. SQL Server 2008 will also continue the support for standard BLOB type through the data type ‘varbinary’.

Compression Backup in Microsoft SQL Server 2008

A compression backup is smaller than uncompressed backup. The backup speed significantly increases because it needs less disk space and I/O operations. The CPU usage is increased and the increased and the additional CPU that is consumed by the process of compression would impact the operations that are running concurrently.

The following processes can be isolated for obtaining a good picture.

1. Physical disk counters
2. Device throughput bytes / second counter of SQL Server Backup Device object
3. Backup/Restore throughput / second counter of SQL Server Databases object.

Microsoft SQL Server 2008 Policy Based Administration Feature

Policy based database administration allows the DBA for managing the instances and objects of Microsoft SQL Server 2008 across the enterprise by a defined policies that are set. These policies are the rules and regulations which determine the possible ways of what to do and what not to do and the way the violations of policies are enforced and reported. The policies are well defined by using facets and conditions. The details of facets and conditions are below.

Facet

An object which contains the properties which well describes a component.

Condition

A logical expression that is defined on a facet. It is used for identification of acceptable property values of the facet.

Use Sparse Columns of SQL Server 2008

A column with an optimized storage for null values is known as sparse column. Sparse columns reduce the storage space needs for null values. In a scenario of saving 20 percent to 40 percent of storage space, sparse columns can be considered. They are created using CREATE TABLE or ALTER TABLE statements. Sparse columns can be used with Column sets and Filtered index.

Column sets

The statements INSERT, DELETE, UPDATE could be referred the sparse columns by name. The same an also be combined into a single XML Column. This is a column set.

Filtered index

As there are several null value rows in sparse columns, they are appropriate for filtered indexes. The filtered index on a sparse column can accommodate only the rows / tuples which populate values. The index created with filtered index is small and more efficient index.

A large number of user defined properties can be accessed by using sparse columns and filtered indexes enabled applications such as Share Point Services of windows are efficiently store and access.

Store and Query Spatial Data in Microsoft SQL Server 2008

Store Data

Spatial data is stored by using Geometry and Geography data types that are introduced in Microsoft SQL Server 2008. Geometry data type can be created as follows :

CREATE TABLE SpatialTable
( id int IDENTITY (1,1),
GeomCol1 geometry,
GeomCol2 AS GeomCol1.STAsText() );
GO

The data into the geometry data column is persisted by using the following INSERT command

INSERT INTO SpatialTable (GeomCol1)
VALUES (geometry::STGeomFromText('LINESTRING (100 100, 20 180, 180 180)', 0));

Query Data

The data in the geometry data columns is queried by using the following Commands.

DECLARE and SELECT statements:

DECLARE @geom1 geometry;
DECLARE @geom2 geometry;
DECLARE @result geometry;

SELECT @geom1 = GeomCol1 FROM SpatialTable WHERE id = 1;
SELECT @geom2 = GeomCol1 FROM SpatialTable WHERE id = 2;
SELECT @result = @geom1.STIntersection(@geom2);
SELECT @result.STAsText();

MERGE Statement in Microsoft SQL Server 2008

Merge statement allows a single statement for INSERT, DELETE and UPDATE a row that depends on a condition. The target table for certain operations is based on the results of join with a source table. 

For example

MERGE InventoryMaster AS invmstr
USING (SELECT InventoryID, Descr FROM NewInventory) AS src
ON invmstr. InventoryID = src. InventoryID
WHEN MATCHED THEN
          UPDATE SET invmstr.Descr = src.Descr
WHEN NOT MATCHED THEN

INSERT (InventoryID, Descr) VALUES (src. InventoryID, src.Descr);

Database Mirroring Enhanced in Microsoft SQL Server 2008

Data base mirroring in Microsoft SQL Server 2008 is enhanced by following ways :

Automatic Page Repair

The principal is enabled and mirror computers for recovering transparently from 823 and 824 errors on data pages, with a request for a fresh copy of the page that is corrupted from the mirroring partner.

Improved Performance

The outgoing log stream is compressed by Microsoft SQL Server 2008 for minimizing the network bandwidth that is required by database mirroring.

Enhanced Supportability

Additional performance counters for enabling more granular accounting of the time, which is spent across the different stages of DBMS log processing. Dynamic Management Views and extensions of the existing views are included, for exposing additional information that is concerned to mirroring sessions.

Encryption and External Key Management in Microsoft SQL Server 2008

A comprehensive solution for encryption and key management is provided by Microsoft SQL Server 2008. The growing need for greater information security within the data centers of enterprises is managed by security keys. 


This could be done by excellent support given by Microsoft SQL Server 2008, by supporting third-party keys management and hardware security module products.

Plan Freezing in Microsoft SQL Server 2008

Plan freezing is a new concept that allows the DBAs to persist plan guides. These guides could be reverted to when the queries either fail or drain the resources after the upgrade. The stability to queries is achieved by Plan Freezing. 


Several monitoring features for checking when the query is succeeded or failed are included with Plan Freezing.

Role of Resource Governor in Microsoft SQL Server 2008

Resource Governor enables the DBA for managing the work load of Microsoft SQL Server and critical system resource consumption. The limits of CPU and memory which are the incoming sessions to the SQL Server will be controlled by Resource Governor.

The various scenarios that occur when sudden spike in CPU and memory utilization that result in slow responses for querying requests. 

The Resource Governor enables the DBA’s to differentiate the workloads and allocates the shared resources which allow the available CPU and memory resources.

Filtered index in Microsoft SQL Server 2008

Filtered index in SQL Server 2008 is an index WHERE clause. A filtered index is an optimized non-clustered index. It allows for defining the filter predicate with WHERE clause at the time of creating index. The rows from the filtered index are available in B-Tree will contain the rows which only satisfy the criteria of the filter while creating the index.

The advantages of Filtered indexes are :

1. Has only that row which satisfies the filter criteria defined. This results in reducing the storage space need for the index.
2. The filtered index statistics are accurate and more compact. The reason is they consider only the tuples / rows in the filtered index and it reduces the cost of overhead of updating the statistics.
3. The data modification impact is less by using filtered index. Because it is updated only at the time where the data of the index is impacted.
4. The cost of maintenance will be reduced, because only the subset of rows will be considered which rebuilding the index.

Row Level and Page Level Data Compression in Microsoft SQL Server 2008

Data compression is a feature which is used to reduce disk storage space and increase the performance of the query by reducing the I/O operations.

SQL Server 2008 supports two types of compression :

Row-level compression 

Page-level compression.

A row-level and page-level compression takes place on the metadata. Page level compression results in persisting certain common data that affects rows in a single location. 

The compression takes place into number of bits. For example, the length of ‘varchar’ will be stored in 3 bits.

Automatic Auditing in Microsoft SQL Server 2008

Automatic auditing is a true auditing solution for enterprise customers. STL Trace can be used for satisfying several auditing needs. SQL Server Auditing feature offers a number of advantages that attracts and help DBAs with much more easily achievable goals. These include meeting regulatory compliance requirements. These are :

1. Provision for centralized storage of audit logs and integration with system center.
2. Better performance that is noticeable.
3. Permits free-grained auditing in which an audit can be targeted for some specific actions by a principle against a particular object.
4. Allows the objects of audit to be treated as first class database objects, which mean Transact-SQL DDL statements can create these objects.
5. The database object is controlled by database engine’s permission model and enforcement control.
6. A new level permission is featured in SQL Audit – ALTER ANY SERVER AUDIT- which allows a principle to CREATE, ALTER and DROP an Audit Specification object.
7. A database level permission – ALTER ANY DATABASE AUDIT – is introduced to allow a principle to CREATE, ALTER and DROP a Database specification object.

Compound Operators in Microsoft Sql Server 2008

Compound operators feature is enhanced in SQL Server 2008. They are like compound operators in languages like C, C++.

Compound operators are a combination of operator with another operator.

The compound assignment operators are : 

arithmetic operators - +=,-=,*=, /=, %=, and
bitwise operators -&=, ^=,|=

For example

Declare @number int
Set @number = 10
--using Compound assignment operator
Set @number+=200
Select @number as MyResult
Go

Inline variable Assignment Process in Microsoft Sql Server 2008

Inline variable assignment in SQL Server 2008 enables to – declare, and assign the variables in a ‘single line’.

For example :

In Traditional Way :

DECLARE @myVar int
SET @myVar = 5

need to declare the variable and assigning it to the variable to split into 2 

lines and use 2 statements - DECLARE and SET.

In SQL Server 2008, as the name suggests ‘inline’, both declaration and assignment can be given in a single line:

DECLARE @myVar int = 5

How Microsoft Sql Server store pdf files

There are two ways to store pdf files in Microsoft sql Server.

1. To store pdf file in Microsoft Sql Server, create a column as type ‘blob’ in a table. Read the content of the file and save in ‘blob’ type column in a table.
2. Store pdf files in a folder and establish the pointer to link them in the database. 

Order of Query Execution in Microsoft SQL Server

SQL query is excuted in following order.

The query goes to the shared pool that has information like parse tree and execution plan for the corresponding statement. Then validates the SQL statement and validates the source(table).

Acquire locks.

Checks all the privileges.

Execute the query.

Fetch the values for SELECT statement

Displays the fetched values.

To sum up, the sequence is:

SELECT
FROM
WHERE
GROUP BY
HAVING 

Cross Join and Full Outer Join in SQL Server

Cross Join

There is no join conditions specified in Cross Join. Results will be Cartesian product of two tables participating in join. Results in pairs of rows.

Full Outer Join

It is combination of both left and right outer joins. Results in every row from both of the tables , at least once. It assigns NULL for unmatched fields.

Establish Remote Connection to Microsoft SQL Server Database

Establish remote connection in database using following process.

• Use SQL Server Surface Area Configuration Tool for enabling the remote connection in database. 
• Click on Surface Area Configuration for Services and Connections.
• Click on SQLEXPRESS/Database Engine/RemoteConnections
• Select the radio button: Local and Remote Connections and select ‘Using TCP/IP only’ under Local and Remote Connections.
• Click on OK button / Apply button.

Linked Server in Microsoft SQL Server

Microsoft SQL Server supports the connection to different OLE DB on an ad hoc basis. This persistent connection is referred as Linked Server.

To use Linked Server for any OLE DB, follow these steps.

• Open SQL Server Management Studio in SQL Server 2005
• Expand Server Objects in Object Explorer.
• Right-click on Linked Servers. Click on New Linked Server.
• Select General page in the left pane and  
• Type any name for the linked server in the first text box
• Select the Other Data Source option.
• Click on Microsoft Jet 4.0 OLE DB Provider from the Provider list. 
• Type the Excel as the name of the OLE DB data source.
• Type the full path and file name of the Excel file in Data Source box.
• Type the Excel version no. (7.0, 8.0 etc) in the Provider String. 
• Use Excel 8.0 for Excel 2000, Excel 2002 or Excel 97.
• To create a linked server click on OK.

Trace frag in Sqlserver

Temporary setting of specific server characteristics is done by trace tags. 

DBCC TRACEON is the command to set the trace flags. Once activated, trace flag will be in effect until the server is restarted. Trace frags are frequently used for diagnosing performance issues.

For example, the trace flag 3205 is used for disabling hard compression for tape drives, when an instance of SQL Server starts.

Buffer Cache and Log Cache in Sql Server

Buffer cache is a memory pool in which data pages are read. It performance of the buffer cache is indicated as follows: 95% indicates that pages that were found in the memory are 95% of time. Another 5% is needed for physical disk access. If the value falls below 90%, it is the indication of more physical memory requirement on the server.

Log cache is a memory pool used to read and write the log pages. A set of cache pages are available in each log cache. The synchronization is reduced between log and data buffers by managing log caches separately from the buffer cache.

Create CLR Trigger

Steps for creating CLR Trigger


Follow these steps to create a CLR trigger of DML (after) type to perform an insert action:
Create a .NET class of triggering action
Make an assembly (.DLL) from that Class
Enable CLR environment in that database.
Register the assembly in SQL Server
Create CLR Trigger using that assembly

CLR Triggers

CLR Triggers


A CLR trigger could be a Date Definition or Date Manipulation Language trigger or could be an AFTER or INSTEAD OF trigger. Methods written in managed codes that are members of an assembly need to be executed provided the assembly is deployed in SQL 2005 using the CREATE assembly statement. The Microsoft.SqlServer.Server Namespace contains the required classes and enumerations for this objective.

Custom Exceptions

What are Custom Exceptions?


Custom Exceptions are user defined exceptions. There are exceptions other than the predefined ones which need to be taken care of. For example, The rules for the minimum balance in a Salary A/C would be different from that in a Savings A/C due to which these things need to be taken care of during the implementation.

Implement Delegates in C#.NET

Explain how to implement Delegates in C#.NET?


Here is an implementation of a very simple delegate that accepts no parameters.


public delegate void MyDelegate();// Declaration
class MyClass
{
     public static void MyFunc()
     {
         Console.WriteLine("MyFunc Called from a Delegate");
     }
     public static void Main()
     {
            MyDelegate myDel = new MyDelegate(MyFunc);
            myDel();
      }
}

What is assembly in .NET

Define assembly.


An assembly is the primary unit of a .NET application. It includes an assembly manifest that describes the assembly.

How Obfuscator works

Explain how Obfuscator works?


Obfuscators protect the source code from being hacked. Encryption and Decryption are processes from where you can get the data back. However these differ from what obfuscation is. In obfuscation, a program become out of the reach of hackers although it functions the same way it is supposed to. Optimizations too get applied to the process of obfuscation in order to make the program fast.



Obfuscator simply renames all types and namespace etc to meaningless code making it non human readable. This diminishes the possibility of reverse engineering. For example


private void AddEmp(Employee employee) 
{
   {
      this.EmpList.Add(employee);
   }
}


It gets converted to following code.


private void a(a b) 
{
    {
      a.a.Add(b);
    }
}

Abstract classes in C#.NET

Define abstract class in C#.NET.


Abstract classes in C#.NET has following features :
Abstract class cannot be instantiated.
Same concept in C++ known as pure virtual method. 
A class that must be inherited and have the methods over-ridden. 
A class without any implementation.

Constructor and Destructor in .NET

What is a Constructor? 


It is the first method that are called on instantiation of a type. It provides way to set default values for data before the object is available for use. Performs other necessary functions before the object is available for use.


What is a Destructor?


It is called just before an object is destroyed. It can be used to run clean-up code. You can't control when a destructor is called since object clean up by common language runtime.

Implement a Web Service in .NET

Explain with code sample how to Implement a Web Service in .NET?

Following is code in VBScript and C# both respectively.

In VBScript :

<%@ WebService Language="VBScript" Class="KmToMConvert" %>

Imports System

Imports System.Web.Services

Public Class KmToMConvert :Inherits WebService

   <WebMethod()> Public Function KilometerToMeter(ByVal Kilometer As String) As String 

         return (Kilometer * 1000)

         end function

end class

In C# :

[WebService(Namespace = http://tempuri.org/)]

[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]

[System.ComponentModel.ToolboxItem(false)]

// To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line. 

// [System.Web.Script.Services.ScriptService]

public class Service1 : System.Web.Services.WebService

{

     [WebMethod]

     public string HelloWorld()

     {

            return "Hello World";

     }

}

Private virtual methods in C#.NET

Can private virtual methods be overridden in C#.NET? 


No, moreover, you cannot access private methods in inherited classes, They have to be protected in the base class to allow any sort of access. 

Multiple inheritance in C#.Net

Does C#.Net support multiple inheritance?


No, C#.Net does not support multiple inheritance,  but we can use interfaces to achieve functionality of multiple inheritance.

What is serialization

Explain serialization ?


Serialization is a process of converting an object into a stream of bytes. .Net has 2 serializers namely XMLSerializer and SOAP/BINARY Serializer. Serialization is maily used in the concept of .Net Remoting.