FINDING TABLE PRESENTED IN WHAT DATABASES

select name from sys.databases

declare @a varchar(max)=''
select @a=@a+'
if exists(select 1 from '+name+'.INFORMATION_SCHEMA.TABLES where TABLE_NAME=''emp'')
print ''emp in :'+name+char(39)
from sys.databases
exec(@a)

result of selected number of tables

declare @a varchar(max)='dept1,dept2,dept3,EMP,EmpDup,ExamResult,ggg'
declare @b varchar(max)=''
select @b=@b+'
select * from '+replace(@a,',',' select * from ')
print(@b)

Phone numbers adding 000

CREATE TABLE TBL_PHON_NUMBS(ID INT IDENTITY,PH_NUM BIGINT)

 INSERT INTO TBL_PHON_NUMBS VALUES(1234567890),(123456789),(987456123),(65498730)

 SELECT * FROM TBL_PHON_NUMBS

 SELECT CASE WHEN LEN(CAST(PH_NUM AS VARCHAR(MAX)))=10 THEN
 LEFT(CAST(PH_NUM AS VARCHAR(MAX)),3)+'-'+
 LEFT(RIGHT(CAST(PH_NUM AS VARCHAR(MAX)),7),3)
 +'-'+RIGHT(CAST(PH_NUM AS VARCHAR(MAX)),4)
 WHEN LEN(CAST(PH_NUM AS VARCHAR(MAX)))=9 THEN
 +'0'+LEFT(CAST(PH_NUM AS VARCHAR(MAX)),2)+'-'+
 LEFT(RIGHT(CAST(PH_NUM AS VARCHAR(MAX)),7),3)
 +'-'+RIGHT(CAST(PH_NUM AS VARCHAR(MAX)),4)
 WHEN LEN(CAST(PH_NUM AS VARCHAR(MAX)))=8 THEN
 +'00'+LEFT(CAST(PH_NUM AS VARCHAR(MAX)),1)+'-'+
 LEFT(RIGHT(CAST(PH_NUM AS VARCHAR(MAX)),7),3)
 +'-'+RIGHT(CAST(PH_NUM AS VARCHAR(MAX)),4)
 ELSE CAST(PH_NUM AS VARCHAR(MAX)) END FROM TBL_PHON_NUMBS

count of signs

CREATE TABLE TBL_COUNT(ID INT)

 INSERT INTO TBL_COUNT VALUES(1),(-1),(-2),(1),(2),(-1),(1)

 SELECT * FROM TBL_COUNT

 SELECT SIGN(ID) AS ID,COUNT(ID) FROM TBL_COUNT GROUP BY SIGN(ID)

Difference Between Rebuild and Reorganize indexes

Syntax :
Rebuild : ALTER INDEX ALL ON  table_name REBUILD
Reorganize : ALTER INDEX ALL ON  table_name REORGANIZE

