Add Auto Increment Column or Set Auto Increment Column SQL Server Management Studio

Add Auto Increment Column or Set Auto Increment Column SQL Server Management Studio

Here I will explain how to set or add auto increment column in SQL server or create auto increment column in SQL server 2008 using management studio.

Description:

In many situations we will insert records in table during that time we need to set unique value for that record if we use auto increment column then automatically generate unique number for newly insert record in table (Like generate EmployeeID of each employee whenever employee record inserted).

To set auto increment column in table we have different methods

First Method

 After completion of table creation open table in design mode and select column for which we need to set identity column. Now go to column properties in that select Identity Specification >> Is Identity

Now change the property of Is Identity from “NO” to “YES”

 After change Is Identity property to “YES” Give Identity Increment value (This is the value which will add for every row inserted) generally this value will be 1 whenever new record inserted column value increases 1 if you want to more value change that value.

If you observe we have another property called Identity Seed this property is used to set starting value of column. Suppose if you want to start column value from 1000 change Identity Seed value from 1 to 1000.

Now our column is ready here we need to remember one point that is to insert new record in table we don’t want to specify value for the column which contains Identity property automatically unique value will added for that column.

Ex:  Insert into UserDetails (UserName, FirstName, LastName, Email) VALUES (Vamsi Indla, Vamsi, Indla, vamsi@gmail.com)

After insertion our table will be like this

UserId	UserName	FirstName	LastName	Email
1	Vamsi Indla	Vamsi	Indla	vamsi@gmail.com

Second Method

We can set auto increment column through query like this

CREATE TABLE UserDetails

(
UserId int PRIMARY KEY IDENTITY,
UserName varchar(200),

FirstName varchar(255),

LastName varchar(255),
Email varchar(255)
)

What is the .NET Framework?

What is the .NET Framework?

The .NET Framework is a new and revolutionary platform created by Microsoft for developing applications.  

• It is a platform for application developers.
• It is a Framework that supports Multiple Language and Cross language integration.
• IT has IDE (Integrated Development Environment).
• Framework is a set of utilities or can say building blocks of your application system.
• .NET Framework provides GUI in a GUI manner.
• .NET is a platform independent but with help of Mono Compilation System (MCS). MCS is a middle level interface.
• .NET Framework provides interoperability between languages i.e. Common Type System (CTS) .
• .NET Framework also includes the .NET Common Language Runtime (CLR), which is responsible for maintaining the execution of all applications developed using the .NET library.
• The .NET Framework consists primarily of a gigantic library of code.

Definition:

 A programming infrastructure created by Microsoft for building, deploying, and running applications and services that use .NET technologies, such as desktop applications and Web services.

Cross Language integration

You can use a utility of a language in another language (It uses Class Language Integration).

.NET Framework includes no restriction on the type of applications that are possible. The .NET Framework allows the creation of Windows applications, Web applications, Web services, and lot more.

The .NET Framework has been designed so that it can be used from any language, including C#, C++, Visual Basic, JScript, and even older languages such as COBOL.

Difference between Visual Studio and Visual Studio .NET

Visual Studio	Visual Studio
It is object based	It is object oriented
Internet based application - Web Application - Web services - Internet enable application - Third party API - Peer to peer Application	All developing facilities in internet based application
Poor error handling Exception/Error	Advance error handler and debugger
Memory Management System Level Task	Memory Management Application Domain with help of GC (Garbage Collector)
DLL HELL	VS .NET has solved DLL HELL Problem

Simple explanation of definition used in the above comparision:

Web Application

All websites are example of web application. They use a web server.

Internet Enabled Application

They are desktop application. Yahoo messenger is an example of desktop application.

Peer to Peer

Communication through computers through some system.

Web Services

It doesn't use web-based server. Internet payment systems are example of web services.

DLL Hell

"DLL Hell" refers to the set of problems caused when multiple applications attempt to share a common component like a dynamic link library (DLL) or a Component Object Model (COM) class.

The reason for this issue was that the version information about the different components of an application was not recorded by the system. (Windows Registry cannot support the multiple versions of same COM component this is called the dll hell problem.)

.Net Framework provides operating systems with a Global Assembly Cache (GAC). This Cache is a repository for all the .Net components that are shared globally on a particular machine. When a .Net component is installed onto the machine, the Global Assembly Cache looks at its version, its public key, and its language information and creates a strong name for the component. The component is then registered in the repository and indexed by its strong name, so there is no confusion between different versions of the same component, or DLL.

Architecture of .NET Framework

 

Architecture of CLR

CLS (Common Language Specification)

