User Defined Table Type

--How to use USER DEFINED TABLE TYPE

--Create table type

CREATE TYPE [dbo].[udtId] AS TABLE(

       [Id] [int] NOT NULL,

       PRIMARY KEY CLUSTERED

(

       [Id] ASC

)

--Create table Customer and insert data

CREATE TABLE [dbo].[customer](

       [ID] [int] NOT NULL,

       [Name] [varchar](50) NULL,

       [Add] [nvarchar](50) NULL,

       [b] [bit] NULL

) ON [PRIMARY]

GO

INSERT INTO [dbo].[customer]

           ([ID]

           ,[Name]

           ,[Add]

           ,[b])

     VALUES

           (1,'HYD','UK',1),

           (2,'Wembley','London',1),

           (3,'HYT','USA',0),

           (43,'Cardiff','Wales',0)

GO

--Create Sproc using table datatype

CREATE PROC tblCustomer

@PlanIds udtId

AS

BEGIN

       Update dbo.customer SET Name = 'Pram' WHERE Id in (SELECT id FROM @PlanIds)

END

--Now Execute Store Proc

DECLARE @PlanIds udtId

INSERT INTO @PlanIds SELECT Id FROM customer

       EXEC tblCustomer @PlanIds = @PlanIds

How to convert rows in to column

--How to get ids in one line usign , Delimeted 

DECLARE @planids TABLE (Id INT)

INSERT INTO @planids VALUES (123),(234),(343),(543),(45345),(543),(4534)

SELECT * FROM @planids

DECLARE @plans varchar(max)

SET @plans = ''

SELECT  @plans = @plans+ cast(Id AS varchar(12)) +',  '

FROM @planids 

--where ParentPlanId in (373289)

SELECT @plans AS PlanList

How to find number of rows in each partition

How to get partition Information including no of rows in each partition.

SELECT

     SCHEMA_NAME(t.schema_id) AS [Schema]

    ,OBJECT_NAME(i.object_id) AS [ObjectName]

    ,p.partition_number AS [Partition_Number]

    ,fg.name AS [Name_Of_FileGroup]

    ,rows AS 'Number_Of_Rows'

    ,au.total_pages AS 'Number_Of_Pages'

    ,CASE boundary_value_on_right

        WHEN 1 THEN '<'

        ELSE '<='

     END AS 'Compare'

    ,value AS 'Compare_Value'

    ,p.data_compression_desc AS 'Data'

    ,p.partition_id AS [Partition ID]

FROM sys.partitions p

    JOIN sys.indexes i ON p.object_id = i.object_id AND p.index_id = i.index_id

    JOIN sys.partition_schemes ps ON ps.data_space_id = i.data_space_id

    JOIN sys.partition_functions f ON f.function_id = ps.function_id

    LEFT JOIN sys.partition_range_values rv ON f.function_id = rv.function_id AND p.partition_number = rv.boundary_id

    JOIN sys.destination_data_spaces dds ON dds.partition_scheme_id = ps.data_space_id AND dds.destination_id = p.partition_number

    JOIN sys.filegroups fg ON dds.data_space_id = fg.data_space_id

    JOIN (SELECT container_id, sum(total_pages) as total_pages

            FROM sys.allocation_units

            GROUP BY container_id) AS au ON au.container_id = p.partition_id

    JOIN sys.tables t ON p.object_id = t.object_id

WHERE i.index_id < 2

ORDER BY 5 Desc

GO

How we can perform a limited character’s search using LIKE?

How do I perform a limited character’s search using LIKE?

Declare @T TABLE (Id Int, Name Varchar(100))

Insert Into @T Values (1,'abc1'),(2,'def2'), (3,'ghi'), (4,'zyx'),(5,'123')

--Select * from @T

--WE WILL GET RESULTS FOR ONLY a TO b

Select * from @T

where Name like '%[a-b]%' COLLATE Latin1_General_CS_AS

--WE WILL GET RESULTS FOR ONLY d TO f

Select * from @T

where Name like '%[d-f]%' COLLATE Latin1_General_CS_AS

--WE WILL GET RESULTS FOR ONLY a TO z

Select * from @T

where Name like '%[a-z]%' COLLATE Latin1_General_CS_AS

--WE WILL GET RESULTS FOR BOTH COLUMNS WITH NUMERIC AND ALPHABETS

Select * from @T

where Name like '%[a-b0-9]%' COLLATE Latin1_General_CS_AS

How To Check Time Difference in SQL Server

-- How To Check Time Difference in SQL Server

 DECLARE @StartTime DateTime = DateAdd(Mi, -15, GetDate())

 Select @StartTime  StartTime;

 Declare @EndTime DateTime = GetDate()

 Select @EndTime  EndTime

 -- Now See the Difference betwen StartTime and EndTime

Select Convert(Varchar(15), DateAdd(Ms, DateDiff(Ms, @StartTime, @EndTime),0),114) as TimeDifference

--WAITFOR DELAY '00:00:10'

Merge Statement E.G

MERGE: Performs insert, update, or delete operations on a target table based on the results of a join with a source table. For example, you can synchronize two tables by inserting, updating, or deleting rows in one table based on differences found in the other table.

Performance Tip: The conditional behavior described for the MERGE statement works best when the two tables have a complex mixture of matching characteristics. For example, inserting a row if it does not exist, or updating the row if it does match. When simply updating one table based on the rows of another table, improved performance and scalability can be achieved with basic INSERT, UPDATE, and DELETE statements. For example:

-- MERGE STATEMENT E.G

DECLARE @sCUSTOMER TABLE (ID INT, NAME VARCHAR(50), ADDD VARCHAR(50))

DECLARE @dCUSTOMER TABLE (ID INT, NAME VARCHAR(50), ADDD VARCHAR(50))

INSERT INTO @sCUSTOMER VALUES

                              (1, 'KRIS','HYDERABAD'),

                              (2,'KUMAR','HAYATHNAGAR'),

                              (3,'SAM','LONDON'),

                              (4,'TOM','NEWYORK')

 INSERT INTO @dCUSTOMER VALUES

                              (100,'MOON','PLANET')

 SELECT * FROM @sCUSTOMER -- 4 RECORDS AVAILABLE

 SELECT * FROM @dCUSTOMER -- 1 RECORDS

 MERGE @dCUSTOMER DW

 USING @sCUSTOMER DC

      ON DW.ID = DC.ID

      WHEN MATCHED THEN UPDATE

            SET ID = DC.ID,

                   NAME = DC.NAME,

                   ADDD = DC.ADDD

      WHEN NOT MATCHED THEN

                  INSERT  (ID, NAME, ADDD)

                  VALUES (DC.ID, DC.NAME, DC.ADDD)

      WHEN NOT MATCHED BY SOURCE

            THEN DELETE ;

  

 SELECT * FROM @sCUSTOMER -- 4 RECORDS AVAILABLE

 SELECT * FROM @dCUSTOMER -- 4  RECORDS AVAILABLE  BUT PLANET RECORD WILL BE  DELETED BCZ ID NOT MATCHING WITH SOURCE

How to check the last restoration done on database.

If you want to know the last restore done on your database run below query.

WITH LastRestores AS

(

SELECT

    DatabaseName = [d].[name] ,

    [d].[create_date] ,

    [d].[compatibility_level] ,

    [d].[collation_name] ,

    r.*,

    RowNum = ROW_NUMBER() OVER (PARTITION BY d.Name ORDER BY r.[restore_date] DESC)

FROM master.sys.databases d

LEFT OUTER JOIN msdb.dbo.[restorehistory] r ON r.[destination_database_name] = d.Name

)

SELECT *

FROM [LastRestores]

WHERE [RowNum] = 1

How to check backup or restore time completion

We may have different way to check completion time Backup/Restore databases using DMV's

I have simple query here.

SELECT

              percent_complete  AS [Completed (%)]

              ,command  AS [Activity]

              ,start_time  AS [ActivityStartTime]

              ,d.name  AS [DatabaseName]

              ,DATEADD(MS,estimated_completion_time ,GETDATE()) AS [TotalTimeRemaining]

              ,(estimated_completion_time /1000/60) AS [RemainingTimeInMins]

              ,(estimated_completion_time /1000) AS [RemainingTimeInSecs]

FROM sys.dm_exec_requests r --ER

INNER JOIN sys.databases d  --sd

ON r.database_id  = d.database_id

WHERE command  LIKE '%RESTORE%'

OR command  LIKE '%BACKUP%'

AND estimated_completion_time  > 0

GO

How to check disabled Index in database

--How to check disabled index in Database

SELECT

    sys.objects.name,

    sys.indexes.name

FROM sys.indexes

    inner join sys.objects ON sys.objects.object_id = sys.indexes.object_id

WHERE sys.indexes.is_disabled = 1

ORDER BY sys.objects.name, sys.indexes.name

How to split string value into Integer

How to split string value into Integer

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

-- how to split string value in to int using delimmiter (you can do these by creating TVF-function.

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

CREATE FUNCTION [dbo].[SplitStringReturnInt]

(      @string varchar(max)

       ,@delimiter varchar(10)

)

RETURNS

@return TABLE (id int)

AS

BEGIN

       SET @string = RTRIM(LTRIM(@delimiter)) + RTRIM(LTRIM(@string)) + RTRIM(LTRIM(@delimiter))

       DECLARE @x bit = 1

      

       ;WITH E1(N)

       AS

       (      SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1

              UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1

       ), E2(N)

       AS

       (      SELECT 1

              FROM E1 a, E1 b

       ), E4(N)

       AS

       (      SELECT ROW_NUMBER() OVER(ORDER BY @x)

              FROM E2 a, E2 b, E2 c

       )

       INSERT INTO @return

    SELECT    CASE ISNUMERIC(SUBSTRING(@string, N + 1, CHARINDEX(@delimiter, @string, N + 1) - N -1))

                           WHEN 1 THEN SUBSTRING(@string, N + 1, CHARINDEX(@delimiter, @string, N + 1) - N -1)

                     END

    FROM E4

    WHERE N <= LEN(@string) - 1

              AND SUBSTRING(@string, N, 1) = @delimiter

      

       RETURN

END

-- Now Lets Test these function.

Declare @stre varchar(max) = ('1234567891,234234,23424,2342,324234,2,4234,23423,243634,345435,53453')

SELECT * FROM [dbo].[SplitStringReturnInt](@stre,',')

Declare @stre varchar(max) = ('5345.53453.3453534.345345.3453453.435345.3')

SELECT * FROM [dbo].[SplitStringReturnInt](@stre,'.')

SQL SERVER CURSORS

CURSORS : - In Simple words, Cursor is a database object used by applications to manipulate data in a set on a row-by-row basis,

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

-- SINGLE  VARIABLE AS INPUT TO EXEC PROCEDURE which takes only one variable as input

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

DECLARE @CLIENTKEY INT           

DECLARE CLIENTKEYCURSOR CURSOR

              FOR SELECT ID FROM customer 

OPEN CLIENTKEYCURSOR                                    -- open cursor

                                                       -- DO SOMETHING USEFUL

       FETCH NEXT FROM CLIENTKEYCURSOR                 -- fetch record

       INTO @CLIENTKEY

       WHILE @@FETCH_STATUS = 0

              BEGIN 

                     FETCH NEXT FROM CLIENTKEYCURSOR   -- loop through every record

                           INTO @CLIENTKEY

                           EXEC dbo.uspUpdateClientDate @CLIENTKEY

                           --SELECT   @CLIENTKEY

               END         

CLOSE CLIENTKEYCURSOR                                  -- close cursor

DEALLOCATE CLIENTKEYCURSOR                             -- deallocate cursor

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

---- MORE THAN ONE VARIABLE AS INPUT TO EXEC PROCEDURE which takes three variable as input

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

DECLARE @ID INT,@NAME VARCHAR(100), @AD VARCHAR(100)

DECLARE  NAMECURSOR CURSOR

               FOR SELECT ID, NAME, [ADD] FROM CUSTOMER

OPEN NAMECURSOR

              FETCH NEXT FROM NAMECURSOR

                     INTO @ID, @NAME, @AD

       WHILE @@FETCH_STATUS = 0

       BEGIN

              FETCH NEXT FROM NAMECURSOR

                     INTO @ID, @NAME, @AD

           EXEC [dbo].[UspInsertCustomerDetails] @ID, @NAME, @AD

       END

CLOSE NAMECURSOR

DEALLOCATE NAMECURSOR