Microsoft SQL Server Help

TableDiff.exe - SQL Server Comparison Utility

2013-06-19T18:28:00.001+05:30

The tablediff.exe is very useful command line utility to compare two tables from SQL Server instances.

Tablediff.exe utility features

· Comparison between two tables from SQL Server instance.

· Row by row fast comparison.

· Perform column level comparison.

· Easily generate change script between production and development SQL server instances.

· Log result can be captured as output file or database table.

Reference : http://msdn.microsoft.com/en-us/library/ms162843.aspx

SQL Puzzle #8

2013-06-18T15:35:00.000+05:30

We need a single select statement to get the count of particular character on a string.

For Eg.,

DECLARE @string varchar(50) = 'Microsoft SQL Server',
@SearchChar char(1) = 'r'

Its result should be '3'

SELECT LEN(@string) - LEN(REPLACE(@string,@SearchChar,''))

SELECT DATALENGTH(REPLACE (@string, @SearchChar, @SearchChar+@SearchChar)) - DATALENGTH (@string) As [Character Count]

SQL Puzzle #7

2013-06-14T18:56:00.002+05:30

We have a table variable say MyTable, structure like below.

declare @MyTable Table
(
TrnDate Date, -- Transaction Date
CrDb char(1), -- Credit/Debit
Amount money
)

-- Data population
insert into @MyTable values
('01-jul-2012','C',1000),('02-jul-2012','D',200),('03-jul-2012','C',2500),('04-jul-2012','C',800),('05-jul-2012','D',5000)

--select * from @MyTable

we need a single query to get the result as below.

TrnDate CrDb Amount Balance
---------- ---- --------------------- ---------------------
2012-07-01 C 1000.00 1000.00
2012-07-02 D 200.00 800.00
2012-07-03 C 2500.00 3300.00
2012-07-04 C 800.00 4100.00
2012-07-05 D 5000.00 -900.00

select TrnDate,CrDb,a.Amount,t.Balance
from @MyTable a
cross apply
(
select sum(case when crdb = 'C' then amount else amount*-1 end) as Balance
from @MyTable b
where b.TrnDate <= a.TrnDate
) t

SQL Puzzle #6

2013-06-14T18:53:00.002+05:30

We have a table say myTable and looks like below.

ID Name Sal
1 a   1000
1 a   2000
2 b   3000
2 b   2000
3 c   2000

And need a query to get the result as below.

ID Name Sal1 Sal2
1 a 1000 2000
2 b 3000 2000
3 c 2000 0

Ans:

Declare @testTable Table
(
ID int,
Name varchar(50),
Sal money
)
Insert into @testTable
Values
(1, 'a', 1000),
(1, 'a', 2000),
(2, 'b', 3000),
(2, 'b', 2000),
(3, 'c', 2000)

select ID,Name,isnull([sal1],0) As [sal1],isnull([sal2],0) As [sal2]
from
(
select id,name,sal,'Sal'+cast(ROW_NUMBER() over (partition by id,name order by id,name) as varchar(10)) as rownum
from @testTable
) sources
pivot
(
sum(sal)
For rownum in([sal1],[sal2])
) pvt

NB: We can also do it with column dynamically..

SQL Puzzle #5

2013-06-13T17:57:00.003+05:30

We have a table, say MyTable and data spreading looks like as follow;

Col1 Col2 Col3 Col4
1 A B C
2 E F G
3 I J K
4 M N O

And need a query to get the output as below;

Col1 MergedCol
1 A
1 B
1 C
2 E
2 F
2 G
3 I
3 J
3 K
4 M
4 N
4 O

And I have a simple strait forward query to get this result.
SELECT col1,col2 from MyTable
Union ALL
SELECT col1,col3 from MyTable
Union ALL
SELECT col1,col4 from MyTable
Union ALL
SELECT col1,col5 from MyTable

Ans:
Create Table TblTest
(
Col1 Int,
Col2 Varchar(50),
Col3 Varchar(50),
Col4 Varchar(50),
)

Insert Into TblTest Values (1,'A','B','C'),(2,'E','F','G'),(3,'I','J','K'),(4,'M','N','O')

Select * From TblTest

Select Col1,Upt.ColNew From TblTest UnPivot (ColNew For ColNewSt In (Col2,Col3,Col4))AS UPT

SQL Puzzle #4 - Comma separated value split up without using loop.

2013-06-11T13:08:00.000+05:30

We have a table variable named @TableVar

