Tips on SQL

• To knowwhich database running on the Server : "exec sp_who2"

ProcessID - get the processId from exec sp_who2

• How to Kill the database running on the Server : Kill ProcessID

• Use Order by but without culumn name
  -> SELECT id,name FROM tablename order by 1 asc / -> SELECT id,name FROM tablename order by 1 desc
• HAVING clause can also be used without aggregates

->SELECT job FROM works_on GROUP BY job HAVING job LIKE 'M%'

• To Get Exact no. of row from table in SQL
  -> select min(salary) from employee where salary in (select top 3 salary from employee order by salary desc )
• How to geT Version and operating System

-> SELECT SERVERPROPERTY('productversion'),SERVERPROPERTY('productlevel'), SERVERPROPERTY('edition')

• Get accurate count of number of records in tha table

-> SELECT rows FROM sysindexes WHERE id=OBJECT_ID(tablename) AND indid<2

• To Rename DB, Table, Column

-> sp_renamedb 'oldDBname', 'newDBname'

before that we have to change the user mode using

-> sp_dboption DBname,'single user', true

after name change 'single user' as fasle

-> sp_rename 'oldTablename', 'newTablename' for Table anme change

->sp_rename 'tablename.column name', Name change', COLUMN

• Find Columns in table

<->Select Column_Name
From Information_Schema.Columns Where Table_Name = 'WBI_sitemap'And Column_Name <> 'RowVersion' Order By Ordinal_Position

• * To get Scalar Value to the variable

DECLARE @sql nvarchar(100),@i INT
SET @sql='select @i= count(*) from TABLE_NAME'
EXEC SP_EXECUTESQL @sql, @params = N'@i INT OUTPUT', @i = @i OUTPUT

• * To Replace some Char in String

REPLACE(@string,'char need to find','char need to change')

• * To identify the most used indexes in your DB run the following query.

This will help you to identify whether or not your indexes are useful and used.
declare @dbid int
--To get Datbase ID
set @dbid = db_id()
select
db_name(d.database_id) database_name
,object_name(d.object_id) object_name
,s.name index_name,
c.index_columns
,d.*
from sys.dm_db_index_usage_stats d
inner join sys.indexes s
on d.object_id = s.object_id
and d.index_id = s.index_id
left outer join
(select distinct object_id, index_id,
stuff((SELECT ','+col_name(object_id,column_id ) as 'data()' FROM sys.index_columns t2 where t1.object_id =t2.object_id and t1.index_id = t2.index_id FOR XML PATH ('')),1,1,'')
as 'index_columns' FROM sys.index_columns t1 ) c on
c.index_id = s.index_id and c.object_id = s.object_id
where database_id = @dbid
and s.type_desc = 'NONCLUSTERED'
and objectproperty(d.object_id, 'IsIndexable') = 1
order by
(user_seeks+user_scans+user_lookups+system_seeks+system_scans+system_lookups) desc