Friday, February 15, 2019

Dot Net FAQ's

When was .NET announced?



Bill Gates delivered a keynote at Forum 2000, held June 22, 2000, outlining the .NET 'vision'. The July 2000 PDC had a number of sessions on .NET technology, and delegates were given CDs containing a pre-release version of the .NET framework/SDK and Visual Studio.NET.



When was the first version of .NET released?



The final version of the 1.0 SDK and runtime was made publicly available around 6pm PST on 15-Jan-2002. At the same time, the final version of Visual Studio.NET was made available to MSDN subscribers.



What platforms does the .NET Framework run on?



The runtime supports Windows XP, Windows 2000, NT4 SP6a and Windows ME/98. Windows 95 is not supported. Some parts of the framework do not work on all platforms - for example, ASP.NET is only supported on Windows XP and Windows 2000. Windows 98/ME cannot be used for development.IIS is not supported on Windows XP Home Edition, and so cannot be used to host ASP.NET. However, the ASP.NET Web Matrix web server does run on XP Home.The Mono project is attempting to implement the .NET framework on Linux.



What is the CLR?



CLR = Common Language Runtime. The CLR is a set of standard resources that (in theory) any .NET program can take advantage of, regardless of programming language. Robert Schmidt (Microsoft) lists the following CLR resources in his MSDN PDC# article:Object-oriented programming model (inheritance, polymorphism, exception handling, garbage collection) Security model Type system All .NET base classes Many .NET framework classes Development, debugging, and profiling tools Execution and code management IL-to-native translators and optimizers What this means is that in the .NET world, different programming languages will be more equal in capability than they have ever been before, although clearly not all languages will support all CLR services.



What is the CTS?



CTS = Common Type System. This is the range of types that the .NET runtime understands, and therefore that .NET applications can use. However note that not all .NET languages will support all the types in the CTS. The CTS is a superset of the CLS. What is the CLS?CLS = Common Language Specification. This is a subset of the CTS which all .NET languages are expected to support. The idea is that any program, which uses CLS-compliant types, can interoperate with any .NET program written in any language.In theory this allows very tight interop between different .NET languages - for example allowing a C# class to inherit from a VB class.



What is IL?



IL = Intermediate Language. Also known as MSIL (Microsoft Intermediate Language) or CIL (Common Intermediate Language). All .NET source code (of any language) is compiled to IL. The IL is then converted to machine code at the point where the software is installed, or at run-time by a Just-In-Time (JIT) compiler.



What does 'managed' mean in the .NET context?



The term 'managed' is the cause of much confusion. It is used in various places within .NET, meaning slightly different things.



Managed code: The .NET framework provides several core run-time services to the programs that run within it - for example exception handling and security. For these services to work, the code must provide a minimum level of information to the runtime. Such code is called managed code. All C# and Visual Basic.NET code is managed by default. VS7 C++ code is not managed by default, but the compiler can produce managed code by specifying a command-line switch (/com+).



Managed data: This is data that is allocated and de-allocated by the .NET runtime's garbage collector. C# and VB.NET data is always managed. VS7 C++ data is unmanaged by default, even when using the /com+ switch, but it can be marked as managed using the __gc keyword.



Managed classes: This is usually referred to in the context of Managed Extensions (ME) for C++. When using ME C++, a class can be marked with the __gc keyword. As the name suggests, this means that the memory for instances of the class is managed by the garbage collector, but it also means more than that. The class becomes a fully paid-up member of the .NET community with the benefits and restrictions that brings. An example of a benefit is proper interop with classes written in other languages - for example, a managed C++ class can inherit from a VB class. An example of a restriction is that a managed class can only inherit from one base class.



What is reflection?



All .NET compilers produce metadata about the types defined in the modules they produce. This metadata is packaged along with the module (modules in turn are packaged together in assemblies), and can be accessed by a mechanism called reflection. The System.Reflection namespace contains classes that can be used to interrogate the types for a module/assembly. Using reflection to access .NET metadata is very similar to using ITypeLib/ITypeInfo to access type library data in COM, and it is used for similar purposes - e.g. determining data type sizes for marshaling data across context/process/machine boundaries.Reflection can also be used to dynamically invoke methods (see System.Type.InvokeMember ) , or even create types dynamically at run-time (see System.Reflection.Emit.TypeBuilder).



What is the difference between Finalize and Dispose (Garbage collection) ?



