MSSQL TSql Interview Questions

There may be other ways too to solve same question. I am posting one I know

--=================================================================
--- Creating Temp Table
--=================================================================
CREATE TABLE #tmpEmpSal
(
Ename VARCHAR(20),
Dept  VARCHAR(10),
Salary INT
);
--=================================================================
--Populating temp table
--=================================================================
INSERT INTO #tmpEmpSal
SELECT 'Emp1','Dept1',10000 UNION ALL 
SELECT 'Emp2','Dept1',1000 UNION ALL
SELECT 'Emp3','Dept1',12000 UNION ALL
SELECT 'Emp4','Dept1',13000 UNION ALL
SELECT 'Emp5','Dept2',10000 UNION ALL
SELECT 'Emp6','Dept2',5000 UNION ALL
SELECT 'Emp7','Dept2',12000 UNION ALL
SELECT 'Emp8','Dept3',11000 UNION ALL
SELECT 'Emp9','Dept3',10000 UNION ALL
SELECT 'Emp10','Dept3',10000 UNION ALL
SELECT 'Emp11','Dept3',4000 UNION ALL
SELECT 'Emp14','Dept3',14000 UNION ALL
SELECT 'Emp12','Dept4',70000 UNION ALL
SELECT 'Emp13','Dept4',10000 ;

--=================================================================
-- Select lowest salary in Each department
--=================================================================
WITH myCte AS
(
SELECT
        dept,
        salary,
        ROW_NUMBER() OVER ( PARTITION BY dept ORDER BY salary asc ) AS RowNum
FROM    #tmpEmpSal
)
SELECT dept,
       salary
  FROM myCte
 WHERE RowNum=1;

--====================================
--- select nth highest record
--====================================

--Frist select all distinct salary to see nth number record
SELECT  DISTINCT salary
  FROM #tmpEmpSal
 ORDER BY Salary DESC;

--Query to fetch nth record
SELECT MIN(salary)
  FROM #tmpEmpSal
 WHERE Salary IN (
        SELECT  DISTINCT TOP 6  Salary
          FROM #tmpEmpSal --replace top to required nth position
         ORDER BY Salary desc);

--=================================================================
-- Select total number if employees who are having salary as 10000
--=================================================================
SELECT salary ,COUNT(*) Employees
 FROM  #tmpEmpSal
GROUP BY Salary
HAVING Salary = 10000 ;

--=================================================================
--Select salary and number of poeple having this salary
--=================================================================

SELECT salary ,COUNT(*) NoOfEmployeesHavingThisSalary
 FROM  #tmpEmpSal
GROUP BY Salary

--=================================================================
--Select repeated/duplicated salary.salary that exist mmore than one
--=================================================================
SELECT salary ,COUNT(*) NoOfEmployeesHavinfThisSalary
 FROM  #tmpEmpSal
GROUP BY Salary
HAVING COUNT(Salary) >1 ;
--DROP TABLE #tmpEmpSal;

Remove HTML Tags from MSSQL Column Data

Recently I have assigned a task to format data in column to display  and remove Html tags and related special characters. It was fun to develop that code. all we need to identify start and end index of htlm tag "< >" to replace with empty string.

Here you go ..

--Create temp table CREATE TABLE #tmp(PrdName VARCHAR(10),HtmlData VARCHAR(2000)
); 

--Loading test values
INSERT INTO #tmpSELECT 'prd1','<b class="newitem"></b> text<sup>&#174;</sup> - prd1' UNION ALLSELECT 'prd2','<b>prd2</b>' UNION ALLSELECT 'prd3','<b>prd3</b>' ;

--================================

--Create Function to remove html tags

--================================  
CREATE  FUNCTION fnClearHTMLTags ( @HTMLString NVARCHAR(MAX) )RETURNS NVARCHAR(MAX)AS
BEGIN
DECLARE     @Start INT ,
            @End INT ,
            @Length INT
    
  WHILE CHARINDEX('<', @HTMLString) > 0 AND CHARINDEX('>', @HTMLString, CHARINDEX('<', @HTMLString)) > 0
   BEGIN
  SELECT  @Start  = CHARINDEX('<', @HTMLString) , --selecting start index of html tag
          @End    = CHARINDEX  ('>', @HTMLString, CHARINDEX('<', @HTMLString)) -- selecting last index of html tag
  SELECT  @Length = ( @End - @Start ) + 1  --selecting length of html tag data to remove
        
      IF  @Length > 0
   BEGIN
   --Stuff function will replace data between <> to empty string ''
   -- replace function is used to replace other special characters e.g. &,# to empty string
  SELECT  @HTMLString = REPLACE(REPLACE(REPLACE(REPLACE(STUFF(@HTMLString,@Start, @Length,''), '&', ''),'#', ''), '; -','.'), '*', '')
              
     END
     END
    
