What is Clutter in Outlook

Whenever we tired of unknown and unwanted mails, In such case we apply rules in outlook and unwanted mails automatically moved to the defined folder. But as we know there is limitation of defining number of rules in outlook. Sometime your rules gets disabled and to overcome this Microsoft introduced Clutter in Outlook. Clutter" can help you filter low-priority email, saving time for your most important messages. The email server keeps track of the email you read and the ones you don't. Once you turn it on, Clutter is automatic. As new email comes in, it takes messages you're most likely to ignore and puts them into the "Clutter" folder. The more you use it, the better it gets. And if you find Clutter isn't for you, you can turn it off.

My experience with Clutter is quit good and i appreciate for this amazing feature. 

SQL Server - Server Roles from Quackit.com

SQL Server - Server Roles from Quackit.com

Locate missing indices

Locate missing indices

 SELECT * FROM sys.dm_db_missing_index_groups G INNER JOIN sys.dm_db_missing_index_group_stats GS ON G.index_group_handle = GS.group_handle INNER JOIN sys.dm_db_missing_index_details D ON G.index_handle = D.index_handle CROSS APPLY sys.dm_db_missing_index_columns (D.index_handle) DC ORDER BY D.index_handle ,[statement] 

Locate unused indices

 SELECT OBJECT_SCHEMA_NAME(I.OBJECT_ID) AS SchemaName ,OBJECT_NAME(I.OBJECT_ID) AS ObjectName ,I.NAME AS IndexName FROM sys.indexes I WHERE OBJECTPROPERTY(I.OBJECT_ID, 'IsUserTable') = 1 AND NOT EXISTS ( SELECT index_id FROM sys.dm_db_index_usage_stats WHERE OBJECT_ID = I.OBJECT_ID AND I.index_id = index_id AND database_id = DB_ID() ) ORDER BY SchemaName ,ObjectName ,IndexName

Back Up your database using Cursor on SQL server 2008

DECLARE @name VARCHAR(50) -- database name
DECLARE @path VARCHAR(256) -- path for backup files
DECLARE @fileName VARCHAR(256) -- filename for backup
DECLARE @fileDate VARCHAR(20) -- used for file name

SET @path = 'D:\Backup\'

SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)

DECLARE db_cursor CURSOR FOR
SELECT name
FROM MASTER.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb')

OPEN db_cursor  
FETCH NEXT FROM db_cursor INTO @name  

WHILE @@FETCH_STATUS = 0  
BEGIN  
       SET @fileName = @path + @name + '_' + @fileDate + '.BAK'
       BACKUP DATABASE @name TO DISK = @fileName

       FETCH NEXT FROM db_cursor INTO @name  
END  

CLOSE db_cursor  
DEALLOCATE db_cursor

Find out job name from command text

USE [msdb]
GO
SELECT j.job_id,
s.srvname,
j.name,
js.step_id,
js.command,
j.enabled
FROM dbo.sysjobs j
JOIN dbo.sysjobsteps js
ON js.job_id = j.job_id
JOIN master.dbo.sysservers s
ON s.srvid = j.originating_server_id
WHERE js.command LIKE N'%Your_text_Here%'
GO

SQL server 2008 Script for job details

set nocount on