Class instances often encapsulate control over resources that are not managed by the runtime, such as window handles (HWND), database connections, and so on. Therefore, you should provide both an explicit and an implicit way to free those resources. Provide implicit control by implementing the protected Finalize Method on an object (destructor syntax in C# and the Managed Extensions for C++). The garbage collector calls this method at some point after there are no longer any valid references to the object. In some cases, you might want to provide programmers using an object with the ability to explicitly release these external resources before the garbage collector frees the object. If an external resource is scarce or expensive, better performance can be achieved if the programmer explicitly releases resources when they are no longer being used. To provide explicit control, implement the Dispose method provided by the IDisposable Interface. The consumer of the object should call this method when it is done using the object. Dispose can be called even if other references to the object are alive. Note that even when you provide explicit control by way of Dispose, you should provide implicit cleanup using the Finalize method. Finalize provides a backup to prevent resources from permanently leaking if the programmer fails to call Dispose.



What is Partial Assembly References?



Full Assembly reference: A full assembly reference includes the assembly's text name, version, culture, and public key token (if the assembly has a strong name). A full assembly reference is required if you reference any assembly that is part of the common language runtime or any assembly located in the global assembly cache.



Partial Assembly reference: We can dynamically reference an assembly by providing only partial information, such as specifying only the assembly name. When you specify a partial assembly reference, the runtime looks for the assembly only in the application directory.We can make partial references to an assembly in your code one of the following ways:-> Use a method such as System.Reflection.Assembly.Load and specify only a partial reference. The runtime checks for the assembly in the application directory.-> Use the System.Reflection.Assembly.LoadWithPartialName method and specify only a partial reference. The runtime checks for the assembly in the application directory and in the global assembly cache Changes to which portion of version number indicates an incompatible change?Major or minor. Changes to the major or minor portion of the version number indicate an incompatible change. Under this convention then, version 2.0.0.0 would be considered incompatible with version 1.0.0.0. Examples of an incompatible change would be a change to the types of some method parameters or the removal of a type or method altogether. Build. The Build number is typically used to distinguish between daily builds or smaller compatible releases. Revision. Changes to the revision number are typically reserved for an incremental build needed to fix a particular bug. You'll sometimes hear this referred to as the "emergency bug fix" number in that the revision is what is often changed when a fix to a specific bug is shipped to a customer.



What is side-by-side execution? Can two application one using private assembly and other using Shared assembly be stated as a side-by-side executables?



Side-by-side execution is the ability to run multiple versions of an application or component on the same computer. You can have multiple versions of the common language runtime, and multiple versions of applications and components that use a version of the runtime, on the same computer at the same time. Since versioning is only applied to shared assemblies, and not to private assemblies, two application one using private assembly and one using shared assembly cannot be stated as side-by-side executables.



Why string are called Immutable data Type ?



The memory representation of string is an Array of Characters, So on re-assigning the new array of Char is formed & the start address is changed . Thus keeping the Old string in Memory for Garbage Collector to be disposed.



What does assert() method do?



In debug compilation, assert takes in a Boolean condition as a parameter, and shows the error dialog if the condition is false. The program proceeds without any interruption if the condition is true.



What's the difference between the Debug class and Trace class?



Documentation looks the same. Use Debug class for debug builds, use Trace class for both debug and release builds.



Why are there five tracing levels in System.Diagnostics.TraceSwitcher?



The tracing dumps can be quite verbose. For applications that are constantly running you run the risk of overloading the machine and the hard drive. Five levels range from None to Verbose, allowing you to fine-tune the tracing activities.



Where is the output of TextWriterTraceListener redirected?



To the Console or a text file depending on the parameter passed to the constructor.



How do assemblies find each other?



By searching directory paths. There are several factors which can affect the path (such as the AppDomain host, and application configuration files), but for private assemblies the search path is normally the application's directory and its sub-directories. For shared assemblies, the search path is normally same as the private assembly path plus the shared assembly cache.



How does assembly versioning work?



Each assembly has a version number called the compatibility version. Also each reference to an assembly (from another assembly) includes both the name and version of the referenced assembly.The version number has four numeric parts (e.g. 5.5.2.33). Assemblies with either of the first two parts different are normally viewed as incompatible. If the first two parts are the same, but the third is different, the assemblies are deemed as 'maybe compatible'. If only the fourth part is different, the assemblies are deemed compatible. However, this is just the default guideline - it is the version policy that decides to what extent these rules are enforced. The version policy can be specified via the application configuration file.



What is garbage collection?



Garbage collection is a system whereby a run-time component takes responsibility for managing the lifetime of objects and the heap memory that they occupy. This concept is not new to .NET - Java and many other languages/runtimes have used garbage collection for some time.



Why doesn't the .NET runtime offer deterministic destruction?



Because of the garbage collection algorithm. The .NET garbage collector works by periodically running through a list of all the objects that are currently being referenced by an application. All the objects that it doesn't find during this search are ready to be destroyed and the memory reclaimed. The implication of this algorithm is that the runtime doesn't get notified immediately when the final reference on an object goes away - it only finds out during the next sweep of the heap.Futhermore, this type of algorithm works best by performing the garbage collection sweep as rarely as possible. Normally heap exhaustion is the trigger for a collection sweep.



Is the lack of deterministic destruction in .NET a problem?



It's certainly an issue that affects component design. If you have objects that maintain expensive or scarce resources (e.g. database locks), you need to provide some way for the client to tell the object to release the resource when it is done. Microsoft recommend that you provide a method called Dispose() for this purpose. However, this causes problems for distributed objects - in a distributed system who calls the Dispose() method? Some form of reference-counting or ownership-management mechanism is needed to handle distributed objects - unfortunately the runtime offers no help with this.



What is serialization?



Serialization is the process of converting an object into a stream of bytes. Deserialization is the opposite process of creating an object from a stream of bytes. Serialization / Deserialization is mostly used to transport objects (e.g. during remoting), or to persist objects (e.g. to a file or database).



Does the .NET Framework have in-built support for serialization?



There are two separate mechanisms provided by the .NET class library - XmlSerializer and SoapFormatter/BinaryFormatter. Microsoft uses XmlSerializer for Web Services, and uses SoapFormatter/BinaryFormatter for remoting. Both are available for use in your own code.



Can I customise the serialization process?



Yes. XmlSerializer supports a range of attributes that can be used to configure serialization for a particular class. For example, a field or property can be marked with the [XmlIgnore] attribute to exclude it from serialization. Another example is the [XmlElement] attribute, which can be used to specify the XML element name to be used for a particular property or field.Serialization via SoapFormatter/BinaryFormatter can also be controlled to some extent by attributes. For example, the [NonSerialized] attribute is the equivalent of XmlSerializer's [XmlIgnore] attribute. Ultimate control of the serialization process can be acheived by implementing the the ISerializable interface on the class whose instances are to be serialized.



Why is XmlSerializer so slow?



There is a once-per-process-per-type overhead with XmlSerializer. So the first time you serialize or deserialize an object of a given type in an application, there is a significant delay. This normally doesn't matter, but it may mean, for example, that XmlSerializer is a poor choice for loading configuration settings during startup of a GUI application.



Why do I get errors when I try to serialize a Hashtable?



XmlSerializer will refuse to serialize instances of any class that implements IDictionary, e.g. Hashtable. SoapFormatter and BinaryFormatter do not have this restriction.



What are attributes?



There are at least two types of .NET attribute. The first type I will refer to as a metadata attribute - it allows some data to be attached to a class or method. This data becomes part of the metadata for the class, and (like other class metadata) can be accessed via reflection. The other type of attribute is a context attribute. Context attributes use a similar syntax to metadata attributes but they are fundamentally different. Context attributes provide an interception mechanism whereby instance activation and method calls can be pre- and/or post-processed. How does CAS work?



The CAS security policy revolves around two key concepts - code groups and permissions. Each .NET assembly is a member of a particular code group, and each code group is granted the permissions specified in a named permission set.For example, using the default security policy, a control downloaded from a web site belongs to the 'Zone - Internet' code group, which adheres to the permissions defined by the 'Internet' named permission set. (Naturally the 'Internet' named permission set represents a very restrictive range of permissions.)



Who defines the CAS code groups?



Microsoft defines some default ones, but you can modify these and even create your own. To see the code groups defined on your system, run 'caspol -lg' from the command-line. On my system it looks like this:Level = MachineCode Groups:1. All code: Nothing 1.1. Zone - MyComputer: FullTrust 1.1.1. Honor SkipVerification requests: SkipVerification 1.2. Zone - Intranet: LocalIntranet 1.3. Zone - Internet: Internet 1.4. Zone - Untrusted: Nothing 1.5. Zone - Trusted: Internet 1.6. StrongName - 0024000004800000940000000602000000240000525341310004000003000000CFCB3291AA715FE99D40D49040336F9056D7886FED46775BC7BB5430BA4444FEF8348EBD06F962F39776AE4DC3B7B04A7FE6F49F25F740423EBF2C0B89698D8D08AC48D69CED0FC8F83B465E0807AC11EC1DCC7D054E807A43336DDE408A5393A48556123272CEEEE72F1660B71927D38561AABF5CAC1DF1734633C602F8F2D5: Note the hierarchy of code groups - the top of the hierarchy is the most general ('All code'), which is then sub-divided into several groups, each of which in turn can be sub-divided. Also note that (somewhat counter-intuitively) a sub-group can be associated with a more permissive permission set than its parent.



How do I define my own code group?Use caspol. For example, suppose you trust code from www.mydomain.com and you want it have full access to your system, but you want to keep the default restrictions for all other internet sites. To achieve this, you would add a new code group as a sub-group of the 'Zone - Internet' group, like this: caspol -ag 1.3 -site www.mydomain.com FullTrust Now if you run caspol -lg you will see that the new group has been added as group 1.3.1: 1.3. Zone - Internet: Internet 1.3.1. Site - www.mydomain.com: FullTrust Note that the numeric label (1.3.1) is just a caspol invention to make the code groups easy to manipulate from the command-line. The underlying runtime never sees it.



How do I change the permission set for a code group?Use caspol. If you are the machine administrator, you can operate at the 'machine' level - which means not only that the changes you make become the default for the machine, but also that users cannot change the permissions to be more permissive. If you are a normal (non-admin) user you can still modify the permissions, but only to make them more restrictive. For example, to allow intranet code to do what it likes you might do this: caspol -cg 1.2 FullTrustNote that because this is more permissive than the default policy (on a standard system), you should only do this at the machine level - doing it at the user level will have no effect. I can't be bothered with all this CAS stuff. Can I turn it off?Yes, as long as you are an administrator. Just run: caspol -s off



Can I look at the IL for an assembly?



Yes. MS supply a tool called Ildasm which can be used to view the metadata and IL for an assembly.



Can source code be reverse-engineered from IL?



Yes, it is often relatively straightforward to regenerate high-level source (e.g. C#) from IL.



How can I stop my code being reverse-engineered from IL?



There is currently no simple way to stop code being reverse-engineered from IL. In future it is likely that IL obfuscation tools will become available, either from MS or from third parties. These tools work by 'optimising' the IL in such a way that reverse-engineering becomes much more difficult.Of course if you are writing web services then reverse-engineering is not a problem as clients do not have access to your IL.



Is there built-in support for tracing/logging?



Yes, in the System.Diagnostics namespace. There are two main classes that deal with tracing - Debug and Trace. They both work in a similar way - the difference is that tracing from the Debug class only works in builds that have the DEBUG symbol defined, whereas tracing from the Trace class only works in builds that have the TRACE symbol defined. Typically this means that you should use System.Diagnostics.Trace.WriteLine for tracing that you want to work in debug and release builds, and System.Diagnostics.Debug.WriteLine for tracing that you want to work only in debug builds.



Can I redirect tracing to a file?



Yes. The Debug and Trace classes both have a Listeners property, which is a collection of sinks that receive the tracing that you send via Debug.WriteLine and Trace.WriteLine respectively. By default the Listeners collection contains a single sink, which is an instance of the DefaultTraceListener class. This sends output to the Win32 OutputDebugString() function and also the System.Diagnostics.Debugger.Log() method. This is useful when debugging, but if you're trying to trace a problem at a customer site, redirecting the output to a file is more appropriate. Fortunately, the TextWriterTraceListener class is provided for this purpose.



What are the contents of assembly?



In general, a static assembly can consist of four elements: i) The assembly manifest, which contains assembly metadata. ii) Type metadata. iii) Microsoft intermediate language (MSIL) code that implements the types. iv) A set of resources.



What is GC (Garbage Collection) and how it works?



One of the good features of the CLR is Garbage Collection, which runs in the background collecting unused object references, freeing us from having to ensure we always destroy them. In reality the time difference between you releasing the object instance and it being garbage collected is likely to be very small, since the GC is always running.[The process of transitively tracing through all pointers to actively used objects in order to locate all objects that can be referenced, and then arranging to reuse any heap memory that was not found during this trace. The common language runtime garbage collector also compacts the memory that is in use to reduce the working space needed for the heap.]



Heap:A portion of memory reserved for a program to use for the temporary storage of data structures whose existence or size cannot be determined until the program is running.



Differnce between Managed code and unmanaged code ?



Managed Code:Code that runs under a "contract of cooperation" with the common language runtime. Managed code must supply the metadata necessary for the runtime to provide services such as memory management, cross-language integration, code access security, and automatic lifetime control of objects. All code based on Microsoft intermediate language (MSIL) executes as managed code.



Un-Managed Code:Code that is created without regard for the conventions and requirements of the common language runtime. Unmanaged code executes in the common language runtime environment with minimal services (for example, no garbage collection, limited debugging, and so on). What is MSIL, IL, CTS and, CLR ? MSIL: (Microsoft intermediate language)When compiling to managed code, the compiler translates your source code into Microsoft intermediate language (MSIL), which is a CPU-independent set of instructions that can be efficiently converted to native code. MSIL includes instructions for loading, storing, initializing, and calling methods on objects, as well as instructions for arithmetic and logical operations, control flow, direct memory access, exception handling, and other operations. Before code can be executed, MSIL must be converted to CPU-specific code, usually by a just-in-time (JIT) compiler. Because the common language runtime supplies one or more JIT compilers for each computer architecture it supports, the same set of MSIL can be JIT-compiled and executed on any supported architecture.When a compiler produces MSIL, it also produces metadata. Metadata describes the types in your code, including the definition of each type, the signatures of each type's members, the members that your code references, and other data that the runtime uses at execution time. The MSIL and metadata are contained in a portable executable (PE) file that is based on and extends the published Microsoft PE and Common Object File Format (COFF) used historically for executable content. This file format, which accommodates MSIL or native code as well as metadata, enables the operating system to recognize common language runtime images. The presence of metadata in the file along with the MSIL enables your code to describe itself, which means that there is no need for type libraries or Interface Definition Language (IDL). The runtime locates and extracts the metadata from the file as needed during execution.



IL: (Intermediate Language)A language used as the output of a number of compilers and as the input to a just-in-time (JIT) compiler. The common language runtime includes a JIT compiler for converting MSIL to native code.



CTS: (Common Type System)The specification that determines how the common language runtime defines, uses, and manages types



CLR: (Common Language Runtime)The engine at the core of managed code execution. The runtime supplies managed code with services such as cross-language integration, code access security, object lifetime management, and debugging and profiling support.



What is Reference type and value type ?



Reference Type:Reference types are allocated on the managed CLR heap, just like object types.A data type that is stored as a reference to the value's location. The value of a reference type is the location of the sequence of bits that represent the type's data. Reference types can be self-describing types, pointer types, or interface types



Value Type:Value types are allocated on the stack just like primitive types in VBScript, VB6 and C/C++. Value types are not instantiated using new go out of scope when the function they are defined within returns.Value types in the CLR are defined as types that derive from system.valueType. A data type that fully describes a value by specifying the sequence of bits that constitutes the value's representation. Type information for a value type instance is not stored with the instance at run time, but it is available in metadata. Value type instances can be treated as objects using boxing.



What is Boxing and unboxing ?



Boxing:The conversion of a value type instance to an object, which implies that the instance will carry full type information at run time and will be allocated in the heap. The Microsoft intermediate language (MSIL) instruction set's box instruction converts a value type to an object by making a copy of the value type and embedding it in a newly allocated object.



