Paul has the best explanation for this -

http://www.sqlskills.com/blogs/paul/post/Indexes-From-Every-Angle-What-happens-to-non-clustered-indexes-when-the-table-structure-is-changed.aspx

SQL Server 2005 Going from a heap to a clustered index: This is the same as SQL Server 2000 - all non-clustered indexes are rebuilt, regardless of whether the clustered index was created online or offline. Going from a clustered index to a heap: Again, all non-clustered indexes are rebuilt, regardless of whether the clustered index is dropped online or offline. Rebuilding a unique clustered index: This is the same as SQL Server 2000 - the cluster keys aren't changing and so the non-clustered indexes are not rebuilt. Rebuilding a non-unique clustered index: Aha! This is different from SQL Server 2000. SQL Server 2005 will RE-USE the old uniquifier values so the cluster keys don't change. This means that non-clustered indexes are NOT rebuilt in this case - that's very cool! Changing the clustered index schema: Changing the cluster key: This behavior has to be the same as SQL Server 2000 - any change to the cluster keys forces all the non-clustered indexes to be rebuilt after the new clustered index has been created. Changing anything else (e.g. partitioning it or moving it): This is one of the cases that confuses people in SQL Server 2005. Applying a partitioning scheme to a clustered index, or moving it to a different filegroup, doesn't change the cluster key at all - so there's no need to rebuild the non-clustered indexes.

DAC (Data Tier Applications) introduced in SQL Server 2008 R2 release, in collaboration with Microsoft Visual Studio 2010 it allows Developers & DBAs to develop and deploy Data Tier Applications in self-contained units (dacpacs)

Using DACs, developers and database administrators can now automate common operations and design practices during their database lifecycles in controlled environments.

All 3 types exists in tempdb.

A local temporary table, #table_name, exists only for the duration of a user session or the procedure that created the temporary table. When the user logs off or when the procedure that created the table completes, the local temporary table is lost.

A global temporary table, ##table_name, also exists for the duration of a user session or the procedure that created the table. Global temporary tables are visible to all SQL Server connections. When the last user session that references the table disconnects, the global temporary table is lost.

A table variable is created in memory, and so performs slightly better than #temp tables (also because there is even less locking and logging in a table variable). Table variables are automatically cleared when the procedure or function goes out of scope, so you don't have to remember to drop or clear the data. Table variables are the only way you can use DML statements (INSERT, UPDATE, DELETE) on temporary data within a user-defined function.

Ref - Web and BOL for exact terminology.

The sp_recompile system stored procedure forces a recompile of a stored procedure the next time that it is run.

Creating a stored procedure that specifies the WITH RECOMPILE option in its definition indicates that SQL Server does not cache a plan for this stored procedure; the stored procedure is recompiled every time that it is executed.

If we only want individual queries inside the stored procedure to be recompiled, instead of the complete stored procedure, specify the RECOMPILE query hint inside each query you want recompiled.

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.

varchar and nvarchar are variable-length which will only use up spaces for the characters you store. It will not reserve storage like char or nchar.

varchar(max) data types can hold the same amount of data BLOBs can hold (2 GB) and they are stored in the same type of data pages used for other data types.

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 generated whenever the number of log records reaches the number the Database Engine estimates it can process during the time specified in the recovery interval option.

If the database is using the simple recovery model, an automatic checkpoint is generated whenever the number of log records reaches the lesser of these two values:

The log becomes 70 percent full. The number of log records reaches the number the Database Engine estimates it can process during the time specified in the recovery interval option.

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 be put in a column. CREATE TABLE custsample ( custid int PRIMARY KEY, custname char(50), custaddress char(50), custcreditlimit money, CONSTRAINT chkid CHECK (custid BETWEEN 0 and 10000 ) )

UNIQUE constraints enforce the uniqueness of the values in a set of columns.

PRIMARY KEY constraints identify the column or set of columns that have values that uniquely identify a row in a table. CREATE TABLE partsample (partnmbr int PRIMARY KEY, partname char(30), partweight decimal(6,2), part_color char(15) );

FOREIGN KEY constraints identify and enforce the relationships between tables. CREATE TABLE orderpart (ordernmbr int, partnmbr int FOREIGN KEY REFERENCES partsample(partnmbr) ON DELETE NO ACTION, qtyordered int); GO

Constraints can be column constraints or table constraints. A column constraint is specified as part of a column definition and applies only to that column. The constraints in the previous examples are column constraints. A table constraint is declared independently from a column definition and can apply to more than one column in a table. Table constraints must be used when more than one column must be included in a constraint. CREATE TABLE factoryprocess (eventtype int, eventtime datetime, eventsite char(50), eventdesc char(1024), CONSTRAINT eventkey PRIMARY KEY (eventtype, eventtime) )

Ref- BOL

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() function should be used to access the data. If you want to check the existence of data exist() method is use. The query() method is used to specify a query against an XML instance that is stored in an xml data type variable or column. Sometimes, you might also want your query to use values from a Transact-SQL variable, or parameter, to bring relational and XML data together. To do this, you use the sql:variable function. Three data retrieval XML methods available are query(),nodes(),value() When you want to modify (insert,delete,update) xml data than modify() method is useful. When you want to return event data, use the XQuery value() method instead of the query() method. The query() method returns XML and ampersand-escaped carriage return and line feed (CR/LF) instances in the output, while the value() method renders CR/LF instances invisible in the output

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 for growth.

Disabling an index prevents user access to the index, and for clustered indexes, to the underlying table data. The index definition remains in metadata and index statistics are kept on nonclustered indexes. Disabling a nonclustered index or clustered index on a view physically deletes the index data. Disabling a clustered index on a table prevents access to the data; the data still remains in the table, but is unavailable for DML operations until the index is dropped or rebuilt. To rebuild and enable a disabled index, use the ALTER INDEX REBUILD statement or the CREATE INDEX WITH DROP_EXISTING statement.

Disabling the index wont gain space,however drop operation does gain space.

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 and all read-write, secondary filegroups.

I have blogged about filegroup level backup/restore here http://sqlsailor.com/2012/01/09/partial-database-availability-a-walk-through-2/