It is a subset of CTS. All instruction is in CLS i.e. instruction of CTS is written in CLS.

Code Manager

Code manager invokes class loader for execution.

.NET supports two kind of coding

1) Managed Code

2) Unmanaged Code

Managed Code

The resource, which is with in your application domain is, managed code. The resources that are within domain are faster.

The code, which is developed in .NET framework, is known as managed code. This code is directly executed by CLR with help of managed code execution. Any language that is written in .NET Framework is managed code.

Managed code uses CLR which in turns looks after your applications by managing memory, handling security, allowing cross - language debugging, and so on.

 

Unmanaged Code

The code, which is developed outside .NET, Framework is known as unmanaged code.

Applications that do not run under the control of the CLR are said to be unmanaged, and certain languages such as C++ can be used to write such applications, which, for example, access low - level functions of the operating system. Background compatibility with code of VB, ASP and COM are examples of unmanaged code.

Unmanaged code can be unmanaged source code and unmanaged compile code.

Unmanaged code is executed with help of wrapper classes.

Wrapper classes are of two types: CCW (COM callable wrapper) and RCW (Runtime Callable Wrapper).

Wrapper is used to cover difference with the help of CCW and RCW.

COM callable wrapper unmanaged code

Runtime Callable Wrapper unmanaged code



Native Code

The code to be executed must be converted into a language that the target operating system understands, known as native code. This conversion is called compiling code, an act that is performed by a compiler.

Under the .NET Framework, however, this is a two - stage process. With help of MSIL and JIT.

MSIL (Microsoft Intermediate Language)

It is language independent code. When you compile code that uses the .NET Framework library, you don't immediately create operating system - specific native code.

Instead, you compile your code into Microsoft Intermediate Language (MSIL) code. The MSIL code is not specific to any operating system or to any language.

JIT (Just-in-Time)

Just - in - Time (JIT) compiler, which compiles MSIL into native code that is specific to the OS and machine architecture being targeted. Only at this point can the OS execute the application. The just - in - time part of the name reflects the fact that MSIL code is only compiled as, and when, it is needed.

In the past, it was often necessary to compile your code into several applications, each of which targeted a specific operating system and CPU architecture. Often, this was a form of optimization.

This is now unnecessary, because JIT compilers (as their name suggests) use MSIL code, which is independent of the machine, operating system, and CPU. Several JIT compilers exist, each targeting a different architecture, and the appropriate one will be used to create the native code required.

The beauty of all this is that it requires a lot less work on your part - in fact, you can forget about system - dependent details and concentrate on the more interesting functionality of your code.

JIT are of three types:

1. Pre JIT
2. Econo JIT
3. Normal JIT

Pre JIT

It converts all the code in executable code and it is slow

Econo JIT

It will convert the called executable code only. But it will convert code every time when a code is called again.

Normal JIT

It will only convert the called code and will store in cache so that it will not require converting code again. Normal JIT is fast.

Assemblies

When you compile an application, the MSIL code created is stored in an assembly. Assemblies include both executable application files that you can run directly from Windows without the need for any other programs (these have a .exe file extension), and libraries (which have a .dll extension) for use by other applications.

In addition to containing MSIL, assemblies also include meta information (that is, information about the information contained in the assembly, also known as metadata) and optional resources (additional data used by the MSIL, such as sound files and pictures).

The meta information enables assemblies to be fully self - descriptive. You need no other information to use an assembly, meaning you avoid situations such as failing to add required data to the system registry and so on, which was often a problem when developing with other platforms.

This means that deploying applications is often as simple as copying the files into a directory on a remote computer. Because no additional information is required on the target systems, you can just run an executable file from this directory and (assuming the .NET CLR is installed) you're good to go.

Of course, you won't necessarily want to include everything required to run an application in one place. You might write some code that performs tasks required by multiple applications. In situations like that, it is often useful to place the reusable code in a place accessible to all applications. In the .NET Framework, this is the Global Assembly Cache (GAC). Placing code in the GAC is simple - you just place the assembly containing the code in the directory containing this cache.

Garbage Collection (GC)

One of the most important features of managed code is the concept of garbage collection. This is the .NET method of making sure that the memory used by an application is freed up completely when the application is no longer in use.

Prior to .NET this was mostly the responsibility of programmers, and a few simple errors in code could result in large blocks of memory mysteriously disappearing as a result of being allocated to the wrong place in memory. That usually meant a progressive slowdown of your computer followed by a system crash.

.NET garbage collection works by inspecting the memory of your computer every so often and removing anything from it that is no longer needed. There is no set time frame for this; it might happen thousands of times a second, once every few seconds, or whenever, but you can rest assured that it will happen.