Un-Boxing:The conversion of an object instance to a value type.



What is JIT and how is works ?



An acronym for "just-in-time," a phrase that describes an action that is taken only when it becomes necessary, such as just-in-time compilation or just-in-time object activation



What is portable executable (PE) ?



The file format used for executable programs and for files to be linked together to form executable programs



What is strong name?



A name that consists of an assembly's identity—its simple text name, version number, and culture information (if provided)—strengthened by a public key and a digital signature generated over the assembly. Because the assembly manifest contains file hashes for all the files that constitute the assembly implementation, it is sufficient to generate the digital signature over just the one file in the assembly that contains the assembly manifest. Assemblies with the same strong name are expected to be identical



What is global assembly cache?



A machine-wide code cache that stores assemblies specifically installed to be shared by many applications on the computer. Applications deployed in the global assembly cache must have a strong name.



What is difference between constants, readonly and, static ?



Constants: The value can’t be changed



Read-only: The value will be initialized only once from the constructor of the class.



Static: Value can be initialized once.



What is difference between shared and public?



An assembly that can be referenced by more than one application. An assembly must be explicitly built to be shared by giving it a cryptographically strong name.



What is namespace used for loading assemblies at run time and name the methods?



System.Reflection



What are the types of authentication in .net?



We have three types of authentication: 1. Form authentication 2. Windows authentication3. PassportThis has to be declared in web.config file.



What is the difference between a Struct and a Class ?



The struct type is suitable for representing lightweight objects such as Point, Rectangle, and Color. Although it is possible to represent a point as a class, a struct is more efficient in some scenarios. For example, if you declare an array of 1000 Point objects,you will allocate additional memory for referencing each object. In this case, the struct is less expensive.When you create a struct object using the new operator, it gets created and the appropriate constructor is called. Unlike classes, structs can be instantiated without using the new operator. If you do not use new, the fields will remain unassigned and the object cannot be used until all of the fields are initialized. It is an error to declare a default (parameterless) constructor for a struct. A default constructor is always provided to initialize the struct members to their default values.It is an error to initialize an instance field in a struct.There is no inheritance for structs as there is for classes. A struct cannot inherit from another struct or class, and it cannot be the base of a class. Structs, however, inherit from the base class Object. A struct can implement interfaces, and it does that exactly as classes do.A struct is a value type, while a class is a reference type.



How big is the datatype int in .NET?



32 bits.



How big is the char?



16 bits (Unicode).



How do you initiate a string without escaping each backslash?



Put an @ sign in front of the double-quoted string.



What's the access level of the visibility type internal?



Current application.



Explain encapsulation?



The implementation is hidden, the interface is exposed.



What data type should you use if you want an 8-bit value that's signed?



sbyte.



Speaking of Boolean data types, what's different between C# and C/C++?



There's no conversion between 0 and false, as well as any other number and true, like in C/C++.



Where are the value-type variables allocated in the computer RAM?



Stack.



Where do the reference-type variables go in the RAM?



The references go on the stack, while the objects themselves go on the heap.



What is the difference between the value-type variables and reference-type variables in terms of garbage collection?



The value-type variables are not garbage-collected, they just fall off the stack when they fall out of scope, GC picks up the reference-type objects when their references go null.



How do you convert a string into an integer in .NET?



Int32.Parse(string)



How do you box a primitive data type variable?



Assign it to the object, pass an object.



Why do you need to box a primitive variable?



Pass it by reference.



What's the difference between Java and .NET garbage collectors?



Sun left the implementation of a specific garbage collector up to the JRE developer, so their performance varies widely, depending on whose JRE you're using. Microsoft standardized on their garbage collection.



How do you enforce garbage collection in .NET?



System.GC.Collect();



What's different about namespace declaration when comparing that to package declaration in Java?



No semicolon.



What's the difference between const and readonly?



You can initialize readonly variables to some runtime values. Let's say your program uses current date and time as one of the values that won't change. This way you declare public readonly string DateT = new DateTime().ToString().



What happens when you encounter a continue statement inside the for loop?



The code for the rest of the loop is ignored, the control is transferred back to the beginning of the loop.



What's the advantage of using System.Text.StringBuilder over System.String?



StringBuilder is more efficient in the cases, where a lot of manipulation is done to the text. Strings are immutable, so each time it's being operated on, a new instance is created.



Can you store multiple data types in System.Array?



No.



What's the difference between the System.Array.CopyTo() and System.Array.Clone()?



The first one performs a deep copy of the array, the second one performs a shallow copy. How can you sort the elements of the array in descending order? By calling Sort() and then Reverse() methods.



What's the .NET datatype that allows the retrieval of data by a unique key?



HashTable.



What's class SortedList underneath?



A sorted Hash Table.



Will finally block get executed if the exception had not occurred?



Yes.



Can multiple catch blocks be executed?



No, once the proper catch code fires off, the control is transferred to the finally block (if there are any), and then whatever follows the finally block.



Why is it a bad idea to throw your own exceptions?



Well, if at that point you know that an error has occurred, then why not write the proper code to handle that error instead of passing a new Exception object to the catch block? Throwing your own exceptions signifies some design flaws in the project.



What's a delegate?



A delegate object encapsulates a reference to a method. In C++ they were referred to as function pointers.



What's a multicast delegate?



It's a delegate that points to and eventually fires off several methods.



How's the DLL Hell problem solved in .NET?



Assembly versioning allows the application to specify not only the library it needs to run (which was available under Win32), but also the version of the assembly.



What are the ways to deploy an assembly?



An MSI installer, a CAB archive, and XCOPY command.



What's a satellite assembly?



When you write a multilingual or multi-cultural application in .NET, and want to distribute the core application separately from the localized modules, the localized assemblies that modify the core application are called satellite assemblies.



What namespaces are necessary to create a localized application?



System.Globalization, System.Resources.



What does assert() do?



In debug compilation, assert takes in a Boolean condition as a parameter, and shows the error dialog if the condition is false. The program proceeds without any interruption if the condition is true.



What's the difference between the Debug class and Trace class?



Documentation looks the same. Use Debug class for debug builds, use Trace class for both debug and release builds.



Why are there five tracing levels in System.Diagnostics.TraceSwitcher?



The tracing dumps can be quite verbose and for some applications that are constantly running you run the risk of overloading the machine and the hard drive there. Five levels range from None to Verbose, allowing to fine-tune the tracing activities.



Where is the output of TextWriterTraceListener redirected?


To the Console or a text file depending on the parameter passed to the constructor.


What are three test cases you should go through in unit testing?


Positive test cases (correct data, correct output), negative test cases (broken or missing data, proper handling), exception test cases (exceptions are thrown and caught properly).


Can you change the value of a variable while debugging a C# application?


Yes, if you are debugging via Visual Studio.NET, just go to immediate window.


What's the implicit name of the parameter that gets passed into the class' set method?


Value, and it's datatype depends on whatever variable we're changing.


How do you inherit from a class in C#?


Place a colon and then the name of the base class. Notice that it's double colon in C++.


Does C# support multiple inheritance?


No, use interfaces instead.


When you inherit a protected class-level variable, who is it available to?


Derived Classes.


What's the top .NET class that everything is derived from?


System.Object.


How's method overriding different from overloading?


When overriding, you change the method behavior for a derived class. Overloading simply involves having a method with the same name within the class.


What does the keyword virtual mean in the method definition?


The method can be over-ridden.


Can you declare the override method static while the original method is non-static?


No, you can't, the signature of the virtual method must remain the same, and only the keyword virtual is changed to keyword override.


Can you override private virtual methods?


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


Can you prevent your class from being inherited and becoming a base class for some other classes?


Yes, that's what keyword sealed in the class definition is for. The developer trying to derive from your class will get a message: cannot inherit from Sealed class WhateverBaseClassName. It's the same concept as final class in Java.


Can you allow class to be inherited, but prevent the method from being over-ridden?


Yes, just leave the class public and make the method sealed.


Why can't you specify the accessibility modifier for methods inside the interface?


They all must be public. Therefore, to prevent you from getting the false impression that you have any freedom of choice, you are not allowed to specify any accessibility, it's public by default.


Can you inherit multiple interfaces? And if they have conflicting method names?


Yes, why not. It's up to you to implement the method inside your own class, so implementation is left entirely up to you. This might cause a problem on a higher-level scale if similarly named methods from different interfaces expect different data, but as far as compiler cares you're okay.


What's the difference between an interface and abstract class?


In the interface all methods must be abstract, in the abstract class some methods can be concrete. In the interface no accessibility modifiers are allowed, which is ok in abstract classes.


How can you overload a method?


By having Different parameter data types.By having different number of parameters.By having different order of parameters.


If a base class has a bunch of overloaded constructors, and an inherited class has another bunch of overloaded constructors, can you enforce a call from an inherited constructor to an arbitrary base constructor?


Yes, just place a colon, and then keyword base (parameter list to invoke the appropriate constructor) in the overloaded constructor definition inside the inherited class.


What's the difference between System.String and System.StringBuilder classes?


System.String is immutable, System.StringBuilder was designed with the purpose of having a mutable string where a variety of operations can be performed.


When you inherit a protected class-level variable, who is it available to?


The derived class.


Are private class-level variables inherited?


Yes, but they are not accessible. Although they are not visible or accessible via the class interface, they are inherited.


Describe the accessibility modifier "protected internal".


