Value types are allocated on the stack , while all reference types are allocated on the heap . A value type contains the actual value. A reference type contains a reference to the value. When a value type is assigned to another value type, it is copied. When a reference type is assigned to another reference type, a reference is assigned to the value. As you can see in below example: usin...

First I create a table Create Table ParentChild_Table (ID int identity(100,1),[Name]varchar(100),ParentID int) Then insert some records INSERT INTO ParentChild_Table VALUES('JOHN',0) INSERT INTO ParentChild_Table VALUES ('RINA',100) INSERT INTO ParentChild_Table VALUES ('RAJU',100) INSERT INTO ParentChild_Table VALUES ('RAJEEV',101) INSERT INTO ParentChild_Table VALUES ('RAVI',103) INSER...

This blog explain how to create alpha numeric id in sql server for this I’m going to create a Table with two columns . CREATE TABLE USER_DATA ([ID] NVARCHAR(10),[NAME] NVARCHAR(50)) Then create a stored procedure for insertion id and name in table here we pass name as parameter alpha numeric id will be generate by code like RP001,RP002 etc. CREATE PROC SP_INSERT_USER @NAME VARCHAR(50) AS...

It stops the message that shows the count of the number of rows affected by a Transact-SQL statement or stored procedure from being returned as part of the result set. Syntax SET NOCOUNT { ON | OFF } for example set nocount on select * from Table here message displayed Command(s) completed successfully. if we don't Set NOCOUNT on then message displayed (n row(s) affected) here n could be...

When writing SQL code, you often need a table in which to store data temporarily when it comes time to execute that code. You have three table options: local temporary tables, global temporary tables and table variables. I'll discuss the differences between using temporary tables in SQL Server versus table variables. Temporary Tables Both local and global temporary tables are physical ta...

This example shows on how to get all duplicate rows from a table for this I’m going to create a Table with two column and inserting some distinct & duplicate rows in it and then write query to display all duplicate records . Step1: CREATE TABLE Employ_Salary_Record ( EmployeeName Nvarchar(50) , Salary int) Step2: Insert into Employ_Salary_Record values('a1',11000) Insert into Employ_...

Even the variable passed as out parameter is similar to ref but there are few implementation differences.Argument passed as ref must be initialized before it is passed to the method, where as in case of out its is not necessary. out parameter can be used when we want to return more than one value from a method. To illustrate, below programs shows how to pass agrument via out and ref : us...

“Delegate is a method template which used to implement the concept of function pointer.” A Delegate is a special type of user-defined variable that define globally ,like a class.In fact ,a delegate is created like a interface but as a method .Based on this ,a delegate provide a template for method ,like an interface provide template for class. like an interface delegate is not defined. D...

Classes vs Structs Structs differ from classes in the way that they are stored in memory and accessed (classes are reference types stored in the heap, structs are value types stored on the stack), and in some of the features (for example, structs don’t support inheritance). You will tend to use structs for smaller data types for performance reasons. class PhoneCustomer { public const str...

The idea of a property is that it is a method or pair of methods that are dressed to look like a field as far as any client code is concerned. A good example of this is the Height property of a Windows Form. "Properties can be thought as virtual fields." Example Public int Age { Get { Return this.age; } Set { this.age=value; } } Person.Age=4;

“An Indexer is a member that enables object to be indexed in a the same way as array.” Indexers are used for treating an object as an array. The indexers are usually known as smart arrays in C# community. Defining a indexer is much like defining properties. We can say that an indexer is a member that enables an object to be indexed in the same way as an array. Syntax of Indexer this [arg...

The two types of temporary tables, local and global, differ from each other in their names, their visibility, and their availability. Local temporary tables have a single number sign (#) as the first character of their names; they are visible only to the current connection for the user; and they are deleted when the user disconnects from instances . Global temporary tables have two numbe...

Transactions group a set of tasks into a single execution unit. Each transaction begins with a specific task and ends when all the tasks in the group successfully complete. If any of the tasks fails, the transaction fails. Therefore, a transaction has only two results: success or failure. Incomplete steps result in the failure of the transaction. Users can group two or more Transact-SQL ...

Union will filter duplicate values where as union all will not filter duplicate values. The difference between UNION ALL and UNION is that, while UNION only selects distinct values, UNION ALL selects all values. for understanding more deeply see bellow example . select 'Ajit','Gurgaon' union all select 'Monu','Guraon' union all select 'vinay','Noida' union all select 'Ajit','Gurgaon the ...

Procedure can return zero or n values whereas function can return one value which is mandatory. Procedures can have input,output parameters for it whereas functions can have only input parameters. Procedure allow select as well as DML statement(Create,Drop,Alter) in it whereas function allow only select statement in it. Functions can be called from procedure whereas procedures cannot be ...

IDENTITY (Property) It Creates an identity column in a table. This property is used with the CREATE TABLE and ALTER TABLE Transact-SQL statements. Syntax IDENTITY [ ( seed , increment ) ] Example create table Mytable ( Identity_Id int identity(1001,1),name varchar(50),city varchar(50) ) @@IDENTITY :- Returns the last-inserted identity value. Syntax SELECT @@IDENTITY AS IDs example select...