Date Formates

SELECT CONVERT(CHAR(101),getdate(),0) as "Feb  1 2012 11:11AM"
SELECT CONVERT(CHAR(101),getdate(),1) as "02/01/12"
SELECT CONVERT(CHAR(101),getdate(),2) as "12.02.01"
SELECT CONVERT(CHAR(101),getdate(),3) as "01/02/12"
SELECT CONVERT(CHAR(101),getdate(),4) as "01.02.12"
SELECT CONVERT(CHAR(101),getdate(),5) as "01-02-12"
SELECT CONVERT(CHAR(101),getdate(),6) as "01 Feb 12"
SELECT CONVERT(CHAR(101),getdate(),7) as "Feb 01,12"
SELECT CONVERT(CHAR(101),getdate(),8) as "11:13:35"
SELECT CONVERT(CHAR(101),getdate(),9) as "Feb  1 2012 11:13:53:717AM"
SELECT CONVERT(CHAR(101),getdate(),10)as "02-01-12"
SELECT CONVERT(CHAR(101),getdate(),11)as "12/02/01"
SELECT CONVERT(CHAR(101),getdate(),12)as "120201"
SELECT CONVERT(CHAR(101),getdate(),13)as "01 Feb 2012 11:15:15:107"
SELECT CONVERT(CHAR(101),getdate(),14)as "11:15:48:340"
SELECT CONVERT(CHAR(101),getdate(),20)as "2012-02-01 11:16:09"
SELECT CONVERT(CHAR(101),getdate(),21)as "2012-02-01 11:16:36.530"
SELECT CONVERT(CHAR(101),getdate(),22)as "02/01/12 11:17:11 AM"
SELECT CONVERT(CHAR(101),getdate(),23) as "2012-02-01"

SELECT CONVERT(CHAR(101),getdate(),24) as "15:10:46"
SELECT CONVERT(CHAR(101),getdate(),25) as "2012-02-01 15:11:22.310"
SELECT CONVERT(CHAR(101),getdate(),100) as "Feb  1 2012  3:11PM"
SELECT CONVERT(CHAR(101),getdate(),101) as "02/01/2012"
SELECT CONVERT(CHAR(101),getdate(),102) as "2012.02.01"
SELECT CONVERT(CHAR(101),getdate(),103) as "01/02/2012"
SELECT CONVERT(CHAR(101),getdate(),104) as "01.02.2012"
SELECT CONVERT(CHAR(101),getdate(),105) as "01-02-2012"
SELECT CONVERT(CHAR(101),getdate(),106) as "01 Feb 2012"
SELECT CONVERT(CHAR(101),getdate(),107) as "Feb 01,2012"
SELECT CONVERT(CHAR(101),getdate(),102) as "2012.02.01"
SELECT CONVERT(CHAR(101),getdate(),109) as "Feb  1 2012  3:14:15:187PM"
SELECT CONVERT(CHAR(101),getdate(),110) as "02-01-2012"
SELECT CONVERT(CHAR(101),getdate(),111) as "2012/02/01"
SELECT CONVERT(CHAR(101),getdate(),112) as "20120201"
SELECT CONVERT(CHAR(101),getdate(),113) as "01 Feb 2012 15:15:10:700"
SELECT CONVERT(CHAR(101),getdate(),114) as "15:15:36:607"
SELECT CONVERT(CHAR(101),getdate(),120) as "2012-02-01 15:15:55"
SELECT CONVERT(CHAR(101),getdate(),121) as "2012-02-01 15:16:21.857"
SELECT CONVERT(CHAR(101),getdate(),126) as "2012-02-01T15:16:38.937"
SELECT CONVERT(CHAR(101),getdate(),130) as "9 ???? ????? 1433  3:16:58:200PM"
SELECT CONVERT(CHAR(101),getdate(),131) as " 9/03/1433  3:17:13:327PM"

Count millions of records

COUNT_BIG() :

It works like a count function, difference is that count returns int value while COUNT_BIG() returns bigint.
while counting bulk of records at that time use count_big().

COUNT_BIG ( { [ ALL | DISTINCT ] expression } | * ) 

Count Duplication

create table books
(
 id int identity(1,1),
 custname varchar(25),
 bookname varchar(25)
)

INSERT [dbo].[books] ( [custname], [bookname]) VALUES ( 'naimish','five point someone')
INSERT [dbo].[books] ( [custname], [bookname]) VALUES ( 'ravi', 'five point someone')
INSERT [dbo].[books] ( [custname], [bookname]) VALUES ( 'jugal','five point someone')
INSERT [dbo].[books] ( [custname], [bookname]) VALUES ( 'jugal','3 mistakes of my life ')
INSERT [dbo].[books] ( [custname], [bookname]) VALUES ( 'naimish','3 mistakes of my life ')
INSERT [dbo].[books] ( [custname], [bookname]) VALUES ( 'nikhi', 'satya na prayogo')
INSERT [dbo].[books] ( [custname], [bookname]) VALUES ( 'ram', 'ravan')
INSERT [dbo].[books] ( [custname], [bookname]) VALUES ( 'misi','evil')
INSERT [dbo].[books] ( [custname], [bookname]) VALUES ( 'michel','evil')