It is available to derived classes and classes within the same Assembly (and naturally from the base class it's declared in).
What's the advantage of using System.Text.StringBuilder over System.String?


StringBuilder is more efficient in cases where there is a large amount of string manipulation. Strings are immutable, so each time it's being operated on, a new instance is created.


Can you store multiple data types in System.Array?


No.


What's the .NET class that allows the retrieval of a data element using a unique key?


HashTable.


Will the finally block get executed if an exception has not occurred?


Yes.


What's an abstract class?


Abstract class is a class that cannot be instantiated. An abstract class is a class that must be inherited and have the methods overridden. An abstract class is essentially a blueprint for a class without any implementation.


When do you absolutely have to declare a class as abstract?


1. When at least one of the methods in the class is abstract. 2. When the class itself is inherited from an abstract class, but not all base abstract methods have been overridden.


What's an interface?


It's an abstract class with public abstract methods all of which must be implemented in the inherited classes.


Why can't you specify the accessibility modifier for methods inside the interface?


They all must be public. Therefore, to prevent you from getting the false impression that you have any freedom of choice, you are not allowed to specify any accessibility, it's public by default.


What's the difference between an interface and abstract class?


In an interface class, all methods must be abstract. In an abstract class some methods can be concrete. In an interface class, no accessibility modifiers are allowed, which is ok in an abstract class.


How is method overriding different from method overloading?


When overriding a method, you change the behavior of the method for the derived class. Overloading a method simply involves having another method with the same name within the class.


Can you write a class without specifying namespace? Which namespace does it belong to by default?


Yes, you can, then the class belongs to global namespace which has no name. For commercial products, naturally, you wouldn't want global namespace.


What is a formatter?


A formatter is an object that is responsible for encoding and serializing data into messages on one end, and deserializing and decoding messages into data on the other end.


Different b/w .NET & J2EE?


Differences between J2EE and the .NET PlatformVendor NeutralityThe .NET platform is not vendor neutral, it is tied to the Microsoft operating systems. But neither are any of the J2EE implementationsMany companies buy into J2EE believing that it will give them vendor neutrality. And, in fact, this is a stated goal of Sun's vision:A wide variety of J2EE product configurations and implementations, all of which meet the requirements of this specification, are possible. A portable J2EE application will function correctly when successfully deployed in any of these products. (ref : Java 2 Platform Enterprise Edition Specification, v1.3, page 2-7 available at http://java.sun.com/j2ee/)Overall MaturityGiven that the .NET platform has a three year lead over J2EE, it should be no surprise to learn that the .NET platform is far more mature than the J2EE platform. Whereas we have high volume highly reliable web sites using .NET technologies (NASDAQ and Dell being among many examples)Interoperability and Web ServicesThe .NET platform eCollaboration model is, as I have discussed at length, based on the UDDI and SOAP standards. These standards are widely supported by more than 100 companies. Microsoft, along with IBM and Ariba, are the leaders in this area. Sun is a member of the UDDI consortium and recognizes the importance of the UDDI standards. In a recent press release, Sun's George Paolini, Vice President for the Java Community Development, says: "Sun has always worked to help establish and support open, standards-based technologies that facilitate the growth of network-based applications, and we see UDDI as an important project to establish a registry framework for business-to-business e-commerce”. But while Sun publicly says it believes in the UDDI standards, in reality, Sun has done nothing whatsoever to incorporate any of the UDDI standards into J2EE.ScalabilityTypical Comparision w.r.t Systems and their costsJ2EECompany System Total Sys. CostBull Escala T610 c/s 16,785 $1,980,179 IBM RS/6000 Enterprise Server F80 16,785 $2,026,681 Bull Escala EPC810 c/s 33,375 $3,037,499 IBM RS/6000 Enterprise Server M80 33,375 $3,097,055 Bull Escala EPC2450 110,403 $9,563,263 IBM IBM eServer pSeries 680 Model 7017-S85 110,403 $9,560,594 .NET platform systemsCompany System Total Sys. CostDell PowerEdge 4400 16,263 $273,487 Compaq ProLiant ML-570-6/700-3P 20,207 $201,717 Dell PowerEdge 6400 30,231 $334,626 IBM Netfinity 7600 c/s 32,377 $443,463 Compaq ProLiant 8500-X550-64P 161,720 $3,534,272 Compaq ProLiant 8500-X700-64P 179,658 $3,546,582 Compaq ProLiant 8500-X550-96P 229,914 $5,305,571 Compaq ProLiant 8500-X700-96P 262,244 $5,305,571 Compaq ProLiant 8500-700-192P 505,303 $10,003,826 Framework SupportThe .NET platform includes such an eCommerce framework called Commerce Server. At this point, there is no equivalent vendor-neutral framework in the J2EE space. With J2EE, you should assume that you will be building your new eCommerce solution from scratchMoreover, no matter what [J2EE] vendor you choose, if you expect a component framework that will allow you to quickly field complete e-business applications, you are in for a frustrating experienceLanguageIn the language arena, the choice is about as simple as it gets. J2EE supports Java, and only Java. It will not support any other language in the foreseeable future. The .NET platform supports every language except Java (although it does support a language that is syntactically and functionally equivalent to Java, C#). In fact, given the importance of the .NET platform as a language independent vehicle, it is likely that any language that comes out in the near future will include support for the .NET platform. Some companies are under the impression that J2EE supports other languages. Although both IBM's WebSphere and BEA's WebLogic support other languages, neither does it through their J2EE technology. There are only two official ways in the J2EE platform to access other languages, one through the Java Native Interface and the other through CORBA interoperability. Sun recommends the later approach. As Sun's Distinguished Scientist and Java Architect Rick Cattell said in a recent interview.PortabilityThe reason that operating system portability is a possibility with J2EE is not so much because of any inherent portability of J2EE, as it is that most of the J2EE vendors support multiple operating systems. Therefore as long as one sticks with a given J2EE vendor and a given database vendor, moving from one operating system to another should be possible. This is probably the single most important benefit in favor of J2EE over the .NET platform, which is limited to the Windows operating system. It is worth noting, however, that Microsoft has submitted the specifications for C# and a subset of the .NET Framework (called the common language infrastructure) to ECMA, the group that standardizes JavaScript.J2EE offers an acceptable solution to ISVs when the product must be marketed to non-Windows customers, particularly when the J2EE platform itself can be bundled with the ISV's product as an integrated offering. If the primary customer base for the ISV is Windows customers, then the .NET platform should be chosen. It will provide much better performance at a much lower cost. Client device independenceThe major difference being that with Java, it is the presentation tier programmer that determines the ultimate HTML that will be delivered to the client, and with .NET, it is a Visual Studio.NET control. This Java approach has three problems. First, it requires a lot of code on the presentation tier, since every possible thin client system requires a different code path. Second, it is very difficult to test the code with every possible thin client system. Third, it is very difficult to add new thin clients to an existing application, since to do so involves searching through, and modifying a tremendous amount of presentation tier logic.The .NET Framework approach is to write device independent code that interacts with visual controls. It is the control, not the programmer, that is responsible for determining what HTML to deliver, based on the capabilities of the client device.. In the .NET Framework model, one can forget that such a thing as HTML even exists!ConclusionSun's J2EE vision is based on a family of specifications that can be implemented by many vendors. It is open in the sense that any company can license and implement the technology, but closed in the sense that it is controlled by a single vendor, and a self contained architectural island with very limited ability to interact outside of itself. One of J2EE's major disadvantages is that the choice of the platform dictates the use of a single programming language, and a programming language that is not well suited for most businesses. One of J2EE's major advantages is that most of the J2EE vendors do offer operating system portability. Microsoft's .NET platform vision is a family of products rather than specifications, with specifications used primarily to define points of interoperability. The major disadvantage of this approach is that if is limited to the Windows platform, so applications written for the .NET platform can only be run on .NET platforms. Their are several important advantages to the .NET platform:* The cost of developing applications is much lower, since standard business languages can be used and device independent presentation tier logic can be written.* The cost of running applications is much lower, since commodity hardware platforms (at 1/5 the cost of their Unix counterparts) can be used.* The ability to scale up is much greater, with the proved ability to support at least ten times the number of clients any J2EE platform has shown itself able to support.* Interoperability is much stronger, with industry standard eCollaboration built into the platform.

What are the Main Features of .NET platform?

Features of .NET Platform are :-Common Language Runtime Explains the features and benefits of the common language runtime, a run-time environment that manages the execution of code and provides services that simplify the development process.

Assemblies Defines the concept of assemblies, which are collections of types and resources that form logical units of functionality. Assemblies are the fundamental units of deployment, version control, reuse, activation scoping, and security permissions.

Application Domains Explains how to use application domains to provide isolation between applications. Runtime Hosts Describes the runtime hosts supported by the .NET Framework, including ASP.NET, Internet Explorer, and shell executables.

Common Type System Identifies the types supported by the common language runtime. Metadata and Self-Describing Components Explains how the .NET Framework simplifies component interoperation by allowing compilers to emit additional declarative information, or metadata, into all modules and assemblies.

Cross-Language Interoperability Explains how managed objects created in different programming languages can interact with one another.

.NET Framework Security Describes mechanisms for protecting resources and code from unauthorized code and unauthorized users. .NET Framework Class Library Introduces the library of types provided by the .NET Framework, which expedites and optimizes the development process and gives you access to system functionality.

What is the use of JIT ?

JIT (Just - In - Time) is a compiler which converts MSIL code to Native Code (ie.. CPU-specific code that runs on the same computer architecture).Because the common language runtime supplies a JIT compiler for each supported CPU architecture, developers can write a set of MSIL that can be JIT-compiled and run on computers with different architectures. However, your managed code will run only on a specific operating system if it calls platform-specific native APIs, or a platform-specific class library.JIT compilation takes into account the fact that some code might never get called during execution. Rather than using time and memory to convert all the MSIL in a portable executable (PE) file to native code, it converts the MSIL as needed during execution and stores the resulting native code so that it is accessible for subsequent calls. The loader creates and attaches a stub to each of a type's methods when the type is loaded. On the initial call to the method, the stub passes control to the JIT compiler, which converts the MSIL for that method into native code and modifies the stub to direct execution to the location of the native code. Subsequent calls of the JIT-compiled method proceed directly to the native code that was previously generated, reducing the time it takes to JIT-compile and run the code.

What meant of assembly & global assembly cache (gac) & Meta data?.

Assembly :-- An assembly is the primary building block of a .NET based application. It is a collection of functionality that is built, versioned, and deployed as a single implementation unit (as one or more files). All managed types and resources are marked either as accessible only within their implementation unit, or as accessible by code outside that unit. It overcomes the problem of 'dll Hell'.

The .NET Framework uses assemblies as the fundamental unit for several purposes: 1.Security 2.Type Identity 3.Reference Scope 4.Versioning 5.Deployment

Global Assembly Cache :-- Assemblies can be shared among multiple applications on the machine by registering them in global Assembly cache(GAC). GAC is a machine wide a local cache of assemblies maintained by the .NET Framework. We can register the assembly to global assembly cache by using gacutil command.We can Navigate to the GAC directory, C:\winnt\Assembly in explore. In the tools menu select the cache properties; in the windows displayed you can set the memory limit in MB used by the GAC

MetaData :--Assemblies have Manifests. This Manifest contains Metadata information of the Module/Assembly as well as it contains detailed Metadata of other assemblies/modules references (exported). It's the Assembly Manifest which differentiates between an Assembly and a Module.

What are the mobile devices supported by .net platform

The Microsoft .NET Compact Framework is designed to run on mobile devices such as mobile phones, Personal Digital Assistants (PDAs), and embedded devices. The easiest way to develop and test a Smart Device Application is to use an emulator.These devices are divided into two main divisions: 1) Those that are directly supported by .NET (Pocket PCs, i-Mode phones, and WAP devices)2) Those that are not (Palm OS and J2ME-powered devices).

