Sum of Salary group by Dept id Query

select u.DeptId,SUM(Salary) as TotSalary from tblUsers u group by u.DeptId

tbl_Dept:--

DeptId int

DeptName nvarchar(50)

tblUsers:--

UserID int Unchecked

FirstName nvarchar(50) Checked

LastName nvarchar(50) Checked

Phone nvarchar(50) Checked

Email nvarchar(50) Checked

Salary decimal(18, 0) Checked

DeptId int Checked

Commit and Rollback Commands in SQL Server

Rollback and Commit are transaction statements that are called Data Control Language for SQL and are used to ensure the integrity of data in databases. In my previous article I describe Grant and Revoke DCL commands; for that visit, Grant and Revoke Command in SQL SERVER.
 
First of all we create a table named emp on which we enforce the Rollback and Commit commands.

Creation of table:
Use the following command to the create table:

create table emp(empid int constraint PRIMARYKEY primary key, empName varchar(15))

Insertion of data:
Use the following command for the insertion of data.

insert into emp
select 11,'d'union allselect 12,'ee'union allselect 13,'p'union allselect 14,'a'union allselect 15,'k'

Output:
Use the following command to see the output.

select * from emp


Commit Command:
Commit is used for the permanent changes. When we use Commit in any query then the change made by that query will be permanent and visible. We can't Rollback after the Commit.
 
Syntax:
begin tran tranName
Command for operation
commit tran tranName
Here tranName is the name of the transaction and the command for operation is the SQL statement that is used for the operation like making a change or inserting data etc.

Example:

begin tran d
update emp set empName ='D' where empid=11
commit tran d
Here d is the name of the transactions and we update empName d to D in the table emp on the basis of empId. The change made by this command will be permanent and we could not Rollback after the commit command.
Output:





Rollback Command:
Rollback is used to undo the changes made by any command but only before a commit is done. We can't Rollback data which has been committed in the database with the help of the commit keyword.

Syntax:
begin tran tranName
Command for operation
Rollback tran tranName

Here tranName is the name of the transaction and the command for the operation is the SQL statement that is used for performing operations like to make any change or insert data etc.

Example:
We want that, if data entered by user has an empId less than 10 then the command is rolled back and a message is shown to the user "An id less than 10 is not valid; query is rolled back". 

begin tran t
declare @id int;
set @id=1;
insert into emp values(@id,'d')
if(@id<10)
begin
print'An id less than 10 is not valid; query is rolled back';
rollback tran t;
end 
else
begin
print 'data is inserted'
end
Here d is the name of transactions. When we provide empId less than 10 then we get
Output:





When we provide empId 16 which is greater then 10 then:
begin tran t
declare @id int;
set @id=16;
insert into emp values(@id,'d')
if(@id<10)
begin
print'Less than 10 id is not valid,query is rollbacked';
rollback tran t;
end
else
begin
print 'data is inserted'
end

Output:

What is SET NOCOUNT (Transact-SQL) in Sql Server

SET NOCOUNTON:-- 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.

When SET NOCOUNT is ON, the count is not returned. When SET NOCOUNT is OFF, the count is returned.

The @@ROWCOUNT function is updated even when SET NOCOUNT is ON.

where viewstate values stored in asp.net?

View state data is stored in one or more hidden fields as base64-encoded strings. You can access view state information using the page's ViewState property, which exposes a dictionary object. Because the data in view state is stored as a string, only objects that can be serialized can be stored.