tutorials, tips and tricks to design a Sql Server, Cloud Service . Micrsoft Interview Question
Wednesday, March 7, 2012
Microsoft's SQL Server 2012 to launch on March 7
Tuesday, February 28, 2012
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
Friday, February 10, 2012
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
Saturday, September 17, 2011
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
Thursday, September 8, 2011
“ 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
Sunday, May 15, 2011
View in Sql Server
View in Sql Server
- · View is a virtual table
- · It s contains columns and data in different table
- · View does not contain any data directly. Its a set of select query
| Table 1 Sno Sname | Table 2 Sno Lname |
| View_table1_table2 Sname Lname |
Above drawing table 1 and table we will write join query after we can create view
Syntax
CREATE VIEW JS_VIEW_NAME
AS
[SELECT STATEMENT]
Why we are use View ?
· View is used for security mechanism .if you restricted particular column for users .
Sql Server Syntex for View
CREATE VIEW
CREATE VIEW JS_VIEW
AS
SELECT *FROM STUD A,LIB L WHERE A.SNO=L.SON
ALTER VIEW
ALTER VIEW JS_VIEW
AS
SELECT *FROM STUDENTTABLE A,LIB L WHERE A.SNO=L.SON
SELECT VIEW
SELECT*FROM JS_VIEW
DROP VIEW:
DROP VIEW JS_VIEW
Subscribe to:
Posts
(
Atom
)