select 'Server'       = left(@@ServerName,20),

       'JobName'      = left(S.name,30),

       'ScheduleName' = left(ss.name,25),

       'Enabled'      = CASE (S.enabled)

                          WHEN 0 THEN 'No'

                          WHEN 1 THEN 'Yes'

                          ELSE '??'

                        END,

       'Frequency'    = CASE(ss.freq_type)

                          WHEN 1  THEN 'Once'

                          WHEN 4  THEN 'Daily'

                          WHEN 8  THEN (case when (ss.freq_recurrence_factor > 1) then  'Every ' + convert(varchar(3),ss.freq_recurrence_factor) + ' Weeks'  else 'Weekly'  end)

                          WHEN 16 THEN (case when (ss.freq_recurrence_factor > 1) then  'Every ' + convert(varchar(3),ss.freq_recurrence_factor) + ' Months' else 'Monthly' end)

                          WHEN 32 THEN 'Every ' + convert(varchar(3),ss.freq_recurrence_factor) + ' Months' -- RELATIVE

                          WHEN 64 THEN 'SQL Startup'

                          WHEN 128 THEN 'SQL Idle'

                          ELSE '??'

                        END,

       'Interval'    = CASE

                         WHEN (freq_type = 1)                       then 'One time only'

                         WHEN (freq_type = 4 and freq_interval = 1) then 'Every Day'

                         WHEN (freq_type = 4 and freq_interval > 1) then 'Every ' + convert(varchar(10),freq_interval) + ' Days'

                         WHEN (freq_type = 8) then (select 'Weekly Schedule' = D1+ D2+D3+D4+D5+D6+D7

                                                       from (select ss.schedule_id,

                                                                     freq_interval,

                                                                     'D1' = CASE WHEN (freq_interval & 1  <> 0) then 'Sun ' ELSE '' END,

                                                                     'D2' = CASE WHEN (freq_interval & 2  <> 0) then 'Mon '  ELSE '' END,

                                                                     'D3' = CASE WHEN (freq_interval & 4  <> 0) then 'Tue '  ELSE '' END,

                                                                     'D4' = CASE WHEN (freq_interval & 8  <> 0) then 'Wed '  ELSE '' END,

                                                                    'D5' = CASE WHEN (freq_interval & 16 <> 0) then 'Thu '  ELSE '' END,

                                                                     'D6' = CASE WHEN (freq_interval & 32 <> 0) then 'Fri '  ELSE '' END,

                                                                     'D7' = CASE WHEN (freq_interval & 64 <> 0) then 'Sat '  ELSE '' END

                                                                 from msdb..sysschedules ss

                                                                where freq_type = 8

                                                           ) as F

                                                       where schedule_id = sj.schedule_id

                                                    )

                         WHEN (freq_type = 16) then 'Day ' + convert(varchar(2),freq_interval)

                         WHEN (freq_type = 32) then (select freq_rel + WDAY

                                                        from (select ss.schedule_id,

                                                                     'freq_rel' = CASE(freq_relative_interval)

                                                                                    WHEN 1 then 'First'

                                                                                    WHEN 2 then 'Second'

                                                                                    WHEN 4 then 'Third'

                                                                                    WHEN 8 then 'Fourth'

                                                                                    WHEN 16 then 'Last'

                                                                                    ELSE '??'

                                                                                  END,

                                                                    'WDAY'     = CASE (freq_interval)

                                                                                    WHEN 1 then ' Sun'

                                                                                    WHEN 2 then ' Mon'

                                                                                    WHEN 3 then ' Tue'

                                                                                    WHEN 4 then ' Wed'

                                                                                    WHEN 5 then ' Thu'

                                                                                    WHEN 6 then ' Fri'

                                                                                    WHEN 7 then ' Sat'

                                                                                    WHEN 8 then ' Day'

                                                                                    WHEN 9 then ' Weekday'

                                                                                    WHEN 10 then ' Weekend'

                                                                                    ELSE '??'

                                                                                  END

                                                                from msdb..sysschedules ss

                                                                where ss.freq_type = 32

                                                             ) as WS

                                                       where WS.schedule_id =ss.schedule_id

                                                       )

                       END,

       'Time' = CASE (freq_subday_type)

                        WHEN 1 then   left(stuff((stuff((replicate('0', 6 - len(Active_Start_Time)))+ convert(varchar(6),Active_Start_Time),3,0,':')),6,0,':'),8)

                        WHEN 2 then 'Every ' + convert(varchar(10),freq_subday_interval) + ' seconds'

                        WHEN 4 then 'Every ' + convert(varchar(10),freq_subday_interval) + ' minutes'

                        WHEN 8 then 'Every ' + convert(varchar(10),freq_subday_interval) + ' hours'

                        ELSE '??'

                      END,

       'Next Run Time' = CASE SJ.next_run_date

                           WHEN 0 THEN cast('n/a' as char(10))

                           ELSE convert(char(10), convert(datetime, convert(char(8),SJ.next_run_date)),120)  + ' ' + left(stuff((stuff((replicate('0', 6 - len(next_run_time)))+ convert(varchar(6),next_run_time),3,0,':')),6,0,':'),8)

                         END

 

   from msdb.dbo.sysjobschedules SJ

   join msdb.dbo.sysjobs         S  on S.job_id       = SJ.job_id

   join msdb.dbo.sysschedules    SS on ss.schedule_id = sj.schedule_id

order by S.name

GO

IF (object_id('sp_ShowJobSchedules') IS NOT NULL)

  PRINT 'Procedure created.'

ELSE

  PRINT 'Procedure NOT created.'

GO

     

Sql queries for IT students

assign no 5
//check
1.create table tebranch (branch_name varchar2(10) primary key , branch_city varchar2(10), assests number(12,2), check (assests >1000));
insert into tebranch values ('sbi','kop',20000);

insert into tebranch values ('icici','kop',30000);

insert into tebranch values ('axis','pune',40000);

insert into tebranch values ('axis','pune',500);//ORA-02290: check constraint (SYSTEM.SYS_C004082) violated

