How to get a random row from a table in SQL server ?

Select Top 1 columName from Tablename Order by newid()

Where the session is stored ?

<sessionState  mode =”InProc” cookieless=”False” timeout=”20”/>

By default Cookieless is set to false – so the session Id is stored in Cookies so as long as user navigating the page you can get the session from cookies.

If you set the cookieless =”True” the session id is stored on the browser url before the file name

Ex: http://yourserver/folder/(sessionid)/default.aspx

Drawback is if the user working on the site it has assigned some sessionId on the url  , by giving the different url on Address bar and do some process once the user type same url at that time it will give different sessionid, the previous session will be lost and lost all of previous state.

Default timeout for session is 20 Mins.

Default timeout for Form authentication 30 Mins.

Difference between timestamp and uniqueidentifier ?

TimeStamp:

Size – 8 bytes.
Note based on system date or time
To track the operation in tables on the database level.

Uniqueidentifier

16 – bytes.
Value is based on the computer MAC Addresss and system datetime.
It remains unique in any system in the world.

How to write stored procedure – give syntax ?

Create Procedue <procedureName>

(
Your parameter
)
As
Begin
< your Sql Statement>
End

What is Session ?

                        Session is used to store user details particularly userid and user information so once you set the information  in session you can get the data anywhere in the project .

Reflection ?

You can see the metadata information through reflection mechanism.

Ability to inspect and manipulate programs elements at runtime.

It’s the process of runtime type discovery.

Reflection Services:

Load an assembly at runtime.
Enumerate members of a type.
Instantiate a new object.
Execute the members of an object.
Find out the information about a type .
Find out the information about a assembly
Inspect custome attributes applied to a type.
Create and compile new assembly.

Boxing and Unboxing ?

Boxing:

Implicit conversion of any value type to the type object.
Value types are stored on stack.
Reference or object type are stored on heap.

Unboxing:

Explicit conversion from object type to value type.

What is Finalize and Dispose ?

Finalize:  - Automatically done by garbage collector  to clean the resource memory.

Dispose: - Force the user to dispose objects.

Difference b/w int.Parse and Convert.ToInt32()?

Int.Parse – It can’t handle NULL value.

Convert.ToInt32 – It handles null value and converts value to default value ‘0’.

Define Delegates ?

Delegate is  a class that can hold reference to a method or a function .

Delegate class has a signature and it only reference those method whose signature is compliant with the class.

Delegates are type safe , secure , object oriented.

What is index ?

Index is a pointer in sql server to retrieve the data faster from database.

It’s a physical structure containing pointers to data.

Its created in existing tables to locate rows more quickly and efficiently.

Types of Index:

a)    Clustered Index : It contains only one index ie: Creating a primary key for the table.
Physical sorting of data in the storage media.

b)    Non-Clustered Index :  A table can contains 249 non-clustered index, It can be created outside of the database tables , that contain a sorted list of references to the table itself.

Define View ?

                 It is nothing but a virtual table that represent the data in one or more tables in a alternative way.

Define Stored procedure ?

                                It is a collection of transact sql statements that can take or return user supplied parameters.Maximum size of stored procedure is 128 MB.

What is Common Language Specification (CLS)?

                                      CLS is a set of basic rules, which must be followed by each .NET language to be a .NET- compliant language. It enables interoperability between two .NET-compliant languages. CLS is a subset of CTS; therefore, the languages supported by CLS can use each other's class libraries similar to their own. Application programming interfaces (APIs), which are designed by following the rules defined in CLS can be used by all .NET-compliant languages.

What is Difference between NameSpace and Assembly?

Following are the differences between namespace and assembly: 


Assembly is physical grouping of logical units, Namespace, logically groups classes.
Namespace can span multiple assembly.

What are tuples?

                                             Tuple is a fixed-size collection that can have elements of either same or different data types. Similar to arrays, a user must have to specify the size of a tuple at the time of declaration. Tuples are allowed to hold up from 1 to 8 elements and if there are more than 8 elements, then the 8th element can be defined as another tuple. Tuples can be specified as parameter or return type of a method.

State the differences between the Dispose() and Finalize()?

                                   CLR uses the Dispose and Finalize methods to perform garbage collection of run-time objects of .NET applications.

The Finalize method is called automatically by the runtime. CLR has a garbage collector (GC), which periodically checks for objects in heap that are no longer referenced by any object or program. It calls the Finalize method to free the memory used by such objects. The Dispose method is called by the programmer. Dispose is another method to release the memory used by an object. The Dispose method needs to be explicitly called in code to dereference an object from the heap. The Dispose method can be invoked only by the classes that implement the IDisposable interface.

What is B-Tree?

                              The database server uses a B-tree structure to organize index information. B-Tree generally has following types of index pages or nodes:

root node: A root node contains node pointers to branch nodes which can be only one.
branch node: A branch node contains pointers to leaf nodes or other branch nodes which can be two or more.
leaf nodes: A leaf node contains index items and orizantal pointers to other leaf nodes which can be many.

What is the difference between UNION an UNION ALL?

                            UNION The UNION command is used to select related information from two tables, much like the JOIN command. However, when using the UNION command all selected columns need to be of the same data type. With UNION, only distinct values are selected.
                            UNION ALL The UNION ALL command is equal to the UNION command, except that UNION ALL selects all values.
The difference between Union and Union all is that Union all will not eliminate duplicate rows, instead it just pulls all rows from all tables fitting your query specifics and combines them into a table.

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.

Which are new data types introduced in SQL SERVER 2008?

                                   The GEOMETRY Type: The GEOMETRY data type is a system .NET common language runtime (CLR) data type in SQL Server. This type represents data in a two-dimensional Euclidean coordinate system.
The GEOGRAPHY Type: The GEOGRAPHY datatype’s functions are the same as with GEOMETRY. The difference between the two is that when you specify GEOGRAPHY, you are usually specifying points in terms of latitude and longitude.
New Date and Time Datatypes: SQL Server 2008 introduces four new datatypes related to date and time: DATE, TIME, DATETIMEOFFSET, and DATETIME2.
DATE: The new DATE type just stores the date itself. It is based on the Gregorian calendar and handles years from 1 to 9999.
TIME: The new TIME (n) type stores time with a range of 00:00:00.0000000 through 23:59:59.9999999. The precision is allowed with this type. TIME supports seconds down to 100 nanoseconds. The n in TIME (n) defines this level of fractional second precision, from 0 to 7 digits of precision.
The DATETIMEOFFSET Type: DATETIMEOFFSET (n) is the time-zone-aware version of a datetime datatype. The name will appear less odd when you consider what it really is: a date + a time + a time-zone offset. The offset is based on how far behind or ahead you are from Coordinated Universal Time (UTC) time.
The DATETIME2 Type: It is an extension of the datetime type in earlier versions of SQL Server. This new datatype has a date range covering dates from January 1 of year 1 through December 31 of year 9999. This is a definite improvement over the 1753 lower boundary of the datetime datatype. DATETIME2 not only includes the larger date range, but also has a timestamp and the same fractional precision that TIME type provides

Store Procedure

                                         In a database management system (DBMS), a stored procedure is a set of Structured Query Language (SQL) statements with an assigned name that's stored in the database in compiled form so that it can be shared by a number of programs. The use of stored procedures can be helpful in controlling access to data (end-users may enter or change data but do not write procedures), preserving data integrity (information is entered in a consistent manner), and improving productivity (statements in a stored procedure only need to be written one time).