Scheduler Job :

Some time we wanted to take backup our database weekly or daily or monthly once to maintain the log files and full data.
Below i pasted step wise scheduling job in SQL Server.
Step 1: First check whether your SQL Server Agent is Running

Step 2: Right click on SQL Server Agent > New>Job

Step 3: Give any name for the scheduler job and check Owner.

Step 4:  got to Step after General in left side of the window.

Step 5: Put Step name and select your Database that you want to take backup.

Step 6: paste this code according to your database name on the command box.

BACKUP DATABASE MSO

TO DISK = 'D:\Backup\MSO.BAK'

GO

Step 7: go to Scheduler option on left , put name and select schedule according to your requirement.

click ok and check next option in left side for your reference.

Playing with Clustered and Non Clustered Index :-

Clustered INDEX :-

A table is nothing but a collection of record sets; by default, rows are stored in the form of heaps unless a clustered index has been defined on the table, in which case, record sets are sorted and stored on the clustered index. The heaps structure is a simple arrangement where the inserted record is stored in the next available space on the table page.

Heaps seem a great option when the motive is simply storing data, but when data retrieval steps in, this option back fires. An index acts as a fire fighter in this scenario. Indexes are arranged in the form of a B-Tree where the leaf node holds the data or a pointer to the data. Since the stored data is in a sorted order, indexes precisely know which record is sitting where. Hence an index optimizes and enhances the data retrieval immensely.

SQL Server 2005:

 1 Clustered Index + 249 Nonclustered Indexes = 250 Indexes per table

SQL Server 2008: 

1 Clustered Index + 999 Nonclustered Indexes = 1000 Indexes per table

-------Clustered INDEX example------

--Creating new table with few columns

create table INDEX_learnig(ID int ,FirsName varchar(30),LastName varchar(30))

select * from INDEX_learnig

--Inserting same data into the table before creating CI

insert into INDEX_learnig values(1,'Reza','AA')

insert into INDEX_learnig values(1,'Masum','BB')

*We can Insert same values with multiple times

--Creating Normal Clustered on ID column

create clustered index U_CI on INDEX_learnig(ID)

insert into INDEX_learnig values(1,'Reza','AA')

insert into INDEX_learnig values(1,'Masum','BB')

select * from INDEX_learnig

still not a problem to insert same values 

-------- Alter or Drop the Clustered values with Unique Constraint

drop index U_CI on INDEX_learnig

truncate table INDEX_learnig

-------

--Creating Unique clustered values

create unique clustered index U_CI on INDEX_learnig(ID)

insert into INDEX_learnig values(1,'Reza','AA')

insert into INDEX_learnig values(2,'Masum','BB')

insert into INDEX_learnig values(1,'Masum','BB')  --error(Same records from row 1)

Following error will Occur:

Msg 2601, Level 14, State 1, Line 25

Cannot insert duplicate key row in object 'dbo.INDEX_learnig' with unique index 'U_CI'. The duplicate key value is (1).

The statement has been terminated.

------

drop index U_CI on INDEX_learnig

truncate table INDEX_learnig

------

--Creating Unique Clustered with multiple column

create unique clustered index U_CI on INDEX_learnig(ID,FirsName)

insert into INDEX_learnig values(1,'Reza','BB')

insert into INDEX_learnig values(2,'Masum','BB')

insert into INDEX_learnig values(1,'Reza','BB') --Error(Same as first row)

and the error will occur like :

Msg 2601, Level 14, State 1, Line 34

Cannot insert duplicate key row in object 'dbo.INDEX_learnig' with unique index 'U_CI'. The duplicate key value is (1, Reza).

The statement has been terminated.

--Clustered Index with Primary KEY and Unique Identity

--Creating Unique Identity Column

drop table INDEX_learnig

create table INDEX_learnig(ID int unique identity(1,1),FirsName varchar(30),LastName varchar(30))

insert into INDEX_learnig values('Masum','BB')