select *from tebranch

BRANCH_NAME BRANCH_CITY ASSESTS
sbi kop 20000
icici kop 30000
axis pune 40000


//unique
2.create table tebranch1 (branch_name varchar2(10)  constraint tecons primary key , branch_city varchar2(10), assests number(12,2), check (assests >1000));

insert into tebranch1 values ('sbi','kop',20000);

insert into tebranch1 values ('icici','kop',30000);

insert into tebranch1 values ('axis','pune',40000);

insert into tebranch1 values ('rbi','pune',40);////ORA-02290:check constraint (SYSTEM.SYS_C004084) violated

insert into tebranch1 values ('sbi','pune',4000);//ORA-00001: unique constraint (SYSTEM.TECONS) violated


//

4.delete from tebranch where branch_name='icici';//ORA-02292: integrity constraint (SYSTEM.SYS_C004046) violated - child record found

//foreign key
5.create table teaccount1 (account_number number(10) not null primary key, branch_name varchar2(10), balance number (12,2), check(balance>100),
foreign key (branch_name) references tebranch1 on delete cascade);

insert into teaccount1 values(1,'sbi',5000);

insert into teaccount1 values(2,'icici',4000);

insert into teaccount1 values(3,'axis',3000);

select *from teaccount1;

ACCOUNT_NUMBER BRANCH_NAME BALANCE
1 sbi 5000
2 icici 4000
3 axis 3000

delete from tebranch1 where branch_name='icici';
1 row(s) deleted.
select * from tebranch;
BRANCH_NAME BRANCH_CITY ASSESTS
sbi kop 20000
axis pune 40000
select *from teaccount1;

ACCOUNT_NUMBER BRANCH_NAME BALANCE
1 sbi 5000
3 axis 3000


//not null
6.create table stebranch (branch_name varchar2(10) primary key , branch_city varchar2(10) not null, assests number(12,2), check (assests >1000));

insert into stebranch values('sbi','kop',2000);

insert into stebranch values('icici','kop',3000);

select *from stebranch;

BRANCH_NAME BRANCH_CITY ASSESTS
sbi kop 2000
icici kop 3000

insert into stebranch values('sbi','kop',4000);//unique constraint (SYSTEM.SYS_C004096) violated

insert into stebranch values('sbi',null,2000);//cannot insert NULL into ("SYSTEM"."STEBRANCH"."BRANCH_CITY")

insert into stebranch values('sbi','kop',300);// check constraint (SYSTEM.SYS_C004095) violated


7.create table stebranch1 (branch_name varchar2(10) constraint xxxx primary key , branch_city varchar2(10) not null, assests number(12,2),
check (assests >1000));  

insert into stebranch1 values('sbi','kop',10000);

insert into stebranch1 values('icici','kop',10000);  

select *from stebranch1

BRANCH_NAME BRANCH_CITY ASSESTS
sbi kop 10000
icici kop 10000

insert into stebranch1 values('sbi','kop',10000);// ORA-00001: unique constraint (SYSTEM.XXXX) violated

insert into stebranch1 values('icici',null,10000); //ORA-01400: cannot insert NULL into ("SYSTEM"."STEBRANCH1"."BRANCH_CITY")


8.//primary key
create table stebranch (branch_name varchar2(10) primary key , branch_city varchar2(10) not null, assests number(12,2), check (assests >1000));

insert into stebranch values('sbi','kop',2000);

insert into stebranch values('icici','kop',3000);

insert into stebranch values('sbi','kop',2000);//ORA-00001: unique constraint (SYSTEM.SYS_C004096) violated

select *from stebranch;

BRANCH_NAME BRANCH_CITY ASSESTS
sbi kop 2000
icici kop 3000

//foreign key

9.
create table steaccount (account_number number(10) not null primary key, branch_name varchar2(10), balance number (12,2), check(balance>100),
foreign key (branch_name) references stebranch);  

insert into steaccount values(1,'sbi',3400);

insert into steaccount values(2,'icici',3400);

select *from steaccount;

ACCOUNT_NUMBER BRANCH_NAME BALANCE
1 sbi 3400
2 icici 3400

insert into steaccount values(1,'sbi',3400); //ORA-00001: unique constraint (SYSTEM.SYS_C004102) violated

insert into steaccount values(2,'rbi',3400);//ORA-02291: integrity constraint (SYSTEM.SYS_C004102) violated - parent key not found

delete from stebranch where branch_name='sbi';//ORA-02292: integrity constraint (SYSTEM.SYS_C004103) violated - child record found

insert into steaccount values(null,'sbi',3400);//ORA-01400: cannot insert NULL into ("SYSTEM"."STEACCOUNT"."ACCOUNT_NUMBER")

