What is the basic functions for master, msdb, model, tempdb databases?

The Master database holds information for all databases located on the SQL Server instance and is the glue that holds the engine together. Because SQL Server cannot start without a functioning master database, you must administer this database with care.

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.

The tempdb holds temporary objects such as global and local temporary tables and stored procedures.

The model is essentially a template database used in the creation of any new user database created in the instance.

Why the Stored Procedure takes long time to run for first time?

The reason for the same is because Stored Procedures are compiled when it runs first time. When I answered the same, he replied that Stored Procedures are pre-compiled, and this should not be the case. In fact, Stored Procedures are not pre-compiled; they compile only during their first time execution.

There is a misconception that stored procedures are pre-compiled. They are not pre-compiled, but compiled only during the first run. For every subsequent runs, it is for sure pre-compiled.

If you create any SP, you will find that there is no cache entry for the execution of that SP.

After running the SP for the first time, the entry for the cache is made in the system.

If we see the following script, we can notice the different of cache when SP was created and SP was executed.

Garbage Collection in .NET

Garbage collection is a process of releasing the memory used by the objects, which are no longer referenced.

The Database Designer

The Database Designer is a visual tool that allows you to design and visualize a database to which you are connected. When designing a database, you can use Database Designer to create, edit, or delete tables, columns, keys, indexes, relationships, and constraints. To visualize a database, you can create one or more diagrams illustrating some or all of the tables, columns, keys, and relationships in it.

Difference betwen sql 2005 & 2008?

difference b/w ddl trigger and dml triggers?

What are magic Tables?

Magic tables are nothing but INSERTED, DELETED table scope level, These are not physical tables, only Internal tables.

select * from  inserted

select * from deleted

This two tables inserted and deleted are called magic tables.

Easy example is

Over loading is Early binding

Over ridding is late binding

what is ACID?

atomicity, consistency, isolation, and durability (ACID) .

what is load balancing?

How to make visible function in IIS, webservice on different server ?

<webServices>

     <soapExtensionTypes>

     </soapExtensionTypes>

     <protocols>

       <add name="HttpGet"/>

       <add name="HttpPost"/>

       <add name="HttpSoap"/>

     </protocols>

    </webServices>

difference B/W convert.tostring() and .tostring()?

convert.tostring() check null to .tostring() not

What is the difference between INT, INT16, INT32 and INT64?

All the type of integer has different range of storage capacity

Type Capacity

 Int 16 -- (-32768 to +32787)

  Int 32 -- (-2,147,483684 to +2,147,483683)

  Int 64 -- (-9223372036854775808 to +9223372036854775807)

Int=Int32 --> Original long type

Int16 --> Original int

Int64 --> New data type become available after 64 bit systems

"int" is only available for backward compatibility. We should be really using new int types to make our programs more precise.