What is GUID , why we use it and where?

GUID :-- GUID is Short form of Globally Unique Identifier, a unique 128-bit number that is produced by the Windows OS or by some Windows applications to identify a particular component, application, file, database entry, and/or user. For instance, a Web site may generate a GUID and assign it to a user's browser to record and track the session. A GUID is also used in a Windows registry to identify COM DLLs. Knowing where to look in the registry and having the correct GUID yields a lot information about a COM object (i.e., information in the type library, its physical location, etc.). Windows also identifies user accounts by a username (computer/domain and username) and assigns it a GUID. Some database administrators even will use GUIDs as primary key values in databases. GUIDs can be created in a number of ways, but usually they are a combination of a few unique settings based on specific point in time (e.g., an IP address, network MAC address, clock date/time, etc.).

Describe the difference between inline and code behind - which is best in a loosely coupled solution

ASP.NET supports two modes of page development: Page logic code that is written inside runat="server"> blocks within an .aspx file and dynamically compiled the first time the page is requested on the server. Page logic code that is written within an external class that is compiled prior to deployment on a server and linked ""behind"" the .aspx file at run time.

Whats MSIL, and why should my developers need an appreciation of it if at all?

When compiling the source code to managed code, the compiler translates the source into Microsoft intermediate language (MSIL). This is a CPU-independent set of instructions that can efficiently be converted to native code. Microsoft intermediate language (MSIL) is a translation used as the output of a number of compilers. It is the input to a just-in-time (JIT) compiler. The Common Language Runtime includes a JIT compiler for the conversion of MSIL to native code. Before Microsoft Intermediate Language (MSIL) can be executed it, must be converted by the .NET Framework just-in-time (JIT) compiler to native code. This is CPU-specific code that runs on the same computer architecture as the JIT compiler. Rather than using time and memory to convert all of the MSIL in a portable executable (PE) file to native code. It converts the MSIL as needed whilst executing, then caches the resulting native code so its accessible for any subsequent calls.

How many .NET languages can a single .NET DLL contain?

One

What type of code (server or client) is found in a Code-Behind class?

Server

What is an assembly?

Assemblies are the building blocks of .NET Framework applications; they form the fundamental unit of deployment, version control, reuse, activation scoping, and security permissions. An assembly is a collection of types and resources that are built to work together and form a logical unit of functionality. An assembly provides the common language runtime with the information it needs to be aware of type implementations. To the runtime, a type does not exist outside the context of an assembly.

How many classes can a single .NET DLL contain?

Unlimited.

What is the difference between string and String ?

No difference

What is manifest?

It is the metadata that describes the assemblies.

What is metadata?

Metadata is machine-readable information about a resource, or ""data about data."" Such information might include details on content, format, size, or other characteristics of a datasource. In .NET, metadata includes type definitions, version information, external assembly references, and other standardized information.

What are the types of assemblies?

There are four types of assemblies in .NET:Static assembliesThese are the .NET PE files that you create at compile time. Dynamic assembliesThese are PE-formatted, in-memory assemblies that you dynamically create at runtime using the classes in the System.Reflection.Emit namespace.Private assembliesThese are static assemblies used by a specific application.Public or shared assembliesThese are static assemblies that must have a unique shared name and can be used by any application.An application uses a private assembly by referring to the assembly using a static path or through an XML-based application configuration file. While the CLR doesn't enforce versioning policies-checking whether the correct version is used-for private assemblies, it ensures that an application uses the correct shared assemblies with which the application was built. Thus, an application uses a specific shared assembly by referring to the specific shared assembly, and the CLR ensures that the correct version is loaded at runtime.In .NET, an assembly is the smallest unit to which you can associate a version number;

What are delegates?where are they used ?

A delegate defines a reference type that can be used to encapsulate a method with a specific signature. A delegate instance encapsulates a static or an instance method. Delegates are roughly similar to function pointers in C++; however, delegates are type-safe and secure.

When do you use virutal keyword?.

When we need to override a method of the base class in the sub class, then we give the virtual keyword in the base class method. This makes the method in the base class to be overridable. Methods, properties, and indexers can be virtual, which means that their implementation can be overridden in derived classes.

What are class access modifiers ?

Access modifiers are keywords used to specify the declared accessibility of a member or a type. This section introduces the four access modifiers: ·

Public - Access is not restricted.·

Protected - Access is limited to the containing class or types derived from the containing class.· Internal - Access is limited to the current assembly.·

Protected inertnal - Access is limited to the current assembly or types derived · from the containing class.·

Private - Access is limited to the containing type.

What Is Boxing And Unboxing?

Boxing :- Boxing is an implicit conversion of a value type to the type object typeEg:-Consider the following declaration of a value-type variable:int i = 123;object o = (object) i;Boxing ConversionUnBoxing :- Unboxing is an explicit conversion from the type object to a value typeEg:int i = 123; // A value typeobject box = i; // Boxingint j = (int)box; // Unboxing

What is Value type and refernce type in .Net?.

Value Type : A variable of a value type always contains a value of that type. The assignment to a variable of a value type creates a copy of the assigned value, while the assignment to a variable of a reference type creates a copy of the reference but not of the referenced object.

The value types consist of two main categories: * Stuct Type* Enumeration Type

Reference Type :Variables of reference types, referred to as objects, store references to the actual data. This section introduces the following keywords used to declare reference types: * Class* Interface* DelegateThis section also introduces the following built-in reference types: * object * string

Tuesday, August 19, 2008

Importing and Exporting Data with SQL Server

Type of Transferring Data
Transferring data, whether into or out of a database, can be a complex process. SQL Server provides several tools that help you in a variety of situations, including the Bulk Copy Program, the Select statement and other tools.

In this section we will see the two methods of transferring data. 1) Bulk Copy Program(BCP) and 2) Using Store Procedures.

Bulk Copy Program (BCP)
BCP is a command-line utility that can read and write files in both ASCII format and in native format. (Native format uses variable-length records with field type and length descriptors embedded in them.) BCP using native format is the fastest way to transfer data between databases in SQL Server or Sybase SQL Server. If you use BCP with ASCII files, the ASCII files can be generated as fixed-length records or delimited files, and you can choose the delimiters. We can use BCP when we want to transfer large amount of data from or Into an external file.

BCP Parameters
The command line for BCP is complicated. Improper use of the options can cause BCP to fail and create significant headaches. BCP runs in an interactive command-line mode, which lets you specify the format of the table to dump if a format file is not provided for export, but other options default unless they are specified on the command line. Knowing when an option can be left out is as important as knowing what the defaults are for other values. We describe the command line in detail here to try to prevent your frustration when you first attempt to use it. Be aware that the options are case-sensitive. Note also that the login information comes at the end, whereas in most other utilities it is the first thing you supply.
bcp [[database_name.]owner.]table_name {in out} datafile
[/m maxerrors] [/f formatfile] [/e errfile]
[/F firstrow] [/L lastrow] [/b batchsize]
[/n] [/c] [/E]
[/t field_term] [/r row_term]
[/i inputfile] [/o outputfile]
/U login_id [/P password] [/S servername] [/v] [/a packet_size]
[[database_name.]owner.]table_name — The table that data will be imported to or exported from. If the user’s ID does not have the desired database as its default, the database name must be specified. If the user ID supplied does not own the table, the owner must be specified and the user must have the appropriate permission. To import data into a table, the user must have Insert permission on the table. To export data from a table, the user must have Select permission on the table and on several of the system tables (sysobjects, sysindexes, and syscolumns). Usually the DBO or SA performs this task, in which case, permissions are not an issue.

{in out} — Specifies the direction of the data transfer, into the database table (in) or out of the database table (out).
datafile — The physical location of file to import or export to. This location must be in the path on the machine executing the BCP or you must specify the file with a complete path.
[/m maxerrors] — The number of errors per batch to ignore before stopping. The default value is 10. If you expect errors, you can raise the maximum number of errors to ignore to let the non-error data load completely.
[/f formatfile] — The physical location of a BCP format file to be used for export or import. You must use a format file if you are not using a native format or default character format (see the /n and /c parameters, below). This parameter must be supplied with a complete path. When you export data, the format file is used to create the required format. When you import a file, the format file specifies the fields in the data file and the columns that the data fields are to be imported into. If you do not specify the file when you’re exporting data, BCP will run interactively and create the format file you describe. If a format file is specified during import and the file does not exist, BCP will generate an error.
[/e errfile] — The physical location of a file to which BCP should write error messages and records with errors. You should specify the complete path. The default path is the directory from which the BCP executable is run.
[/F firstrow] — The row on which the import or export is to start. This parameter is particularly useful if you want to skip headers or break up large files into smaller batches.
[/L lastrow] — The row on which to stop the import or export. This parameter is useful for very large loads that need to be broken up.
[/b batchsize] — The number of records to load between committing transactions. The default value is all the records being inserted in one transaction. If this parameter is not set appropriately for the job, the load can cause transaction logs to become full (see the discussion about fast and slow BCP on page 266). It works with the number of errors to determine when it will abort.
[/n] [/c] — Native format or character format. These two options are mutually exclusive; you can specify a format with the /f parameter instead of using either. They are discussed in more detail on page 265.
[/E] — Turns off the identity insert, allowing files containing rows with identity column information to be inserted with their current identity value. Otherwise, the identity column is set according to the seed and increment defined at the time of creation. If you don’t want to preserve the identity column, it is best to use the format file to leave it out of the data export.
[/t field_term] — The field delimiter indicator; tab (\t) is the default. The most common delimiter is the double quote (“ ”) or the comma (,), but be aware that comma-delimited data will fail if the data records include commas. We recommend that you choose a character that you are sure is not in the data, such as the pipe () or tilde (~), if the source of the data supports it.
[/r row_term] — The row terminator, typically a carriage return/linefeed (\r \n).
[/i inputfile] — A file to replace standard input. You can use the file to provide the information BCP requests interactively.
[/o outputfile] — The physical path to the file to which standard output should be redirected. The file logs information such as the number of rows and other non-error output.
/U login_id — A valid login ID and user ID for the database(s) the tables are being imported to or exported from. The system administrator login and ID are used by default.
[/P password] — The correct password for the above user ID. The BCP utility will prompt for a password if none is provided.
[/S servername] — Server name to connect to. The default is the local server. BCP is faster if you use it locally instead of across the network.
[/v] — Gives the current version information for BCP. This information is important for the format files.
[/a packet_size] — Network packet size to be used in transferring data. Values can be between 512 and 36635. Larger packets can improve performance for larger BCP data transfers. Defaults for SQL on Windows NT are now 4096. This parameter can be set for SQL Server under the Configurations menu and overridden on a case-by-case basis through BCP. BCP reports information concerning the network packet size, number of rows processed, and the rate of processing. Microsoft recommends using a value of between 4096 and 8192 for SQL Server running on Windows NT.

