(I)What is CODE Access security?
CAS is part of .NET security model that determines whether a piece of code is allowed to run and
what resources it can use while running. Example CAS will allow an application to read but not
to write and delete a file or a resource from a folder..
(I)What is a satellite assembly?
Refer Localization chapter for more details
(A) How to prevent my .NET DLL to be decompiled?
By design, .NET embeds rich Meta data inside the executable code using MSIL. Any one can
easily decompile your DLL back using tools like ILDASM (owned by Microsoft) or Reflector for
.NET which is a third party. Secondly, there are many third party tools, which make this
decompiling process a click away. So any one can easily look in to your assemblies and reverse
engineer them back in to actual source code and understand some real good logic, which can
make it easy to crack your application.
The process by which you can stop this reverse engineering is using “obfuscation”. It is a
technique, which will foil the decompilers. Many third parties (XenoCode, Demeanor for .NET)
provide .NET obfuscation solution. Microsoft includes one that is Dotfuscator Community
Edition with Visual Studio.NET.



(I) what is the difference between Convert.toString and .toString ()
method?
Just to give an understanding of what the above question means see the below code.
int i =0;
MessageBox.Show(i.ToString());
MessageBox.Show(Convert.ToString(i));
We can convert the integer “i” using “i.ToString()” or “Convert.ToString” so what is the
difference. The basic difference between them is “Convert” function handles NULLS while
“i.ToString()” does not it will throw a NULL reference exception error. So as a good coding
practice using “convert” is always safe.
(A) What is Native Image Generator (Ngen.exe)?
The Native Image Generator utility (Ngen.exe) allows you to run the JIT compiler on your
assembly's MSIL and generate native machine code which is cached to disk. After the image is
created .NET runtime will use the image to run the code rather than from the hard disk. Running
Ngen.exe on an assembly potentially allows the assembly to load and execute faster, because it
restores code and data structures from the native image cache rather than generating them
dynamically.
Below are some points to be remembered for Native Image Generator:-
• Native images load faster than MSIL because JIT compilation and type-safety
verifications is eliminated.
• If you are sharing code between process Ngen.exe improves the performance
significantly. As Native image generated Windows PE file so a single DLL file can
be shared across applications. By contrast JIT produced code are private to an
assembly and cannot be shared.
• Native images enable code sharing between processes.
• Native images require more storage space and more time to generate.
• Startup time performance improves lot. We can get considerable gains when
applications share component assemblies because after the first application has been
started the shared components are already loaded for subsequent applications. If
assemblies in an application must be loaded from the hard disk, does not benefit as
much from native images because the hard disk access time shadows everything.
• Assemblies in GAC do not benefit from Native image generator as the loader
performs extra validation on the strong named assemblies thus shadowing the
benefits of Native Image Generator.
• If any of the assemblies change then Native image should also be updated.
• You should have administrative privilege for running Ngen.exe.
• While this can fasten, your application startup times as the code is statically compiled
but it can be somewhat slower than the code generated dynamically by the JIT
compiler. Therefore, you need to compare how the whole application performance
with Ngen.exe and with out it.
To run Ngen.exe, use the following command line.
ngen.exe install
This will synchronously precompile the specified assembly and all of its dependencies. The
generated native images are stored in the native image cache.
In .NET Framework 2.0 there is a service (.NET Runtime Optimization Service) which can
precompile managed assemblies in the background. You can schedule your assemblies to be
precompiled asynchronously by queuing them up with the NGEN Service. Use the following
command line.
Ngen.exe install /queue :
Assemblies, which are critical to your application’s start up time, should be precompiled either
synchronously or asynchronously with priority 1. Priority 1 and 2 assemblies are precompiled
aggressively while Priority 3 assemblies are only precompiled during machine idle-time.
Synchronously precompiling your critical assemblies guarantees that the native images will be
available prior to the first time your end user launches the application but increases the time taken
to run your application's set up program.
You can uninstall an assembly and its dependencies (if no other assemblies are dependent on
them) from the native image cache by running the following command.
ngen.exe uninstall
Native images created using Ngen.exe cannot be deployed; instead, they need to be created on the
end user's machine. These commands therefore need to be issued as part of the application's setup
program. Visual Studio .NET can be used to implement this behavior by defining custom actions
in a Microsoft Installer (MSI) package.
Note:- One of the things the interviewer will expect to be answered is
what scenario will use a Native Image generator. Best is to say that we
first need to test the application performance with Native Image and
with out it and then make a decision. If we see that we have
considerable performance difference we can then use native image
generator.
(A) If we have two version of same assembly in GAC how do we make
a choice?
Note:- We really want to explain this in depth for two reasons. First
we have seen this question been frequently asked and second it’s of
real practical importance. So let’s try to get this fundamental not in
our brain but in our heart.
OK first let us try to understand what the interviewer is talking about. Let us say you have made
an application and its using a DLL which is present in GAC. Now for some reason you make
second version of the same DLL and put it in GAC. Now which DLL does the application refer?
Ok by default, it always uses the version by which you have compiled. However, you want that it
should actually use the older version.
So first, we answer in short. You need to specify “bindingRedirect” in your config file. For
instance in the below case “ClassLibraryVersion” has two versions “1.1.1830.10493” and
“1.0.1830.10461” from which “1.1.1830.10493” is the recent version. However, using the
bindingRedirect we can specify saying “1.0.1830.10461” is the new version. Therefore, the client
will not use “1.1.1830.10493”.





