I work on SQL server 2012 I face issue I can't display Part Number with Mask related for family without using like because it is more slower running
when data is big
so can i do result below without using like
CREATE TABLE [dbo].[Partspc](
[PortionKey] [nvarchar](255) NULL,
[GroupID] [float] NULL,
[familyid] [float] NULL
) ON [PRIMARY]
INSERT [dbo].[Partspc] ([PortionKey], [GroupID], [familyid]) VALUES (N'T496', 1, 7524090)
INSERT [dbo].[Partspc] ([PortionKey], [GroupID], [familyid]) VALUES (N'X', 2, 7524090)
INSERT [dbo].[Partspc] ([PortionKey], [GroupID], [familyid]) VALUES (N'B', 2, 7524090)
INSERT [dbo].[Partspc] ([PortionKey], [GroupID], [familyid]) VALUES (N'754', 3, 7524090)
INSERT [dbo].[Partspc] ([PortionKey], [GroupID], [familyid]) VALUES (N'755', 3, 7524090)
INSERT [dbo].[Partspc] ([PortionKey], [GroupID], [familyid]) VALUES (N'K', 4, 7524090)
INSERT [dbo].[Partspc] ([PortionKey], [GroupID], [familyid]) VALUES (N'M', 4, 7524090)
INSERT [dbo].[Partspc] ([PortionKey], [GroupID], [familyid]) VALUES (N'blank', 4, 7524090)
CREATE TABLE [dbo].[Masksspc](
[PortionKey] [nvarchar](255) NULL,
[GroupID] [float] NULL,
[familyid] [float] NULL
) ON [PRIMARY]
INSERT [dbo].[Masksspc] ([PortionKey], [GroupID], [familyid]) VALUES (N'T496', 1, 7524090)
INSERT [dbo].[Masksspc] ([PortionKey], [GroupID], [familyid]) VALUES (N'X', 2, 7524090)
INSERT [dbo].[Masksspc] ([PortionKey], [GroupID], [familyid]) VALUES (N'B', 2, 7524090)
INSERT [dbo].[Masksspc] ([PortionKey], [GroupID], [familyid]) VALUES (N'754', 3, 7524090)
INSERT [dbo].[Masksspc] ([PortionKey], [GroupID], [familyid]) VALUES (N'755', 3, 7524090)
INSERT [dbo].[Masksspc] ([PortionKey], [GroupID], [familyid]) VALUES (N'_', 4, 7524090)
INSERT [dbo].[Masksspc] ([PortionKey], [GroupID], [familyid]) VALUES ('blank', 4, 7524090)
DECLARE @GetFinalResultParts as table
( [familyid] [int] not NULL INDEX IXkfamily NONCLUSTERED,
[GroupID] [int] not NULL INDEX IXkgroup NONCLUSTERED,
[PartNumber] [nvarchar](200) NOT NULL INDEX IXkpart NONCLUSTERED
)
insert into @GetFinalResultParts
SELECT distinct r.familyid,r.GroupID,IIF(r.PortionKey='blank','',r.PortionKey)
FROM dbo.Partspc r with(nolock)
;WITH cte AS (
SELECT t1.familyid,t1.GroupID,cast((t1.PartNumber) as nvarchar(200)) PartNumber
FROM @GetFinalResultParts t1
WHERE t1.GroupID = 1
UNION ALL
SELECT t.familyid,t.GroupID,cast((s.PartNumber+ t.PartNumber) as nvarchar(200)) PartNumber
FROM @GetFinalResultParts t INNER JOIN
cte s ON t.GroupID = s.GroupID + 1
)
SELECT familyid,PartNumber,GroupID
into getfinaldatapc
from cte
where GroupID =(select max(GroupID) from dbo.Partspc with(nolock))
group by familyid,PartNumber,GroupID
DECLARE @GetFinalMasks as table
( [familyid] [int] not NULL INDEX IXkfamily NONCLUSTERED,
[GroupID] [int] not NULL INDEX IXkgroup NONCLUSTERED,
[MaskNumber] [nvarchar](200) NOT NULL INDEX IXkmask NONCLUSTERED)
insert into @GetFinalMasks
SELECT distinct m.familyid,m.GroupID,IIF(m.PortionKey='blank','',m.PortionKey)
FROM dbo.Masksspc m with(nolock)
;WITH cte AS (
SELECT t1.familyid,t1.GroupID,cast((t1.MaskNumber) as nvarchar(200)) MaskNumber
FROM @GetFinalMasks t1
WHERE t1.GroupID = 1
UNION ALL
SELECT t.familyid,t.GroupID,cast((s.MaskNumber+t.MaskNumber) as nvarchar(200)) MaskNumber
FROM @GetFinalMasks t INNER JOIN
cte s ON t.GroupID = s.GroupID + 1
)
SELECT familyid,MaskNumber
into getfinaldatapcmask
from cte
where GroupID =(select max(GroupID) from dbo.Masksspc with(nolock))
group by familyid,MaskNumber
SELECT * FROM getfinaldatapc
select * from getfinaldatapcmask
What I have tried:
SELECT * FROM getfinaldatapc g
JOIN getfinaldatapcmask gm
ON g.familyid=gm.familyid AND (g.PartNumber=gm.MaskNumber OR
(g.PartNumber LIKE gm.MaskNumber+'%' AND LEN(g.PartNumber)=LEN(gm.MaskNumber)))