ASP.NET XML Web Service Basics

ASP.NET XML Web Service Basics

Since ASP.NET provides the infrastructure for the inner workings of a Web service, developers can focus on implementing the functionality of their specific Web service. Enabling a Web service using ASP.NET entails creating a file with an .asmx file name extension, declaring a Web service in that file and possibly another file, and defining Web service methods. The procedures are listed in Walkthrough: Building a Basic XML Web Service Using ASP.NET and are elaborated upon here.

Declaration of Web Services
When you create a Web service in ASP.NET, you place the required @ WebService directive at the top of a text file with an .asmx file name extension. The presence of the .asmx file and the @ WebService directive correlate the URL address of the Web service with its implementation. You also implement the Web service class that defines the methods and data types visible by Web service clients.

The Web service class you define can be included directly in the .asmx file, or in a separate file. If you use a separate file, it must be compiled into an assembly. Optionally, you can apply a WebService attribute to the class that implements the Web service. The class that implements the Web service can derive from the WebService class.

By applying the optional WebService attribute to a class that implements a Web service, you can set the default XML namespace for the Web service along with a string to describe the Web service. It is highly recommended that this default namespace, which originally is http://tempuri.org, be changed before the Web service is made publicly consumable. This is important because the Web service must be distinguished from other Web services that might inadvertently use the namespace as the default ().

Classes that implement a Web service created using ASP.NET can optionally derive from the WebService class to gain access to the common ASP.NET objects, such as Application, Session, User, and Context. The Application and Session properties provide access to storing and receiving state across the lifetime of the Web application or a particular session. For more information on state management, see How to: Manage State in Web Services Created Using ASP.NET. The User property contains the identity of the caller, if authentication is enabled, for the Web service. With the identity, a Web service can determine whether the request is authorized. For more information on authentication, see Securing XML Web Services. The Context property provides access to all HTTP-specific information about the Web service client's request. For more information on the Context property, see WebService.Context Property.

Definition of Web Service Methods
Methods of a class that implement a Web service do not automatically have the ability to receive Web service requests and send back responses, but with Web services created using ASP.NET, it is very simple to add that capability. Apply a WebMethod attribute to public methods. Methods of a Web service class that can be communicated with over the Web are called Web service methods.

Web service methods are a key part of the messaging infrastructure employed by Web services. That is, a client and a Web service communicate using messages, specifically SOAP messages, by default. Clients send a SOAP request to a Web service and a Web service method typically returns a SOAP response. Web services define the type of messages they accept using operations, as defined by Web Services Description Language (WSDL). These operations correlate to each of the Web service methods within a Web service. Even though each of these Web service methods are defined in ASP.NET using a method of a class, it is important to realize that the data that is eventually communicated over the network must be serialized into XML. As such, it is important to remember that Web services are not a replacement for DCOM, but rather a messaging infrastructure for communicating across platforms using industry standards.

Source: MSDN Library

Monday, July 21, 2008

SQL Server - Basic Interview Questions

What is RDBMS?
Relational Data Base Management Systems (RDBMS) are database management systems that maintain data records and indices in tables. Relationships may be created and maintained across and among the data and tables. In a relational database, relationships between data items are expressed by means of tables. Interdependencies among these tables are expressed by data values rather than by pointers. This allows a high degree of data independence. An RDBMS has the capability to recombine the data items from different files, providing powerful tools for data usage.
What is normalization?
Database normalization is a data design and organization process applied to data structures based on rules that help build relational databases. In relational database design, the process of organizing data to minimize redundancy. Normalization usually involves dividing a database into two or more tables and defining relationships between the tables. The objective is to isolate data so that additions, deletions, and modifications of a field can be made in just one table and then propagated through the rest of the database via the defined relationships.

What are different normalization forms?
1NF: Eliminate Repeating Groups
Make a separate table for each set of related attributes, and give each table a primary key. Each field contains at most one value from its attribute domain.
2NF: Eliminate Redundant Data
If an attribute depends on only part of a multi-valued key, remove it to a separate table.
3NF: Eliminate Columns Not Dependent On Key
If attributes do not contribute to a description of the key, remove them to a separate table. All attributes must be directly dependent on the primary key
BCNF: Boyce-Codd Normal Form
If there are non-trivial dependencies between candidate key attributes, separate them out into distinct tables.
4NF: Isolate Independent Multiple Relationships
No table may contain two or more 1:n or n:m relationships that are not directly related.
5NF: Isolate Semantically Related Multiple Relationships
There may be practical constrains on information that justify separating logically related many-to-many relationships.
ONF: Optimal Normal Form
A model limited to only simple (elemental) facts, as expressed in Object Role Model notation.
DKNF: Domain-Key Normal Form
A model free from all modification anomalies.
Remember, these normalization guidelines are cumulative. For a database to be in 3NF, it must first fulfill all the criteria of a 2NF and 1NF database.

What is Stored Procedure?
A stored procedure is a named group of SQL statements that have been previously created and stored in the server database. Stored procedures accept input parameters so that a single procedure can be used over the network by several clients using different input data. And when the procedure is modified, all clients automatically get the new version. Stored procedures reduce network traffic and improve performance. Stored procedures can be used to help ensure the integrity of the database.
e.g. sp_helpdb, sp_renamedb, sp_depends etc.
What is Trigger?
A trigger is a SQL procedure that initiates an action when an event (INSERT, DELETE or UPDATE) occurs. Triggers are stored in and managed by the DBMS.Triggers are used to maintain the referential integrity of data by changing the data in a systematic fashion. A trigger cannot be called or executed; the DBMS automatically fires the trigger as a result of a data modification to the associated table. Triggers can be viewed as similar to stored procedures in that both consist of procedural logic that is stored at the database level. Stored procedures, however, are not event-drive and are not attached to a specific table as triggers are. Stored procedures are explicitly executed by invoking a CALL to the procedure while triggers are implicitly executed. In addition, triggers can also execute stored procedures.
Nested Trigger: A trigger can also contain INSERT, UPDATE and DELETE logic within itself, so when the trigger is fired because of data modification it can also cause another data modification, thereby firing another trigger. A trigger that contains data modification logic within itself is called a nested trigger.

What is View?
A simple view can be thought of as a subset of a table. It can be used for retrieving data, as well as updating or deleting rows. Rows updated or deleted in the view are updated or deleted in the table the view was created with. It should also be noted that as data in the original table changes, so does data in the view, as views are the way to look at part of the original table. The results of using a view are not permanently stored in the database. The data accessed through a view is actually constructed using standard T-SQL select command and can come from one to many different base tables or even other views.
What is Index?
An index is a physical structure containing pointers to the data. Indices are created in an existing table to locate rows more quickly and efficiently. It is possible to create an index on one or more columns of a table, and each index is given a name. The users cannot see the indexes, they are just used to speed up queries. Effective indexes are one of the best ways to improve performance in a database application. A table scan happens when there is no index available to help a query. In a table scan SQL Server examines every row in the table to satisfy the query results. Table scans are sometimes unavoidable, but on large tables, scans have a terrific impact on performance.

Clustered indexes define the physical sorting of a database table’s rows in the storage media. For this reason, each database table may have only one clustered index.
Non-clustered indexes are created outside of the database table and contain a sorted list of references to the table itself.
What is the difference between clustered and a non-clustered index?
A clustered index is a special type of index that reorders the way records in the table are physically stored. Therefore table can have only one clustered index. The leaf nodes of a clustered index contain the data pages.
A nonclustered index is a special type of index in which the logical order of the index does not match the physical stored order of the rows on disk. The leaf node of a nonclustered index does not consist of the data pages. Instead, the leaf nodes contain index rows.
What are the different index configurations a table can have?
A table can have one of the following index configurations:
No indexes
A clustered index
A clustered index and many nonclustered indexes
A nonclustered index
Many nonclustered indexes
What is cursors?
Cursor is a database object used by applications to manipulate data in a set on a row-by-row basis, instead of the typical SQL commands that operate on all the rows in the set at one time. In order to work with a cursor we need to perform some steps in the following order:
Declare cursor
Open cursor
Fetch row from the cursor
Process fetched row
Close cursor
Deallocate cursor
What is the use of DBCC commands?
DBCC stands for database consistency checker. We use these commands to check the consistency of the databases, i.e., maintenance, validation task and status checks.
E.g. DBCC CHECKDB - Ensures that tables in the db and the indexes are correctly linked.
DBCC CHECKALLOC - To check that all pages in a db are correctly allocated.
DBCC CHECKFILEGROUP - Checks all tables file group for any damage.
What is a Linked Server?
Linked Servers is a concept in SQL Server by which we can add other SQL Server to a Group and query both the SQL Server dbs using T-SQL Statements. With a linked server, you can create very clean, easy to follow, SQL statements that allow remote data to be retrieved, joined and combined with local data. Storped Procedure sp_addlinkedserver, sp_addlinkedsrvlogin will be used add new Linked Server.
What is Collation?
Collation refers to a set of rules that determine how data is sorted and compared. Character data is sorted using rules that define the correct character sequence, with options for specifying casesensitivity, accent marks, kana character types and character width.