PublicKeyToken="b035c4774706cc72"
Culture="neutral"/>

NewVersion= "1.0.1830.10461"/>




Ok now we will try to answer it in long way by doing a small sample project. Again, this project
will be done using C#. In CD you can find the “Versioning” project. Below is the solution
display, it has two projects one the windows client project (“WindowsVersioningCSharp”) and
second the class library project (“ClassLibraryVersion”) which will be installed in GAC with two
versions.
Figure 1.11: - Solution files for the versioning project.
Our first primary goal is to put two different versions of the same DLL in GAC. So let us make a
walk through of “ClassLibraryVersion” project. It is a very simple class that has “Version”
function, which just sends a string “This is old Version”. Second, we will also just ensure that the
assembly version is “1.0” in the “AssemblyInfo.cs”.
Figure 1.12: - Assembly Version 1.0
Second, in order that we can put a DLL in GAC we need to create generate strong names and
assign the same to the class. For instance, in below figure I have generated the strong name in
“mykey.snk” and assigned the same to the DLL.
Figure 1.13: - Strong naming your DLL
Finally, we need to install the same in GAC using “gacutil” tool. Below is the figure that shows
the same. This installs one version of “ClassLibraryVersion.dll” in GAC.
Figure 1.14: - Install the same in GAC
Now it is time to create a second version of the DLL. So here is what we will do first, we will just
return a different string value for this new version DLL. You can see in the below figure I have
changed the string to return “This is New Version”. Secondly we also need to change the
AssemblyVersion to “1.1.*” in the “AssemblyInfo.cs” file. After that again compile the DLL and
run the “gacutil” to register this second version of the “ClasLibraryVersion.dll”.
Figure 1.15: - Rename to Assembly Version 1.1
Now when we view the GAC we can see two version of “ClassLibraryVersion” i.e.
“1.1.1832.2619” and “1.0.1832.2172” (see figure below).
Figure 1.16: - Two version of “ClassLibraryVersion” dll.
Now that we have created the environment of two version of the same DLL in GAC its time to
look at how client can make a choice between those versions. We need to generate
“publicKeyToken” in order to move ahead. Below is a sample print screen, which shows how we
can use “sn.exe” to generated the public key token. Note the “-T” parameter.
Figure 1.17: - Get the PublicKeyToken
Now let us look at the client that will consume this DLL. I have just added windows form and a
button to the same. In the button click, we will try to call the version function and display the
data. Therefore, below is the code in the first step we create the object of
“ClassLibraryVersion.Class1” and in the second step we call the “Version” function to display
the data.
Figure 1.18: - Client code calling the GAC class.
Now comes the most important part of the whole thing the “app.config” file, which will decide
which version, should be used. So add a new “app.config” file in the project and add the
“AssemblyBinding” section as show below. Therefore, you need to specify the following things:-
• Assembly name in the “name” attribute of “assemblyIdentity” section.
• Specify the “publicKeyToken” value in the “assemblyIndentity”section which was
generated using “sn.exe –T ‘dllname.dll’ “.
• Specify the “oldVersion” and “newVersion” values in the “bindingRedirect” element. So
whatever version we want the client to use should be specified in the “newVersion”
attribute.
You can see from the figure below I have specified that client should use “1.0.*” version.
Therefore, the client will display “This is old Version”.
Figure 1.19: - App.config file using the BindingRedirect
If you run the source code with changing version numbers you can see the below two message
boxes on different version numbers.” This is old version” will be displayed when “newVersion”
value is “1.0.1832.5411” and “This is new Version” will be displayed when “newVersion” value
is “1.1.1832.5427”.
Figure 1.20: - Different Display depending on version numbers
Note:- Source code is provided in “versioning” folder. But as you
compile the DLL’s different publicToken numbers are created so you need
to run the sn.exe in your machine and change the token number
accordingly in the “App.config” file.

