Moving master database

Moving master database is a bit different process than moving any other system database. Here is the process on how to move the master database.
Before doing anything, make sure you are having proper backups.
Stop the SQL Services.
Go to the "SQL Server Configuration Manager".
In SQL Server 2005, Click "Start" >> "All Programs" >> "Microsoft SQL Server 2005" >> "Configuration Tools" >> SQL Server Configuration Manager
In SQL Server 2008, Click "Start" >> "All Programs" >> "Microsoft SQL Server 2008" >> "Configuration Tools" >> SQL Server Configuration Manager
In the left pane, click on "SQL Server Services" 
Now in the right pane, select the SQL Server Service component  (which looks like "SQL Server (InstanceName)" ) and go to its properties.
In the "Properties" page, go to the "Advanced" tab
In the "Startup Parameters", click on the drop list and modify the parameters -d and -l to the new location where you want the master data file (master.mdf) and log file (mastlog.ldf) to reside respectively.


-d stands for the fully qualified data file path of master database.
-l stands for the fully qualified log file path of master database.
-e stands for the fully qualified path of the error log file.


Now move the files manually to the new location.
Start the SQL Services.

Hide an Instance of SQL Server in the network

1. Launch SQL Server Configuration Manager
2. Under "SQL Server Network Configuration" > Right-Click "Protocols for SQL01" > Select Properties
3. Set "Hide Instance" value to "Yes" from the drop-down list:
4. The Instance needs to be restarted in order for changes to take effect:Once the instance is restarted it will not longer appear in the "Network Servers" list:


We can do this through T-SQL also:
The information to Show/Hide instance is stored in the registry. We can use extended stored procedure xp_instance_regwrite to update the registry value to Show/Hide instance. Below T-SQL will hide the instance. To unhide instance set @value to 0 :
EXEC master..xp_instance_regwrite
      @rootkey = N'HKEY_LOCAL_MACHINE',
      @key =
N'SOFTWARE\Microsoft\Microsoft SQL Server\MSSQLServer\SuperSocketNetLib',
      @value_name = N'HideInstance',
      @type = N'REG_DWORD',
      @value = 1
      – 0 = No, 1 = Yes
To check if an instance is hidden you can use xp_instance_regread to check registry values:
DECLARE @getValue INT
EXEC master..xp_instance_regread
      @rootkey = N'HKEY_LOCAL_MACHINE',
      @key=
N'SOFTWARE\Microsoft\Microsoft SQL Server\MSSQLServer\SuperSocketNetLib',
      @value_name = N'HideInstance',
      @value = @getValue OUTPUT
SELECT @getValue
This method only prevents the instance from being listed on the network, It does not prevent users from connecting to server if they know the instance name.

Moving model databases

Since model is a system databases, unfortunately we cannot move this just by detach and attach process, as we cannot attach or detach a system database.


First get the list of model database files by using this query
select name,physical_name from sys.master_files whereDB_NAME(database_id) = 'model'  
Then for each model database file that you need to move, execute statements like below
Alter Database model modify
file (NAME = 'modeldev' ,
FILENAME = 'Drive:\Path\model.mdf') -- Mention the new location


Alter Database model modify
file (NAME = 'modellog' ,
FILENAME = 'Drive:\Path\modellog.ldf') -- Mention the new location
Stop SQL Services
Move the files manually to the new location
Start SQL Services
Verify the new Location
select name,physical_name from sys.master_files whereDB_NAME(database_id) = 'model'

Rename all stored procedures in database

Below is the query to rename all stored procedures with some a specific word as prefix.

SELECT 'EXEC sp_rename '+ ''''+s.NAME+ '.' + p.NAME+''','''+s.NAME+ '.' +'your_prefix'+p.NAME+'''' FROM SYS.PROCEDURES p ,SYS.SCHEMAS s WHERE p.schema_id = s.schema_id

Warning: Executing this command is dangerous because it may rename system stored procedures too. To avoid such disaster, don't forget to include WHERE condition.

Clustered Index vs Non Clustered Index

Clustered and Non clustered indexes are stored in B-Tree Structure.
Clustered Index

• Clustered index enforce the logical order. (Misconception: Clustered index does not enforce the physical order)

• A table has only one clustered index because, the original table stored in leaf level of the clustered index (Data pages).

• When you create a primary key by default clustered index will be created internally.(If the table has clustered index already then the non clustered index will be created internally)

• If the table does not has clustered index it’s called “Heap”

Non Clustered Index

• Non clustered indexes are separate storage. (I.e. original table and an index stored separately)

• Non clustered index does not enforce the logical order. The physical order of the rows is not the same as the index order.

• A table has 999 non clustered indexes in sql-2008, 249 non clustered indexes prior to 2008. 

• When you create a unique key by default non clustered index will be created internally.