S.no	REBUILD	REORGANIZE
1	This process drops the existing Index and Recreates the index	This process doesn’t drop indexes; It physically reorganizes the leaf nodes of the index.
2	Syntax: ALTER INDEX ALL ON table_name REBUILD	Syntax: ALTER INDEX ALL ON table_name REORGANIZE
3	Index should be rebuild when index fragmentation is great than 30%	Index should be reorganized when index fragmentation is between 10% to 30%
4	Index rebuilding process uses more CPU and it locks the database resources	Index reorganize process uses less CPU and it doesn’t the database resources
5	Rebuilding an index can be executed online or offline	Reorganizing an index is always executed online
6	A nice thing about rebuilding an index is that you can change the setting for index properties like the fill factor and index padding	Index options cannot be specified when reorganizing an index
7	Requires ALTER permission on the table or view or  User must be a member of the sysadmin fixed server role or the db_ddladmin and db_owner fixed database roles	Requires ALTER permission on the table or view or  User must be a member of the sysadmin fixed server role or the db_ddladmin and db_owner fixed database roles
8	REBUILD locks the table for the whole operation period except in Enterprise Edition with ONLINE = ON	REORGANIZE doesn't lock the table.
9	It is changing the whole B-tree, recreating the index	It is a process of cleaning, organizing, and defragmenting of only  "leaf level" of the B-tree
10	REBUILD will automatically rebuild all index-column statistics	Statistics are NOT updated with a reorganization
11	This reclaims disk space by compacting the pages in new index	It works in two phases – compaction and defrag
12	Rebuild does require extra space as same the size of index	Reorganize essentially just swaps one page with another and thus does not require free space
13	Rebuild can fix extent fragmentation as well	Reorganize can remove almost all of the logical fragmentation but it cannot necessarily fix extent fragmentation
14	Index (clustered or non-clustered) cannot be built online if it contains LOB data (text, ntext, varchar(max), nvarchar(max), varbinary(max), image and xml data type columns).	Its always online operation.
15	If an index has multiple partitions, then you cannot rebuild a single partition online	You can reorganize a single index partition online
16	After rebuild statistics on non-indexed columns doesn’t get re-computed	Statistics are NOT updated with a reorganization
17	Rebuild is an atomic operation	Reorganize can be stopped while it is occurring and you won't lose the work it has already completed
18	Rebuild indexes always generates similar amount of  t-log for same size index	For large, heavily fragmented indexes the reorganization operation will generate more t-log as compared to less fragmented index
19	REBUILD requires enough free space in the database to accommodate the new index	REORGANIZE only requires 8KB of additional space in the database
20	REBUILD can use multiple CPUs so the operation runs faster	REORGANIZE is always single-threaded
21	REBUILD can use minimal-logging to reduce transaction log growth	REORGANIZE is always fully logged, but doesn’t prevent transaction log clearing.
22	An index rebuild will always rebuild the entire index, regardless of the extent of fragmentation	An index reorganize will only take care of the existing fragmentation
23	SQL 2000 use DBCC DBREINDEX	SQL 2000 use DBCC INDEXDEFRAG
24	Even If the index spans multiple data files, it will be taken care	If the index spans multiple data files, reorganizing will only reorder pages within the same file, it won’t move pages betweenfiles

CONVERT DATE FUNCTIONS

SQL CAST and CONVERT

It converts an expression from one data type to another.
CAST and CONVERT have similar functionality.

SQL CAST and CONVERT Syntax
Using CAST:
CAST ( expression AS data_type ) 

Using CONVERT:CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

Example of SQL Cast and Convert
SQL Cast and Convert - StringSELECT SUBSTRING('CAST and CONVERT', 1, 3)
Return Value = CAS (it get from index 1 to 3)

SELECT CAST('CAST and CONVERT' AS char(3))
Return Value = CAS (it get 3 char only)


SQL Cast and Convert - Date Time
-Converting date time to character data(vachar)
-The default values (style 0 or 100, 9 or 109, 13 or 113, 20 or 120, and 21 or 121) always return the Without century year(yy).
-Add 100 to a style value to get a four-place year that includes the century year(yyyy).
-Below is example for converting 1 format of date time to different format of date time, so that it can be use in various condition.

Value of current Date Time GETDATE()
SELECT (GETDATE()) = 2007-06-06 23:41:10.153

SELECT CONVERT(varchar,GETDATE(),0)
Return Value = Jun 6 2007 11:07PM
SELECT CONVERT(varchar,GETDATE(),100)
Return Value = Jun 6 2007 11:07PM


SELECT CONVERT(varchar,GETDATE(),1)
Return Value = 06/06/07
SELECT CONVERT(varchar,GETDATE(),101)
Return Value = 06/06/2007


SELECT CONVERT(varchar,GETDATE(),2)
Return Value = 07.06.06
SELECT CONVERT(varchar,GETDATE(),102)
Return Value = 2007.06.06

SELECT CONVERT(varchar,GETDATE(),3)
Return Value = 06/06/07
SELECT CONVERT(varchar,GETDATE(),103)
Return Value = 06/06/2007