select * from INDEX_learnig

--Try to create Unique Clustered Index

create unique clustered index U_CI on INDEX_learnig(FirsName,LastName)

insert into INDEX_learnig values('Masum','BB') --Error

error will occur like :

Msg 2601, Level 14, State 1, Line 46

Cannot insert duplicate key row in object 'dbo.INDEX_learnig' with unique index 'U_CI'. The duplicate key value is (Masum, BB).

The statement has been terminated.

When you create a unique constraint, the database engine creates a unique nonclustered index. 

However, you can specify that a unique clustered index be created if a clustered index does not already exist.

For all practical purposes, a unique constraint and unique index are one in the same.

Once You Create Primarey Key it will autometically create Clustered Index

drop table INDEX_learnig

create table INDEX_learnig(ID int Primary key,FirsName varchar(30),LastName varchar(30))

Try to create Unique Clustered Index

create unique clustered index U_CI on INDEX_learnig(FirsName,LastName)

The error will throw like :

Msg 1902, Level 16, State 3, Line 51

Cannot create more than one clustered index on table 'INDEX_learnig'. Drop the existing clustered

index 'PK__INDEX_le__3214EC27E5FBFD04' before creating another.

When you create a primary key constraint, the database engine also creates a unique clustered index, 

if a clustered index doesn’t already exist. However, you can override the default behavior and specify 

that a nonclustered index be created. If a clustered index does exist when you create the primary key, 

the database engine creates a unique nonclustered index.

check which line is currently executing inside the sp in sql server :-

Step 1 :

SELECT   SPID       = er.session_id,

STATUS         = ses.STATUS,

[Login]        = ses.login_name,

Host           = ses.host_name,

BlkBy          = er.blocking_session_id,

DBName         = DB_Name(er.database_id),

CommandType    = er.command,

ObjectName     = OBJECT_NAME(st.objectid),

CPUTime        = er.cpu_time,

StartTime      = er.start_time,

TimeElapsed    = CAST(GETDATE() - er.start_time AS TIME),

SQLStatement   = st.text

FROM    sys.dm_exec_requests er

OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) st

LEFT JOIN sys.dm_exec_sessions ses

ON ses.session_id = er.session_id

LEFT JOIN sys.dm_exec_connections con

ON con.session_id = ses.session_id

WHERE   st.text IS NOT NULL

Take SPID from above statement and paste into new the statement :

SELECT SUBSTRING(st.text, ( r.statement_start_offset / 2 ) + 1, 

( ( CASE WHEN r.statement_end_offset <= 0

THEN DATALENGTH(st.text) 

ELSE r.statement_end_offset END - 

r.statement_start_offset ) / 2 ) + 1) AS statement_text 

FROM   sys.dm_exec_requests r 

CROSS APPLY sys.dm_exec_sql_text(sql_handle) st 

WHERE  session_id = 57

Create multiple columns in dynamic way :-

Create multiple columns in dynamic way and perform update operation on it :

create table #temp(Name varchar(30))
insert into #temp(Name) values('Masum')
select * from #temp


DECLARE  @i INT=1
declare @x int
select @x=5
declare @n varchar(max)
set @n='REZA'

WHILE @i<=@x 
BEGIN
EXEC ('ALTER TABLE #temp ADD Col'+@i+' VARCHAR(100);')

exec ('Update #temp set Col'+@i+'=''' + @n + '''')


SET @i=@i+1
END

Convert HEX values into Actual data in SQL SERVER : -

First create a table, you can create a temp table.

 Step 1.
CREATE TABLE #hex(
[hex_Value] [varbinary](max) NULL
)

Step 2.
Insert data into the table, Example
insert into #hex values(0x300008000F000000030000020015001B00536976754D79736F7265)

Step 3.
SELECT LTRIM(RTRIM(CONVERT(VARCHAR(max),REPLACE(hex_Value, 0x00, 0x20))))
FROM #hex

check the final result after converting the HEX values into actual format.