RETURN LTRIM(RTRIM(@HTMLString ))
END  ;
--================================

--Display Data

--================================   
 SELECT prdName,
       HtmlData,
       dbo.fnClearHTMLTags(HtmlData) AS CleanHtmlData
FROM #tmp;




--================================

--Drop Temp Table

--================================  
DROP TABLE #tmp;-- Drop Function
-- DROP FUNCTION fnClearHTMLTags;

Oracle Schema Security Against Authorize Access

Few time back our dev team reported that application started crash and the reason was missing table.

As it was a legacy application and dev knew schema credentials. One of the dev dropped a table and application started throwing error.

This was alarming and I decided to find suitable way to overcome such situation in future.we can simply  create a new user and assign required privileges to new user.But as we can't change application , it was difficult to implement it. we have to avoid solution where we have to provide schema qualification to access objects.

Initially I though about but synonyms but it seems additional overhead to maintain then I found current_schema option from Oracle documentation.

Here is the step by step guide for you to implement and test that solution.

--Connect as sys
1 --Create UserA
CREATE USER USERA IDENTIFIED BY usera;

2 --Grant required privileges to userA
  GRANT CONNECT TO USERA;
  GRANT RESOURCE TO USERA;

3 -Connect as userA
sqlplus usera/usera

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Mar 11 20:36:04 2014

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL>                             

4 --Create table
  CREATE TABLE USERA_TEST
    ( Id NUMBER, Name VARCHAR2(20)
    );
   
5 --Insert test values USERA.USERA_TEST
INSERT INTO usera_test
SELECT 1,'Val1' FROM dual
UNION ALL
SELECT 2,'Val2' FROM dual
UNION ALL
SELECT 3,'Val3' FROM dual;
COMMIT;
   
6- -Create stored proc
CREATE PROCEDURE usera_sp
AS
BEGIN
  dbms_output.put_line('This is USERA stored Proc');
END;
/

7 -- disconnect USERA

8 --  Connect as sys and process following

9 --Creating role
CREATE ROLE ROLE_USERA_PRIVS;

10 --Create UserB
CREATE USER USERB IDENTIFIED BY userb;

GRANT CONNECT TO USERB;
 
11 --Create Grant Script
Spool ON
spool /path/usera_Privs.sql -- path can be any of your preference

SELECT
  CASE
    WHEN object_type ='TABLE'
    THEN 'Grant select on USERA.'||Object_Name||' to ROLE_USERA_PRIVS;'
    WHEN object_type IN ('PROCEDURE','FUNCTION')
    THEN 'Grant Execute on USERA.'||Object_Name||' to ROLE_USERA_PRIVS;'
    WHEN object_type IN ('SEQUENCE')
    THEN 'Grant SELECT on USERA.'||Object_Name||' to ROLE_USERA_PRIVS;'
    WHEN object_type IN ('MATERIALIZED VIEW','VIEW')
    THEN 'Grant select on USERA.'||Object_Name||' to ROLE_USERA_PRIVS;'
  END AS script
FROM dba_objects
WHERE owner='USERA' ;

spool OFF

12 -- Execute script usera_Privs.sql

13 -- Grant role to UserA and disconnect sys
Grant ROLE_USERA_PRIVS to USERB;

14 -- Connect as USERB
--Now if you will connect to USERB and execute following you will have error "ORA-00942: table or view does not exist"
SQL> select * from usera_test;
select * from usera_test
              *
ERROR at line 1:
ORA-00942: table or view does not exist 

However schema qualification will work
SQL> SELECT * FROM USERA.usera_test;

        ID NAME
---------- --------------------
         1 Val1
         2 Val2
         3 Val3
       
15 -- Disconnect USERB

16 -- Connect as sys.
17 -- Create Trigger to change current_schema to required one. So that our application/user need not to mention schema qualification