Read more ...

Basic .NET Framework

Chapter 1: Basic .NET Framework
(B)What is an IL?
Twist: - What is MSIL or CIL, What is JIT?
(IL)Intermediate Language is also known as MSIL (Microsoft Intermediate Language) or CIL
(Common Intermediate Language). All .NET source code is compiled to IL. 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.
(B)What is a CLR?
Full form of CLR is Common Language Runtime and it forms the heart of the .NET framework.
All Languages have runtime and it is the responsibility of the runtime to take care of the code
execution of the program. For example, VC++ has MSCRT40.DLL, VB6 has MSVBVM60.DLL,
and Java has Java Virtual Machine etc. Similarly, .NET has CLR. Following are the
responsibilities of CLR
• Garbage Collection: - CLR automatically manages memory thus eliminating
memory leaks. When objects are not referred, GC automatically releases those
memories thus providing efficient memory management.
• Code Access Security: - CAS grants rights to program depending on the security
configuration of the machine. Example the program has rights to edit or create a
new file but the security configuration of machine does not allow the program to
delete a file. CAS will take care that the code runs under the environment of
machines security configuration.
• Code Verification: - This ensures proper code execution and type safety while the
code runs. It prevents the source code to perform illegal operation such as accessing
invalid memory locations etc.
• IL (Intermediate language)-to-native translators and optimizer’s:- CLR uses
JIT, compiles the IL code to machine code, and then executes. CLR also determines
depending on platform what is optimized way of running the IL code.
(B)What is CTS?
In order that two language communicate smoothly CLR has CTS (Common Type
System).Example in VB you have “Integer” and in C++ you have “long” these datatypes are not
compatible so the interfacing between them is very complicated. In order that these two different
languages communicate Microsoft introduced Common Type System. So “Integer” data type in
VB6 and “int” data type in C++ will convert it to System.int32, which is data type of CTS. CLS,
which is covered in the coming question, is subset of CTS.
Note: If you have undergone COM programming period interfacing VB6
application with VC++ application was a real pain as the datatype of
both languages did not have a common ground where they can come and
interface, by having CTS interfacing is smooth.
(B)What is a CLS (Common Language Specification)?
This is a subset of the CTS, which all .NET languages are expected to support. It was always a
dream of Microsoft to unite all different languages in to one umbrella and CLS is one-step
towards that. Microsoft has defined CLS, which are nothing but guidelines, that language should
follow so that it can communicate with other .NET languages in a seamless manner.
(B)What is a Managed Code?
Managed code runs inside the environment of CLR i.e. .NET runtime. In short, all IL are
managed code. However, if you are using some third party software example VB6 or VC++
component they are unmanaged code, as .NET runtime (CLR) does not have control over the
source code execution of these languages.
(B)What is a Assembly?
• Assembly is unit of deployment like EXE or a DLL.
• An assembly consists of one or more files (dlls, exe’s, html files etc.), and
represents a group of resources, type definitions, and implementations of those
types. An assembly may also contain references to other assemblies. These
resources, types and references are described in a block of data called a manifest.
The manifest is part of the assembly, thus making the assembly self-describing.
• An assembly is completely self-describing. An assembly contains metadata
information, which is used by the CLR for everything from type checking an
security to actually invoking the components methods. As all information is in
the assembly itself, it is independent of registry. This is the basic advantage as
compared to COM where the version was stored in registry.
• Multiple versions can be deployed side by side in different folders. These different
versions can execute at the same time without interfering with each other.
Assemblies can be private or shared. For private assembly deployment, the
assembly is copied to the same directory as the client program that references it. No
registration is needed, and no fancy installation program is required. When the
component is removed, no registry cleanup is needed, and no uninstall program is
required. Just delete it from the hard drive.
• In shared assembly deployment, an assembly is installed in the Global Assembly
Cache (or GAC). The GAC contains shared assemblies that are globally accessible
to all .NET applications on the machine.
(A) What are the different types of Assembly?
There are two types of assembly Private and Public assembly. A private assembly is normally
used by a single application, and is stored in the application's directory, or a sub-directory
beneath. A shared assembly is normally stored in the global assembly cache, which is a repository
of assemblies maintained by the .NET runtime. Shared assemblies are usually libraries of code,
which many applications will find useful, e.g. Crystal report classes that will be used by all
application for Reports.
(B) What is NameSpace?
Namespace has two basic functionality:-
• NameSpace Logically group types, example System.Web.UI logically groups UI
related features.
• In Object Oriented world, many times it is possible that programmers will use the
same class name. Qualifying NameSpace with class name can avoid this collision.
(B) What is Difference between NameSpace and Assembly?
Following are the differences between namespace and assembly:
• Assembly is physical grouping of logical units, Namespace, logically groups
classes.
• Namespace can span multiple assembly.
(A) If you want to view an Assembly how do you go about it?
Twist: What is ILDASM?
When it comes to understanding of internals, nothing can beat ILDASM. ILDASM converts the
whole ‘exe’ or ‘dll’ in to IL code. To run ILDASM you have to go to ‘C:\Program
Files\Microsoft Visual Studio .NET 2003\SDK\v1.1\Bin’. Note that we had v1.1 you have to
probably change it depending on the type of framework version you have.
If you run IDASM.EXE from the path you will be popped with the IDASM exe program as
shown in figure ILDASM. Click on file and browse to the respective directory for the DLL whose
assembly you want to view. After you select the DLL you will be popped with a tree view details
of the DLL as shown in figure ILDASM. On double clicking on manifest, you will be able to
view details of assembly, internal IL code etc as shown in Figure ‘Manifest View’.
Note : The version number are in the manifest itself which is defined
with the DLL or EXE thus making deployment much easier as compared to
COM where the information was stored in registry. Note the version
information in Figure Manifest view.
You can expand the tree for detail information regarding the DLL like methods, properties,
functions etc.
Figure: - 1.1 ILDASM
Figure: - 1.2 Manifest View
(A) What is Manifest?
Assembly metadata is stored in Manifest. Manifest contains all the metadata needed to do the
following things (See Figure Manifest View for more details):
• Version of assembly.
• Security identity.
• Scope of the assembly.
• Resolve references to resources and classes.
The assembly manifest can be stored in a PE file either (an .exe or) .dll with Microsoft
intermediate language (MSIL code with Microsoft intermediate language (MSIL) code or in a
stand-alone PE file, that contains only assembly manifest information.
(B) Where is version information stored of an assembly?
Version information is stored in assembly inside the manifest.
(I) Is versioning applicable to private assemblies?
Versioning concept is only applicable to global assembly cache (GAC) as private assembly lie in
their individual folders. This does not mean versioning is not needed , you can still version it to
have better version control on the project.
(B) What is GAC?
Twist: In what situations will you register .NET assembly in GAC?
GAC (Global Assembly Cache) is where all shared .NET assembly reside. GAC is used in the
following situations:-
• If the application has to be shared among several application.
• If the assembly has some special security, requirements like only administrators can
remove the assembly. If the assembly is private then a simple delete of assembly
the assembly file will remove the assembly.
Note:- Registering .NET assembly in GAC can lead to the old problem of
DLL hell, where COM version was stored in central registry. So GAC
should be used when absolutely necessary.
(I) what is the concept of strong names?
Twist: - How do we generate strong names?
Twist: - What is use the of SN.EXE?
Twist: - How do we apply strong names to assembly?
Twist: - How do you sign an assembly?
Strong name is similar to GUID (It is supposed to be unique in space and time) in COM
components. Strong Name is only needed when we need to deploy assembly in GAC. Strong
Names helps GAC to differentiate between two versions. Strong names use public key
cryptography (PKC) to ensure that no one can spoof it.PKC use public key and private key
concept.
Following are the step to generate a strong name and sign a assembly:-
• Go to “Visual Studio Command Prompt”. See the below figure “Visual studio Command
prompt”. Note the samples are compiled in 2005 but 2003 users do not have to worry
about it. Same type of command prompt will be seen in 2003 also.
Figure: - 1.3 Visual Studio Command Prompt
• Once you are in command, prompt type sn.exe -k “c:\test.snk”.
Figure: - 1.4 Running SN.EXE
Figure: - 1.5 Successful output of SN.EXE
Figure: - 1.6 Sample view of test.snk file
• After generation of the file you can view the SNK file in a simple notepad
• After the SNK file is generated its time to sign the project with this SNK file.
Figure: - 1.7 Click on project & then click on “classlibrary1 properties” menu to sign the assembly
• Click on project -- properties and the browse the SNK file from the respective folder
and compile the project.
Figure: - 1.8 Click on ‘Use a key file’ to sign the assembly with strong name
(I) How to add and remove an assembly from GAC?
There are two ways to install .NET assembly in GAC:-
• Using Microsoft Installer Package. You can get download of installer from
http://www.microsoft.com.
• Using Gacutil. Go to “Visual Studio Command Prompt” and type “gacutil –i
(assembly name)”, where (assembly name) is the DLL name of the project.
(B) What is Delay signing?
During development process you will need strong name keys to be exposed to developer which is
not a good practice from security aspect point of view.In such situations you can assign the key
later on and during development you an use delay signing
Following is process to delay sign an assembly:
• First obtain your string name keys using SN.EXE.
• Annotate the source code for the assembly with two custom attributes from
System.Reflection: AssemblyKeyFileAttribute, which passes the name of the file
containing the public key as a parameter to its constructor.
AssemblyDelaySignAttribute, which indicates that delay signing, is being used by
passing true as a parameter to its constructor. For example as shown below:
[Visual Basic]


[C#]
[Assembly: AssemblyKeyFileAttribute ("myKey.snk")]
[Assembly: AssemblyDelaySignAttribute (true)]
The compiler inserts the public key into the assembly manifest and reserves space in the PE file
for the full strong name signature. The real public key must be stored while the assembly is built
so that other assemblies that reference this assembly can obtain the key to store in their own
assembly reference.
• Because the assembly does not have a valid strong name signature, the verification of
that signature must be turned off. You can do this by using the –Vr option with the
Strong Name tool. The following example turns off verification for an assembly called
myAssembly.dll.
Sn –Vr myAssembly.dll
• Just before shipping, you submit the assembly to your organization signing authority
for the actual strong name signing using the –R option with the Strong Name tool. The
following example signs an assembly called myAssembly.dll with a strong name using the
sgKey.snk key pair.
Sn -R myAssembly.dll sgKey.snk
(B) What is garbage collection?
Garbage collection is a CLR feature, which automatically manages memory. Programmers forget
to release the objects while coding ... Laziness (Remember in VB6 where one of the good
practices is to set object to nothing). CLR automatically releases objects when they are no longer
in use and refernced. CLR runs on non-deterministic to see the unused objects and cleans them.
One side effect of this non-deterministic feature is that we cannot assume an object is destroyed
when it goes out of the scope of a function. We should avoid using destructors because before GC
destroys the object it first executes destructor in that case it will have to wait for code to release
the unmanaged resource. This results in additional delays in GC. So it is recommended to
implement IDisposable interface, write cleanup code in Dispose method, and call
GC.SuppressFinalize method. Its like instructing GC not to call your constructor. For more details
read why is it preferred to not use finalize for clean up? in OOPS chapter..
(I) Can we force garbage collector to run?
System.GC.Collect () forces garbage collector to run. This is not recommended but can be used if
situations arise.
(B) What is reflection?
All .NET assemblies have metadata information stored about the types defined in modules. This
metadata information can be accessed by mechanism called as “Reflection”. System. Reflection
can be used to browse through the metadata information.
Using reflection, you can also dynamically invoke methods using System.Type.Invokemember.
Below is sample source code if needed you can also get this code from CD provided, go to
“Source code” folder in “Reflection Sample” folder.
Public Class Form1
Private Sub Form1_Load (ByVal sender As System. Object, ByVal e as
System.EventArgs) Handles MyBase.Load
Dim Pobjtype As Type
Dim PobjObject As Object
Dim PobjButtons As New Windows.Forms.Button ()
Pobjtype = PobjButtons.GetType ()
For Each PobjObject in Pobjtype.GetMembers
LstDisplay.Items.Add (PobjObject.ToString ())
Next
End Sub
End Class
Note:- Sample source code is compiled using VB.NET 2005.
Figure:- 1.9 Sample reflection display
Sample source code uses reflection to browse through “Button” class of “Windows.Forms”. If
you compile and run the program following is output as shown in “Sample Reflection Display”.
Using this reflection, you can also dynamically invoke a method using
“System.Type.InvokeMember”.
Note:- System.Type.InvokeMember is left as homework for readers.
Believe me you will enjoy doing it yourself and the concept of
reflection will be clearer.
(A) What are different types of JIT?
JIT compiler is a part of the runtime execution environment.
In Microsoft .NET there are three types of JIT compilers:
• Pre-JIT: - Pre-JIT compiles complete source code into native code in a single
compilation cycle. This is done at the time of deployment of the application.
• Econo-JIT: - Econo-JIT compiles only those methods that are called at runtime.
However, these compiled methods are removed when they are not required.
• Normal-JIT: - Normal-JIT compiles only those methods that are called at runtime.
These methods are compiled the first time they are called, and then they are stored in
cache. When the same methods are called again, the compiled code from cache is used
for execution.
(B) What are Value types and Reference types?
Value types directly contain their data that are either allocated on the stack or allocated in-line in
a structure. So value types are actual data.
Reference types store a reference to the value's memory address, and are allocated on the heap.
Reference types can be self-describing types, pointer types, or interface types. You can view
reference type as pointers to actual data.
Variables that are value types each have their own copy of the data, and therefore operations on
one variable do not affect other variables. Variables that are reference types can refer to the same
object; therefore, operations on one variable can affect the same object referred to by another
variable. All types derive from the System. Object base type.
(B) What is concept of Boxing and Unboxing ?
Boxing and unboxing act like bridges between value type and reference types. When we convert
value type to a reference type it’s termed as boxing. Unboxing is just vice-versa. When an object
box is cast back to its original value type, the value is copied out of the box and into the
appropriate storage location.
Below is sample code of boxing and unboxing where integer data type are converted in to object
and then vice versa.
int i = 1;
object obj = i; // boxing
int j = (int) obj; // unboxing
(B) What is the difference between VB.NET and C#?
Well this is the most debatable issue in .NET community and people treat languages like religion.
It is a subjective matter which language is best. Some like VB.NET’s natural style and some like
professional and terse C# syntaxes. Both use the same framework and speed is very much
equivalents. Still let us list down some major differences between them:-
Advantages VB.NET:-
• Has support for optional parameters that makes COM interoperability much easy.
• With Option Strict off late binding is supported.Legacy VB functionalities can be
used by using Microsoft.VisualBasic namespace.
• Has the WITH construct which is not in C#.
• The VB.NET parts of Visual Studio .NET compiles your code in the background.
While this is considered an advantage for small projects, people creating very
large projects have found that the IDE slows down considerably as the project
gets larger.
Advantages of C#
• XML documentation is generated from source code but this is now been
incorporated in Whidbey.
• Operator overloading which is not in current VB.NET but is been introduced in
Whidbey.
• Use of this statement makes unmanaged resource disposal simple.
• Access to Unsafe code. This allows pointer arithmetic etc, and can improve
performance in some situations. However, it is not to be used lightly, as a lot of
the normal safety of C# is lost (as the name implies).This is the major difference
that you can access unmanaged code in C# and not in VB.NET.
(I) what is the difference between System exceptions and Application
exceptions?
All exception derives from Exception Base class. Exceptions can be generated programmatically
or can be generated by system. Application Exception serves as the base class for all applicationspecific
exception classes. It derives from Exception but does not provide any extended
functionality. You should derive your custom application exceptions from Application Exception.
Application exception is used when we want to define user-defined exception, while system
exception is all that is defined by .

Read more ...

Maximum Capacity Specifications for SQL Server

Maximum Sizes/Numbers
Object	SQL Server 7.0	SQL Server 2000	SQL Server 2005 (32-bit)
Batch size	65,536 * Network Packet Size1	65,536 * Network Packet Size1	65,536 * Network Packet Size1
Bytes per short string column	8,000	8,000	8,000
Bytes per text, ntext, or image column	2 GB-2	2 GB-2	2 GB-2
Bytes per GROUP BY, ORDER BY	8,060	8,060	8,060
Bytes per index	900	9002	9002
Bytes per foreign key	900	900	900
Bytes per primary key	900	900	900
Bytes per row	8,060	8,060	8,0608
Bytes in source text of a stored procedure	Lesser of batch size or 250 MB	Lesser of batch size or 250 MB	Lesser of batch size or 250 MB
Bytes per VARCHAR(MAX), VARBINARY(MAX),XML, TEXT, or IMAGEcolumn	N/A	N/A	2^31-1
Characters per NTEXT or NVARCHAR(MAX)column.	N/A	N/A	2^30-1
Clustered indexes per table	1	1	1
Columns in GROUP BY, ORDER BY	Limited only by number of bytes per GROUP BY, ORDER BY	Limited only by number of bytes per GROUP BY, ORDER BY	Limited only by number of bytes per GROUP BY, ORDER BY
Columns or expressions in a GROUP BY WITH CUBE or WITH ROLLUP statement	10	10	10
Columns per index	16	16	167
Columns per foreign key	16	16	16
Columns per primary key	16	16	16
Columns per base table	1,024	1,024	1,024
Columns per SELECT statement	4,096	4,096	4,096
Columns per INSERT statement	1,024	1,024	1,024
Connections per client	Maximum value of configured connections	Maximum value of configured connections	Maximum value of configured connections
Database size	1,048,516 TB	1,048,516 TB	1,048,516 TB
Databases per instance of SQL Server	32,767	32,767	32,767
Filegroups per database	256	256	32,767
Files per database	32,767	32,767	32,767
File size (data)	32 TB	32 TB	16 TB
File size (log)	4 TB	32 TB	2 terabytes
Foreign key table references per table4	253	253	253
Identifier length (in characters)	128	128	128
Instances per computer	N/A	16	16
Length of a string containing SQL Statements (batch size)	65,536 * Network packet size 1	65,536 * Network packet size 1	65,536 * Network packet size 1
Locks per connection	Maximum locks per server	Maximum locks per server	Maximum locks per server
Locks per instance of SQL Server	2,147,483,647 (static) 40% of SQL Server memory (dynamic)	2,147,483,647 (static) 40% of SQL Server memory (dynamic)	Up to 2,147,483,6475
Nested stored procedure levels6	32	32	32
Nested subqueries	32	32	32
Nested trigger levels	32	32	32
Nonclustered indexes per table	249	249	249
Objects concurrently open in an instance of SQL Server3	2,147,483,647 (or available memory)	2,147,483,647 (or available memory)
Objects in a database	2,147,483,6473	2,147,483,6473	2,147,483,6473
Parameters per stored procedure	1,024	1,024	2,100
Parameters per user-defined function	1,024	1,024	2,100
Partitions per partitioned table or index	N/A	N/A	1,000
REFERENCES per table	253	253	253
Rows per table	Limited by available storage	Limited by available storage	Limited by available storage
Statistics on non-indexed columns			2,000
Tables per database	Limited by number of objects in a database3	Limited by number of objects in a database3	Limited by number of objects in a database3
Tables per SELECT statement	256	256	256
Triggers per table	Limited by number of objects in a database3	Limited by number of objects in a database3	Limited by number of objects in a database3
UNIQUE indexes or constraints per table	249 nonclustered and 1 clustered	249 nonclustered and 1 clustered	249 nonclustered and 1 clustered
XML indexes	N/A	N/A	249

Read more ...