--// Creating Table variable
Declare @TableVar Table
(
Data varchar(max)
)

--// Inserting data to table variable
Insert into @TableVar values('01-May-2012,05-May-2012,13-Apr-2012')

Qn: Write a query to get the result as shown below without using loop.

Data
-------------
2012-05-01
2012-05-05
2012-04-13

Ans :

DECLARE @XML XML

SELECT @XML = '<Root><Child><Data>'+REPLACE(Data,',','</Data></Child><Child><Data>')+'</Data></Child></Root>' FROM @TableVar

SELECT CAST(DOC.COL.value('Data[1]','NVARCHAR(400)') AS DATE) AS Data
FROM @XML.nodes('/Root/Child') DOC(COL)

Another Method :

--// Creating Table variable
Declare @TableVar Table
(
Data varchar(max)
)

--// Inserting data to table variable
Insert into @TableVar values('01-May-2012,05-May-2012,13-Apr-2012')

Declare @Str AS Varchar(Max)
Select @Str = 'Select ' +'''' + REPLACE (Data,',',' ''UNION Select ''') FRom @TableVar
Set @Str = @Str + ''''
Exec (@Str)

SQL Puzzle #3 - How to delete all the records from the entire table in a database using single line query?

2013-06-11T13:03:00.003+05:30

How to delete all the records from the entire table in a database using single line query?

Ans : sp_MSForEachTable "Delete ?"

Try it another method;

DECLARE @str VARCHAR(MAX);
SELECT @str =
(
SUBSTRING
(
(SELECT 'DELETE FROM ' + TABLE_NAME + ';' FROM INFORMATION_SCHEMA.TABLES
FOR XML PATH('')),1,99999
)
)
EXEC (@str)

SQL Puzzle #2 - How to populate the same data 5 times to table without using any loop/cursor/recursive query.

2013-06-11T13:02:00.001+05:30

We have a table, say MyTable and we want to populate data to the table using one INSERT query.
My question is, how to populate the same data 5 times to table without using any loop/cursor/recursive query.

--// Table
Create Table MyTable
(
FirstName Varchar(50),
SecondName Varchar(50)
)

--// Sample Insert query as follows;

Insert into MyTable values('Sachin','Tendulkar')

Ans:

Insert into MyTable values('Sachin','Tendulkar')

GO 5

SQL Server - Date Formats

2013-06-11T12:58:00.002+05:30

-- Formatting Dates

Declare @d datetime
select @d = getdate()

select @d as OriginalDate,
convert(varchar,@d,100) as ConvertedDate,
100 as FormatValue,
'mon dd yyyy hh:miAM (or PM)' as OutputFormat
union all
select @d,convert(varchar,@d,101),101,'mm/dd/yyyy'
union all
select @d,convert(varchar,@d,102),102,'yyyy.mm.dd'
union all
select @d,convert(varchar,@d,103),103,'dd/mm/yyyy'
union all
select @d,convert(varchar,@d,104),104,'dd.mm.yyyy'
union all
select @d,convert(varchar,@d,105),105,'dd-mm-yyyy'
union all
select @d,convert(varchar,@d,106),106,'dd mon yyyy'
union all
select @d,convert(varchar,@d,107),107,'Mon dd, yyyy'
union all
select @d,convert(varchar,@d,108),108,'hh:mm:ss'
union all
select @d,convert(varchar,@d,109),109,'mon dd yyyy hh:mi:ss:mmmAM (or PM)'
union all
select @d,convert(varchar,@d,110),110,'mm-dd-yyyy'
union all
select @d,convert(varchar,@d,111),111,'yyyy/mm/dd'
union all
select @d,convert(varchar,@d,112),112,'yyyymmdd'
union all
select @d,convert(varchar,@d,113),113,'dd mon yyyy hh:mm:ss:mmm(24h)'
union all
select @d,convert(varchar,@d,114),114,'hh:mi:ss:mmm(24h)'
union all
select @d,convert(varchar,@d,120),120,'yyyy-mm-dd hh:mi:ss(24h)'
union all
select @d,convert(varchar,@d,121),121,'yyyy-mm-dd hh:mi:ss.mmm(24h)'
union all
select @d,convert(varchar,@d,126),126,'yyyy-mm-dd Thh:mm:ss:mmm(no spaces)'
union all
select @d as OriginalDate,
convert(varchar,@d,0) as ConvertedDate,
0 as FormatValue,
'mon dd yyyy hh:miAM (or PM)' as OutputFormat
union all
select @d,convert(varchar,@d,1),1,'mm/dd/yy'
union all
select @d,convert(varchar,@d,2),2,'yy.mm.dd'
union all
select @d,convert(varchar,@d,3),3,'dd/mm/yy'
union all
select @d,convert(varchar,@d,4),4,'dd.mm.yy'
union all
select @d,convert(varchar,@d,5),5,'dd-mm-yy'
union all
select @d,convert(varchar,@d,6),6,'dd mon yy'
union all
select @d,convert(varchar,@d,7),7,'Mon dd, yy'
union all
select @d,convert(varchar,@d,8),8,'hh:mm:ss'
union all
select @d,convert(varchar,@d,9),9,'mon dd yyyy hh:mi:ss:mmmAM (or PM)'
union all
select @d,convert(varchar,@d,10),10,'mm-dd-yy'
union all
select @d,convert(varchar,@d,11),11,'yy/mm/dd'
union all
select @d,convert(varchar,@d,12),12,'yymmdd'
union all
select @d,convert(varchar,@d,13),13,'dd mon yyyy hh:mm:ss:mmm(24h)'
union all
select @d,convert(varchar,@d,14),14,'hh:mi:ss:mmm(24h)'
union all
select @d,convert(varchar,@d,20),20,'yyyy-mm-dd hh:mi:ss(24h)'
union all
select @d,convert(varchar,@d,21),21,'yyyy-mm-dd hh:mi:ss.mmm(24h)'

SQL Puzzle #1 - Write the shortest code that produces results as 1 without using any numbers in the select statement.

2013-06-11T12:57:00.001+05:30

Ans:

SELECT DIFFERENCE('Ant', 'Elephant')

SELECT DATALENGTH(' ')

SELECT LEN('x')

SELECT ISNUMERIC($)

SELECT ISNUMERIC('+')

SELECT ISNUMERIC('-')

SELECT ISNUMERIC('.')

SELECT COUNT_BIG(*)

SELECT COUNT(*)

SELECT CEILING(RAND())

SELECT ISDATE(GETDATE())

SELECT MONTH(GETDATE())/MONTH(GETDATE())

SELECT DAY(GETDATE())/DAY(GETDATE())

SELECT YEAR(GETDATE())/YEAR(GETDATE())

SELECT POWER($,$)

SELECT ISNULL(NULL,COUNT(*))

SELECT LEN(NEWID())/LEN(NEWID())

SELECT @@SPID/@@SPID

SELECT UNICODE('A')/ UNICODE('A')

SELECT ISNUMERIC(@@SPID)

SELECT COS('')

SELECT ROW_NUMBER() over (order by A )From (Select 'A' As A )A

select Count('a')

Select SCHEMA_ID()

Select COUNT(Schema_id())

Select Len(Schema_id())

Select CHARINDEX('A','A')

Select LEN(CHARINDEX('A','A'))

Select Count(CHARINDEX('A','A'))

How to delete table data based on the foreign key relationship.

2012-06-20T18:18:00.002+05:30

with Fkeys as (

select distinct

OnTable = OnTable.name
,AgainstTable = AgainstTable.name

from

sysforeignkeys fk

inner join sysobjects onTable
on fk.fkeyid = onTable.id

inner join sysobjects againstTable
on fk.rkeyid = againstTable.id

where 1=1
AND AgainstTable.TYPE = 'U'
AND OnTable.TYPE = 'U'
-- ignore self joins; they cause an infinite recursion
and OnTable.Name <> AgainstTable.Name
)

,MyData as (

select
OnTable = o.name
,AgainstTable = FKeys.againstTable

from

sys.objects o

left join FKeys
on o.name = FKeys.onTable

where 1=1
and o.type = 'U'
and o.name not like 'sys%'
)

,MyRecursion as (

-- base case
select
TableName = OnTable
,Lvl = 1
from
MyData
where 1=1
and AgainstTable is null

-- recursive case
union all select
TableName = OnTable
,Lvl = r.Lvl + 1
from
MyData d
inner join MyRecursion r
on d.AgainstTable = r.TableName
)
select
max(Lvl) As Lvl
,TableName
,strSql = 'delete from [' + tablename + ']'
from
MyRecursion
group by
TableName
order by
1 desc
,2 desc

Query to get the parent level and child level hierarchy data of a particular account.

2012-06-13T12:38:00.002+05:30

Declare @TestTable Table
(
ID int,
Name varchar(50),
ParentID int
)

Insert into @TestTable values
(1,'A',null),
(2,'Aa',1),
(3,'Aaa',1),
(4,'Ab',2),
(5,'Abb',4),
(6,'B',null),
(7,'Ba',6),
(8,'Bbb',7),
(9,'Abc',4),
(10,'Acc',9)

Declare @ID int = 4

;with cte
(
ID,Name,ParentID
)
As
(
Select ID,Name,ParentID
From @TestTable where ID = @ID

Union All

Select a.ID , a.Name,a.ParentID
from cte c inner join @TestTable a on c.ParentID = a.ID
)
,
cte1
(
ID,Name,ParentID
)
As
(
Select ID,Name,ParentID
From cte where ParentID is null

Union All

Select a.ID , a.Name,a.ParentID
from cte1 c inner join @TestTable a on a.ParentID = c.ID
)

Select * from Cte1 Option (MAXRECURSION 32767)

Query to get the child level hierarchy data of a particular account.

2012-06-13T12:32:00.000+05:30

Declare @TestTable Table
(
ID int,
Name varchar(50),
ParentID int
)

Insert into @TestTable values
(1,'A',null),
(2,'Aa',1),
(3,'Aaa',1),
(4,'Ab',2),
(5,'Abb',4),
(6,'B',null),
(7,'Ba',6),
(8,'Bbb',7),
(9,'Abc',4),
(10,'Acc',9)

Declare @ID int = 4

;with cte

(

ID , Name,ParentID,ParentName

)

As

(

Select ID , Name,ParentID,
(select Name from @TestTable where ID = a.ParentID) parentName
From @TestTable a where ID = @ID

Union All

Select m.ID , m.Name,c.ID,c.Name
from cte c inner join @TestTable m on m.ParentID = c.ID

)

Select * from cte Option (MAXRECURSION 32767)

SQL Server Interview Questions

2012-06-13T11:41:00.000+05:30

1. What is indexing?
Types of index. details about Clustered and non clustered index.

2. What is CTE (Common Table Expression)? advantage?
Use of it, Recursive CTE?

3. Difference between Temp table and table variable?

4. What is Transaction?
Describe Isolation levels deeply.

5. What is locking ? Types of locking., locking levels

6. What is service broker?

7. Backup and restoration. Describe backup types?
one scenario to Restore using 3 backup types.

8. Query for delete duplicate records keep original.

9. Query for paging

10. Query for hierarchy structure.

11. varChar vs nvarchar vs Char

12. How to clear identity field.

13. Nth largest salary of an employee.

14. Rows to column without using loop.

15. What is Cursor ? Types of cursor?

16. Performance Tuning? Profiler,Query tuning advisor.

17. What precautions taking for Query optimization?

18. Triggers ? Instead of Trigger? Have u used DDL trigger?

19. Normalization? Denormalization? Types of normal forms?

20. Apply Clause?

21. Pivot and UnPivot?

22. Difference of SQL Server 2005 and 2008 / 2008 and 2008R2

23. DB Mail vs SQL Mail?

24. Database design for Online shopping.

25. ER diagram ? Explain what for ER Diagram? Table Relation ship.

26. Indexed view? What conditions consider when creating indexed view?

27. SSRS Architecture? Tablix? Types of Group? Chart? New Controls in SQL server 2008R2? Have u used map control?

28. SCD ?

29. What is Collation? explain sensitivities.

30. How can i pass multiple value as a parameter to SP. explain table valued type and xml.

31. What is Dirty Read? phantom read,un repeatable read , lost updates

32. Deadlocking?

33. Explain system databases?

34. Replication , logshipping , mirroring

35. what datatype for using image?

36. What is SQL Injection? how to protect sql injection?

37. Error handling?

38. Table scan Index Seek and Index Scan?

39. DBCC Commands?

40. What are Ranking Functions?

41. Date Functions / string functions?

42. What is the FILLFACTOR?

43. Correlated subquery ? Is it good method?

How to stop or disable Recursive Trigger/Nested Trigger in SQL Server.

2012-04-25T15:14:00.001+05:30

A trigger execution may occured another trigger execution or same trigger execution. This trigger called Nested Trigger Or Recursive Trigger. SQL Server allows maximum 32 Level recursion.

Disable Nesting/Recursing Triggers

Method 1:

sp_CONFIGURE 'nested_triggers',0
GO
RECONFIGURE
GO

Method 2:

ALTER DATABASE databasename
SET RECURSIVE_TRIGGERS ON | OFF

Method 3:

Restrict Trigger Nesting to certain level.

Put following script in trigger code. This will stop the trigger recursion after certain levels. In following case it will stop after 5 recursion.

IF ((
SELECT TRIGGER_NESTLEVEL()) > 5 )
RETURN

Comma seperated date splitting without using loop.

2012-04-24T11:04:00.002+05:30

--Comma seperated date splitting without using loop.

--// Table variable declarion
Declare @TableVar Table
(
    ID            int,
    CustomDates varchar(max)
)

--// Data insertion
Insert into @TableVar values(1,'May 01 2012 4:00AM,May 05 2012 4:00AM,Apr 13 2012 10:00AM')

select * from @TableVar

;with cte
(
    ID,CustomDates,strDt,pos
)
As
(
    select ID,
        CustomDates,
        substring(
                    CustomDates,1,case when (charindex(',',CustomDates) -1) = -1 then Len(CustomDates) else charindex(',',CustomDates) -1 end
                ) strDt,charindex(',',CustomDates) as pos

    from @TableVar
    union all
    select s.ID,
        s.CustomDates,

            substring(s.CustomDates,cte.pos+1,
                    case when (charindex(',',substring(s.CustomDates,cte.pos+1,len(s.CustomDates)))-1) = -1 then len(s.CustomDates) else charindex(',',substring(s.CustomDates,cte.pos+1,len(s.CustomDates)))-1 end)

           ,pos + case when charindex(',',substring(s.CustomDates,cte.pos+1,len(s.CustomDates))) = 0 then len(substring(s.CustomDates,cte.pos+1,len(s.CustomDates))) else charindex(',',substring(s.CustomDates,cte.pos+1,len(s.CustomDates))) end as pos

    from @TableVar s inner join cte on cte.ID = s.ID
    where cte.pos < len(s.CustomDates)
)

select strDt from cte OPTION (MAXRECURSION 32767);

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

Another Method:

Declare @TableVar Table

(

ID int,

Data varchar(max)

)

Insert into @TableVar values(1,'May 01 2012 4:00AM,May 05 2012 4:00AM,Apr 13 2012 10:00AM')

DECLARE @XML XML

SELECT @XML = '<Root><Child><Data>'+REPLACE(Data,',','</Data></Child><Child><Data>')+'</Data></Child></Root>' FROM @TableVar

SELECT CAST(DOC.COL.value('Data[1]','NVARCHAR(400)') AS DATE) AS Data

FROM @XML.nodes('/Root/Child') DOC(COL)

Normal query instead of MERGE operation

2012-04-11T15:38:00.001+05:30

/****** Object: StoredProcedure [dbo].[spBlockHr_SAVE]    Script Date: 04/11/2012 15:32:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    Author    : Nikhildas
    Date    : July 01 2011 10:45AM
    Purpose : To Save Block HR value.
    Execute : Exec [spBlockHr_SAVE] <xml Value>,@P_RET_VAL    OUTPUT
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Modified By                On                Remarks
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    <Next Entry>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~*/
ALTER PROCEDURE [dbo].[spBlockHr_SAVE]
(
    @P_XML        XML,
    @P_RET_VAL    INT OUTPUT
)
AS
    SET NOCOUNT ON

    DECLARE @DocHandle        INT

    DECLARE @BlockHr TABLE
    (
        ID            int ,
        SECTOR        nvarchar(255) ,
        Route        nvarchar(255) ,
        CODE        nvarchar(255) ,
        Month01     float ,
        Month02     float ,
        Month03     float ,
        Month04     float ,
        Month05     float ,
        Month06     float ,
        Month07     float ,
        Month08     float ,
        Month09     float ,
        Month10     float ,
        Month11     float ,
        Month12     float ,
        Total        float
    )

    BEGIN TRY
        BEGIN TRANSACTION
        EXEC sp_xml_preparedocument @DocHandle OUTPUT,@P_XML

        INSERT INTO @BlockHr
        SELECT
            ID,
            SECTOR    ,
            Route,
            CODE,
            Month01    ,
            Month02    ,
            Month03 ,
            Month04    ,
            Month05    ,
            Month06    ,
            Month07    ,
            Month08    ,
            Month09    ,
            Month10    ,
            Month11    ,
            Month12    ,
            Total
        FROM    OPENXML(@DocHandle,'/Root',2)
        WITH
        (
            ID            INT,
            SECTOR        NVARCHAR(1000),
            Route        NVARCHAR(1000),
            CODE        nvarchar(255),
            Month01        FLOAT(53),
            Month02        FLOAT(53),
            Month03        FLOAT(53),
            Month04        FLOAT(53),
            Month05        FLOAT(53),
            Month06        FLOAT(53),
            Month07        FLOAT(53),
            Month08        FLOAT(53),
            Month09        FLOAT(53),
            Month10        FLOAT(53),
            Month11        FLOAT(53),
            Month12        FLOAT(53),
            Total        FLOAT(53)

        )

        EXEC sp_xml_removedocument @DocHandle
/*    Commented on 11-Jul-2011 (For Avoid Merge statement)
        MERGE BlockHr AS T
        USING @BlockHr    AS S
        ON S.ID = T.ID
        WHEN MATCHED
        THEN
            UPDATE
                SET SECTOR    = S.SECTOR,
                    Route    = S.Route,
                    CODE    = S.CODE,
                    Month01    = S.Month01,
                    Month02    = S.Month02,
                    Month03 = S.Month03,
                    Month04    = S.Month04,
                    Month05    = S.Month05,
                    Month06    = S.Month06,
                    Month07    = S.Month07,
                    Month08    = S.Month08,
                    Month09    = S.Month09,
                    Month10    = S.Month10,
                    Month11    = S.Month11,
                    Month12    = S.Month12,
                    Total    = S.Total
        WHEN NOT MATCHED BY TARGET
        THEN
            INSERT
            (
                SECTOR    ,
                Route,
                CODE,
                Month01    ,
                Month02    ,
                Month03 ,
                Month04    ,
                Month05    ,
                Month06    ,
                Month07    ,
                Month08    ,
                Month09    ,
                Month10    ,
                Month11    ,
                Month12    ,
                Total
            )
            VALUES
            (
                S.SECTOR    ,
                S.Route        ,
                S.CODE        ,
                S.Month01    ,
                S.Month02    ,
                S.Month03 ,
                S.Month04    ,
                S.Month05    ,
                S.Month06    ,
                S.Month07    ,
                S.Month08    ,
                S.Month09    ,
                S.Month10    ,
                S.Month11    ,
                S.Month12    ,
                S.Total
            );
*/
            --// Updation
            UPDATE T
                SET SECTOR    = S.SECTOR,
                    Route    = S.Route,
                    CODE    = S.CODE,
                    Month01    = S.Month01,
                    Month02    = S.Month02,
                    Month03 = S.Month03,
                    Month04    = S.Month04,
                    Month05    = S.Month05,
                    Month06    = S.Month06,
                    Month07    = S.Month07,
                    Month08    = S.Month08,
                    Month09    = S.Month09,
                    Month10    = S.Month10,
                    Month11    = S.Month11,
                    Month12    = S.Month12,
                    Total    = S.Total
            FROM [BlockHr] T INNER JOIN @BlockHr S
            ON S.ID = T.ID

            --// Insertion
            INSERT INTO [BlockHr]
            SELECT
                S.SECTOR    ,
                S.Route        ,
                S.CODE        ,
                S.Month01    ,
                S.Month02    ,
                S.Month03    ,
                S.Month04    ,
                S.Month05    ,
                S.Month06    ,
                S.Month07    ,
                S.Month08    ,
                S.Month09    ,
                S.Month10    ,
                S.Month11    ,
                S.Month12    ,
                S.Total        ,
                1
            FROM [BlockHr] T RIGHT OUTER JOIN @BlockHr S
            ON S.ID = T.ID
            WHERE (T.ID IS NULL)


            EXEC [SPAD_TRX_LOG_SAVE] 0,'USER-COST CENTER','','MODIFY',1,'',NULL
            SET @P_RET_VAL =   1
    COMMIT TRANSACTION
    SET @P_RET_VAL = 1
    END TRY
    BEGIN CATCH
        SET @P_RET_VAL = -1
        ROLLBACK TRANSACTION
    END CATCH

DateTime Formats in SQL Server

2012-01-09T17:51:00.001+05:30

The conversion of datetime data into many formats is everyday requirement. Here is a table of commonly used formats, to deal with datetime data.

For Eg., Use Convert function as follow

SELECT CONVERT(VARCHAR(20), GETDATE(), 108) As dtm

SQL Query to select a random row from a table

2012-01-09T17:23:00.000+05:30

There are lots of ways to select a random record or row from a database table. Here are some example SQL statements that don't require additional application logic,but each database server requires different SQL syntax.

Select a random row with Microsoft SQL Server:

SELECT TOP 1 column FROM table
ORDER BY NEWID()

Select a random record with Oracle:

SELECT column FROM
( SELECT column FROM table
ORDER BY dbms_random.value )
WHERE rownum = 1

Select a random row with IBM DB2

SELECT column, RAND() as IDX
FROM table
ORDER BY IDX FETCH FIRST 1 ROWS ONLY

Select a random row with MySQL:

SELECT column FROM table
ORDER BY RAND()
LIMIT 1

Select a random row with PostgreSQL:

SELECT column FROM table
ORDER BY RANDOM()
LIMIT 1

Microsoft SQL Server 2008 Sys.Objects Catalog View object types

2012-01-09T14:53:00.003+05:30

For Eg., Select * From Sys.Objects where type = 'P'

It returns the all the user defined stored procedure in the database.

I have listed below the various objects types of sysojects table.

type='AF' 'Aggregate function (CLR)'

type='C' 'CHECK constraint'

type='D' 'DEFAULT (constraint or stand-alone)'

type='F' 'FOREIGN KEY constraint'

type='FN' 'SQL scalar function'

type='FS' 'Assembly (CLR) scalar-function'

type='FT' 'Assembly (CLR) table-valued function'

type='IF' 'SQL inline table-valued function'

type='IT' 'Internal table'

type='P' 'SQL Stored Procedure'

type='PC' 'Assembly (CLR) stored-procedure'

type='PG' 'Plan guide'

type='PK' 'PRIMARY KEY constraint'

type='R' 'Rule (old-style, stand-alone)'

type='RF' 'Replication-filter-procedure'

type='S' 'System base table'

type='SN' 'Synonym'

type='SQ' 'Service queue'

type='TA' 'Assembly (CLR) DML trigger'

type='TF' 'SQL table-valued-function'

type='TR' 'SQL DML trigger'

type='U' 'Table (user-defined)'

type='UQ' 'UNIQUE constraint'

type='V' 'View'

type='X' 'Extended stored procedure'

SSRS Interview Questions

2012-01-09T13:07:00.000+05:30

What are sub reports and how to create them?
A sub report is like any other reports which can be called in main report and can be generate
through main report. Parameters can be passed from main report to sub report and basis of
that report can be generated.

What is the report model project?
Report model project is for creating Adhoc reporting. You can create the adhoc reports
through report builder. Report model project can be created on bids or report server. This
model can have simple view.

What is report server project?
Report Server Project contains the RDL file and it need to be deployed on report server to
view the report files to application and user.
It a solution where we design our reports. You can add it by going into BIDS clicking on
new item and then selecting reports server project. Once the solution is created you can start
creating reports.

What is the report builder?
Report builder is used to create small reports and it a define interface. You can’t change the
report interface in report builder it pre designed. You can just drag columns in the report.
Report builder creates reports on database objects available with report model project.

In which SQL Server version report builder introduced?
Report builder introduced in SQL Server 2005. While creating or deploying report model
project on report server you can get error or it might not get created. For this you need to
check whether the service pack 22 is installed or not.

How to deploy the Report?
Report can be deployed in three ways.
1. Using visual studio: In visual studio you can directly deploy the report through
solution explorer by providing the report server URL in project properties at Target
Server URL. This will deploy entire project or single report as per you selection.
2. Using report server: Can directly go to the report server and deploy the report by
browsing the report from the disk location on server.
3. Creating the utility: SQL server provides the utility using that which can be used to
create a customize utility for your report deployment in bulk.

What is RS.exe utility?
Rs.exe utility is used for deploying the report on report server. It comes with the report
server and can be customize accordingly.

What is the name of reporting services config file and what’s it’s used for?
Reporting service config file is used for report configuration details. It contains the report
format and also the report import types. Report service config reside at ISS.

What are the three different part of RDL file explain them?
In visual studio RDL files has three parts.
1. Data: It contains the dataset on which we write the query. Data set is connected with
data source.
2. Design: In design you can design report. Can create tables and matrix reports. Drag
columns values from source.
3. Preview: to check the preview after the report run.

Which language rdl files made of?
RDL files are written in XML.

What is the chart in report?
Chart reports are for graphical representation. You can get pie charts columns harts and
various other options.
3d charts are also available in reporting services.

What is Data Set in report?
Data set are the set of data which we want to show in report. Data creates on data source.
Data source is the source of data from where we are getting this data i.e. database server and
database name connection string.

What are the different types of data sources in SSRS?
SSRS use different data source. Some of them are listed below.
1. Microsoft SQL Server
2. OLEDB
3. Oracle
4. ODBC
5. SQL Server Analysis Service
6. Report Server Model
7. SAP Net weaver BI
8. Hyperion
9. Teradata
10. XML

What is the web service used for reporting services?
Reporting Service Web Service used in SSRS. By accessing this web service you can access all
report server component and also get the report deployed on report server.

How to add the custom code in Report?
To add the custom codes in report go to report tab on top then properties and there you will
find the options for custom code.

How to find the SQL Server version?

2012-01-09T12:56:00.002+05:30

The Microsoft SQL Server T-SQL system variable @@version contains the version signature of the server and also SERVERPROPERTY provides specific information.

USE master;
GO

Select @@Version As SQLServerVersion

GO

SELECT

'Microsoft SQL Server ' +

convert(varchar, SERVERPROPERTY('ProductVersion') ) + ' -- ' +

convert(varchar, SERVERPROPERTY('ProductLevel') ) + ' -- ' +

convert(varchar, SERVERPROPERTY('Edition') ) As SQLServerVersion;

How to import and export image/file column data in SQL Server?

2012-01-09T12:06:00.000+05:30

Several methods are available for importing & exporting images / photos / videos / documents / BLOBs.
Here i have used "OPENROWSET" for importing image to database and BCP command (Bulk copy) for exporting. We can also use BCP command for import images/documents.

VARBINARY(MAX) Datatype using for store images that consisting approximately 2 GB.

USE AdventureWorks;

GO

-- Create image warehouse for importing image into sql server database

CREATE TABLE dbo.PhotoLibrary
(

PhotoLibraryID       INT    IDENTITY ( 1 , 1 )    PRIMARY KEY,

ImageName            VARCHAR(100),

Photo               VARBINARY(MAX)
)

GO

-- SQL Server import image - storing images to sql server database

INSERT INTO dbo.PhotoLibrary

           ([ImageName])

VALUES     ('nature.JPG')

GO

UPDATE dbo.PhotoLibrary

SET    Photo = (SELECT *

                FROM   OPENROWSET(BULK 'D:\image\photo\nature.JPG',

                       SINGLE_BLOB) AS x)

WHERE [ImageName] = 'nature.JPG'

GO

-- Check table population

SELECT *

FROM   dbo.PhotoLibrary

GO

-- SQL Server export image - Using BCP Command (Bulk Copy)

DECLARE @Command NVARCHAR(4000)

-- Keep the command on ONE LINE - SINGLE LINE!!!

SET @Command = 'bcp "SELECT Photo FROM AdventureWorks.dbo.PhotoLibrary" queryout "D:\image\photo\natureNew.jpg" -T -n -S <Server name\Instance name>'

-- PRINT @Command -- debugging

EXEC xp_cmdshell @Command

GO

Len() and DataLength() function in SQL Server

2012-01-03T19:06:00.001+05:30

Let me show the difference of the Len() and DataLength() function in sql server through a simple example.

Declare @TestTable Table
(
    ID        int,
    Name    varchar(50)
)

Insert Into @TestTable
values(1,'SQL Server')
    ,(2,'SQL Server ')
    ,(3,' SQL Server')
    ,(4,' SQL Server ')

Select Len(Name) As LengthOfString From @TestTable
Select DataLength(Name) As DataLengthOfString From @TestTable

Regarding the above sample query, the Len() function just avoiding the trailing space from the column data and returns the length of the string.But DataLength function returns the full length of column data , it won't avoid the trailing space.

How to get indexes on a database

2011-12-29T15:46:00.002+05:30

The following query returns the indexes used on tables in a database. I think it's very useful query for all the sequel server guys.We can use the query result of 'IndexRebuild' column for rebuild index.

SELECT
    OBJECT_SCHEMA_NAME(OBJECT_ID) As [Schema],
    OBJECT_NAME(OBJECT_ID) [Table],
    Name,
    'ALTER INDEX ' + name + ' ON '+object_schema_name(OBJECT_ID)+'.'+OBJECT_NAME(OBJECT_ID)+' REBUILD;' As IndexRebuild
    ,*
FROM    sys.indexes
WHERE   TYPE > 0
ORDER BY
OBJECT_SCHEMA_NAME(OBJECT_ID),
OBJECT_NAME(OBJECT_ID)

The following system stored procedure returns the index information of a single table.

EXEC sp_helpindex 'TableName'