SQL>  CREATE OR REPLACE TRIGGER sys.afterlogon_myapp AFTER logon ON DATABASE
  DECLARE
  BEGIN
    -- Trigger Used to Set Correct Schema based on Role
    IF (dbms_session.is_role_enabled('ROLE_USERA_PRIVS')) THEN
      EXECUTE immediate 'alter session set current_schema=USERA';
    END IF;
  END;
  /

18 -- Disconnect sys

19 -- connect USERB 
sqlplus userb/userb

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Mar 11 20:26:09 2014

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
-- execute sql without schema qualification
SQL> select * from usera_test;

        ID NAME
---------- --------------------
         1 Val1
         2 Val2
         3 Val3
-- execute stored proc without schema qualification       
SQL> set serveroutput on
SQL> execute usera_sp
This is USERA stored Proc

PL/SQL procedure successfully completed.
                                          
Enjoy !

Handle Pagination in Stored Procedure

On one of my project I had requirement from team to write a stored proc that take page number and returns results accordingly.It was fun to develop that logic.
I am sharing it for readers to help them writing similar code. following code is developed using MSSQL 2005 .
I hope this will help others.

Table Structure.
-----------------
CREATE TABLE [dbo].[tblQuantityPricing](
    [QuantityPricingId] [int] IDENTITY(1,1) CONSTRAINT [PK_tblQuantityPricing] PRIMARY KEY CLUSTERED ,
    [QuantityFrom] [int] NULL,
    [QuantityTo] [int] NULL,
    [Price] [decimal](16, 4) NULL,
    [ItemId] [int] NULL

);

--Sample DATA loading
-----------
INSERT INTO tblQuantityPricing(QuantityFrom,QuantityTo,Price,ItemId)
SELECT 10,20,12.5,123
UNION ALL
SELECT 10,20,12.5,1234
UNION ALL
SELECT 10,20,12.5,1235
UNION ALL
SELECT 10,20,12.5,1231
UNION ALL
SELECT 10,20,12.5,1234
UNION ALL
SELECT 10,20,12.5,1234
UNION ALL
SELECT 10,20,12.5,1235
UNION ALL
SELECT 10,20,12.5,1235
UNION ALL
SELECT 10,20,12.5,1236
UNION ALL
SELECT 10,20,12.5,1236
UNION ALL
SELECT 10,20,12.5,1236
UNION ALL
SELECT 10,20,12.5,1236
UNION ALL
SELECT 10,20,12.5,1238
UNION ALL
SELECT 10,20,12.5,122;


Stored Proc

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

CREATE PROCEDURE spQuantityPricingByPageGet
    (
      @PageNum TINYINT ,
      @TotalRows INT OUTPUT
    )
AS
    BEGIN
        DECLARE @PageSize INT ;
-- pages size= rows per page. Currently fixed as 10 rows/page. You can set it accordingly or can use input parameter to let application decide
        SET @PageSize = 5 ;
   
-- Page wise result fetch
        WITH    QPrice
                  AS ( SELECT   qp.QuantityPricingId ,
                                qp.itemId ,
                                qp.QuantityFrom ,
                                qp.QuantityTo ,
                                qp.Price,
                                ROW_NUMBER() OVER ( ORDER BY ItemID ASC ) AS RowIndex
                       FROM     dbo.tblQuantityPricing qp
                    
                     )
            SELECT  *
            FROM    Qprice
            WHERE   Qprice.RowIndex BETWEEN ( @PageNum - 1 ) * @PageSize + 1
                                    AND     @PageNum * @PageSize ;
                      
-- Counting Total Rows                                 
        SELECT  @TotalRows = COUNT(*)
        FROM    dbo.tblQuantityPricing;

  

    END ; 

-- Sample Execution
DECLARE @totalRows INT;
EXECUTE spQuantityPricingByPageGet 2,@TotalRows = @TotalRows OUTPUT;
SELECT    @TotalRows as N'@TotalRows'

-- Drop Proc
--DROP PROCEDURE  spQuantityPricingByPageGet
  
--Drop table
--DROP TABLE tblQuantityPricing

Parametrized OpenQuery

While working on an ETl routine, I need to pass parameter to OpenQuery sql.

