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.

Extracting Only Numbers from a String

Sometimes we may need to extract only numbers from a string.
The simple approach is to run a while loop on given string to check each and every character and extract it to get the result.
Here is one alternative approach:

Declare @strTemp varchar(100),@strResult varchar(100)
set @strTemp='1zxcv123asd5fqw4er'
set @strResult=''
select @strResult = @strResult+
case when number like '[0-9]' then number else '' end from
(select substring(@strTemp,number,1) as number from 
(select number from master..spt_values 
where type='p' and number between 1 and len(@strTemp)) as t
) as t 
select @strResult as [Numbers] 
go
Result:
112354

Disable a Job via TSQL

You can disable a Job either through Job ID or Job Name:
exec msdb..sp_update_job @job_id = 0xC0923E436928064EA33B46B2A47BFF61 , @enabled = 0


exec msdb..sp_update_job @job_name = 'Job Name', @enabled = 0
You can also manipulate via job category:
UPDATE j
SET j.Enabled = 0
FROM MSDB.dbo.sysjobs j
INNER JOIN MSDB.dbo.syscategories c ON j.category_id = c.category_id
WHERE c.[Name] = 'Database Maintenance';
The other approach would be to add a step at the beginning of your other jobs which checks the status of the backup job and then either aborts or sleeps the current job if the backup is running.
Follow below procedure to Disable or Enable a SQL Server Job Programatically:
Step 1: Find the JobId 
SELECT job_id,name,description,enabled
FROM msdb.dbo.sysjobs
ORDER BY name ASC
Step 2: Update the enabled flag
– Disable SQL Job
DECLARE @my_job_id UNIQUEIDENTIFIER
SET @my_job_id = '1CB89951-9ED1-45F2-A5E8-A20D9164613F'
EXEC sp_update_job @job_id = @my_job_id , @enabled = 0
– Enable SQL Job
DECLARE @my_job_id UNIQUEIDENTIFIER
SET @my_job_id = '1CB89951-9ED1-45F2-A5E8-A20D9164613F'
EXEC sp_update_job @job_id = @my_job_id , @enabled = 1