SELECT CONVERT(varchar,GETDATE(),4)
Return Value = 06.06.07
SELECT CONVERT(varchar,GETDATE(),104)
Return Value = 06.06.2007

SELECT CONVERT(varchar,GETDATE(),5)
Return Value = 06-06-07
SELECT CONVERT(varchar,GETDATE(),105)
Return Value = 06-06-2007


SELECT CONVERT(varchar,GETDATE(),6)
Return Value = 06 Jun 07
SELECT CONVERT(varchar,GETDATE(),106)
Return Value = 06 Jun 2007


SELECT CONVERT(varchar,GETDATE(),7)
Return Value = Jun 06, 07
SELECT CONVERT(varchar,GETDATE(),107)
Return Value = Jun 06, 2007


SELECT CONVERT(varchar,GETDATE(),8)
Return Value = 23:38:49
SELECT CONVERT(varchar,GETDATE(),108)
Return Value = 23:38:49


SELECT CONVERT(varchar,GETDATE(),9)
Return Value = Jun 6 2007 11:39:17:060PM
SELECT CONVERT(varchar,GETDATE(),109)
Return Value = Jun 6 2007 11:39:17:060PM


SELECT CONVERT(varchar,GETDATE(),10)
Return Value = 06-06-07
SELECT CONVERT(varchar,GETDATE(),110)
Return Value = 06-06-2007


SELECT CONVERT(varchar,GETDATE(),11)
Return Value = 07/06/06
SELECT CONVERT(varchar,GETDATE(),111)
Return Value = 2007/06/06


SELECT CONVERT(varchar,GETDATE(),12)
Return Value = 070606
SELECT CONVERT(varchar,GETDATE(),112)
Return Value = 20070606


SELECT CONVERT(varchar,GETDATE(),13)
Return Value = 06 Jun 2007 23:40:14:577
SELECT CONVERT(varchar,GETDATE(),113)
Return Value = 06 Jun 2007 23:40:14:577


SELECT CONVERT(varchar,GETDATE(),14)
Return Value = 23:40:29:717
SELECT CONVERT(varchar,GETDATE(),114)
Return Value = 23:40:29:717


SELECT CONVERT(varchar,GETDATE(),20)
Return Value = 2007-06-06 23:40:51
SELECT CONVERT(varchar,GETDATE(),120)
Return Value = 2007-06-06 23:40:51


SELECT CONVERT(varchar,GETDATE(),21)
Return Value = 2007-06-06 23:41:10.153
SELECT CONVERT(varchar,GETDATE(),121)
Return Value = 2007-06-06 23:41:10.153


SELECT CONVERT(varchar,GETDATE(),126)
Return Value = 2007-06-06T23:41:10.153


SELECT CONVERT(varchar,GETDATE(),131)
Return Value = 21/05/1428 11:41:10:153PM

STRING FNCTIONS

Example SQL String Function - ASCII
- Returns the ASCII code value of a keyboard button and the rest etc (@,R,9,*) .
Syntax - ASCII ( character)SELECT ASCII('a') -- Value = 97
SELECT ASCII('b') -- Value = 98
SELECT ASCII('c') -- Value = 99
SELECT ASCII('A') -- Value = 65
SELECT ASCII('B') -- Value = 66
SELECT ASCII('C') -- Value = 67
SELECT ASCII('1') -- Value = 49
SELECT ASCII('2') -- Value = 50
SELECT ASCII('3') -- Value = 51
SELECT ASCII('4') -- Value = 52
SELECT ASCII('5') -- Value = 53 


Example SQL String Function - SPACE 
-Returns spaces in your SQL query (you can specific the size of space).
Syntax - SPACE ( integer)
SELECT ('SQL') + SPACE(0) + ('TUTORIALS')
-- Value = SQLTUTORIALS
SELECT ('SQL') + SPACE(1) + ('TUTORIALS')
-- Value = SQL TUTORIALS 