What are different type of Collation Sensitivity?
Case sensitivity
A and a, B and b, etc.
Accent sensitivity
a and á, o and ó, etc.
Kana Sensitivity
When Japanese kana characters Hiragana and Katakana are treated differently, it is called Kana
sensitive.
Width sensitivity
When a single-byte character (half-width) and the same character when represented as a double-byte character (full-width) are treated differently then it is width sensitive.
What's the difference between a primary key and a unique key?
Both primary key and unique enforce uniqueness of the column on which they are defined. But by default primary key creates a clustered index on the column, where are unique creates a nonclustered index by default. Another major difference is that, primary key doesn't allow NULLs, but unique key allows one NULL only.
How to implement one-to-one, one-to-many and many-to-many relationships while designing tables?
One-to-One relationship can be implemented as a single table and rarely as two tables with primary and foreign key relationships.
One-to-Many relationships are implemented by splitting the data into two tables with primary key and foreign key relationships.
Many-to-Many relationships are implemented using a junction table with the keys from both the tables forming the composite primary key of the junction table.
What is difference between DELETE & TRUNCATE commands?
Delete command removes the rows from a table based on the condition that we provide with a WHERE clause. Truncate will actually remove all the rows from a table and there will be no data in the table after we run the truncate command.
TRUNCATE
TRUNCATE is faster and uses fewer system and transaction log resources than DELETE.
TRUNCATE removes the data by deallocating the data pages used to store the table’s data, and only the page deallocations are recorded in the transaction log.
TRUNCATE removes all rows from a table, but the table structure and its columns, constraints, indexes and so on remain. The counter used by an identity for new rows is reset to the seed for the column. You cannot use TRUNCATE TABLE on a table referenced by a FOREIGN KEY constraint. Because TRUNCATE TABLE is not logged, it cannot activate a trigger. TRUNCATE can not be Rolled back. TRUNCATE is DDL Command. TRUNCATE Resets identity of the table.
DELETE
DELETE removes rows one at a time and records an entry in the transaction log for each deleted row. If you want to retain the identity counter, use DELETE instead. If you want to remove table definition and its data, use the DROP TABLE statement. DELETE Can be used with or without a WHERE clause DELETE Activates Triggers. DELETE Can be Rolled back.
DELETE is DML Command. DELETE does not reset identity of the table.
Difference between Function and Stored Procedure?
UDF can be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section where as Stored procedures cannot be. UDFs that return tables can be treated as another rowset. This can be used in JOINs with other tables. Inline UDF's can be though of as views that take parameters and can be used in JOINs and other Rowset operations.
When is the use of UPDATE_STATISTICS command?
This command is basically used when a large processing of data has occurred. If a large amount of deletions any modification or Bulk Copy into the tables has occurred, it has to update the indexes to take these changes into account. UPDATE_STATISTICS updates the indexes on these tables accordingly.
What types of Joins are possible with Sql Server?
Joins are used in queries to explain how different tables are related. Joins also let you select data from a table depending upon data from another table.
Types of joins: INNER JOINs, OUTER JOINs, CROSS JOINs. OUTER JOINs are further classified as LEFT OUTER JOINS, RIGHT OUTER JOINS and FULL OUTER JOINS.
What is the difference between a HAVING CLAUSE and a WHERE CLAUSE?
Specifies a search condition for a group or an aggregate. HAVING can be used only with the SELECT statement. HAVING is typically used in a GROUP BY clause. When GROUP BY is not used, HAVING behaves like a WHERE clause. Having Clause is basically used only with the GROUP BY function in a query. WHERE Clause is applied to each row before they are part of the GROUP BY function in a query.
What is sub-query? Explain properties of sub-query.
Sub-queries are often referred to as sub-selects, as they allow a SELECT statement to be executed arbitrarily within the body of another SQL statement. A sub-query is executed by enclosing it in a set of parentheses. Sub-queries are generally used to return a single row as an atomic value, though they may be used to compare values against multiple rows with the IN keyword. A subquery is a SELECT statement that is nested within another T-SQL statement. A subquery SELECT statement if executed independently of the T-SQL statement, in which it is nested, will return a result set. Meaning a subquery SELECT statement can standalone and is not depended on the statement in which it is nested. A subquery SELECT statement can return any number of values, and can be found in, the column list of a SELECT statement, a FROM, GROUP BY, HAVING, and/or ORDER BY clauses of a T-SQL statement. A Subquery can also be used as a parameter to a function call. Basically a subquery can be used anywhere an expression can be used.
Properties of Sub-Query
A subquery must be enclosed in the parenthesis.
A subquery must be put in the right hand of the comparison operator, and
A subquery cannot contain a ORDER-BY clause.
A query can contain more than one sub-queries.
What are types of sub-queries?
Single-row subquery, where the subquery returns only one row.
Multiple-row subquery, where the subquery returns multiple rows,.and
Multiple column subquery, where the subquery returns multiple columns.
What is SQL Profiler?
SQL Profiler is a graphical tool that allows system administrators to monitor events in an instance of Microsoft SQL Server. You can capture and save data about each event to a file or SQL Server table to analyze later. For example, you can monitor a production environment to see which stored procedures are hampering performance by executing too slowly.
Use SQL Profiler to monitor only the events in which you are interested. If traces are becoming too large, you can filter them based on the information you want, so that only a subset of the event data is collected. Monitoring too many events adds overhead to the server and the monitoring process and can cause the trace file or trace table to grow very large, especially when the monitoring process takes place over a long period of time.
What is User Defined Functions?
User-Defined Functions allow to define its own T-SQL functions that can accept 0 or more parameters and return a single scalar data value or a table data type.
What kind of User-Defined Functions can be created?
There are three types of User-Defined functions in SQL Server 2000 and they are Scalar, Inline Table- Valued and Multi-statement Table-valued.
Scalar User-Defined Function
A Scalar user-defined function returns one of the scalar data types. Text, ntext, image and timestamp data types are not supported. These are the type of user-defined functions that most developers are used to in other programming languages. You pass in 0 to many parameters and you get a return value.
Inline Table-Value User-Defined Function
An Inline Table-Value user-defined function returns a table data type and is an exceptional alternative to a view as the user-defined function can pass parameters into a T-SQL select command and in essence provide us with a parameterized, non-updateable view of the underlying tables.
Multi-statement Table-Value User-Defined Function
A Multi-Statement Table-Value user-defined function returns a table and is also an exceptional
alternative to a view as the function can support multiple T-SQL statements to build the final result where the view is limited to a single SELECT statement. Also, the ability to pass parameters into a TSQL select command or a group of them gives us the capability to in essence create a parameterized, non-updateable view of the data in the underlying tables. Within the create function command you must define the table structure that is being returned. After creating this type of user-defined function, It can be used in the FROM clause of a T-SQL command unlike the behavior found when using a stored procedure which can also return record sets.
Which TCP/IP port does SQL Server run on? How can it be changed?
SQL Server runs on port 1433. It can be changed from the Network Utility TCP/IP properties –> Port number.both on client and the server.
What are the authentication modes in SQL Server? How can it be changed?
Windows mode and mixed mode (SQL & Windows).
To change authentication mode in SQL Server click Start, Programs, Microsoft SQL Server and click SQL
Enterprise Manager to run SQL Enterprise Manager from the Microsoft SQL Server program group.
Select the server then from the Tools menu select SQL Server Configuration Properties, and choose the Security page.
Where are SQL server users names and passwords are stored in sql server?
They get stored in master db in the sysxlogins table.
Which command using Query Analyzer will give you the version of SQL server and operating system?
SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY
('edition')

What is SQL server agent?
SQL Server agent plays an important role in the day-to-day tasks of a database administrator (DBA). It is often overlooked as one of the main tools for SQL Server management. Its purpose is to ease the implementation of tasks for the DBA, with its full-function scheduling engine, which allows you to schedule your own jobs and scripts.
Can a stored procedure call itself or recursive stored procedure? How many level SP nesting possible?
Yes. Because Transact-SQL supports recursion, you can write stored procedures that call themselves. Recursion can be defined as a method of problem solving wherein the solution is arrived at by repetitively applying it to subsets of the problem. A common application of recursive logic is to perform numeric computations that lend themselves to repetitive evaluation by the same processing steps. Stored procedures are nested when one stored procedure calls another or executes managed code by referencing a CLR routine, type, or aggregate. You can nest stored procedures and managed code references up to 32 levels.
What is @@ERROR?
The @@ERROR automatic variable returns the error code of the last Transact-SQL statement. If there was no error, @@ERROR returns zero. Because @@ERROR is reset after each Transact-SQL statement, it must be saved to a variable if it is needed to process it further after checking it.
What is Raiseerror?
Stored procedures report errors to client applications via the RAISERROR command. RAISERROR doesn't change the flow of a procedure; it merely displays an error message, sets the @@ERROR automatic variable, and optionally writes the message to the SQL Server error log and the NT application event log.
What is log shipping?
Log shipping is the process of automating the backup of database and transaction log files on a
production SQL server, and then restoring them onto a standby server. Enterprise Editions only
supports log shipping. In log shipping the transactional log file from one server is automatically updated into the backup database on the other server. If one server fails, the other server will have the same db can be used this as the Disaster Recovery plan. The key feature of log shipping is that is will automatically backup transaction logs throughout the day and automatically restore them on the standby server at defined interval.
What is the difference between a local and a global variable?
A local temporary table exists only for the duration of a connection or, if defined inside a compound statement, for the duration of the compound statement.
A global temporary table remains in the database permanently, but the rows exist only within a given connection. When connection are closed, the data in the global temporary table disappears. However, the table definition remains with the database for access when database is opened next time.
What command do we use to rename a db?
sp_renamedb ‘oldname’ , ‘newname’ If someone is using db it will not accept sp_renmaedb. In that case first bring db to single user using sp_dboptions. Use sp_renamedb to rename database. Use sp_dboptions to bring database to multi user mode.
What is sp_configure commands and set commands?
Use sp_configure to display or change server-level settings. To change database-level settings, use ALTER DATABASE. To change settings that affect only the current user session, use the SET statement.
What are the different types of replication? Explain.
The SQL Server 2000-supported replication types are as follows:
· Transactional
· Snapshot
· Merge
Snapshot replication distributes data exactly as it appears at a specific moment in time and does not monitor for updates to the data. Snapshot replication is best used as a method for replicating data that changes infrequently or where the most up-to-date values (low latency) are not a requirement. When synchronization occurs, the entire snapshot is generated and sent to Subscribers. Transactional replication, an initial snapshot of data is applied at Subscribers, and then when data modifications are made at the Publisher, the individual transactions are captured and propagated to Subscribers. Merge replication is the process of distributing data from Publisher to Subscribers, allowing the Publisher and Subscribers to make updates while connected or disconnected, and then merging the updates between sites when they are connected.
What are the OS services that the SQL Server installation adds?
MS SQL SERVER SERVICE, SQL AGENT SERVICE, DTC (Distribution transac co-ordinator)
What are three SQL keywords used to change or set someone’s permissions?
GRANT, DENY, and REVOKE.
What does it mean to have quoted_identifier on? What are the implications of having it off?
When SET QUOTED_IDENTIFIER is ON, identifiers can be delimited by double quotation marks, and literals must be delimited by single quotation marks. When SET QUOTED_IDENTIFIER is OFF, identifiers cannot be quoted and must follow all Transact-SQL rules for identifiers.
What is the STUFF function and how does it differ from the REPLACE function?
STUFF function to overwrite existing characters. Using this syntax, STUFF(string_expression, start, length, replacement_characters), string_expression is the string that will have characters substituted, start is the starting position, length is the number of characters in the string that are substituted, and replacement_characters are the new characters interjected into the string.
REPLACE function to replace existing characters of all occurance. Using this syntax
REPLACE(string_expression, search_string, replacement_string), where every incidence of
search_string found in the string_expression will be replaced with replacement_string.
Using query analyzer, name 3 ways to get an accurate count of the number of records in a table?
SELECT * FROM table1
SELECT COUNT(*) FROM table1
SELECT rows FROM sysindexes WHERE id = OBJECT_ID(table1) AND indid How to rebuild Master Database?
Shutdown Microsoft SQL Server 2000, and then run Rebuildm.exe. This is located in the Program Files\Microsoft SQL Server\80\Tools\Binn directory.
In the Rebuild Master dialog box, click Browse.
In the Browse for Folder dialog box, select the \Data folder on the SQL Server 2000 compact disc or in the shared network directory from which SQL Server 2000 was installed, and then click OK.
Click Settings. In the Collation Settings dialog box, verify or change settings used for the master
database and all other databases.
Initially, the default collation settings are shown, but these may not match the collation selected during setup. You can select the same settings used during setup or select new collation settings. When done, click OK.
In the Rebuild Master dialog box, click Rebuild to start the process.
The Rebuild Master utility reinstalls the master database.
To continue, you may need to stop a server that is running.
Source: http://msdn2.microsoft.com/en-us/library/aa197950(SQL.80).aspx
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.
What are primary keys and foreign keys?
Primary keys are the unique identifiers for each row. They must contain unique values and cannot be null. Due to their importance in relational databases, Primary keys are the most fundamental of all keys and constraints. A table can have only one Primary key. Foreign keys are both a method of ensuring data integrity and a manifestation of the relationship
between tables.

