CUBE,COMPUTE,COMPUTE BY and ROLLUP OPERATOR IN SQL SERVER

The CUBE,COMPUTE,COMPUTE BY and ROLLUP operators are useful in generating reports that contain subtotals and totals. 

There are extensions of the GROUP BY clause.

The result set of a ROLLUP operation has functionality similar to that returned by a COMPUTE BY; however, ROLLUP has these advantages:

• ROLLUP returns a single result set; COMPUTE BY returns multiple result sets that increase the complexity of application code.
  
  
• ROLLUP can be used in a server cursor; COMPUTE BY cannot.
  
  
• The query optimizer can sometimes generate more efficient execution plans for ROLLUP than it can for COMPUTE BY.

STEP 1:

create table JSSTUD(COL nvarchar(20),PER numeric(10))

STEP 2:

INSERT INTO JSSTUD VALUES('JJ COLLEGE',100) 

INSERT INTO JSSTUD VALUES('JJ COLLEGE',200) 

INSERT INTO JSSTUD VALUES('JJ COLLEGE',300) 

INSERT INTO JSSTUD VALUES('PSG',150) 

INSERT INTO JSSTUD VALUES('PSG',50) 

INSERT INTO JSSTUD VALUES('PSG',1050) 

INSERT INTO JSSTUD VALUES('GRG',2000) 

STEP 3:

ROLLUP

SELECT  COL,SUM(PER) FROM JSSTUD GROUP BY COL

WITH ROLLUP

OR

COMPUTE  bY

SELECT COL,PER FROM JSSTUD ORDER BY COL

COMPUTE SUM(PER) BY COL

OR

SELECT COL,PER FROM JSSTUD ORDER BY COL

COMPUTE SUM(PER) 

CUBE

select COL, sum(per) 

from JSSTUD

group by COL with CUBE

Microsoft's SQL Server 2012 to launch on March 7

mofcomp "C:\Program Files (x86)\Microsoft SQL Server\100\Shared\sqlmgmproviderxpsp2up.mof"

Sql Server Error :

Cannot connect to WMI provider. You do not have permission or the server is unreachable. Note that you can only manage SQL Server 2005 servers with SQL Server Configuration Manager. The specified module could not be found. [0x8007007e]

Solution :

 

Start ==> Run => Type below command  

 mofcomp "C:\Program Files (x86)\Microsoft SQL Server\100\Shared\sqlmgmproviderxpsp2up.mof"

Thanks

Reset Identity Value Using Sql Query

Reset Identity Value Using Sql  Query 

DBCC CHECKIDENT('UserTable' , RESEED, 0)

Database Go Online and OFFline using Query

Database GO Online:

ALTER DATABASE DBTest2 SET ONLINE

Database Go Offline

 

ALTER DATABASE DBTest2 SET OFFLINE

Add / REMOVE Primary Key to existing table

CREATE TABLE WITHOUT PRIMARY KEY

CREATE TABLE JSSTUDTABLE (SNO NUMERIC(10) NOT NULL)

ADD PRIMARY KEY EXISTING TABLE

ALTER TABLE JSSTUDTABLE ADD  PRIMARY KEY (SNO);

REMOVE PRIMARY KEY EXISTING TABLE 

ALTER TABLE JSSTUDTABLE DROP  constraint  PK__JSSTUDTA__CA1EE06C63D8CE75

“ COALESCE “ Method in Sql Server

 “ COALESCE “  Method in Sql Server

Table Have so many column like that

Name	HomeAddress	OfficeAddress	Temp_Address
Suthahar	Null	Null	Pudukkottai
Suresh	Pullanviduthi	Null	Null
Sumathi	Null	Alangudi	Null
Sujatha	Pullanviduthi	Pudukkottai	Trichy

If someone have home address or office address suppose if you display available first record means you can use coalesce method

CREATE TABLE devenvexe(Name  nvarchar(10),homeaddress nvarchar(10),officeaddress nvarchar(10), Temp_addressnvarchar(10))

Query :

SELECT name,COALESCE(homeaddree,officeaddress,temp_address) Addreess FROM devenvexe

Output:

Name	Address
Sutahhar	Pudukkottai
Suresh	Pullanviduthi
Sumathi	Alangudi
Sujatha	Pullanviduthi

Concatinate Column in Single Column

CREATE TABLE JS(SNAME NVARCHAR(10))

INSERT INTO JS VALUES('SUTHAHAR')

INSERT INTO JS VALUES('SURESH')

INSERT INTO JS VALUES('SUMATHI')

INSERT INTO JS VALUES('SUJATHA')

DECLARE @VAL NVARCHAR(1024)

SELECT @VAL=COALESCE(@VAL+',', '')+ SNAME FROM JS

SELECT JS= @VAL

Output:

Suthahar,Suresh,Sumathi,Sujatha