Example SQL String Function - CHARINDEX
-Returns the starting position of a character string.
Syntax - CHARINDEX ( string1, string2 [ , start_location ] ) 
SELECT CHARINDEX('SQL', 'Well organized understand SQL tutorial') 
-- Value = 27SELECT CHARINDEX('SQL', 'Well organized understand SQL tutorial', 20) 
-- Value = 27SELECT CHARINDEX('SQL', 'Well organized understand SQL tutorial', 30)
-- Value = 0 (Because the index is count from 30 and above)


Example SQL String Function - REPLACE
-Replaces all occurrences of the string2 in the string1 with string3.
Syntax - REPLACE ( 'string1' , 'string2' , 'string3' )
SELECT REPLACE('All Function' , 'All', 'SQL')
-- Value = SQL Function


Example SQL String Function - QUOTENAME
-Returns a Unicode string with the delimiters added to make the input string a valid Microsoft® SQL Server™ delimited identifier.
Syntax - QUOTENAME ( 'string' [ , 'quote_character' ] ) 
SELECT QUOTENAME('Sql[]String')
-- Value = [Sql[]]String]


Example SQL String Function - STUFF
- Deletes a specified length of characters and inserts string at a specified starting index.
Syntax - STUFF ( string1 , startindex , length , string2 ) 
SELECT STUFF('SqlTutorial', 4, 6, 'Function')
-- Value = SqlFunctional
SELECT STUFF('GoodMorning', 5, 3, 'good')
-- Value = Goodgoodning


Example SQL String Function - LEFT
-Returns left part of a string with the specified number of characters.
Syntax - LEFT ( string , integer) 
SELECT LEFT('TravelYourself', 6) 
-- Value = Travel
SELECT LEFT('BeautyCentury',6) 
-- Value = Beauty


Example SQL String Function - RIGHT
-Returns right part of a string with the specified number of characters.
Syntax - RIGHT( string , integer)
SELECT RIGHT('TravelYourself', 6)-- Value = urself
SELECT RIGHT('BeautyCentury',6)-- Value = Century


Example SQL String Function - REPLICATE
-Repeats string for a specified number of times.
Syntax - REPLICATE (string, integer)SELECT REPLICATE('Sql', 2) 
-- Value = SqlSql


Example SQL String Function - SUBSTRING
-Returns part of a string.
Syntax - SUBSTRING ( string, startindex , length )
SELECT SUBSTRING('SQLServer', 4, 3) 
-- Value = Ser


Example SQL String Function - LEN
-Returns number of characters in a string.
Syntax - LEN( string) 
SELECT LEN('SQLServer')
-- Value = 9


Example SQL String Function - REVERSE
-Returns reverse a string.Syntax - REVERSE( string)SELECT REVERSE('SQLServer') 
-- Value = revreSLQS


Example SQL String Function - UNICODE
-Returns Unicode standard integer value.
Syntax - UNICODE( char) 
SELECT UNICODE('SqlServer') 
-- Value = 83 (it take first character)
SELECT UNICODE('S')
-- Value = 83


Example SQL String Function - LOWER
-Convert string to lowercase.Syntax - LOWER( string )SELECT LOWER('SQLServer') 
-- Value = sqlserver


Example SQL String Function - UPPER
-Convert string to Uppercase.
Syntax - UPPER( string ) 
SELECT UPPER('sqlserver') 
-- Value = SQLSERVER


Example SQL String Function - LTRIM
-Returns a string after removing leading blanks on Left side.
Syntax - LTRIM( string )SELECT LTRIM(' sqlserver')-- Value = 'sqlserver' (Remove left side space or blanks)


Example SQL String Function - RTRIM 
-Returns a string after removing leading blanks on Right side.
Syntax - RTRIM( string )SELECT RTRIM('SqlServer ')
-- Value = 'SqlServer' (Remove right side space or blanks)

DATE FNCTIONS

select GETDATE()

select DAY('12-22-2014')

