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