Hints
Specifies that query optimizer enforce a join strategy between two tables.
Join hints are specified in the FROM clause of a query. Join hints enforce a join strategy between two tables. If a join hint is specified for any two tables, the query optimizer automatically enforces the join order for all joined tables in the query, based on the position of the ON keywords. When a CROSS JOIN is used without the ON clause, parentheses can be used to indicate the join order.
(for more detail search hints keyword in Msdn’http://msdn.microsoft.com/en-us/library/ms187713(v=sql.100).aspx‘)
Some Query
–Select Employee name with total number of phone number.
Emplist(id,Name) values((1,a),(2,b),(3,c),(4,d))
Emp_phone(id,phonenumber)values((1,5454)(1,45454),(2,6767),(3,34343),(3,454545),(7,34343),(11,56565))
select m.name,m.id,COUNT(m_1.phone) as numphone from Emplist as m
left outer join Emp_phone as m_1 on m.id =m_1.id
group by m.name,m.id ;
—-Swap the integer value without using third variable
declare @var1 integer=2;
declare @var2 int =1;
set @var1 =@var1+@var2;– 3
set @var2 =@var1-@var2 –2
set @var1 =@var1-@var2; –1
–set @var1 =@var2 ;
select @var1,@var2;
—-Swap the string value
declare @var3 varchar(max)=’ram’;
declare @var4 varchar(max)=’ram1′;
declare @var5 varchar(max);
set @var5=@var3;–‘ram’
set @var3=@var4;–ram1
set @var4=@var5;
select @var3,@var4;
— IDENTITY_INSERT
Allows explicit values to be inserted into the identity column of a table.
SET IDENTITY_INSERT dbo.table1 off;
If it is off then Cannot insert explicit value for identity column in table ‘table1’ when IDENTITY_INSERT is set to OFF.but in on it is vice versa i.e. insert value in identity column.
note—for set statement detail describe in Msdn ‘http://msdn.microsoft.com/en-us/library/ms190356.aspx‘
–cross join or cartesian join
select * from Emp_phone cross join Emplist;
or (old system-)
select * from Emp_phone ,Emplist;
id phone name id
1 999 amit_1 1
1 9925 amit_1 1
3 5656 amit_1 1
3 4545 amit_1 1
5 5454 amit_1 1
5 544541 amit_1 1
6 54513 amit_1 1
11 524845 amit_1 1
1 999 st 2
1 9925 st 2
3 5656 st 2
3 4545 st 2
5 5454 st 2
5 544541 st 2
6 54513 st 2
11 524845 st 2
1 999 st1 3
1 9925 st1 3
3 5656 st1 3
3 4545 st1 3
5 5454 st1 3
5 544541 st1 3
6 54513 st1 3
11 524845 st1 3
1 999 st2 4
1 9925 st2 4
3 5656 st2 4
3 4545 st2 4
5 5454 st2 4
5 544541 st2 4
6 54513 st2 4
11 524845 st2 4
1 999 st3 5
1 9925 st3 5
3 5656 st3 5
3 4545 st3 5
5 5454 st3 5
5 544541 st3 5
6 54513 st3 5
11 524845 st3 5
1 999 mukesh 6
1 9925 mukesh 6
3 5656 mukesh 6
3 4545 mukesh 6
5 5454 mukesh 6
5 544541 mukesh 6
6 54513 mukesh 6
11 524845 mukesh 6
1 999 amit 7
1 9925 amit 7
3 5656 amit 7
3 4545 amit 7
5 5454 amit 7
5 544541 amit 7
6 54513 amit 7
11 524845 amit 7
1 999 amit7 8
1 9925 amit7 8
3 5656 amit7 8
3 4545 amit7 8
5 5454 amit7 8
5 544541 amit7 8
6 54513 amit7 8
11 524845 amit7 8
–Select for salary count more than 4999 and count zero if salary less than 4999.
create table #tmp(eid int not null ,ename varchar(25),salary int ,depid varchar(25) );
insert into #tmp values (22 ,‘shyam’,3000,‘d5’),( 23 ,‘mohan’, 5500 ,‘d6’ ),(44 ,‘radha’, 8000 , ‘d33’ ),(48 ,‘abhi’ , 1000 ,‘d9’);
select *from #tmp
select depid, COUNT(casewhen salary >4999 then 1 end)as Tcount from #tmp groupby depid
Difference between Sql server 2000 and 2005
Difference between Sql server 2005 and 2008
11. SSIS is started using. K. SSIS avails in this version