Database

SELECT RIGHT('0000000' + @STR, CASE WHEN LEN(CAST(CAST(@STR AS INT) AS NVARCHAR(MAX))) < 7 THEN 7 ELSE LEN(CAST(CAST(@STR AS INT) AS NVARCHAR(MAX))) END )

,RIGHT('0000000' + dbo.SD.PRODUCT, CASE WHEN LEN(CAST(CAST(dbo.SD.PRODUCT AS INT) AS NVARCHAR(MAX))) < 7 THEN
        7 ELSE LEN(CAST(CAST(dbo.SD.PRODUCT AS INT) AS NVARCHAR(MAX))) END)

DECLARE @INPUT NVARCHAR(MAX) = 'your product code with or without leading zeros goes here'
DECLARE @TMP_INPUT INT = CAST(@INPUT AS INT)

SELECT
 CASE
  WHEN LEN(CAST(@TMP_INPUT AS NVARCHAR)) < 7 THEN SUBSTRING(@INPUT, 1, 7)
  ELSE CAST(@TMP_INPUT AS NVARCHAR)
 END

studentno    subjectid    mark
20              1          50
20              2          60
20              3          70
20              4          60
21              1          70
...            ...        ...

SELECT s.subjectname, m.mark FROM marks m inner join subjects s
on m.subjectid = s.subjectid WHERE studentno = 20

Table ExamType
    ExamTypeId           (Primary Key)
    ExamTypeName         (Exam I / Exam II / Exam III)
 
Table Subject
    SubjectId            (Primary Key)
    SubjectName          (English / Geography / ...)

Table Student
    StudentId            (Primary Key)
    StudentName

Table Exam
    ExamId               (Primary Key)
    ExamTypeId           (Foreign Key referencing ExamType.ExamTypeId)
    SubjectId            (Foreign Key referencing Subject.SubjectId)
    ExamTakenDate

Table Mark
    ExamId               (Foreign Key referencing Exam.ExamId)       \_ Primary Key
    StudentId            (Foreign Key referencing Student.StudentId) /
    Score

SELECT St.StudentName,
       Sj.SubjectName,
       Et.ExamTypeName,
       Ex.ExamTakenDate,
       Mk.Score
FROM Student AS St
JOIN Mark AS Mk ON St.StudentId = Mk.StudentId
JOIN Exam AS Ex ON Mk.ExamId = Ex.ExamId
JOIN ExamType AS Et ON Ex.ExamTypeId = Et.ExamTypeId
JOIN Subject AS Sj ON Ex.SubjectId = Sj.SubjectId
WHERE
     StudentId = ...   /* however you want to narrow your query result */
     ExamId = ...
     SubjectId = ...

Ludwing RS wrote:

I have a query and it takes too much time to fetch

Bastard Programmer from Hell
If you can't read my code, try converting it here[^]

create index myTable_num_fnix
on myTable (substr(NUM,0,3),substr(NUM,3,15));

delgaillo wrote:

Yes, I have tried with sql compare of redgate and the problem is the same