select MONTH(getdate())

select YEAR(getdate())

select DATEPART(WEEKDAY,getdate())

select DATEPART(DAY,GETDATE())


select DATEPART(MONTH,getdate())

select DATEPART(YEAR,GETDATE())

select DATEPART(WEEK,GETDATE())

select DATENAME(WEEKDAY,GETDATE())

select DATENAME(DAY,GETDATE())

select DATENAME(WEEK,GETDATE())

select DATENAME(MONTH,GETDATE())

SELECT DATENAME(YEAR,GETDATE())

SELECT DATEADD(DAY,1,GETDATE())

SELECT DATEADD(WEEKDAY,5,GETDATE())

SELECT DATEADD(WEEK,5,GETDATE())

SELECT DATEADD(MONTH,1,GETDATE())

SELECT DATEADD(YEAR,1,GETDATE())

SELECT DATEDIFF(DAY,'01-01-2014',GETDATE())

SELECT DATEDIFF(WEEKDAY,'01-01-2010','12-02-2014')

SELECT DATEDIFF(WEEK,'01-01-2010','12-02-2014')

SELECT DATEDIFF(MONTH,'01-01-2010','12-02-2014')

SELECT DATEDIFF(YEAR,'03-20-1989',getdate())

Duplicate records

Create a table EmpDtl1 with some duplicate rows as shown below to understand different methods of delete duplicate rows.

    create table EmpDup(empid int,name varchar(20))
    
    insert into EmpDup values(1,'Andy')
    insert into EmpDup values (1,'Andy')
    insert into EmpDup values(2,'Bill')
    insert into EmpDup values(2,'Bill')
    insert into EmpDup values (2,'Bill')
    insert into EmpDup values (3,'Chris')
    

User Name "Andy" repeated 2 times and User Name "Bill" repeated 3 times.

Following are the different methods for deleting duplicate rows.

Method 1:

Insert the distinct rows from the duplicate rows table to new temporary table. Delete data from table which has duplicate rows then insert the distinct rows from the temporary table as shown below.

    select distinct * into #tmp From EmpDup
    delete from EmpDup
    insert into EmpDup                
    select * from #tmp drop table #tmp
    

Method 2:

If you want to consider only few columns in a table for duplication criteria to delete rows then Method 1 will not work(in our example, if EMDup table has more than 2 columns and delete rows if empid and name repeats more than one time).

In this case, Add an identity column as a serial number that acts as a row unique identifier(auto incremental ascending order).Then get the Rank against each empid,name. If Rank is greater than 1 means it is a duplicate row and delete the same. After deleting the duplicated rows, remove the identity column which is used for rank. See the below example.

    alter table EmpDup add  sno int identity(1,1)

    delete E
    from  EmpDup E
    inner join
    (select *,
    RANK() OVER ( PARTITION BY empid,name ORDER BY sno DESC )rank
    From EmpDup )T on E.sno=t.sno
    where T.Rank>1

    alter table EmpDup 
    drop  column sno
    

If you feel this query is little difficult to understand then use following same query but in different way!

    alter table EmpDup add  sno int identity(1,1)
    delete from EmpDup where sno in
    (
    select sno from (
    select *,
    RANK() OVER ( PARTITION BY empid,name ORDER BY sno DESC )rank
    From EmpDup
    )T
    where rank>1
    )

    alter table EmpDup 
    drop  column sno
    

If your sql server version is below SQL2005 then above queries (above 2 queries in Method2) won't work since Rank() command is not available in SQL 2000 and below versions.

If your sql server version is below SQL2005 then use below query. Same logic to get the rank but without using the Rank() command!

    alter table EmpDup add  sno int identity(1,1)
    delete from EmpDup where sno in
    (
    select sno  from EmpDup D where 
    1<(select count(*) from EmpDup A where A.empid=D.empid and A.name=D.name and D.sno>=A.sno)
    )

    alter table EmpDup
    drop  column sno
    

Method 3:

