Posts

Showing posts from May, 2012

Three different ways to find blocking in SQL

SP_WHO2 It shows blocking by the presence of blkby column value. Sysprocesses select * from master..sysprocesses where blocked >0 Activity Monitor Open Activity Monitor at any time by pressing CTRL+ALT A.

Difference between 3 types of Restoring modes.

S.No With Recovery With Norecovery Standby\Readonly 1 Leave the database in online mode. Leave the database in restoring mode. Leave the database in  readonly mode. 2 No further restore is possible. Further restore is possible. Further restore is possible. 3 User can able to access Users can’t. Users can able to read the  databases. 4 Rollback the uncommitted transactions Does not rollback the uncommitted transactions It undoes uncommitted transactions , but saves the undo actions in a  standby file so that recovery effects  can be reverted.

Difference between database roles and server roles

S.No Server Roles Database Roles 1 It is defined at the server level. It is defined at the database level. 2 It exists outside of user databases. It exists in each database. 3 There are fixed server roles only. User defined database roles available. 4 Bulkadmin,dbcreator,diskadmin, processadmin,securityadmin, serveradmin,setupadmin and sysadmin(8) Db_accesadmin,db_backupoperator, db_datareader,db_datawriter, db_ddladmin,db_denydatareader, db_denydatawriter,db_owner and db_securityadmin(9)

Differences between SQL2000,SQL2005 and SQL2008.

S.No SQL 2000 SQL2005 SQL2008 1 Query Analyzer and Enterprise Manager are separate. Both are combined as SQL SERVER MANAGEMEN STUDIO. Both are combined as SQL SERVER MANAGEMEN STUDIO. 2 No XML datatype is used. XML datatype is introduced. XML datatype is used. 3 65,535 databases can be created. 2(pow(20))-1 databases can be created. 2(pow(20))-1 databases can be created. 4 Nill Exception handling Exception handling 5 Nill Varchar(Max) datatype Varchar(Max) datatype 6 Nill DDL Triggers