Will try to explain the processing in terms of C# code which is written using .NET Framework.

Step 1- Application code is written using a .NET - compatible language C#.

 

Step 2 - Code is compiled into MSIL, which is stored in an assembly (see Figure 1 - 2).

 

Step 3 - When this code is executed (either in its own right if it is an executable or when it is used from other code), it must first be compiled into native code using a JIT compiler.

 

Step 4 - The native code is executed in the context of the managed CLR, along with any other running applications or processes.
 

DIFFERENCE BETWEEN SQL DELETE, DROP & TRUNCATE

SQL DELETE, DROP & TRUNCATE

SQL Delete Statement

The DELETE Statement is used to delete rows/records from a table. After performing a 

DELETE operation you need to COMMIT or ROLLBACK the transaction to make the change 

permanent or to undo it. Note that this operation will cause all DELETE triggers on the table to 

fire.

SYNTAX:

DELETE FROM table_name WHERE [condition];

• table_name -- the table name which has to be updated.

NOTE:The WHERE clause in the sql delete command is optional and it identifies the rows

 in the column that gets deleted. Notice the WHERE clause in the DELETE syntax. 

The WHERE clause specifies which record or records that should be deleted.

 If you omit the WHERE clause, all records will be deleted.

TABLE: Persons

P_Id	LastName	FirstName	Address	City
1	Hasen	Oklia	Taralabalu 10	Sambalpur
4	Niel	Takia	Vasnath 23	Sitara
3	Parashuram	Kari	Sambar 20	Sitara
2	Svastik	Tove	Bagalpur 23	Sambalpur

For Example: To delete a person’s record with last name ‘Tove’ from the Persons table, 

the sql delete query would be like,

DELETE FROM Persons WHERE Lastname = ‘Tove’;

The first line in the SQL DELETE statement above specifies the table that we are deleting the

 record(s) from table Persons. The second line (the WHERE clause) specifies which rows exactly 

do we delete (in our case all rows which has LastName of ‘Tove’). As you can see the 

DELETE SQL queries have very simple syntax and in fact are very close to the natural language.

But wait, there is something wrong with the statement above! The problem is that we can

 have more than one person having last name of ‘Tove’, and all with this last name will be deleted. 

Because we don’t want to do that, we need to find a table field or combination of fields 

that uniquely identifies the Person ‘Svastik Tove’.

Looking at the Persons table an obvious candidate for such a unique field is the P_ID column.

 Our improved SQL query which deletes only the record of Svastik Tove’’s record will look like 

this:

DELETE FROM Persons
WHERE P_ID = 2;

To delete all the rows from the table, the query would be like,

DELETE FROM employee;

SQL TRUNCATE Statement

The SQL TRUNCATE command is used to delete all the rows from the table and free the

 space containing the table.

SYNTAX:

TRUNCATE TABLE table_name;

For Example: To delete all the rows from employee table, the query would be like,

TRUNCATE TABLE Persons;

Difference between DELETE,DROP and TRUNCATE Statements:

DELETE Statement: This command deletes only the rows from the table based on the 

condition given in the where clause or deletes all the rows from the table if no condition is

 specified. But it does not free the space containing the table. It generates REDO information.

TRUNCATE statement: This command is used to delete all the rows from the table

 and free the space containing the table. The operation cannot be rolled back and 

no triggers will be fired, TRUCATE is faster and doesn't use as much undo space as a DELETE.

TRUNCATE command resets the High Water Mark for the table

SQL DROP Statement:

The SQL DROP command is used to remove an object from the database.

 If you drop a table, all the rows in the table is deleted and the table structure is

 removed from the database. Once a table is dropped we cannot get it back, so be careful 

while using RENAME command. When a table is dropped all the references 

(rows, indexes and privileges) to the table will not be valid. Drop command removes the

 table from data dictionary.

SYNTAX:

DROP TABLE table_name;

For Example: To drop the table employee, the query would be like

DROP TABLE employee;

Difference between DROP and TRUNCATE Statement:

If a table is dropped, all the relationships with other tables will no longer be valid, 

the integrity constraints will be dropped, grant or access privileges on the table will also

 be dropped, if want use the table again it has to be recreated with the integrity 

constraints, access privileges and the relationships with other tables should be

 established again. But, if a table is truncated, the table structure remains the same; 

therefore any of the above problems will not exist.

DROP and TRUNCATE are DDL commands, whereas DELETE is a DML command. 

Therefore DELETE operations can be rolled back (undone), while DROP and TRUNCATE

 operations cannot be rolled back.