Ans :-


SELECT bookname,COUNT(*) NoOfbooks
FROM books
GROUP BY bookname
HAVING COUNT(*) > 1
ORDER BY COUNT(*) DESC

Split Function

create FUNCTION Naimish_split
( @string varchar(max),  @Delimeter varchar(2))
RETURNS @temp TABLE
(
id int identity(1,1),
name varchar(25)
)
AS
begin
set @string = replace(@string,' ','')
declare @count int , @countDest int,@no int
select @count = 0,@countDest = LEN(@String) - LEN(REPLACE(@String, @Delimeter, ''))
while @count < @countDest
begin
set @no =  charindex(@Delimeter,@string,1)-1
insert into @temp (name) values(left(@string,@no))
set @string = right(@string,len(@string)- (@no+1))
set @count = @count + 1
end
insert into @temp(name) values(@string)
return
end

--select * from Naimish_split('Prashant,naimish,ravi,vimal,jugal,Bhadresh,mohit' , ',')

O/P:

csv values in single query

create table emailData
(
id int identity(1,1),
email varchar(30)
)
insert into emaildata values('nbravrani@india.com')
insert into emaildata values('mohit.raiyani@india.com')
insert into emaildata values('jkundal@india.com')
insert into emaildata values('vimal@india.com')
insert into emaildata values('ravijohsi@india.com')
insert into emaildata values('sutariya.bhadresh@india.com')
insert into emaildata values('riteshpatadiya@india.com')
insert into emaildata values('ajay@india.com')


Que:
O/P like(Comma seperated using single query):
nbravrani@india.com,mohit.raiyani@india.com,.....

ANS:
select email + ',' from emailData for xml path('')

Get Random record

Que : How To get  Random Record every time from a table using a single query ?

create table _record
(
   id int identity(1,1),
   name varchar(15),
  address varchar(15)
)

insert into _record values('naimish','Gondal')
insert into _record values('vimal','Devchadi')
insert into _record values('jugal','junagadh')
insert into _record values('ravi','Amreli')
insert into _record values('Bhadresh','Bhader')
insert into _record values('Mohit','Rajkot')

sqlchallenge 1

Give bookname which is more then one time and number of books also


create table books
(
id int identity(1,1),
custname varchar(25),
bookname varchar(25)
)

INSERT [dbo].[books] ( [custname], [bookname]) VALUES ( N'naimish', N'five point someone')
INSERT [dbo].[books] ( [custname], [bookname]) VALUES ( N'ravi', N'five point someone')
INSERT [dbo].[books] ( [custname], [bookname]) VALUES ( N'jugal', N'five point someone')
INSERT [dbo].[books] ( [custname], [bookname]) VALUES ( N'jugal', N'3 mistakes of my life ')
INSERT [dbo].[books] ( [custname], [bookname]) VALUES ( N'naimish', N'3 mistakes of my life ')
INSERT [dbo].[books] ( [custname], [bookname]) VALUES ( N'nikhi', N'satya na prayogo')
INSERT [dbo].[books] ( [custname], [bookname]) VALUES ( N'ram', N'ravan')
INSERT [dbo].[books] ( [custname], [bookname]) VALUES ( N'misi', N'evil')
INSERT [dbo].[books] ( [custname], [bookname]) VALUES ( N'michel', N'evil')

like this.

sqlchallenge 2

id           Bookname
----------------------
1           GlobleWorld
2           Tunner
3           Roster in city
4          GNom
5          Three mistake
6          other,Not specified
7          The Secret
8          inspiration

give result order by bookname but last row must be a "other,Not specified"
like this

-------------------------------------------------------------------------

id           Bookname
----------------------
1           GlobleWorld
4           GNom 
8           inspiration 
3           Roster in city 
7          The Secret  
5          Three mistake
2           Tunner
6          other,Not specified

Some inbuilt useful store procedures.

--Drop all table from database
EXEC sp_MSForEachTable "Drop TABLE ?"

--Delete data from all tables
EXEC sp_MSForEachTable "DELETE FROM ?"

--truncate data from all tables
EXEC sp_MSForEachTable "Truncate table ?"

--Desable all constraints
EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"

--Enable all constraints
exec sp_msforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"

--we want to reseed identity
EXEC sp_MSforeachtable "DBCC CHECKIDENT ( '?', RESEED, 0)"

--Shrink Database
EXEC sp_MSForEachDB 'DBCC SHRINKDATABASE (''?'' , 0)'

Nth Highest query

create table emp
(
empid int identity(1,1) primary key,
empname varchar(25),
salary int,
deptid int references dept(deptid)
)
insert into emp values ('naimish',900,1)
insert into emp values ('ravi',2200,2)
insert into emp values ('vimal',1000,2)
insert into emp values ('ajay',1400,3)
insert into emp values ('jugal',1100,3)
insert into emp values ('ritesh',1500,1)
insert into emp values ('gati',3000,2)
insert into emp values ('mohit',1600,1)

select * from
(
select ROW_NUMBER() over(order by salary desc)rank,* from emp
)as t
where rank  = 1