What is data integrity? Explain constraints?
Data integrity is an important feature in SQL Server. When used properly, it ensures that data is
accurate, correct, and valid. It also acts as a trap for otherwise undetectable bugs within applications. A PRIMARY KEY constraint is a unique identifier for a row within a database table. Every table should have a primary key constraint to uniquely identify each row and only one primary key constraint can be created for each table. The primary key constraints are used to enforce entity integrity. A UNIQUE constraint enforces the uniqueness of the values in a set of columns, so no duplicate values are entered. The unique key constraints are used to enforce entity integrity as the primary key constraints.
A FOREIGN KEY constraint prevents any actions that would destroy links between tables with the corresponding data values. A foreign key in one table points to a primary key in another table. Foreign keys prevent actions that would leave rows with foreign key values when there are no primary keys with that value. The foreign key constraints are used to enforce referential integrity. A CHECK constraint is used to limit the values that can be placed in a column. The check constraints are used to enforce domain integrity.
A NOT NULL constraint enforces that the column will not accept null values. The not null constraints are used to enforce domain integrity, as the check constraints.
What are the properties of the Relational tables?
Relational tables have six properties:
· Values are atomic.
· Column values are of the same kind.
· Each row is unique.
· The sequence of columns is insignificant.
· The sequence of rows is insignificant.
· Each column must have a unique name.
What is De-normalization?
De-normalization is the process of attempting to optimize the performance of a database by adding redundant data. It is sometimes necessary because current DBMSs implement the relational model poorly. A true relational DBMS would allow for a fully normalized database at the logical level, while providing physical storage of data that is tuned for high performance. De-normalization is a technique to move from higher to lower normal forms of database modeling in order to speed up database access.
How to get @@error and @@rowcount at the same time?
If @@Rowcount is checked after Error checking statement then it will have 0 as the value of
@@Recordcount as it would have been reset. And if @@Recordcount is checked before the error-checking statement then @@Error would get reset.
To get @@error and @@rowcount at the same time do both in same statement and store them in local
variable. SELECT @RC = @@ROWCOUNT, @ER = @@ERROR
What is Identity?
Identity (or AutoNumber) is a column that automatically generates numeric values. A start and
increment value can be set, but most DBA leave these at 1. A GUID column also generates numbers, the value of this cannot be controled. Identity/GUID columns do not need to be indexed.
What is a Scheduled Jobs or What is a Scheduled Tasks?
Scheduled tasks let user automate processes that run on regular or predictable cycles. User can
schedule administrative tasks, such as cube processing, to run during times of slow business activity. User can also determine the order in which tasks run by creating job steps within a SQL Server Agent job. E.g. Back up database, Update Stats of Tables. Job steps give user control over flow of execution. If one job fails, user can configure SQL Server Agent to continue to run the remaining tasks or to stop execution.
What is a table called, if it does not have neither Cluster nor Non-cluster Index? What is it used for?
Unindexed table or Heap. Microsoft Press Books and Book On Line (BOL) refers it as Heap.
A heap is a table that does not have a clustered index and, therefore, the pages are not linked by
pointers. The IAM pages are the only structures that link the pages in a table together.
Unindexed tables are good for fast storing of data. Many times it is better to drop all indexes from table and than do bulk of inserts and to restore those indexes after that.
What is BCP? When does it used?
BulkCopy is a tool used to copy huge amount of data from tables and views. BCP does not copy the structures same as source to destination.
How do you load large data to the SQL server database?
BulkCopy is a tool used to copy huge amount of data from tables. BULK INSERT command helps to Imports a data file into a database table or view in a user-specified format.
Can we rewrite subqueries into simple select statements or with joins?
Subqueries can often be re-written to use a standard outer join, resulting in faster performance. As we may know, an outer join uses the plus sign (+) operator to tell the database to return all non-matching rows with NULL values. Hence we combine the outer join with a NULL test in the WHERE clause to reproduce the result set without using a sub-query.
Can SQL Servers linked to other servers like Oracle?
SQL Server can be lined to any server provided it has OLE-DB provider from Microsoft to allow a link.
E.g. Oracle has a OLE-DB provider for oracle that Microsoft provides to add it as linked server to SQL Server group.
How to know which index a table is using?
SELECT table_name,index_name FROM user_constraints
How to copy the tables, schema and views from one SQL server to another?
Microsoft SQL Server 2000 Data Transformation Services (DTS) is a set of graphical tools and
programmable objects that lets user extract, transform, and consolidate data from disparate sources into single or multiple destinations.
What is Self Join?
This is a particular case when one table joins to itself, with one or two aliases to avoid confusion. A self join can be of any type, as long as the joined tables are the same. A self join is rather unique in that it involves a relationship with only one table. The common example is when company have a hierarchal reporting structure whereby one member of staff reports to another.
What is Cross Join?
A cross join that does not have a WHERE clause produces the Cartesian product of the tables involved in the join. The size of a Cartesian product result set is the number of rows in the first table multiplied by the number of rows in the second table. The common example is when company wants to combine each product with a pricing table to analyze each product at each price.
Which virtual table does a trigger use?
Inserted and Deleted.
List few advantages of Stored Procedure.
· Stored procedure can reduced network traffic and latency, boosting application performance.
· Stored procedure execution plans can be reused, staying cached in SQL Server's memory,
reducing server overhead.
· Stored procedures help promote code reuse.
· Stored procedures can encapsulate logic. You can change stored procedure code without
affecting clients.
· Stored procedures provide better security to your data.
What is DataWarehousing?
· Subject-oriented, meaning that the data in the database is organized so that all the data
elements relating to the same real-world event or object are linked together;
· Time-variant, meaning that the changes to the data in the database are tracked and recorded
so that reports can be produced showing changes over time;
· Non-volatile, meaning that data in the database is never over-written or deleted, once
committed, the data is static, read-only, but retained for future reporting;
· Integrated, meaning that the database contains data from most or all of an organization's
operational applications, and that this data is made consistent.
What is OLTP(OnLine Transaction Processing)?
In OLTP - online transaction processing systems relational database design use the discipline of data modeling and generally follow the Codd rules of data normalization in order to ensure absolute data integrity. Using these rules complex information is broken down into its most simple structures (a table) where all of the individual atomic level elements relate to each other and satisfy the normalization rules.
How do SQL server 2000 and XML linked? Can XML be used to access data?
FOR XML (ROW, AUTO, EXPLICIT) You can execute SQL queries against existing relational databases to return results as XML rather than standard rowsets. These queries can be executed directly or from within stored procedures. To retrieve XML results, use the FOR XML clause of the SELECT statement and specify an XML mode of RAW, AUTO, or EXPLICIT.
OPENXML. OPENXML is a Transact-SQL keyword that provides a relational/rowset view over an in-memory XML document. OPENXML is a rowset provider similar to a table or a view. OPENXML provides a way to access XML data within the Transact-SQL context by transferring data from an XML document into the relational tables. Thus, OPENXML allows you to manage an XML document and its interaction with the relational environment.
What is an execution plan? When would you use it? How would you view the execution plan?
An execution plan is basically a road map that graphically or textually shows the data retrieval methods chosen by the SQL Server query optimizer for a stored procedure or ad-hoc query and is a very useful tool for a developer to understand the performance characteristics of a query or stored procedure since the plan is the one that SQL Server will place in its cache and use to execute the stored procedure or query. From within Query Analyzer is an option called "Show Execution Plan" (located on the Query drop-down menu). If this option is turned on it will display query execution plan in separate window when query is ran again.