Tuesday, May 29, 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
DDL Triggers
7
Nill
Database mirroring
Database mirroring
8
Nill
Row number function for paging use.
Row number function for paging use.
9
Nill
Table fragmentation
Table fragmentation
10
Nill
Full text search
Full text search
11
Nill
Bulk copy update
Bulk copy update
12
Nill
Can’t encrypt
Can encrypt entire database
13
Compression not possible
Can compress tables and indexes(introduced in 2005 SP2)
Can compress tables and indexes
14
Datetime is used for both date and time
Datetime is used for both date and time
Date and time are separately used for date and time datatype
15
Nill
Narchar(max) and varbinary(max) is used.
Narchar(max) and varbinary(max) is used.
16
Nill
Nill
Table datatype is introduced.
17
Nill
 SSIS is used.
SSIS is used.
18
Nill
Nill
Central Management Server(CMS) is introduced.
19
Nill
Nill
Policy based management is available.

MYSQL:::Seamless Data Archiving: Exporting, Importing, and Pruning MySQL Tables

  Seamless Data Archiving: Exporting, Importing, and Pruning MySQL Tables Introduction: In the dynamic landscape of database management...