//foreign & delete cascade
10.create table steaccount1 (account_number number(10) not null primary key, branch_name varchar2(10), balance number (12,2), check(balance>100),
foreign key (branch_name) references stebranch on delete cascade);

insert into steaccount1 values(1,'sbi',10000);

insert into steaccount1 values(2,'icici',10000);

select *from steaccount1;

ACCOUNT_NUMBER BRANCH_NAME BALANCE
1 sbi 10000
2 icici 10000

delete from steaccount1;
2 row(s) deleted.
//delete from stebranch where branch_name='sbi'
select *from steaccount1;
no data found




//character function

1.select upper(branch_name) as branch_name from tebranch;

BRANCH_NAME
SBI
ICICI
AXIS

2.select lower(branch_name) from tebranch;

LOWER(BRANCH_NAME)
sbi
icici
axis

3.select initcap(branch_name) from tebranch;

INITCAP(BRANCH_NAME)
Sbi
Icici
Axis

4.select concat(branch_name,branch_city) from tebranch;

CONCAT(BRANCH_NAME,BRANCH_CITY)
sbikop
icicikop
axispune
   select concat(branch_name,assests) from tebranch;

CONCAT(BRANCH_NAME,ASSESTS)
sbi20000
icici30000
axis40000

5.select substr(branch_name,2,3)from tebranch;

SUBSTR(BRANCH_NAME,2,3)
bi
cic
xis

6.select instr(branch_name,'i')from tebranch;

INSTR(BRANCH_NAME,'I')
3
1
3

7.select trim('i' from branch_name)from tebranch;

TRIM('I'FROMBRANCH_NAME)
sb
cic
axis

8.select length(branch_name)from tebranch;

LENGTH(BRANCH_NAME)
3
5
4

9.select lpad(branch_name,10,'xx')from tebranch;

LPAD(BRANCH_NAME,10,'XX')
xxxxxxxsbi
xxxxxicici
xxxxxxaxis
   select rpad(branch_name,10,'xx')from tebranch;

RPAD(BRANCH_NAME,10,'XX')
sbixxxxxxx
icicixxxxx
axisxxxxxx



//number fuction

1.select round(123.467,2) from dual;

ROUND(123.467,2)
123.47
   select round(assests,3)from tebranch

ROUND(ASSESTS,3)
20000
30000
40000
   select *from tebranch

2.select trunc(123.467,2) from dual;

TRUNC(123.467,2)
123.46
   select trunc(assests,2) from tebranch;

TRUNC(ASSESTS,2)
20000
30000
40000

3.select power(2,3) from dual;

POWER(2,3)
8
   select power(assests,2) from tebranch;

POWER(ASSESTS,2)
400000000
900000000
1600000000
 
4.select abs(-2) from dual;

ABS(-2)
2
   select abs(-3) from tebranch;

ABS(-3)
3
3
3
 
5.select mod(5,3) from dual;

MOD(5,3)
2
   select mod(5,3) from tebranch;

MOD(5,3)
2
2
2


//date function
1.select sysdate from dual;

SYSDATE
20-MAR-13

2.select months_between('10-mar-13', '10-july-13') from dual;

MONTHS_BETWEEN('10-MAR-13','10-JULY-13')
-4

3.select add_months('20-mar-13', 5) from dual;
or
 select add_months(sysdate, 5) from dual;

ADD_MONTHS('20-MAR-13',5)
20-AUG-13

4.select next_day('20-mar-13', 'sunday') from dual;
or
   select next_day(sysdate, 'sunday') from dual;

NEXT_DAY('20-MAR-13','SUNDAY')
24-MAR-13

5.select last_day('20-mar-13') from dual;
or
  select last_day(sysdate) from dual;

LAST_DAY('20-MAR-13')
31-MAR-13

6.select round(sysdate,'year') from dual;

ROUND(SYSDATE,'YEAR')
01-JAN-13
  select round(sysdate,'mm') from dual;
ROUND(SYSDATE,'MM')
01-APR-13

7.select trunc(sysdate,'mm') from dual;
or
  select trunc(sysdate,'year') from dual;

TRUNC(SYSDATE,'MM')
01-MAR-13

8.select to_char(sysdate,'yyyy') from dual;

TO_CHAR(SYSDATE,'YYYY')
2013

  select to_char(sysdate,'mm') from dual;

TO_CHAR(SYSDATE,'MM')
03


9.select to_date('20032012','dd-mm-yyyy') from dual;

TO_DATE('20032012','DD-MM-YYYY')
20-MAR-12

10.select to_number(marks,'99.9') from testud;