Data collector is an out of box feature which comes with SQL Server 2008/above versions.This feature deals with capturing performance data mainly using API's.The data is stored and manipulated using a database MDW(Management Data Warehouse).When configu...

PBM was a feature which was introduced in SQL Server 2008 and not SQL Server 2005. PBM deals with defining policies for SQL Server within the enterprise. Defining policies allows the DBA to proactively choose how SQL Server instances, databases, and ...

A fill factor is a specification done during the creation of indexes so that a particular amount of space can be left on a leaf level page to decrease the occurrence of page splits when the data has to be accommodated in the future. As far as I under...

What is database mirroring option? DBM is a DR solution and when synchronous DBM is implemented along with witness server,then a disaster situation can be managed efficiently.Database mirroring involves 2 server to a minimum and a third witness serv...

Databases that contain multiple filegroups can be restored and recovered in stages through a process known as piecemeal restore.A piecemeal restore sequence restores and recovers a database in stages at the filegroup level, beginning with the primary an...

So, how do you enable and disable an Index? What is the behavior of the same when compared to dropping an Index? How does storage change? In ideal situation when capacity planning is done,Index will be residing in a separate LUN with sufficient space...

5 methods are query() Method,value() Method,exist() Method,modify() Method,nodes() Method. If values are needed rather than XML nodes then the value function should be used to access the data. If XML nodes are needed rather than values then the node...

Constraints let you define the way the Database Engine automatically enforces the integrity of a database. NOT NULL specifies that the column does not accept NULL values. CHECK constraints enforce domain integrity by limiting the values that can b...

Checkpoint is a process which will write all dirty pages (modified page in buffer cache which is not written to disk) from buffer cache to disk. If the database is using either the full or bulk-logged recovery model, an automatic checkpoint is gene...

nchar and nvarchar can store Unicode characters. char and varchar cannot store Unicode characters. char and nchar are fixed-length which will reserve storage space for number of characters you specify even if you don't use up all that space. va...