I searched and found on most of the place that it can be done only with dynamic sql. I want to avoid dynamic sql.
atlast I found Jeff Moden post on sqlservercentral.com. I am posting it here with the intentions that it may help more peoples and  may come up in search engine results.
Thanks to Jeff Moden.

select <stuff ...>
from OPENQUERY(Banner_Prod, 'SELECT a.Major
,a.FIRSTNAME AS NameFirst
,a.MIDDLENAME AS NameMiddle
,a.LASTNAME AS NameLast
,a.CLASSCODE AS PersonSubType
FROM fredtrailer.student_Directory_info a'
) oq
WHERE oq.STUD_ID = @STUD_ID 

Delete Millions of Rows From MSSQL Table

Recently we faced a poor performance in one of our ETL routine and we found that it uses join on 2 tables and structure was very simple. Both table has pk index and sql is seeking right results. Only problem was with data size. Our table2 holds about 27 millions records and it joins to table1 that has only 10000 records. So there was cleanup missing that caused not to delete records from table2 :(.

So the task was to remove millions of unwanted rows. we can initiate delete but this huge amount can cause lot of redo and ultimately can lead to table lock by RDBMS. So the good way is to delete in chunks. 

here is the test run
--======================
-- Creating First Table
--=======================
CREATE TABLE test1
(id INT NOT null);
ALTER TABLE test1 ADD CONSTRAINT pk_test1 PRIMARY KEY (id);

--Loading data to table 1
declare @i int
declare @rows_to_insert int
set @i = 0
set @rows_to_insert = 10000

while @i < @rows_to_insert
    begin
    INSERT INTO test1 VALUES (@i)
    set @i = @i + 1
    end
-- verifying load
select count(*) from test1;

--======================
-- Creating Second Table
--=======================
CREATE TABLE test2
(id INT NOT null);
ALTER TABLE test2 ADD CONSTRAINT pk_test2 PRIMARY KEY (id);

--Loading data, set  @rows_to_insert for rows you want to load
declare @i int
declare @rows_to_insert int
set @i = 0
set @rows_to_insert = 200000

while @i < @rows_to_insert
    begin
    INSERT INTO test2 VALUES (@i)
    set @i = @i + 1
    END

-- verifying load   
SELECT count(*) FROM test2;

-- Query to fetch records that exist in table2 but not in table1

SELECT  test1.id AS id1,test2.id AS id2  FROM    test2 t2
                LEFT JOIN test1 t1 ON t1.id = t2.id
        WHERE   t1.id IS NULL;

-- Delete in chunk
WHILE @@rowcount > 0
    BEGIN
        DELETE TOP ( 5000 )
        FROM    TEST2
        FROM    test2 t2
                LEFT JOIN test1 t1 ON t1.id = t2.id
        WHERE   t1.id IS NULL

    END

MYSql 5.0 Slow Query Log

Performance is always tricky to maintain. few days back I heard from users that they are having some performance issues on site , search is not working as required and some other
components.Developers as always have no clue :) , so I decided to start slow query log.It is easy in 5.1 or above and you don't need to restart mysqld. I am using mysql 5.0
on CentOs,so I have to edit some configurations files and a restart of mysqld service.
I found it handy when you don't have clue from application , so let rdbms work for you.


Step-1 : Create directory where you need to have log file. I have created it in /backup
mkdir     /backup/mysql_slow_log

Step-2 : Create log file .If you will edit configurations and restart mysqld, it will not create file for you, so you have to make it manually.
touch /backup/mysql_slow_log/slow.log

Step-3 : setting folder permissions.
chown mysql.mysql -R /backup/mysql_slow_log/

Step-4 : edit mysql configurations for logging. You need to add 2 parameters under "[mysqld]" in /etc/my.cnf (your mysql conf file) . you can set more parameter like  " log_queries_not_using_indexes" etc
1-log-slow-queries  =log file location
2-long_query_time =time in seconds . if you set it to 1, it mean log queries that are consuming 1 or more seconds.

vi /etc/my.cnf

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
log-slow-queries=/backup/mysql_slow_log/slow.log
long_query_time =1   

Step-5 : Restart mysql service
service mysqld restart

At this stage mysql will start writing log file for you can use mysqldumpslow to make more readable format
mysqldumpslow /backup/mysql_slow_log/slow.log

if you want to disable logging, just remove entries from my.cnf/ini and restart service.