SQL Server Advanced Queries


1)Using XML to Pivot

DECLARE @Table TABLE (tst VARCHAR(10))
INSERT INTO @Table VALUES(1)
INSERT INTO @Table VALUES(2)
INSERT INTO @Table VALUES(3)
INSERT INTO @Table VALUES(4)

--Select the original data set
SELECT * FROM @Table

select ','+tst from @table for xml path('')


--using coalesc
declare @str varchar(max)
select @str=coalesce(@str+',','')+tst from @Table
select @str as Nos
 
--Now pivot the data using XML
select STUFF((select ','+tst from @table for xml path('')),1,1,'') Nos

--Now pivot the data using XML
select STUFF((select ''+tst from @table for xml path('')),1,0,'') Nos



2)Multiple Tables using single Drop Statement

create table #A(eno int)
create table #B(eno int)
create table #C(eno int)

insert into #A values(1)
insert into #B values(2)
insert into #C values(3)

select * from #A
select * from #B
select * from #C

drop table #A,#B,#C

3)Shrinking a data file to a specified target size

use tempdb
SELECT file_id, name FROM sys.database_files;
DBCC SHRINKFILE(templog, 1) ----Shrink a data file
DBCC SHRINKFILE (1, TRUNCATEONLY); ------Truncating a data file


4)Missing Index Details From Your Data Base

select * from sys.dm_db_missing_index_details

5)Delete Duplicate Rows Without Using CTE

--Creating Table
Create table #Emp(empno int,ename varchar(20),sal money)

--Inserting Data
insert into #Emp values(1,'raju',10000),(2,'kapil',20000),(2,'Kapi',20000)

--select for duplicate data

select *
FROM (
select empno,ename,sal,ROW_NUMBER() OVER (partition by empno order by empno) as Dup
from #Emp
) f
WHERE f.Dup =2





--delete Query to find out Duplicate data
DELETE f
FROM (
select empno,ename,sal,ROW_NUMBER() OVER (partition by empno order by empno) as Dup
from #Emp
) f
WHERE f.Dup =2


6)Count Number of Rows for All Tables of a Database

select object_name(id) TableName,rows NumberOfRows
from sysindexes s
join SYS.objects o on o.object_id=s.id
where INDID IN (0,1) and type='U'
order by 2 desc

7)Last Ran Query – Recently Ran Query

SELECT deqs.last_execution_time AS [Time], dest.TEXT AS [Query]
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
ORDER BY deqs.last_execution_time DESC

8)Tables Space Information

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[STATION]') AND type in (N'U'))
BEGIN
CREATE TABLE STATION(ID INTEGER PRIMARY KEY,CITY CHAR(20),STATE CHAR(2),LAT_N REAL,LONG_W REAL);
INSERT INTO STATION VALUES (13, 'Phoenix', 'AZ', 33, 112);
INSERT INTO STATION VALUES (44, 'Denver', 'CO', 40, 105);
INSERT INTO STATION VALUES (66, 'Caribou', 'ME', 47, 68);
SELECT * FROM STATION;
END
GO
SP_SPACEUSED 'STATION'




9)Find out all column names in database?

SELECT t.name AS table_name,
SCHEMA_NAME(schema_id) AS schema_name,
c.name AS column_name
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
ORDER BY schema_name, table_name;


10)Sleep Command in T-SQL?

select * from emp
-- wait for 1 second
WAITFOR DELAY '00:00:01'



11)Cumulative totals Query with T-SQL,
1     5001.00
2     45000.00
3     20000.00

Question:user wants to see the data in below format:
1     5001.00     5001.00
3     20000.00    25001.00
2     45000.00    70001.00


select t.empno,t.sal,
(select sum(sal) from EMP9 p where p.sal<=t.sal)as Cumulative_Totals
from emp9 t
order by t.sal



3:54 AM | |

 

0 comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Your Location