Using "Delete Top( )" clause:

If you want to delete duplicate rows for a particular empid then use "Top()" command in delete query as shown below.

    delete top(2) From EmpDup where empid=2

OR

    delete top(select count(*)-1 From EmpDup x where x.empid=2) From EmpDup where empid=2
    

Method 4:

If you want to delete all the rows if the selected columns repeated more than 1 time then use below query.

Query to delete 3 duplicated rows (in our example table) or repeated more than 1 time.

    delete from EmpDup where EmpID in(select EmpID from EmpDup group by EmpId having
    count(*) >1)

Some Queries About CTE

For selecting dept wise total number of employees

SELECT DEPTNO,COUNT(*) as no_of_employees FROM EMP GROUP BY DEPTNO

For selecting deptname and total number of employees in a dept

;WITH CTE AS (SELECT DEPTNO,COUNT(1)EMPCOUNT FROM EMP GROUP BY DEPTNO)
SELECT D.DNAME,D.DEPTNO,C.EMPCOUNT FROM DEPT D INNER JOIN CTE C ON C.DEPTNO=D.DEPTNO

Selecting hierarchy

;WITH A AS (SELECT * FROM EMP WHERE mgr is null
UNION ALL
SELECT E.* FROM emp e INNER JOIN A ON E.mgr=A.empno
)select * from A

For selecting 2 highest salary

SELECT TOP 1 SAL FROM (SELECT TOP 2 sal from emp ORDER BY SAL asc ) E ORDER BY SAL desc

Selecting top2 salaries

select top 2 sal from emp order by sal asc

Selecting top 1 sal from each dept

;WITH D AS(SELECT *,ROW_NUMBER() OVER (PARTITION BY DEPTNO ORDER BY SAL DESC ) RNO FROM EMP)
SELECT * FROM D WHERE RNO=1

Selecting 5,6 highest sal 

;with x as (select rank()over (order by sal desc) salary ,* from emp)
select * from x where salary in (5,6)

SQL Server Database Mail Setup

Database mail feature was introduced in SQL server 2005 by Microsoft. This feature is also available in SQL Server 2008 and 2012. Before Database mail we have SQL Mail feature in Sql Server 2000.

Database Mail is more reliable, secure, faster than SQL Mail in SQL Server 2000. SQL Mail is based on MAPI (Messaging Application Programming Interface) where as Database Mail is based on SMTP (Simple Mail Transfer Protocol. Moreover database mail uses service broker service and this service need to be enabled for Database Mail.

By default, SQL Database mail is not enabled. We can enable this feature by using system defined stored procedure, configuration manager or by Database Mail Wizard. I am sharing both the tricks to enable this feature.

Setup SQL Database Mail

We can configure SQL Database mail in following steps by using Database mail wizard as show below.

1. Create Profile and Account

   In first step we will create a profile and account by using the Configure Database Mail Wizard as shown below.
              
   A profile can have multiple email accounts. It can be of two types.

   1. Public Profile

      A public profile can be accessed by any users and these users will have the ability to send emails.

   2. Private Profile

      A private profile only accessed by granted users and only these users have the ability to send emails.

2. Configure Database Mail

   After successfully creation of Profile and Account, we will configure the Database Mail using system defined stored procedure “sp_configure ” as shown below.

   
    GO 
   sp_CONFIGURE Database Mail XPs', 1 
   GO 
   RECONFIGURE 
   
   

3. Send Test Mail

   We can send test mail by using wizard and T-SQL statement as shown below.
   Using Wizard
     
   Using T-SQL Statement

   
    USE msdb 
   GO 
   EXEC sp_send_dbmail @profile_name='Shailendra Chauhan Profile', @recipients='shailendra@ymail.com', @subject='Database Mail Test', @body= ‘This is a test e-mail sent from Database Mail' 
   
   

4. Check Your Inbox

   After sending test mail, you need to check the mail received in your inbox. I received the mail "Database Mail Test" in my inbox as shown below:
5.